Episode Transcript
[00:00:00] So when are we getting Postgres 19? Usually all major Postgres releases are in the fall, at least historically. But it looks like two major features have already been removed from Postgres 19, and as of right now the release date seems a little uncertain. So that's some of what we'll cover this week, but I hope you, your friends, family and co workers continue to do well.
[00:00:24] Our first piece of content is actually this week's episode of Postgres Weekly.
[00:00:29] Now normally they just post the links and then a summary of what's on those links, but here it kind of reads out a little bit like a story itself.
[00:00:38] So Robert Haas posted a scary Postgres 19 patch contest to just take the pulse of where Postgres 19 is at and different feature sets and their readiness to be able to release when Postgres 19 releases. And potentially as a result of that, the PGQ basically the graph query interface has been dropped for version 19. All the other ones that he mentions are still in there, but also Merge and split partition has been dropped as well, and that wasn't even mentioned in his post. And I do have that post linked here, so if you want to read over it the different areas that were discussed so that source material is there, or you can click on the links in Postgres Weekly as well. And originally it looked like Postgres 19 was going to be released this month, but now there's going to be a fourth beta. The official release date is to be determined and there's additional information and links about this discussion, including quote that Tom Lane, the one who probably contributes the most to Postgres, was quite afraid of the SQL PGQ feature.
[00:01:49] You have David Rowley saying, you know, the revert bar can be a little bit lower this year, and part of this may be because of the volume of security CVE fixes that have had to been done this year with AI finding all these vulnerabilities. And Joshua Drake even suggested moving the release to spring of 2027 because he said, I'd rather have a late 19 than all these great features and hard work gutted. So we'll definitely have to see when Postgres 19 is actually going to be released, hopefully in another week or two or sometime after Beta 4 is released. We'll get some more insight into that. Another post related to that is two features just left Postgres 19. This is from Command Prompt.com he's talking of course about the SQL PGQ property graphs being reverted as well as alter table merge, split partition was reverted and has a little bit of information added with regard to the status of the the other features coming that still need additional development work done on them. But if you want to learn more definitely check out these resources.
[00:02:54] Next piece of content 100,000 lines of C later PG safe at PGday UK 2026 this is from virus.org and this is Jimmy Angelakos and apparently when PG Backrest had its support issues where they needed to get funding but were struggling to do that and I think it was David who said he would basically have to step away from the project, only then did support come in the form of corporations contributing to this open source project. Well what Jimmy did is he proceeded to build his own backup solution called pgsafe and it was written in scratch from Go and basically took the principles of PG Backrest and just rewrote it in Go.
[00:03:40] So he says it shares PG Backrest's concept and operational rules and none of its code. So he wants functional parity, but the code is going to be entirely different. So that would mean full and incremental backups, point in time recovery and five different storage backends. Now he said currently this is in the alpha state, so it's not ready to do production backups yet. And he goes into more detail about how he built it and he said this does include AI coding assistance, but it's not AI slop because basically the AI assistant was essentially supervised following his development principles, essentially. So he says, quote, the architecture tenants and invariants are mine and every code decision was reviewed and with this new tool it's basically half the size in terms of code compared to the C version. And he goes over how this works and at the high level Overview it also looks similar to pghard storage where you have a dedicated backup host and all it needs is a connection to the database to take backups and of course to archive the wall and it stores all that data in a storage system that could be a disk on the backup host or it could be S3, Azure, SFTP, etc. But the technical details of how the backup happened definitely closer to BG Backrest.
[00:05:02] But if you want to learn more about this project, definitely check out this blog post and the presentation as well. Next piece of content Inserting state transitions in Postgres this is from thoughtbot.com and this follows on a post they did about modeling state transitions in Postgres where they replaced a status column on the users table with a dedicated user statuses table. So you can basically see the history of status changes and how you would pull out the most current status for a given user. But this post talks about handling state transitions and conflicts with regard to that, because you can run into a race condition where transaction A reads the current status, sees it's pending, and then transaction B reads the current status, sees it's pending. Well, transaction A goes ahead and does its insert row into the user statuses and it says approved, whereas transaction B says it's so now in the database you see it's pending, then it was approved and then one second later it was denied. So you probably don't want this to happen.
[00:06:07] And he says if you had the status column on the users table, you wouldn't have the contradictory state, but you would mask the issue that something was approved and then suddenly denied. So how you handle a potentially concurrent update like this is used for update. So you do a select and you do a for update to lock that row while you do the update. So transaction A begins. Does a select for update to lock the row. Transaction B begins. It tries to do a Select 4 update, but it gets blocked. While this transaction is going on, it reads the status of pending and it says, okay, I can go ahead and insert. It's approved. Well, now this gets unblocked, it gets its lock, it reads the current status and it says approved.
[00:06:56] So now at this point you can decide what to do. Maybe you exit early with an error message or you send a notification to someone with regard to that. Maybe you update it to a different status if the decision was going to be denied. It basically catches and helps you understand how you want to handle this status. And he shows an example of the implementation here, here. And in this case he chose to do a rollback as opposed to transitioning from approved to denied. He says you can also use serializable isolation, but you may get some false positives and you're going to have to have retry logic. So in his opinion, the Select 4 update is the simpler and more predictable choice for this particular problem. But if you want to learn more, definitely check this out. Next piece of content. Percona operator for PostgreSQL 3.1.0 Transparent Data Encryption, Logical replicas and persistent logging this is from percona.com and so this is the new version of their operator and it includes their PGTDE extension by default. So if you're looking for an operator to run a Kubernetes cluster with this, this version has it built in as well as lets you set up a logical replica relatively easily for analytics and reporting purposes. P Because usually the Kubernetes cluster is set up as three systems, one a primary and two physical replicas.
[00:08:19] Because any of these replicas may become a primary, dedicating one to reporting can be difficult, whereas using logical replication to maintain another instance or copy of only the data you need to report on is a really good best practice. So they have that built in automatically and they do use PG backrest for backing up and now they do have persistent logging for that as well. So if you're interested, definitely check out this blog post.
[00:08:45] Next piece of content Is it safe to give an AI agent access to your production database? This is from tigerdata.com and their assessment is pretty much no.
[00:08:57] So it's not safe giving an AI agent unrestricted access to a production database and of course they follow on from there. Now it is acceptable in some cases to give it read only access to certain areas and there is a scenario where you might want to give it write access to a small portion of the data set. But you know, definitely keep a human in the loop for anything that writes or changes schema. Because these agents are non deterministic, they're more like people. So a deterministic system you can trust because you can explicitly have it follow a set of rules, whereas a non deterministic system like AI, it doesn't have to necessarily follow these rules and it's subject to poison data it reads from the actual data it's consuming. So this goes into a lot more detail, but basically I would just treat it like human access to the database, give it a restricted role to only what it needs to do and audit whatever changes are made very carefully.
[00:10:02] But if you want to learn more, definitely check this out. And the last piece of content testing the connection pooling in multigres. What does 100% pass rate mean? So they continue to move forward with multigres. So basically in their development they said we decided to run the same tests, pgregress, PG isolation, contrib and external extensions as is against multigres to see how it fares. So they said they had 100% passes on these tests, but what with a caveat. So it doesn't sound like 100%. They said it doesn't mean everything works against multigres identically to Postgres. But they have accepted certain divergences based on the fact of what they're trying to support in terms of connection pooling, safety and currently unsupported features, at least with multigres. So they explain a little bit why the proxy can't be byte identical. Part of it's due to the connection pooling, part of it's for query rewriting or even unsafe statements.
[00:10:58] And they have a section that says what pass really means.
[00:11:02] So they have an example here where maybe multigres outputs an additional warning when a user creates an unlogged table, the reason being because an unlogged table is not replicated to other instances and is lost on a failover. And then it calls out some of the differences as well. So it looks like multigres continues to move forward. And if you're interested in scaling out your database, definitely a project to watch.
[00:11:27] I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find links to all the content discussed, as well as sign up to receive weekly notifications of each episode there. You can also find an audio version of the show, as well as a full transcript. Thanks. I'll see you next week.
