Episode Transcript
[00:00:00] One of the great benefits of Postgres partitioning is that it looks to exclude partitions based upon your partition key. So if you're partitioning by month with a timestamp and you're looking for data in the month of December 2025, usually that will only pull the one partition, assuming you set your timestamp range correctly. But the problem is if you don't use a partition key in your query now you're looking at every partition of that partition table. So if you have say a particular account or a particular user, and that's the only query in the where clause, it's going to look at every table for that particular account or that particular user. But there might be some ways to achieve partition exclusion without using a partition key, and we'll talk about that today.
[00:00:54] But I hope you, your friends, family and co workers continue to do well. Our first piece of content is how to achieve pruning when querying by non partitioned columns in PostgreSQL. This is from hakibanita.com and the first part of this blog post goes over partitioning in general, how to set it up and he's using the example of you have an events table, so you're collecting all sorts of events or user actions and they did partition by the timestamp. So if you query with a particular timestamp range, it will scan only one table that that timestamp is valid for that excludes the other timestamp. Whereas if you query say by the session id, you're going to get every partition. In this case he just has a two partition setup, so he sees every partition and if you do index it, it will give you an index on that local table. So it will make this lookup file faster, but it still must scan every single index. And unfortunately global indexes are not a thing with Postgres yet, so those won't help either. But he goes into a technique on how to actually prune on non partition key columns and this will only work in certain use cases. In this example here he has the case where the events table is append only. So you're not going to have data being updated.
[00:02:16] You have session IDs that are generated sequentially so it's not a random uuid for example, and sessions are short lived. So what you can do is you can find the min and max session ID for a given year, whatever the partition is. If you do monthly partitions you would want to do it per month and you essentially have the bounds that that session ID exists Within because there's strong correlation between the session ID and the timestamp, because they're sequential, of course. And the trick is you put these session IDs as a check constraint on each partition. So in the 2025 partition he has here he puts the constraint between 1 and 4320 and then in the 2026 partition he puts 4320 up to 10,000, because that's what session IDs are set in that partition.
[00:03:11] Now, when you query by a session id, it is going to automatically exclude the non relevant partition. So it gives you partition exclusion even when the partition key was not included in the query.
[00:03:26] Now this works because it's using constraint exclusion, so it is on by default for partition tables. So you can apply essentially any check constraint you want to a partition table to achieve this type of pruning.
[00:03:41] Now this works in a narrow use case, but there can be issues even with this example. And it's when a session ID is exceptionally long lived, like days, that rolls over into the next year, how could you handle that? For example, in the 2026 year it shows session one to session 10,000. Essentially all the sessions are viable for this particular partition table. And he shows for the 2026 partition table, you can call out those outliers and saying when the session ID is between one and one, choosing that one outlier session or the session ID is between that range, that's the data that's valid for this particular table. So now you can still query session ID1000, you'll get back one table, you can query ID session ID6000, still get back one table, a different one, but then when you query session ID one that lasted across two years, both tables will be scanned to answer the query. And he even gives an example of a query to identify these as well as something that generates a parameterized query to automatically build these check constraints. So this is pretty cool. And if you have this type of data correlation, using this technique might be a benefit, but you definitely can't do it on data you can update, of course, but if you want to learn more, definitely check out this blog post.
[00:05:09] Next piece of content. There was another episode of Postgres fm. This one was on PG Backrest. So Nick and Michael had David Steele on who was the creator primary maintainer of PG Backrest. So they talked about the reasons why a backup tool is beneficial to be outside of Core as opposed to inside of it. And it's basically development velocity. You can develop much faster and have more frequent releases when you're working on an external tool or an extension compared to the once a year feature release schedule of Postgres. And they said, you know, Postgres has basic backup tooling, but there's so much more you need to add to make it an actual backup system. You can either do it yourself through scripts or use a tool like PG Backrest or Barman to do it or. Or now even the new PG Hard Storage. They discussed the positives and negatives of backing up from a primary versus backing up from a replica. They even had discussions about wall replay. And I think David said he had an experience with a client whose database system could never keep up with the wall level. So they didn't have any replicas, but yet they could archive their wall very quickly with PG Backlust, I believe. And they also highlighted that it's actually faster in terms of generating the wall to do a log shipping method as opposed to streaming it, although you still have a bottleneck with the wall being applied to the replicas. He also talked about the new sponsorships that PG Backrest has planned feature development, and I would say his two north stars are reliability in terms of making sure data is never lost and second in terms of performance. So those are the two things he really push as far as he can in his development work. But if you want to learn more, definitely encourage you to listen to the episode here or watch the YouTube video down here. Next piece of content why Pghard storage has no incremental chain this is from Cyberdeck. PostgreSQL and PG hard Storage is the new storage system announced a couple of weeks ago. We did cover it on scaling. Postgres has an awesome website talking about it and he said one of the fundamental differences between pghard Storage, this backup solution and PG Backrests and Barmans is it doesn't have an incremental mode even compared to the new incremental backup features added to Postgres Core. And one of the reasons they did that is because it's so easy to lose a full backup or lose one of the incrementals and now you can no longer restore to a point in time because any single corrupted backup invalidates everything downstream. So maybe you have an S3 lifecycle policy that deletes one of the incrementals, you have a bit flip and a storage backend that corrupts a file. An operator runs backup expire with too aggressive retention or a manifest schema bump between releases and the migration script had a rounding bug. I don't know If I've seen or heard of that one. But how they do it is content addressing. So Pghard Storage stores its manifests by chunks that are SHA256 hashed, so each chunk's file name is its hash. And two backups that have the exact same data are sharing that chunk. So in terms of the manifest, when you delete the older backup, the new one's chunks stay until garbage collection finds zero references to them.
[00:08:42] And this is not unheard of because he says Restic, Copia and Borg Backup all do this. So it's quote not novel.
[00:08:51] But it is different than all the other backup tools in the Postgres ecosystem, including incremental backup that was just added to Postgres itself. Now he concedes there are some wins of chained incremental backups. One is you don't write the same byte twice when nothing has changed. And and second, you don't take an I o hit of a full read when 99% of the pages are unchanged. Now, in terms of their implementation, you do get not writing the same byte twice for free because the chunks have identical hashes. But not taking an I O hit, he says, is harder.
[00:09:27] So this backup currently has to read the full PGDATA directory on every backup and then dedupe against the chunk pool. So with 100 terabyte DB, that's not necessarily free. But he says they are working on a snapshot aware fast path that uses file system level copy on write snapshots when available, but that particular feature isn't ready yet. But apparently they have used this tool on a 487 gigabyte production grade fleet taking hourly backups for a week. I think I must be missing something because it says total storage is 12 terabytes. So are there multiple databases that are 487 gigabytes? Because he says the raw data is 89 terabytes. So I think I might be missing something. But anyway, if you want to learn more about pghard storage, definitely check out this blog post or look at the pghard Storage project website down here. Next piece of content. Replication deadlock bug in current Postgres releases 14 through 16.
[00:10:27] So if you just upgraded Postgres and you are using version 14 to 16, so basically you updated to 14, 23, 15, 18 or 16.14 there is a replication deadlock bug that means your replica can be stuck in deadlocks, specifically a multi exact offset SLRU deadlock. Postgres version 17 and above are not affected by this. And specifically the wall needs to be generated by a primary or a leader running a couple of older versions back like about from a year ago and the replica is on this newest release and essentially the startup process hangs with the wait event lightweight lock multi exact offset slru so there doesn't look like there's going to be a hotfix. So the only way to mitigate it is to use versions that aren't impacted by this either on the primary or the replica, or wait for the upcoming minor releases, which looks like it's currently targeted for August 13th. So if you want to learn more, definitely check this out Next piece of content are you dot ready? This is from richyan.com and this is essentially what he says here, a practical guide to what ready and dot done mean and why wall sticks around so this is mostly about wall generation and how it's handled and specifically ready done refer to how wall files get archived when you're using an archive command. So this copies the wall from the pgwall directory to some other location and ready essentially says the file is pending or ready to be queued to be processed and done is that the wall archive was successful when our done file is deleted. It's not deleted immediately, but it does eventually happen.
[00:12:19] And he says how is this related to streaming replication? And it's not really. It's mostly tied to the archive command. How is this related to replication slots? Again, it's not really related, it's related to the archive command because replication slots are for retaining wall that are needed for an actively streaming standby or in the case of a logical replication slot for logical decoding or subscribers.
[00:12:45] And he mentions a few quick checks you can do for each of these. So if you want to learn more, definitely check out this blog post. Next piece of content OralDB beta 15 and beta 16 stability testing and replication hardening this is from AureLDB.com and this is just an update on the work they've been doing on oriodb. It sounds like it's mostly just stability and accuracy testing, so still no real timeline on when this might be released. What I find interesting is that I don't remember oraldb being mentioned as one of the open source projects on the Supabase article where it was talking about supporting open source, it was predominantly mentioning multigras. I don't remember them mentioning oriodb, but I do hope this eventually comes out as an extension you can simply add to Postgres to create the ability to run update heavy workloads so that the row versions get stored in a redo log as opposed to actually in the heap. But check this out if you want to learn more. Next piece of content why we built yet another Postgres connection pooler this is from pgdog.dev so it's not that it's just a connection pooler, it's a sharding connection pooler. And he's talking about some of the reasons why he says to be able to of course scale out Postgres, but also the fact that because pgdoc has a built in parser it can handle set statements unlike PgBouncer running in transaction mode. So it actually retains those set values on the client connection in the proxy and it can set them to to the database when needed. So this particular connection needs the statement timeout of one second. This particular connection needs a timeout of five seconds. Well when it talks to pgdog it remembers what that setting should be and if the database setting differs it goes ahead and sets it before it executes whatever task needs to be done. It can also support Listen notify as well.
[00:14:52] So that's something that PgBouncer and transaction pooling mode can can't handle either. And then also of course multi threading which PgBouncer is single threaded and you have to set up multiple services if you want more than one process running and you have to deal with additional configuration to do it. But being multi threaded and a multiple CPUs is an advantage. But if you want to learn more, definitely check this out. Next piece of content Vacuum at the page level this is from Boring SQL.com and this pretty comprehensively goes over how vacuum works, as he says, byte by byte. So they take a snapshot of the page before and after each vacuum phase to see what vacuum does at each step for every page, essentially using the page inspect, page visibility and free map space tools. So if you want to learn more about that, definitely check this out. Next piece of content looking forward to Postgres 19 checksums for all. This is from pgedge.com and he's talking about the new feature where you can enable PG checksums without having to completely restart the database and you essentially run this command select pgenable data checksums and it quote kicks off the whole process in the background, but you can also throttle it as well.
[00:16:12] So check out this blog post if you want to learn more details about that next piece of content. Waiting for PostgreSQL 20 admin and max aggregate search support for UUID. This is from depeche.com and you can now min and max UUIDs, whereas you couldn't before at least once Postgres 20 releases and he says, you know, quote obviously will make the most sense with UUID version 7, which is timestamp ordered at the beginning.
[00:16:40] So that's pretty cool. And next piece of content also from depeche.com is waiting for postcard school 20 add backend level locks statistics. So now you're able to look at lock stats by backend.
[00:16:52] So again, another enhancement that might land in version 20.
[00:16:57] I hope you enjoyed this episode. Be sure to check out scalingpostgrads.com where you can find links to all the content mentioned, as well as sign up to receive weekly notifications of each episode. There you can find an audio version of the show, as well as a full transcript. Thanks. I'll see you next week.
