 Welcome to Introduction to PostgreSQL ColumnStore Indexes. I'm joined by speaker Sebastian Dressler, Solutions Architect at Swarm64, and Andy Ellicott, head of Product and Marketing at Swarm64, who's going to facilitate asking and answering questions. In today's talk, we're going to cover how ColumnStore Indexes work, ColumnStore Index syntax and PostgreSQL, ColumnStore Index capabilities and best practices, table coverage, index updates, compression levels, ColumnStore Index replication, crash safety, and then performance demo and benchmarks. My name is Lindsay Hooper. I'm one of the PostgreSQL Conference organizers, and I'll be your moderator for this webinar. Let me give you a little background about your speakers. Sebastian loves to play with different technologies and bring them together to build creative solutions. In his role as Solutions Architect at Swarm64, he helps customers to performance optimize their PostgreSQL and understand how to fit Swarm64 DA into their environments. With that, I'm going to hand it off to Sebastian. Take it away. Thanks, Lindsay. This is Andy, and just a quick introduction to Swarm64 for those of you who may not be familiar with us. Thank you, everybody, for joining us today. Swarm64 company in Berlin and Boston, and we're in the business of developing extensions for Postgres that make it faster. We're inventors of things that primarily make querying happen a lot faster, and Sebastian's going to talk about one of those things today. If you look at the next slide, please. As Lindsay mentioned, the agenda is to really go through the columnar storage in Swarm64 extension, which we've recently with our newest version which is in beta, and beta testers being called if anybody is interested, is that we've re-implemented our columnar storage, which used to be implemented as a foreign table through the FDW foreign data wrapper in Postgres. We've migrated that to a column store index, which is a lot easier to use, and a lot less overhead and actual fact, quite a bit faster than the FDW foreign table implementation we used to have. But column store indexing along with greater parallelization and query planning and a bunch of other things that we do to which we extend Postgres all add up to in TPCH benchmarks like you see here, about 20x faster overall query performance for Postgres, and this is a terabyte scale with some queries running around 5x faster and others running 60x faster. So your mileage might vary, but at this point, next slide, I will turn it over to Sebastian. Sorry, right after this, but if you can with faster query engine and query performance in Postgres, we see people being able to migrate a lot more of those expensive Oracle and SQL Server projects to free open source Postgres, and especially projects that leverage the column store indexing and SQL Server, and as you'll see, our implementations quite similar to that, or the in-memory column store feature in Oracle. Again, it's very similar to that. Also very helpful for those of you who are used Postgres to support not only updates and transactions, but also querying and reporting workload, especially with lots of concurrent users. Swarm will help Postgres deal with that kind of workload a lot more predictably and a lot less expensively. So now, I think with that, I will turn it over to Sebastian and take it away. Thanks, Andy. Hello, everybody. This is Sebastian from Swarm64, and thanks to the nice introduction, everybody of you knows now what we're talking about, and I will start with some very high-level overview of storage and relational database management systems. Of course, not in a very scientific way. We will be very brief and high-level, and from a very, let's say, naive point of view, you could basically split the two into two separate distinct areas, and the methodology here would be that we can distinguish between Rostore-oriented storage, which is very typical for OTP, because you just can pinpoint where your data is, and then you go exactly to this location, you update your data, or maybe you delete the record, or you insert something, whatever, and this is very straightforward as an operation, because as said, you know exactly where it is. Versus, if you go more into the direction of analytical queries, you're more talking about column store-oriented storage. Why is that? Because you typically fetch so much more data, you typically are not going to look into all columns at once, but only into a subset of columns, but still the amount of data or the range of data you want to fetch typically spends more than only a fraction of your table, and therefore this kind of storage is much more appealing to this kind of operation. Now, having these two extremes basically, we could think about the following, and the idea basically is that you take what we have with Postgres, like our beloved workhorse in terms of databases, and everybody knows that Postgres is very strong on the transactional side, but at the same time, people start to use it also for analytics more and more. Here, the interesting point is that Postgres gives you basically all the tools up to a certain scale with what it has in terms of indexes. You know basically, you have the B3 indexes, which is like the just go-to index for almost every single Postgres. You have range indexes and other specialized types of indexes like GIN and GIST if you do more like on the text search. However, at some scale, they will just basically not 100 percent work or not 100 percent give you the performance any longer you're looking for, and that scale is then typically reached the moment your data does not fit into RAM any longer. At the same time, this is then also the point where some like to switch over for the analytical queries to something which is more column store oriented, and now the idea is, and this is obviously not a new concept, so it's like we dusted it off a bit, gave it our own touch basically, but the idea is that you give your Postgres table something that looks like columnar without changing the Postgres table, and that's the main thing. We have our Postgres table, this is like this row oriented storage, which we all know, where we know how it works, and we know it's upsides and downsides, and we just take something that looks columnar, and we're not really sure what it is now, but we just put the two together, and what you get out of this is like a column store index, and this has its origins as the most prominent example is SQL Server, and the idea here basically is you have your data as it is, and you just put another index on top, and this is a very known concept because we are all using indexes day to day, and we all know how they work, we know their benefits, and therefore this is not such a new concept, but the difference here is that the access path we provide together with this form of index gives you a tremendous speed boost. Now, in case you're wondering why I left out this very last row here, in this example, and this is something you always have with indexes, they don't necessarily are 100% up to date, so because your database is not something that is read-only or something that is write-only, you typically have a mix, so you get data in somehow, you have to get data out of it somehow, and we are basically focusing more on this question of how do we get data out without neglecting that you also have to get data in on the other hand, and therefore your index has to be kept up to date, and you all know in Postgres there's the auto vacuum worker, and this as a maintenance demon basically is responsible for keeping your index up to date, and this is also a distinction from our previous product as Andy mentioned, where we used the foreign data wrapper interface, which made it so much harder for you to keep your data up to date for your analytics because you had for instance to use a transformation, or you had to directly ingest into these tables, but then when you did so, you lost other advantages of Postgres, like for instance data safety guarantees in terms of transactional safety and uniqueness constraints and what have you. So this is basically now the combination of both things, and yeah, why would you care about an index actually? Because the solution is pretty minimal, so all you have to do as a user is you call create index. You don't have to create different table types. You don't have to think much about it actually. I'm going to show later you an example of how we thought about this, like that you basically don't have to think too much about how do I create this thing now. If some of you remember these foreign data wrapper tables we used, you had to think about, okay, which columns I want to optimize for, and this was only possible three of them at max, and they had certain downsides and combinations and so on and so forth. So this is way easier here. Of course, the columns that are created are compressed. This is a big advantage of columnar storage as well, because the values inside these columns are typically very close to each other and very close values in mean basically, you can compress them very well, and that's something we use as an advantage, and of course, smaller data gives you faster reads, and also you get some sort of decoupling mechanism here, because in our implementation, this index is reading directly from the disk, and this helps you to free up resources like RAM, while at the same time you keep a single source of truth. So your data updates, a bit later your index will be updated as well, and you just don't have to care about this because Postgres is doing it for you. We also have some downsides, obviously. Most notably, your write and read is going to be a bit more expensive, meaning you have to have a pretty good IO subsystem, especially in this case, where we read and write directly from the disks, and of course the compression plays a role because it eats your CPU to some degree, but depending on the compression algorithm you choose, it's not that much impact, so there is some overhead here, and also if you decide to scan your table completely, meaning you have to use all the columns, then what could happen is that the IO advantage reduces obviously because it's like you get the most benefit when you read only a fraction of the columns, you get less benefit of the solution if you read more columns, it's just like very simple equation here, and as I already mentioned, it has to be maintained, meaning there is always a window where the index is not up to date. Now that does not mean that you're not getting your latest data because Postgres guarantees you data consistency for your query, it just means on the part that is basically missing, you have to go to your row-based storage again and fetch it from there, which then could cause some slower disk access depending on how you configured your auto vacuum. Now, if you're actively following the community, and I'm sure you do, you will notice that Postgres has something like this already, and there are basically two very prominent implementations. First one is Zstore. Zstore actually uses the storage engine that was provided in Postgres, starting with 12, I believe, where you then can define your own storage engine and simply basically plug it into Postgres, and then Postgres is not using its own mechanisms, but it's using a different mechanism. However, the problem we see here is you have first in a different table format, which is transparent to the user, of course, but the implementation has to guarantee you that you get all the benefits Postgres gives you as well, meaning you have to guarantee you get again the primary key and unique key constraints and also your foreign key constraints if you ever use them, and other data consistency measures, and this has to be re-implemented essentially in the storage itself, and therefore it's basically not very lean to do so, whereas reusing a interface that you have where Postgres takes over for your storage is typically the better choice, and also if you introduce a completely new table format, then you basically lose the advantages Postgres gives you, and as we saw in basically almost the first slide, if you opt for column storage, then typically you have a disadvantage when it comes to transactional operations because of this different storage format in the most straightforward implementation, you would, for instance, compress your complete column, and when you have, let's say, an update, you need to decompress the column, and even multiple columns depending on your update, and then update the data, compress it again, store it again, and this can be harmful for your transactions because it slows them down. Now, the other implementation is Fujitsu VCI. What we see here is that this is only available for the Fujitsu Enterprise Postgres, which is not there for your open source Postgres, like our implementation is, and also if you want to use Enterprise DB Postgres, then this is also not really available. However, this implementation is very close to what we're presenting, so it's a more or less hybrid implementation, also basically builds an index on top, however, it's in memory, meaning the advantage you potentially gain by reading directly from your disks is gone because you have to consume additional memory for it, and in the worst case, you have to add more memory into your system, which is then, again, cause driver eventually, and making other operations potentially slower if you have less. Now, in the nutshell, basically, what the Column Store Index from Swarm provides you is first you get direct IO access, meaning you read directly from the disks, it saves you RAM for what I would call the important things. So your joins, sort operations, group buys, aggregates, whatever, you simply have more RAM for these operations, and therefore less risk to need to go to the disk for them because those are slow on disk, whereas reading something directly in a streaming fashion from the disk is really fast these days. You can get streaming replication. This was, by the way, not possible with the foreign data wrapper interface because we would have to have implemented and never used the wall in Postgres, so there was simply no streaming replication. Now it is as possible because we are not leaving Postgres in the end. You get still your Postgres guarantees for consistency and your whole ecosystem works with all changes. Again, if for instance you set store, then you have to define the table storage when you create your tables. Now when you use the Column Store Index, you simply create an index. Now this is also some change to your data definition language, but still the tools are working with everything. So if you ever need to do a backup of your system, you just go ahead, do a backup, and then you restore it and the index is going to be created later because it's part of your data definition language and it can't get any easier. Now let's have a quick look into the product we are offering, which we call Swarm640A, the A is for data accelerator. And in a nutshell, what we want to achieve is it's an accelerated Postgres, but not only for OLAP, it also works for hybrid cases. So it's not going to give you a tremendous boost on your transactions. However, in certain cases, you will see some speed gains. And what we provide as Andy said, is extension. You can load this into your Postgres, you can try it out. If you don't like it, you just throw it out again. So there is no vendor login and this works for the Community Postgres and also for Enterprise DB Postgres at the moment. And if you load a plugin, what it gives you is you get better query planning, faster query execution and the column store indexing we are just talking about. And it also enables you, as I said, certain speed ups for your OLTP. So let's assume you have a transactional workload going on there, but you want to put analytics on it. Then typically what you are going to do is you look at your analytical queries and then you add indexes on top. Now, this can be harmful for your transactions because you also have to maintain these indexes. And you have to find a balance basically between how many indexes I'm going to add to satisfy my analytical queries versus how many can I add to not make my transactions go slow. And the column store index gives you the ability to define basically a single index per table on top which accelerates your analytics and at the same time, it's way easier to maintain for the system. So the transactional performance is going less down than in comparison to if you would do the same with Postgres. Now the idea of the extension basically is we already scratched a bit on the surface. So I'm going to go a little bit deeper here. There are basically three pillars in Postgres, if you will. And the first one is query planning. And on the query planning, the extension in addition to this column store index gives you query rewriting, which is focused on speed efficiency and parallelism, meaning if you have a query which starts off very much parallel, then it can happen that it goes sequential throughout. And this is going to slow you down, obviously. And the query rewriting here basically helps you with that. It can enable you that the query stays parallel through or at the end, by for instance, adding additional nodes into the query, which then take care that the data path stays parallel. You also have some planner cost improvements there, which then essentially, again, help you to get a much more efficient plan. Best possible example is if you have like joints and you're not necessarily joining on integer columns, but on text columns, for instance, then at some point you can observe that Postgres is going to go wrong in the estimations. And that's something the extension fixes for you. The next layer would be the data access layer. And here we have this column store index, which then enables you to get much faster onto your data. We are going to look into this in a moment. And the last layer would be query execution where we feature some faster join implementation. We have more parallelism depending on your case. And again, there's faster data movement. So even if you're not using the column store index, there is a improved sequential scan in the box, which gives you faster access to your data, even if you use the, let's say, a traditional way of storing data in Postgres. Now, if you combine all of these features together, then if you have a query structure, like it's depicted here on the left side, this is exactly this example I talked about. So you have something that goes parallel in the beginning and then you have a merge or an aggregate something in between. And then suddenly the operation continues in a sequential fashion and this is costing you valuable resources. So your, for instance, 24 virtual core machine is suddenly doing everything only on a single core and this is just a waste of resources. Now, if you use the extension basically, what you get is a completely parallel plan throughout the end and also more parallelism on the horizontal axis here. And this is enabled by, for instance, introducing additional nodes and by that, keeping the plan parallel until the end, using the resources your machine has and effectively enabling you to basically drive more queries onto the system or even utilize it more with multiple users. Now, so much for fury, let's dive a bit more into the topic of the column store index. And I have created a simple straightforward example here. So let's assume you have a table, which we call geo points and the geo points table has three fields only, latitude, longitude and the value and the value is for instance, a text here in that case. Normally you would probably use post GIS for this to keep things simple here. We just did it like that. So you have your table and you want to use the column store index because you know this table is going to be big and you want to have some savings and faster access. So we call create index on this table. And as you see in line two in the middle here, there is the using statement. Some of you know this probably from using other indexes like the gin or gist or range indexes and we just introduced column store here. So you call it with the column store argument and then you pass in simply all the columns. If your table has not more than 32 columns, just pass in all of them. If you're having more than 32 columns, then you have to do some selection based on your queries. Typically you would not select the ones which are never touched or almost never touched on your queries. Last not least, you call a vacuum. You don't have to do this because auto vacuum can take this over for you. But like when you do this the very first time, it's a good idea to call a vacuum and possibly even analyze afterwards. And then you immediately see the benefit because your query plan is going to show you that it does have a column store scan on your geo points table. And the data because it's compressed is going to be read much faster from the disk. And then your results are there much faster. Slightly more involved example, we like to cite the TPCH benchmark. You probably noticed this already. For those who are not familiar, TPCH is the data warehouse benchmark. It's quite old actually, but there are comparable results and that makes it really interesting because you can measure your own system against other systems. And it's scalable in terms of data. We typically test with a terabyte of data, but you can scale it even more tens of terabytes or even hundreds of terabytes. And consists more or less of a star schema and has in total 22 queries. And what we're seeing here is query number 14. And as you see, this contains a join between the line item and the part table. And there's a date filter on this line item table, which basically selects data within a month. And now we're taking this query and we're just going to run it on Postgres and I prepared this for you. So just going to present you the plan. This is the plan you would get out of it, the explain analyze plan. The visualization is with path two, by the way, a really great tool to get your plans visual. And if we boil this down together, then we see we have a total runtime of 15.6 seconds. So this is a hundred gigabytes here. And this is not such a bad result, but the moment you scale this dataset up from let's say a hundred gigabytes to a terabyte, your query runtime is going to explode because it doesn't fit in RAM anymore. You have to go to the disk with Postgres and typically disk accesses with Postgres are rather slow, depending also on your indexing structure. So we see here a parallel bit of heap scan and those are typically slower when it comes to parallel. So this is a bit hard to configure sometimes, but yeah, so you will get your result a bit slower maybe. If we dissect this query into its path, then we can dig out a scanning part of 13.6 seconds here. So the majority of the query time actually goes into scanning, which is not so surprising here because it has to first read a lot of data, then filter it down to this one month of data selection and basically do the job on this selection and what it has to do with the aggregate and gather is not so heavy in this case. Now, I'm going to show you next what's going to happen if you just load the extension we provide and with that your query run time half suddenly, yeah. So this is because you have some more parallelism, but most notably, as you see in the scan time, the scan time halved roughly as well, it's even a bit more than that, we've more than halved. This is because we already provide you a new parallel customized scan. So this is not using the column store index yet, but we have a different scan path for native Postgres data, which is typically much faster than what Postgres provides you out of the box. So this is half the speed. Sorry, double the speed half the time. Now, what's going to happen if we add our column store index and the question is how are we going to do this? And the answer to the question is don't think too much about it, just create your index. Create it on the table you want and just use all the columns, easy as that. So for all the tables we are having in this benchmark, which is like a seven in total, we call create index for the table with the column store statement and together with all the columns that is in each table after that we call vacuum analyze and that's it. And we did not change the schema at all. So everything is in there, you have your primary keys, you have your unique keys, you have a foreign keys, everything is in. You can even partition the schema if you want. And typically that's what most people do to get some more performance out of it. So you can even put partitions on top. So none of the features of Postgres is taken away from you, we just add things. And as a result, we trim down our runtime once more. So we are now down at 4.8 seconds. And as you see the total scan time half again, so the scan time now only is three seconds because of the compression you have and because of your only reading the columns you're needing in the end. Now, if you map this to the complete benchmark and I said earlier, there are 22 queries, we get a roughly 20 X class, a TPCH result. And this here by the way, is measured with a Postgres 12.3. We have in this case a terabyte of data and if you look into the graph here, we just looked in the query 14. So in this case, query 14 is something about 300 seconds. And as a reminder, we had 15 seconds before. So this is quite a lot more. And we are on a very beefy machine here with 144 vCores, it's a four socket machine and a complete SSD storage array which can deliver 12 gigabyte per second of data to the CPUs and RAM. So this is quite a machine and still query 14 is relatively slow. So you're having to wait five minutes to get your result back. Now, if you add the extension and you use the column store, the effect now is that all queries return within 80 seconds or less. And query 14 here is, I think, ballpark 25 seconds, something like that. So this is quite some speed up. Plus as you notice, we have a timeout here of 900 seconds, 15 minutes. And for those who know this benchmark, typically the more difficult queries are 18 and 21 because they either do not complete within like a reasonable amount of time, meaning within let's say 15 minutes, 30 minutes or an hour, however you want to set this timeout. And typically for 18 and 21, they tend to consume also much more memory. So there's even a chance that your system, depending on its configuration crashes. However, as said, if you put all the things together, you can keep your valuable RAM for the more important operations and get to this result where your queries return in a reasonable amount of time. And having said that, I hand it back to Andy. And thank you. Thank you, Sebastian. We have some time for some questions and we have a few already. If anybody has other questions, just type them into the chat window. First question is whether or not the column store index can the maintenance of those be automated? Yes, it can. You'd need to configure your auto-vacuum correctly for this and like configure it such that it can keep up with your influx of data. So this is possible out of the box. Okay. Another question is on the, I think you mentioned this, but somebody wanted to know, what's the limit on the number of columns that can be added to an index? So that's 32. This is actually a process limitation, meaning you can, if your table has 32 columns, then simply go ahead at all 32. If you have more than 32 columns, go ahead and have a look into your queries for sure you know them and then basically select based on this. So for instance, possibly you're having a comment column or so in your data warehouse. You maybe don't want to include this to just reduce the amount of columns if you're having more than 32. Great. Somebody is asking, so according to previous presentations and depending on how long you've followed swarm 64, you may have heard us talk about FPGA acceleration. So using a special hardware to accelerate Postgres query performance. That is something that we no longer do with the 4.0 release in April. We, that was the first version of swarm that did not require an FPGA to speed up Postgres. And actually with 5.0, we don't support the FPGAs at all. Just we found that we were able to accelerate Postgres just as fast without requiring a specialized hardware. So as of 5.0, actually a 4.0 and presently you don't require special hardware to run swarm runs on regular CPUs. But another question here for you, Sebastian, is Postgres compatibility, like what versions of Postgres can you use the column store indexing on? Yeah, so this is currently available for Postgres 11 and 12 and we are also supporting Enterprise DB E-PASS. Thank you. And another question on, does the column store indexing, how does that get along with foreign key constraints? They are completely supported. Basically as said, it's just another additions works like any other index and the constraints you put on your tables are controlled via the table in that case. They might put another index on top to just make these operations fast but this is not competing with our index and the Postgres planner basically or the costing model that is behind knows about this and the moment you basically access more data it will then pick the column store index in contrast to any other index that is there. Great. And how well does it get along with table partitioning? Works also very well. Same thing holds here again, it's an addition. So let's say you have your master table and whatever you create in terms of indexes on that master table is then pushed down to the children tables as well. And if you have partition pruning enabled or the partition-wise aggregate join functionality then the index is going to be accessed on the child tables basically with reduced data. Thank you. Sorry, the questions are coming in pretty quickly. Does this, does Swarm64 work with Amazon RDS Postgres? Answer there I think is no. I don't think RDS permits you to install Postgres extensions and since Swarm64 is a Postgres extension it's not an option although you could, you can launch a Swarm64 accelerated Postgres instance on EC2 if you wanna manage it yourself or have us help you manage it from the Amazon Marketplace or from our website. How well, so once the initial index has been created through the initial vacuum does the column store index still provide benefit after updates in between vacuums? All right, so what happens? Yeah, great question brings me back to this slide here. Sorry, this slide. This portion is here. So what we try to depict here is you create your index initially and then for instance you have data flowing in addition and this is not immediately covered by the index but then later the moment auto vacuum kicks in. So if you want to read the whole table then this index is completely scanned and this is going to be very fast and then in addition these rows which are not yet covered are scanned and typically they are only a fraction so you will get quite a lot of speed plus a bit of some slower speed to read the remaining data or better say turn it around you get always a benefit from the index and at the same time you get always up to date data. Okay, and another question is if you're running, is the Swarm64DA instance on EC2 and AMI? Yes it is, you can launch it directly from the marketplace. So if you Google for Swarm64 or search on the Amazon marketplace you will find the 5.0 beta there and then can start it directly from the marketplace. Great, and the, I think probably time for one more question here, where'd it go? The, how well does it get along with bulk loading methods? For example copy to- Yes, better than the current B3 index implementation so this will slow you less down basically. If you want to have bulk ingest into your tables then the recommendation would be to try to strip all constraints you're having to get the best possible speed basically and then let, yeah, you have the index created already and let auto vacuum do the job basically to update the index. So this works very well. Okay, actually one more question here, it just came in. Will the attach and detach partition work the same with column store indexes and does it acquire any additional lock in or locks? Should not because the index on a partition is stored separately much like all the other indexes in Postgres. Okay, great. Let's go back to the last slide there. Yep. All right. So yeah, thanks everybody for joining us today and invite you all to come and try Swarm64. We're, as I mentioned earlier we're looking for feedback on 5.0 and yeah, if you can try it for free on Amazon or pretty much anywhere else just visit Swarm64 to request it and we look forward to hearing from you and seeing you online in future PGCon events. Thank you, Sebastian. Thank you, Andy. Thank you to everyone who spent part of their day online with us. So regardless of where you are I hope you have a wonderful rest of your morning, afternoon or evening. So cheers and thank you. Thanks a lot.