Episode Transcript
[00:00:00] It's pretty common for web applications to have some means of protecting what connections you allow to your web server or your application server. Maybe you're being spammed or someone's trying to hit you with a denial of service attack. There are open source tools available to help protect against these type of activities. So for example, I use Ruby on Rails and there's a library available called Rack Attack.
[00:00:28] Now in terms of postgres, I don't know if anything like this exists because pretty much every query will be executed as soon as it can be by postgres.
[00:00:38] The best thing postgres has is timeout limits, so you can put strict timeout. So hopefully certain queries don't take over the system. But let me know if you know some open source tools that actually do this. But we're going to talk about a proprietary solution that exists within PlanetScale that they just released called Database Traffic Control.
[00:01:01] And normally I don't cover too many proprietary postgres things, but I thought this was super interesting.
[00:01:08] But I hope you, your friends, family and co workers continue to do well. Our first piece of content is introducing Database Traffic Control. This is from planetscale.com and it's a solution to try to avoid spikes of bad queries or runaway workload hitting your database and bringing it down.
[00:01:29] So it lets you enforce flexible budgets for your database traffic. So they say you can decide quote in real time how much of your database's resources any given workload is allowed to consume. And you can define budgets across several different dimensions. A query pattern, because it's specifically tracking the queries that are being sent to postgres by an application name that's being sent by the postgres user or custom tags that you can add via SQL Comments.
[00:01:59] So you can have it run in a warn mode or in an enforced mode that actually stops those queries from executing. Now this is built into their PG Insights extension, so that already tracks the statistics for every query running. Now it has this new capability to be able to stop queries queries from running.
[00:02:20] So they have a couple of use cases. If you have an incident response, maybe you have a rogue query that's spiking the CPU and is affecting the entire database. You can find it in their Insights platform, budget its resource usage and stabilize the database until you get a fix in. You can tag queries by priority, say high, medium, low, and create budgets for each tier. So ideally low priority work won't take out the high priority work. You can isolate human from AI agent traffic or this seems like a novel use case, prioritizing paid tiers versus, I guess, free tiers. The next blog post related to this is behind the scenes how database traffic control works. And from what I understand, this is part of their PG Insights extension and they just added the additional capability to stop queries with it.
[00:03:15] So it install hooks on the executor run.
[00:03:18] So basically from what I understand, it lets the planning happen, but before it actually gets executed, it can halt that query execution. So you have a flow chart of basically how it works and it determines whether it's going to block, it's going to warn and execute the query, or just execute the query. Then they talk a little bit about their cost prediction to assign costs, how they use leaky buckets to determine utilization, and then the different rule sets that you create.
[00:03:51] And I think these rule sets are created in the planetscale control plane, but then on the database itself it of course has the PG Insights extension. So it is running real time with the database making these decisions. But the rule sets are actually done the PostgreSQL conf file. So those rule sets I think are created in the control plane, but then they're actually in the database, in the database configuration and they put the rules in the PostgreSQL conf files because they didn't want you to not be able to run a query to update the rule sets. So basically you can just update the configuration and I'm assuming do a reload.
[00:04:30] So again, I thought this was super fascinating.
[00:04:33] Again, it's not something available for open source, although maybe in time we hope that something like this could evolve in the Postgres ecosystem. But check it out if you're interested.
[00:04:45] Next piece of content. PG18 preserves planner statistics on upgrade even from PG14. This is from Postgresai.com and Postgres18 can preserve the planner statistics during a major version upgrade. But Nick was wondering, could it preserve statistics from older versions?
[00:05:04] So if you actually use the PGDump from 18 and run it for an older database, it does extract those statistics that you can then load into a version of the database that you're upgrading.
[00:05:17] So he's using PGDUMP statistics only to extract them from an older server. So he showed an example using PG16 and PG18's PGDump to do this.
[00:05:28] And he does make an important note is that of course extended statistics are not carried over or not preserved. So you do need to run say vacuumdb with the missing stats only to get those.
[00:05:42] Now this is great if you're using PGUpgrade to do your database upgrades, but if you're using something like logical replication to do the upgrade, I don't see this too much as a benefit and you should probably just run analyze on the new cluster itself.
[00:05:56] But check this out if you're interested. Next piece of content Database schema migrations in 2026 survey this is from ardentperf.com and he did a survey across many different open source projects and proprietary projects to see what they used when doing schema migrations. And his biggest takeaway was the framework provided by your programming language is the most common pattern.
[00:06:22] And that's exactly what I do. I use Ruby on Rails so I'm using ActiveRecord and their means of doing migrations. And the main reason is for anyone new coming onto the project using the code base, they're already familiar with it as opposed to something that's more unique now, he says in terms of the method of triggering the schema migrations, he says the most common pattern is that the application process itself triggers the schema migration.
[00:06:49] Following that is different kubernetes jobs that are doing it. So it has all sorts of information about different projects, the code base that they use and the techniques that they use to do schema migration. So a lot of information in the survey. So if you're interested, definitely check this out.
[00:07:06] Next piece of content Using patrony to build a highly available Postgres cluster part 3 haproxy this is from pgedge.com and this is the third and final blog post in the series about building a Petroni postgres cluster. And this one is on adding a routing layer to the environment using haproxy. So this will be one endpoint you can connect to to interface with the cluster and do the queries that you need. So of course they have read write endpoints as well as read only endpoints showing up here. And he says even though this example post was done on three servers each running every responsibility, typically you would have a separate ETCD system or to run your DCS and that could potentially manage multiple independent clusters. You would typically have haproxy on your separate servers. So you definitely have one endpoint to contact to interface with the cluster. And then of course however many database clusters you have will be separate servers.
[00:08:09] But if you're interested in it, definitely check out this blog post.
[00:08:12] Next piece of content waiting for PostgreSQL19 introduce the repack command now initially I was excited about this. Is this going to be online repacking of tables?
[00:08:24] No this seems to be more a this makes sense patch as opposed to adding new functionality. So basically they want the repack command to eventually replace the vacuumfull command, because vacuuming really has nothing to do with rewriting tables necessarily. So they want to get away from that call it repack as well as cluster, because cluster is a loaded term. It can mean so many different things. So they basically want to make the job that cluster does and the job that vacuumfull does a responsibility of repack. So there's different ways you can run repackage, which essentially rebuilds the table. And Depesh here checks this out and puts it through its paces and shows all the different ways you can repack basically with and without an index. But there's a way you can determine to make it a cluster index or not. But he's definitely hoping in the future there will be a concurrently mode which would allow the online rebuilding of tables. But that feature is not pretty present in this particular patch. But if you want to learn more, check this out Next piece of content Explains Other Superpowers this is from richyan.com and he's talking about Normally when someone starts using the database to explain a query, they use explain. Then they use Explain Analyze to actually see what the runtime results are. But there are other capabilities you should add to explain to get more information.
[00:09:53] Buffers. This is a great way to see whether you're reading from cache or whether you're actually reading from disk, he says. Postgres18 has memory usage related to planning. You can also add wall to determine how many wall records were generated by a particular query. And you can do settings to determine what the query settings were when this query was planned, as well as verbose to get even more detailed information.
[00:10:22] But feel free to check this out.
[00:10:24] Next piece of content pgregresql truly portable postgresql statistics this is from boringsql.com and this introduces a new extension because he built a regression tool he calls RegressQL.
[00:10:40] But part of the issue is that rel pages really doesn't transfer when you're transferring statistics, because the planner checks the actual file size. Well, this extension tries to prevent that because if you're running automated regression testing, you don't want it to use smaller file size, which you're probably doing.
[00:11:01] So this extension basically fools it into thinking it has the same size of the files as production. So if you want to learn more about that, you can check out this blog post.
[00:11:11] Next piece of content is actually a bullet book that's available called Mastering PostgreSQL. Accelerate your weekend projects and seamlessly scale to millions. And this is by Supabase and Manning Publications and you can download it for free. So just put in your contact information if you want to receive the book and the last piece of content. There was another episode of the Talking Postgres podcast. This one was on Building Postgres Services on Azure with Charles Feddersen, and he leads product for Postgres at Microsoft. So if you're interested in that, you can listen to the webcast here or watch the YouTube video down here. I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find the links of 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 and I'll see you next week.
