Stat Tracking, Temporary Tables, pg_squeeze, pg_auto_failover | Scaling Postgres 136

In this episode of Scaling Postgres, we discuss stat tracking, temporary tables, a new utility called pg_squeeze and an update to pg_auto_failover.

Content Discussed

• Tracking Postgres Stats
• PostgreSQL: Sophisticating temporary tables
• pg_squeeze: Optimizing PostgreSQL storage
• What's new in pg_auto_failover 1.4 for Postgres high availability
• Multitenancy with Postgres schemas: key concepts explained
• PostgreSQL Monitoring for App Developers: Alerts & Troubleshooting
• Tuning Your Postgres Database for High Write Loads
• Postgres 13 Observability Updates
• Talking PostGIS on Podcasts
• Greg Sabino Mullane

YouTube Video

Podcast Audio

Transcript

All right. I hope you, your friends, family, and coworkers continue to do well. Our first piece of content is "Tracking Postgres Stats". This is from pgcraftsman.io. This is a blog post about a very simple SQL script that has been put together that looks at existing system tables within your database and it basically takes snapshots of them. So a lot of these stats tables that exist within Postgres either have point-in-time statistics like pg_stat_activity, of course, or they have cumulative statistics like pg_stat_all_tables. He argues you basically need to create snapshots of these tables to glean useful information from this information over time. He has this pgcraftsmans-snapshots SQL script that basically takes snapshots and records them in the database itself of these different statistics. So basically, maybe you want to schedule this with a cron job on a periodic basis and take a look at the data, or maybe do some work or some job and then take another snapshot to compare how your system has been modified by changes. 

So he gives an example of running the command once you have this SQL script and it creates a separate schema for itself, as well as a number of tables and indexes. He shows an example of the tables that are present here. Now, he does mention some of the things like memory and CPU aren't currently used because there's no way to query Postgres to get that, but he's alluding to something coming in the future because he mentions a future post. I suspect he may be using an extension that actually contacts the underlying OS to pull things like CPU stats and things of that nature. But basically, taking a snapshot is rather simple. You just run this command to take a snapshot from those existing tables. 

Now he talks a little bit about what it takes to do a snapshot and it basically depends on how many different objects you have in the database or in the case of static activity, how many connections you have. This post basically goes through this and allows you to check out what the snapshots, as they exist in the separate schema are and then do a report from one snapshot to another and it gives you stats from this table. So for example, for the pgbench history, you could see from a workload that was run, over half a million rows were inserted. So even though this looks like it was just introduced and it's relatively simple, it looks like something that could build up to something pretty interesting. It's basically a very bare-bones way to collect a time series of statistics information from these tables over time. So if you're interested in that, definitely check out this blog post.

The next piece of content is "POSTGRESQL: SOPHISTICATED TEMPORARY TABLES". This is from cybertec-postgresql.com, and it's all about temporary tables. These are tables that are created within a session and then once that session is complete or the connection is closed, the temporary table goes away. Now, in addition, he mentions a few other configuration options you can use with temporary tables. The default is to preserve rows on a COMMIT of that table. However, you could also choose to delete rows like do a bunch of work, and then when you do a COMMIT, it actually deletes the rows and blanks out the table. 

Or you could actually choose to entirely drop the table. Now, if you're going to start using more of these temporary tables, one thing he says here to be aware of is that the amount of memory dedicated to temporary tables, by default, is at eight megabytes. Well, you may want to increase that if you want more space for temporary tables to continue to be memory residents. So that's a configuration you may want to change. So this is a very quick, simple post about temporary tables in Postgres. So feel free to check it out.

The next piece of content is "PG_SQUEEZE: OPTIMIZING POSTGRESQL STORAGE". This is from cybertec-postgresql.com. So this is a new utility that's available that enables you to shrink tables live. While the database is working, move tables and indexes from one tablespace to another, index organize, or cluster a table, as well as change the on-disk fill factor. Now, it seems like the primary use case is to shrink tables in a live database similar to what pg_repack does. What's interesting about this, it actually uses logical replication to do the majority of its work. So this may require less locking to be taking place than maybe pg_repack. Now, they discuss a little bit about table bloat in general, and basically because of how Postgres works, whenever you do an UPDATE, a separate row is created and then that old row is deleted eventually. 

But even though vacuum happens and cleans up that dead row, it never reclaims the disk space. So for example, if you have a table, you've inserted some data, it's at 69 megabytes. If you then update all those rows, it goes to 138 megabytes. But vacuuming the table won't reduce the size of the table. However, if you use their utility, pg_squeeze, it's actually able to compress the table back down to the original size of 69 megabytes that they show here. They also talk about how you can set up a scheduling system with it because they have squeeze tables where you can INSERT a row in here to be able to squeeze tables on a periodic basis. They have a schedule option that appears to work like cron in order to schedule these operations. So it looks like a new utility to do something similar to what pg_repack does, but it does it slightly differently. So if you're interested in that maybe you want to check out this new utility

