Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about columnar storage docker containers notify and listen and tuning Red Hat Enterprise Linux I'm creston jamison. And this is scaling postgres episode 156. You all right? I hope you, your friends, family and coworkers continue to do well. Our first piece of content is Citus ten brings columnar compression to Postgres. This is from Citusdata.com. They're talking about the newly released open source Citis Ten and its support of columnar storage. So typically, Postgres uses a heap access method, but Citus has implemented a columnar access method using the Postgres Twelve table access API. So to get started, the prerequisites are having Postgres version twelve or later and having the open source Citis version ten or later, although I'm sure you could use the non open source version. And they're using the psql client. So the first thing you need to do is you need to add Citis to the shared libraries of Postgres, make sure it's enabled and restarted. Then you need to create the extension in any database you want to use. And for their example here, they created a simple row using the standard heap storage, and then a simple columnar table using the columnar storage here. Then they generated a series of 100,000 records in each and then did an average between each of these. So no indexes have been created so far. And they're saying here you can see that the table access method is listed as heap for the row and then listed as columnar for the columnar table. Now they have a video that kind of runs through this whole blog post, and I encourage you to also check it out as well. So you can just click here and it brings you to a YouTube video that essentially reiterates the blog post so you can check that out. So in terms of the benefits of doing this, columnar storage are, number one, that it reduces storage requirements because they're using compression to store this data. It takes much less space, and I think we'll see that in an example here. In addition, because it's compressed, there's less I o needed to actually scan the table. So not as many blocks need to be read to order to read what's in the table. The next benefit is projection push down, meaning that queries can skip over the columns they don't need. So if you have a table of, say, 20 columns, and you only need to return or look through two columns, it will only focus on those columns. So that's where columnar storage has huge benefits, in that you can only access the columns that are of interest. And then the fourth benefit is they use chunk group filtering. So it's a way to skip over groups of data to identify where they are. And they say it can skip past a lot of the data quickly without even decompressing it. So I guess it stores kinds of references of ranges that exist within the data so that it knows what chunks of data to access or not. So they did an example here of using these row column storage and column storage to look at compression and being able to skip over unneeded rows. Although I'm not sure how much of a use case this will be because eventually you would want to query against it and would you want to store wide rows in a column based table? Not really sure how much of a benefit that is. I can definitely see the compression but a lot of analytics things that I've looked at table width has kept minimal for that reason. So I'm not sure how this is advantageous in the real world. But looking at all of these integers that it's stored, you could see that the compression ratio is eight, meaning that the columnar storage takes up eight times less room than the row based storage. So that's how good the compression is. Now, when actually querying the data, they're only using three out of the ten columns. So again, you're going to get a big difference in performance out of this. I'm not sure how applicable this is to the real world because why would you put all that data into a table you're hardly querying because it has to be in columnar storage. So, again, I'm not sure on the use case for this, but you could see with this example, they're getting a 25 x speed up because again, you're only accessing the columns that you need to. And they're also showing you the number of buffers. Red is over 5 million for the row storage, whereas it's only 27,000 for the columnar storage. So that's a big reason for this speed up. And again, remember, there's no indexes involved with this. Now let's get into the limitations. Number one, as of right now, there's no update or delete support. So it's best for append only tables or append only solution. Now they said even if they eventually implement update and delete it's not going to be great performance because it's using column based storage. Right now there's no index support right now there's no logical replication or logical decoding support. And there's a few more limitations they don't mention in here, but it's in the README. Now with that being able to update and delete, they said, well, there is one solution you can use, is that a hybrid columnar and row table storage using range partitioning. So in the example they used, they created an events table and partitioned it by a date range. The older partitions you can use the column based storage, the newer partitions you can use as row based storage so that you can do updates and deletes if you want. But then essentially you can rewrite the row based storage into a column based storage once you're moving on to a new partition. So they're saying this kind of gives you the best of both worlds and they're using the special function that they develop called Alter table Set access method that's included in the Citizens extension to be able to do this. And they give a little bit of some of the query examples and the size improvements you could get by doing this. Now they also mentioned this is based upon a previous extension called the CSTORE Foreign Data Wrapper, but that has a lot of disadvantages compared to the current one. So the old extension, CSTORE Foreign Data Wrapper, did not have transactional integrity. So for example, did not do Rollbacks, it did not support the write ahead logging. You couldn't do physical replication with it. With the Citus columnar storage you can do physical streaming replication with it. And the newer one does support PG upgrades, which is great news and it's probably because it's based upon that table access method API in Postgres twelve. So this is a great addition and if you think that your workload could benefit from column based storage, maybe you want to check out this extension to see what it can do for you.
[00:06:33] The next piece of content is running Postgres in Docker. Why and how. This is from CyberTech Postgresql.com and I think this post is based upon the other one that said resistance to containers is futile, when they were saying that Postgres is essentially going to be running on containers a lot. And this I think has a balanced view of it. And they talk about how Docker and containers in general were basically built as immutable ways to transport code, but essentially with a database it is mutable. You have data constantly changing in a database. So is it kind of a square peg in a round hole using containers with databases? Because containers are supposed to be stateless and databases are all about the state. Now there are ways to get around this with containers essentially setting up separate volumes so that the state is stored outside of the container. So one of the first questions the post asks is should I use Postgres with Docker or containers in general, and particularly for production workloads? And their advice is if you do, you should live fully on a container framework such as Kubernetes or OpenShift. And secondly, no, you're going to have to depend on some sort of third party software projects not affiliated with the PostgreSQL Global Development Group. And you may have to maintain your own docker images to include extensions that you need or other scripts to handle upgrading to major versions and things like that. So it's possible, but there will be work involved and I imagine that over time that will continue to improve. But as of right now it's still early days and you're probably going to have to do a little bit more work for that. And this Post talks about a measured approach that is similar to my thinking that yes, you can do it, but you need to be aware of the ramifications of doing it. But one area they do advocate for using postgres with containers is in testing. So they say, quote, it's a tester's dream. So, for example, he says he personally has every version of postgres as a container he can just spin up to do work in, which is great. And then the post rounds out actually discussing how to actually use docker with postgres and how he uses it. And he gives the different commands you can use, taking a look inside different images and the docker containers he uses as well as how to set up volumes and some different configurations. So overall the post talks about that. Yes, you can use containers with Postgres, but quote, you had better be aware of the pitfalls one basically talking about it's meant for immutable state, so you need a way to handle that. Containers won't give you any automatic magical high availability capabilities for that. You have to look for container frameworks such as Kubernetes. Third, they say life will be relatively easy only when you go all in on some container management framework like Kubernetes and additionally some operator software such as Solando and Crunchy Postgres. So basically another post talking about using postgres with containers and the issues you need to be aware of. And I thought this was a very balanced post that I agree with all of their points. So I definitely encourage you to check out this post from CyberTech Postgresql.com.
[00:09:39] The next piece of content is Postgres notify for real time dashboards. This is from Archetype.com and they're talking about using the postgres features, notify and listen. So the use case that they were designing was being able to develop a real time dashboard to keep track of production of a manufacturing operation. So they had a backend where employees were inputting the work that they did that inserted when a particular step was complete into postgres that would fire a trigger that eventually notify a Node, JS and WebSockets front end to retrieve more data and present it. So they showed how they actually built this. So they gave you the base schema of what they developed for tracking the production changes and then they created a function. And what that function did is simply did a perform PG notify with event, type of order, progress event and then some sort of payload. Now they just went with a simple payload. They didn't want to actually transfer the data as a part of this, although you could do that, they just wanted the clients to receive this and then they would retrieve new data from the database. So basically they were just using as an event notification system and then you place this function in a trigger on a particular tape. So before Row got inserted, it would fire this procedure. Now they went on to the listen syntax and again, like I mentioned before, the client would receive this notification, but then they would go do their own query against the database for the data that they needed. So they developed a view for that purpose. So whenever it received a notification that something had been updated, it would just query this view. So this is the essentially JavaScript that was used. So at the point that the client connects, it actually runs a query to listen for this event, type in postgres, and then when a notification occurs, it calls this callback called event callback, and that event callback queries that view and then presents the data to the client. And then they have a little picture of how it presents that data. So this is a very simple great example of using Postgres listen, notify for real time notifications. If you're interested in that, check out this blog post.
[00:11:49] The next piece of content tuning red Hat Enterprise Linux family for PostgreSQL this is from Enterprisedb.com. They're talking about Configuring Linux or Red Hat Enterprise Linux for Postgres use case and in this example they're using the tuned package to do it and that enables you to set different things like systemctl, some CPU metrics as well as the VM. And they show some of the configuration changes that they've made specifically for running on Postgres. They explain all the different parameters and how they've determined that those were the ones to set. Then they went into optimizing the file system in terms of doing things like setting no access time and then they closed out talking about huge pages and how to determine the best configuration for huge pages and how to enable it and how to make sure that Postgres is using it. So if you're interested in that, you can check out this blog post.
[00:12:40] The next piece of content best Practices for Amazon RDS for PostgreSQL major upgrades and Replicas this is from AWS Amazon.com. They're talking about the things to take into account when you're upgrading your Postgres RDS databases. And basically you can upgrade essentially in parallel, although it does the primaries in serial first and then does the Replicas in parallel. But you can also choose to only upgrade a primary and then create a new Replica. And with RDS there's some trade offs for that. If you choose to do a concurrent upgrade, the upgrade will take longer. So we have longer downtime using this method. This method, the downtime is shorter to do the upgrade, but depending on the size of your database to create a new replica, you could be running without a replica for quite a while as it takes time to actually rebuild it. So there's a trade off. You have to consider full downtime of the application using the database versus continuing operations without an existing Replica. And they go through the different process of how you can do this, as well as give advice on different testing scenarios you can use to make sure that the upgrade works before you actually do the implementation. And this also has some good advice that you could apply to postgres upgrades in general. Now, it works a little bit different in RDS because as far as I know, they don't use standard PostgreSQL physical streaming replication to do their Replicas. It's more of a disk based replication to my knowledge. So you can do independent upgrades like this with a Replica, but if you're upgrading postgres not on RDS and using physical streaming replication, there is a different method you can use to upgrade the Replicas. So when I've done upgrades for clients, we use PG Upgrade to upgrade the masters, and then a specific technique that's included in the postgres documentation to upgrade those streaming Replicas as well. But definitely check out this blog post if you want to learn more.
[00:14:40] The next piece of content is speeding up PG bench using Copy Freeze. This is from Pgsqlpgpool blogspot.com. They're talking about when you're setting up PG bench and you are loading up data for the first time. The area that takes the most time to load the data at the start of the Pgbench run is actually the vacuum operation they talk about. Why is vacuum so slow? Well, vacuum takes a while because it does a number of different things, such as update the hint bits on each tuple, update the visibility map, update the freeze map, basically do things to make sure that the table is optimized for doing queries as fast as possible. But there is an alternative and that is Copy Freeze. But it only does the first required thing that Vacuum does, but not the others. But according to this post, Copy Freeze will be enhanced in postgres 14 to do all of these things. So this post talks about updating PG bench to using Copy Freeze for this as opposed to Vacuum. And what they've noticed is a dramatic improvement in the startup time and that they imagine this enhancement will actually get in postgres 15. But if you use PG bench, definitely some enhancements to look forward to.
[00:15:56] The next piece of content is PostgreSQL GitHub Actions Continuous Integration. And this is from Cybertechn Postgresql.com, and they're talking about using GitHub Actions in relation to postgres. So if you have an interest in learning how to use GitHub Actions, particularly with working with postgres, definitely check out this blog post.
[00:16:16] And the last piece of content. The PostgreSQL person of the week is Guillaume Le Arge. So if you're interested in learning more about Guillaume and his contributions to postgres, definitely check out this blog post that does it. For this episode of Scaling Postgres, you can get links to all the content mentioned in the Show Notes. Be sure to head over to Scalingpostgres.com, where you can sign up to receive weekly notification of each episode, or you can subscribe via YouTube or itunes. Thanks.
