PostgreSQL: Performance Optimizations and Better Backup Tools

Transcript

Hey there, PostgreSQL enthusiasts! Welcome back to another episode of the PostgreSQL podcast. I'm thrilled you're here with me today because we've got some really exciting developments to dive into from March 21st, 2026. Grab your favorite beverage and let's talk about some changes that are going to make your database life a whole lot better.

So today we're looking at 16 commits with no merged pull requests, but don't let that fool you - these standalone commits are packed with goodness. The PostgreSQL team has been busy optimizing performance, improving backup tools, and making the developer experience smoother all around.

Let's start with the star of the show - Tom Lane has delivered what I'm calling a "stealth performance boost" for PL/pgSQL. You know how sometimes you write `SELECT some_expression INTO my_variable` instead of `my_variable := some_expression`? Maybe you're coming from SQL Server or you just think it reads better? Well, historically that choice came with a performance penalty because PostgreSQL was routing those SELECT statements through the heavier SPI machinery. Not anymore! Tom's optimization detects when you're doing a simple SELECT INTO with a scalar expression and automatically uses the faster assignment path. The best part? This is completely transparent to you - your code just gets faster without any changes needed.

Now, if you're dealing with backups and WAL files, Andrew Dunstan and the team have some fantastic news. They've completely revolutionized how pg_waldump works by adding support for reading WAL files directly from tar archives. This might sound technical, but here's why it matters: if you're storing your backups in compressed tar files, you can now analyze them without extracting everything first. The implementation is clever too - it handles out-of-order WAL segments by temporarily extracting them when needed, and it supports gzip, lz4, and zstd compression. Plus, pg_verifybackup now automatically finds your WAL archives whether they're in separate files or bundled in your base backup.

Tom Lane strikes again with another optimization, this time fixing how PostgreSQL estimates index costs. The database was accidentally counting metadata pages when estimating how many index pages it would need to visit, which could lead to some surprising query plan choices, especially with smaller indexes. Now it properly accounts for non-leaf pages, which means better planning decisions and potentially faster queries.

On the user experience side, there are some nice quality-of-life improvements. Nathan Bossart has made transaction wraparound warnings much more helpful. Instead of just telling you there's a problem, PostgreSQL now tells you exactly what percentage of available transaction IDs you have left. No more guessing about how urgent the situation really is! They've also bumped the warning threshold from 40 million to 100 million transactions, giving you more breathing room to respond.

For those working with data export, there's a new `force_array` option for COPY with JSON format. When enabled, it wraps your entire output in a JSON array, making it a single valid JSON document instead of a stream of objects. It's one of those features that seems simple but solves a real pain point when you're integrating with other systems.

Behind the scenes, the team has been doing some excellent refactoring work. They've introduced a cleaner CopyFormat enum system and moved tar detection logic to common code, making it easier to maintain and extend these features in the future.

Today's Focus: If you're using PL/pgSQL functions, take a moment to appreciate that your SELECT INTO statements just got faster automatically. If you're managing backups, consider exploring the new pg_waldump capabilities - they might simplify your backup verification workflows. And if you've been putting off dealing with those transaction ID warnings, the new detailed messages will help you understand exactly where you stand.

That's a wrap for today's episode! The PostgreSQL community continues to prove that the little optimizations and user experience improvements can make a huge difference in how we work with our databases. Keep coding, keep learning, and I'll catch you on the next episode. Until then, may your queries be fast and your backups reliable!