The next piece of content is "What's new in pg_auto_failover 1.4 for Postgres high availability". This is from citusdata.com and pg_auto_failover is a very simple utility to do a primary to a secondary failover to maintain a High Availability Postgres solution. Basically, it just requires having a monitor that does health checks against a primary and a secondary instance. So relatively simple setup and it just takes these four commands to get a setup running. Now what they're mentioning here in 1.4 that's new is the ability to have multiple secondary or multiple replicas available. How it worked previously if the primary went down, of course, it would promote the standby or the secondary node to become the primary. 

But what if your secondary node goes down? What happens then? Well, if you have synchronous_commit set, it's actually going to stop writing to both the primary and the secondary because they need to happen in synchrony. So what the utility did was actually turn off that synchronous replication. So you can still do writes to the primary but then essentially your data is unprotected. But with being able to support multiple replicas, even if a secondary node goes down, you still have data redundancy on both the primary and the secondary if you have two or more nodes available. So this version supports that capability as well as Postgres 13. So if you're looking for a High Availability solution that looks relatively simple to set up but they continue to add new features to it, definitely check out this post.

The next piece of content is "Multitenancy with Postgres schemas: key concepts explained". This is from the blog at arkency.com and basically using schemas they are essentially namespaces for tables. So if you're doing multitenancy where one account has a dedicated schema, another customer account has another dedicated schema if you want to set it up this way. This blog post describes that and it basically shows you how you can create additional schemas, how they namespace the objects within them, and how when you're going to query against it, it's basically separate objects under different namespaces. So they had created a tenant one namespace and now you can look in the things table in that namespace and there exists a default namespace public that all objects are created in by default unless you specify a namespace. 

So it talks about being able to query different ones by specifying the full namespace or you can define a search path and customize it so that it will only look in particular schemas for tables and how you could do this with a set command for sessions in Postgres. Of course, the thing that you need to keep in mind that they cover here is that if you're using something like PgBouncer and it's set to transaction pooling, you're probably going to be mixing tenants' data up because transaction pooling is not compatible with sessions. So it's definitely a big caveat to keep in mind. But this post goes over some of the things to keep in mind if you're exploring using schema-based separation for multitenancy.

The next piece of content is "PostgreSQL Monitoring for App Developers: Alerts & Troubleshooting". This is from blog.crunchydata.com and it's following up a series of posts that they've been talking about monitoring, particularly their tool Postgres Operator which works with Kubernetes clusters and pgMonitor for the monitoring. It discusses using the Alertmanager from Prometheus in order to set up these alerts to be able to get notified if there's something that's outside the bounds of what's expected. It looks like they have some defaults that have been set up for their particular solution. The most important ones that they talk about are in terms of setting an alert on "Is this thing on?". Basically, if the Postgres database is up and available. The second one is "Am I out of space?". 

Basically tracking disk usage to make sure that you're not going to run out of space either due to excessive logging or just database growth. Next, "Why is this not clean?". Basically tracking vacuum operations, in particular keeping an eye out for transaction ID wraparound to make sure you don't get close to that limit. Next is falling behind the leader, which is monitoring replication lag as well as running out of numbers, which is a sequence exhaustion. This means a sequence is set to a particular limit and you either run out of that sequence number or your table itself. The column that the sequence is used for you overrun the size of the integer that's present there. So definitely some key things to monitor that are discussed in this post. So definitely a post to check out for monitoring these critical things as well as the tools that they have set up if you're interested in checking those out.

The next piece of content is "Tuning Your Postgres Database for High Write Loads". This is also from blog.crunchydata.com and basically, they're only talking about one parameter change here, and that is the max_wal_size. Now, they do discuss some others related to checkpoints because basically what's happening that they've seen in log files sometimes is that the checkpoints are occurring too frequently. That's because not enough WAL size is maintained to be able to prevent early checkpoints. So basically the only thing you have to adjust here to get things back to normal is increasing the max_wal_size. So this is a very brief post on that, but of course, you do want to consider more configuration changes. For high write loads, such as adjusting your checkpoint timeout, the warning completion target. A lot of these parameters you want to change, but this particular post only covers the max_wal_size, so you can check this post out if you want to learn more.

The next piece of content is "Postgres 13 Observability Updates". This is from dataegret.com and they've taken an image and have updated it with the changes to Postgres 13 with regard to where you can get insight into performance with Postgres and it points to all the different subsystems of Postgres and the system views you can look at to get insight into it. So for example, you can look at the pg_stat_wal_receiver or the pg_stat_replication. So it lists all these different system views where you can get insight into the performance of Postgres and they talk a little bit about the changes from 13. But if you're interested in an image like this, definitely check out this post.

The next piece of content is "Talking PostGIS on Podcasts". This is from blog.cleverelephant.ca and he has referenced a couple of different podcasts he's mentioned since he hasn't been attending conferences recently. You can get more GIS content here.

The last piece of content, the PostgreSQL person of the week is Greg Sabino Mullane. So if you're interested in learning more about Greg and his contributions to Postgres, definitely check out this blog post.