Episode Transcript
[00:00:00] I've mentioned this before, but historically I've always rolled my own backup solutions. That means I have developed scripts that come with stock Postgres, so PGbase backup, PGDumps, etc. With a few clients I did look into PG Backrest, but ultimately we never switched to it. They mostly just decided to just rework their existing scripts and it's always been on my to do list to look into PG Backrest, but I still to this day haven't done it. Now there was over the last couple of months some turmoil with regard to the PG Backrest project that seems to be in a better place. But we have a very interesting new entrant into the Postgres backup space and we'll talk about that more today, but I hope you, your friends, family and co workers continue to do well Our first piece of content is PostgreSQL backup done right now. This is not actually a blog post, there is a blog post we'll look at, but this is a new website that has been spun up called pghardstorage.org and the actual tool is called pghard storage. It's an open source PostgreSQL backup tool and this is a project backed by Cybertech and it says it is version one, so I haven't even heard of the previous versions. It's an Apache 2 license and it supports PostgreSQL 15 and above. And for a project I haven't heard of, it has the most comprehensive website for it I've ever seen, talking about all the capabilities and features and why it was done so that alone is impressive. And here's basically how it works. It's a tool that resides on another server.
[00:01:47] You connect up to Postgres via replication slot that receives the wall stream. It then does a chunk and dedupe and assigns a shot to it. It encrypts it and then stores it in a repository. So it does it via chunks to make restores easier. And just the number of docs and examples is truly impressive for again something I haven't really heard of before.
[00:02:12] And looking at the how it works, you basically create a dedicated role on your database and grant it pgreadalldata and it has a content address stored so every backup is a full essentially and dedupe is free.
[00:02:27] So here's a quick example. They set up the connection and where you want it to write the files, the repo essentially and this is just a command line tool to take a base backup and then to start reading from the wall stream. So you would Want to put this in some type of systemd service, for example, and then if you need to do a restore, it's this third command.
[00:02:51] So again, this is just a single static binary. You don't install anything on your database. And looking at some of the examples, they give an example of a Linux server in a Petroni cluster, and just looking at the Linux cluster again, it shows create a role, run the command to do a backup, and then you can do a subsequent backup. But of course, if you want point in time recovery, you also need to set the wall stream. And again, using a systemd service would be the way to do that. And they show you how to do a point in time recovery. And this is great that you can restore a single table without rolling back the rest. So you restore the state to a sidecar directory and then you start Postgres within that sidecar on a spare port to copy the table out. If you want to schedule your backups, you just use a cron job and you can encrypt your backup using a local key or they also support multiple key stores as well. And of course you can also store your backups on object storage as well. So what I really like about this is the simplicity of it from the perspective of getting started and then incrementally adding the capabilities you need. If we look at the blog post that was posted about it introducing Pghard Store, a new community driven approach to PostgreSQL backup and recovery. This is from cypr.postgresql.com and he calls it a new open source community project focused on PostgreSQL backup and recovery. And they view it just as another open source option within the PostgreSQL ecosystem. You know, and this is an Apache 2 license, so it's built for the community to try out, work on, improve, etc. And it's interesting how they have presumably sidestepped the traditional incremental backup change like pgbase Backup just got incremental backups essentially. But if you want to learn more, definitely check this out.
[00:04:40] Next piece of content. The long road to bottomless Postgres. This is from pgedge.com and what he's basically talking about is tiered storage. So he says, you know, you start storing a lot of data, but after that data is a number of years old, maybe you hardly access it, and it would be great to put it into essentially cold storage. Now he says the first way you probably do that is partitioning, so you can partition your tables maybe by time, and then you can detach those partitions at certain points. At that point you can move that data somewhere else.
[00:05:13] And he even includes maybe you put it on another database system and use a form data wrapper to still be able to access that data if you need it. But but it no longer needs to be on, say, your primary database. So using a foreign data wrapper can be a great solution for that. Or taking it to the next level is converting your data into parquet files or Iceberg.
[00:05:34] And I think you could use a foreign data wrapper for that. But there are also a lot of other tools, mostly using DuckDB to be able to access that data as well, given it's in a column storage format. Now he talks about one solution, neon for having bottomless storage because they actually store all of their data in S3 and just retrieve it via page servers. So sometimes there can be latency involved with that, but it does truly give you essentially bottomless storage. Then he talks about DuckDB being able to access parquet files and Iceberg files, and mentions some extensions that work with Postgres to do it. But he's also mentioning a new tool that their company PGED has developed called Coldfront. So he talks a little bit more about that here. And I think the image in this next blog post introducing Coldfront seamlessly uniting OLTB analytics and AI workloads on Postgres. This image gives a good perspective of their tools. So it combines a recent version of Postgres with pgdocdb and a code for an extension.
[00:06:42] And you have heap partitions in the hot layer, so you have multiple partitions, but then you have a cold layer in Iceberg access via pgduckdb and some other tooling to be able to archive data from the hot tier to the cold tier on S3 and also using Lakeeper. So if you want to explore this bottomless storage concept, you can definitely check out these two blog posts.
[00:07:06] Next piece of content looking forward to Postgres 19 logically sequenced. This is from pgedge.com and he's talking about One of my top features coming in Postgres 19 is the ability to synchronize sequences. A few times that this has been tried or done before with pglogical. And even it almost got into Postgres 16 to synchronize sequences, but it was already reverted because sequences really weren't built for transactions, they're independent of it. For example, there's no concept of a rollback for a sequence once you've used it, you've used it you can't go back, but what they've introduced in 19 is not real time synchronization, but it's a one time sync. So it syncs them when a subscription is created, it syncs them when you alter the subscription and the publication is refreshed or you have an explicit refresh sequences that you can do when you alter the subscription. And essentially I think it's a command like this would replace the magic line or lines that synchronize all the sequences when you're doing a logical replication, upgrade to a new version of Postgres for example, alter subscription, name a subscription refresh sequences.
[00:08:18] But if you want to learn more, definitely check out this blog post.
[00:08:22] Next piece of Content Happiness Alarm on checkpoint time so he puts a few things at the top he says here one you you should keep your checkpoint timeout setting at five minutes. Now I've actually never heard that advice.
[00:08:39] Now there is a disadvantage of extending it because you're increasing your recovery time objective. But frequent checkpoints are a big burden on the system and spreading those out can reduce the workload on the system. But he definitely highlights the trade off with extending it. Second he says make sure log checkpoints are enabled. Definitely true. Third is set up alarming based upon how long checkpoints are taking so you can know if you're not checkpointing as frequently as you expect. And then lastly also look for checkpoints are occurring too frequently because that means you probably need to increase your Maxwell size. Now he talks about a few incidences where a lack of checkpoint monitoring caused real problems like one resulted in a 40 hour production outage which is pretty bad. It did happen a number of years ago, but what happened? They started getting application errors so they decided to restart the database but the database wasn't coming back up. And he said somehow there had not been a successful checkpoint on this database for a week, which is not good. And quote over the course of a week while check pointer was stalled and never up unlinked any of the underlining files for drop relation statements because they had temporary files. The system accumulated 27 million files on the file system until it ran out of inodes. And what took so long is the entire buffer cache needed to be scanned anytime a relation was dropped. So during that recovery process that's what took it forever to be recovered. So it was the buffer cache scans that were taking forever and they actually had to shrink the buffer cache to speed up the replay. Second instance was also due to the application having problems and the database being restarted and this was in cloud native PG and the issue was the hot standby instance never came back up. So the primary had high write activity. The checkpoints began to fall significantly behind up to an hour when the checkpoint timeout was five minutes and due to too much wall being replayed, it exceeded the 60 minute startup probe and it would kill itself. So basically each of these problems highlight tracking how your checkpointing process is doing and make sure it's relatively healthy. So for example, he gives an example of timeouts that he pulled from a log where you can see with a 5 minute checkpoint timeout it looks roughly like this, bouncing between say 20 seconds up to almost five minutes, whereas it indicates issues if you're seeing spikes up to 50 minutes or 60 minutes when you have a five minute checkpoint timeout. So this system is unhealthy in some way and definitely investigate what's going on. But if you want to learn more, definitely check this out.
[00:11:32] Next piece of content PGstats how Postgres internal stats work this is from Richyan.com this is from a presentation he gave at Posette 2026 and he talks about, you know, when Postgres plans a query, it doesn't necessarily look at the whole table to plan how to do it. It relies on statistics it collects from each table. He says imagine you have a million row table of customers here and you want to find all the California customers. And let's say you have an index on the state. You do a query but actually does a sequential scan and filters on the state. It's not using the index well, why not? And it goes into statistics, how they're collected, where they're stored. They store things like the number of distinct values, correlation, most common values, etc. And the planner is a cost based planner. So it takes into consideration the random page cost for random seeks on a disk, sequential page cost, and the cptuple cost of pulling each row out of a page. And the reason it gave a sequential scan for California was because Most of the rows 18% of the table was from California. And an index would actually be much less efficient and it gives a high cost estimate for it. Whereas if you did another state like Wyoming, it has no problem using the index. Andy also describes histograms as well in terms of statistics taken in and correlation between columns. So if you want to learn more about statistics, definitely check out this blog post. Next piece of content it's not magic, it's method. This is from mydbanotebook.org and she's talking about her ability to read source code, particularly for Postgres and Postgres extensions, to understand what they do and to potentially even be able to fix a small bug. She says it's not magic and it's not some rare gift, it's just the method she uses. She takes time to read and understand, maybe break some things, fix them, etc. So it's working through things to understand them better. And she actually goes through the process of looking at new version of a PGSQL tweaks extension to go through the process of looking at the source code to understand what it's doing. Can she understand the structure, how it works, and could she even fix a small bug? So as she said, it's basically not magic, it's method and taking the time to understand things. But if you want to learn more, definitely check out this blog post. Next Piece of content heterogeneous graphs in SQL PGQ on PostgreSQL 19 this is from cyber.postgresql.com and this is a more comprehensive example of using the new graph syntax in Postgres 19. So if you want a more comprehensive example, definitely check out this blog post and the last piece of content. All the slides for Posette, which is an event for Postgres that happened in 2026, have been posted, so if you want to look at the 44 different talks, they are now available.
[00:14:36] I hope you enjoyed this episode. Be sure to check out scalingpostgres.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.
