Zero Bloat Postgres Queue | Scaling Postgres 416

追加: 2026-05-11

[00:00:00]Thankfully, I've never had to deal with queue issues that much. Most of my larger clients have a queue system set up outside of Postgres. Maybe it's Redis or some other technology. And at a certain scale, you can run a queue in Postgres. For example, my current SaaS app uses Rails active storage library, which is essentially using a Postgres queue using for update skip locked, etc. But what if you wanted to keep your queue in Postgres, but you want to push the limits of what's possible when doing things like for update skip locked?

[00:00:39]Well, we're going to talk about that today.

[00:00:42]But I hope you, your friends, family, and co-workers continue to do well.

[00:00:46]Our first piece of content is actually a repository, and this is Nick's repository from Postgres FM.

[00:00:54]And just recently, he released this tool called PGQ that he describes as a zero-bloat Postgres queue built on top of battle-proven Skype's PGQ, one SQL file to install PG cron to tick. So, the first notable thing about this, this is not an extension.

[00:01:13]If you look at the code distribution, it's basically a lot of PL/pgSQL files, but you basically clone the repo and run a SQL file to install it.

[00:01:25]You need to have Postgres greater than 14 and have something that calls PGQ ticker periodically. So, you could use PG cron.

[00:01:35]So, apparently, you can set up like a 1-second tick, and then it reticks every 100 milliseconds, so 10 ticks per second, basically.

[00:01:45]So, that means this is available essentially everywhere. You don't have to install an extension to get started.

[00:01:52]But what really makes this interesting is the implementation. So, it does not use for update skip locked.

[00:01:59]And you can see this cool graphical representation where it's showing dead tuples rising with all the other queuing systems, whereas PGQ essentially has no dead tuples because it never updates or deletes.

[00:02:13]What it does is use snapshot based batching.

[00:02:17]So, it uses snapshots to determine what transactions are new within the queue table to be able to process. And then a truncate based rotation method for tables. So, it cycles through three tables at a time.

[00:02:32]So, that results in zero bloat, no performance decay, it's built for heavily loaded system just like the Skype PGQ architecture was built for.

[00:02:43]It has all the same Postgres guarantees, and it even works on managed Postgres on cloud vendors.

[00:02:51]Now, there is one trade-off for this, and that's latency because he says, quote, "PG ticks 10 times per second by default."

[00:03:00]So, jobs are going to have a 50 to 150 millisecond latency.

[00:03:04]And I think for a vast majority of use cases, that's perfectly fine, but it kind of depends on what you're using your queue for.

[00:03:11]He said you could maybe reduce the latency by doing 20 ticks per second.

[00:03:16]And he said there might be a way to push it even more, but if you need single millisecond dispatch, PGQ's not the right tool for that use case.

[00:03:25]But if you want to be stable under load without load, that's what PGQ was built to do.

[00:03:32]Now, the next blog post related to this is PGQ two snapshots and a diff. This is from the build.com.

[00:03:40]And of course, he's talking about PGQ.

[00:03:42]He talks about how current queues are typically made. You basically insert into some sort of a jobs table, then you use a CTE to identify the jobs you want to process using for update skip locked.

[00:03:57]Then you update that jobs table indicating which jobs were pulled off of the table and eventually you probably delete those rows. But once you start scaling this up you hit what he says Brandur called Q spiral of death. Where you have a long-running transaction of some sort holds back the global X men and auto vacuum can't reclaim the dead two poles, job table grows, index lookups slow down and then the inserts slow down because the indexes are full of dead entries.

[00:04:31]And then the skip block scan slows down because of all the extra two poles it has to sort through. That causes the throughput to drop and then the backlog grows and it's a never-ending cycle or a death spiral. And PGQ uses a different technique to do that. So like I mentioned it maintains three tables and it looks like you can specify independent queues and each queue will have its own set of three tables and it just cycles through and truncates a table once it's done. So there's no deletes, there's no updates ever to it.

[00:05:05]And the ticker that runs once per second although it looked like from the documentation it's actually 10 times per second populates a table that looks like this and it's basically storing a snapshot for each tick.

[00:05:21]So it's basically X men X max and the list of in progress transaction IDs at that moment in time. And when a consumer, someone who's connecting up to the queue to see what jobs are available runs basically takes the last tick and the current tick to determine are there any transactions that need to be processed. So you can see here where the visible in snapshot for the current tick but the last snapshot, it's not visible.

[00:05:51]So, the only changes that happened to this table are the inserts of the new jobs coming in. The only consumer hot path is just simply a select to say, "Hey, is anything available for me within this snapshot range?"

[00:06:05]And he, of course, goes into more detail about the implementation, also talking about the truncate. And essentially, the disadvantage of this is the latency involved with picking up each job.

[00:06:17]But he says, "You know, everyone's been setting up a similar queue system using for update skip locked, but this is an innovative way circa 20 years ago.

[00:06:27]Basically, what the name of this blog post is taking two snapshots and doing a diff between them to see what has changed in a queue that needs to be processed."

[00:06:37]So, check this out if you're interested.

[00:06:39]Another blog post related to this, potential consequences of using Postgres as a job queue. This is from richyen.com, and he talks about the typical queue using for update skip locked and some problems related to that.

[00:06:54]And he mentions some alternatives like using advisory locks instead, talking about PGQ that Skype developed, but is no longer really maintained. But we, of course, have the new PGQ. You could also use Redis or Kafka as well.

[00:07:09]Next piece of content, maybe PG backrest isn't dead. So, there's been a maintenance update that's been added to the PG backrest repository, where the maintainer, David, said, "Uh my inbox blew up, especially with messages from people or organizations who have PG backrest users of their own to support.

[00:07:33]They would prefer the project to continue with him as the primary maintainer."

[00:07:38]And he said he didn't think this was going to happen trying to do fundraising earlier, but now based upon of this influx of support, it appears quote all but certain that I will be able to secure enough funding to continue the project.

[00:07:52]And I'm assuming that means as the primary maintainer.

[00:07:55]And that this will be supported by a coalition of sponsors. As well as potentially bringing on another maintainer to distribute the workload and provide continuity in the future.

[00:08:06]And this is what I kind of suspected might happen because if there are organizations relying on this tool and recommending it, they're probably going to step in to try and save the project or alternatively choose another solution.

[00:08:19]So, this is definitely interesting outcome. Check this out if you're interested. Uh next blog post related to that PGX backup continuity support for PG backrest. This is from the bill.com.

[00:08:32]And apparently they have forked PG backrest to make PGX backup.

[00:08:38]That's because that's what the request with David is was that if it's going to be forked that you should rename it. And it lists the support that PGX will be providing for this backup solution. But I wonder if this will be necessary based upon the previous article. We'll just have to see.

[00:08:56]Next piece of content, multi-exact members at 64-bits. One last wrap-around to worry about. This is from the bill.com.

[00:09:03]And there's been an enhancement in Postgres 19 setting the multi-exact offset to 64-bits.

[00:09:11]Now, this is not the multi-exact ID.

[00:09:14]That still has the same limit, but these are the members. And I believe we covered a blog post a number of months ago where there was an organization that hit the PG multi-exact members limit.

[00:09:27]And they were doing things like trying to track disk space to monitor the size that that was at. But apparently it's been updated to 64-bits.

[00:09:36]So, that should no longer be a problem.

[00:09:39]But, he said one downside of this is that when you are going to be upgrading, if you run PG upgrade, it's going to have to take a while to run this type of migration due to the size change.

[00:09:52]But, if you want more insight into this, you can definitely check out this blog post.

[00:09:56]Next piece of content, it depends using session variables in Postgres. This is from pgedge.com.

[00:10:03]And he's talking about how you can use variables in Postgres, and it's basically using session variables.

[00:10:10]First, he looks at what MySQL does. You can just use a set command to set a variable and then use it directly in queries.

[00:10:18]With SQL Server, it's a little bit more formal, but you can declare variables the same way.

[00:10:24]In Oracle, it has its own syntax as well.

[00:10:28]Now, if you use PSQL, you can set PSQL client-side variables to do this type of thing.

[00:10:36]But, if you're trying to do it within an application, that won't work because it's only through PSQL.

[00:10:42]So, you can set session variables, and as long as you include a dot in the variable you're naming, it will accept it as a custom variable. So, you can create a variable to set anything you want, as you can see here.

[00:10:56]And then you can show it. But, the problem is you can't use these in queries because these are essentially commands, set, show, reset, etc. But, what you can do is use methods that set these session variables. So, you can do set config to set it, or use current setting to extract it or get it.

[00:11:17]So, he shows various different examples of using that.

[00:11:21]So, if you want to learn more about this, definitely check out this blog post.

[00:11:25]Next piece of content, Cybertec's contributions to PostgreSQL 19. This is from cybertec-postgresql.com.

[00:11:32]And a lot of these have been covered in previous episodes of scaling Postgres, but I did want to say that the technique that they're using for creating repack concurrently is a result of working with the people at Cybertec who developed PG squeeze because they were using the technique to do logical replication to keep the old table and the new table in sync and do the changeover. So, thanks to all their work to get this new feature into Postgres.

[00:12:00]Hopefully, it'll make it into Postgres 19. They also mentioned various documentation usability enhancements, as well as getting Postgres to run on all the different features of Debian operating systems. So, if you want to learn more, definitely check this out.

[00:12:16]Next piece of content, PG Keeper, building the bouncer we needed for Postgres. This is from figma.com.

[00:12:22]And this is yet another connection pooler for Postgres, and Figma decided to build their own. So, they were using PG bouncer for a time, but they were hitting limits with regard to that.

[00:12:35]Mainly, it is single-threaded, so I understand that is a nuisance.

[00:12:40]They also couldn't prioritize any particular traffic. They wanted to have better connection management logic, and they wanted some better operational tooling, essentially.

[00:12:51]They did also evaluate PG Cat as well, so that did give them a multi-threaded pooler, but adding all the different observability features they needed and feature flagging and admission control, they felt there would be too many changes to address, so they built PG Keeper instead.

[00:13:10]So, if you want to learn more about the tool that they built, as well as its rollout, definitely check out this blog post.

[00:13:17]Next piece of content, Christoph's seven rules of disaster response. This is from the build.com.

[00:13:23]And this first one is wind your watch, which is to his point is basically take a deep breath and take your time to assess the situation. Don't react too quickly. The second is no one on the call who doesn't have something to do. So, basically a small focused team. Three is have a final decision maker.

[00:13:42]Don't have a bunch of developers sitting around on the call wondering what to do.

[00:13:46]You definitely need to have a decision maker there.

[00:13:49]Next is analyze, don't argue.

[00:13:52]So, basically rely on the data to lead you where it leads as opposed to trying to be right, for example.

[00:13:58]Next is point and call. Explicitly say, "All right, our plan is to do A and then we are going to do A now." Explicitly call out what you're doing.

[00:14:09]Next is appoint someone to deal with senior management. So, designate someone for communications so that management's kept up to date, but ideally keep them off the call.

[00:14:19]And lastly, no postmortem until after the corpse is cold. Basically, wait for the issue to be resolved and then you can examine what went wrong and why.

[00:14:30]So, I thought these were great guidelines to cover.

[00:14:33]And the last piece of content, how are committers selected? This is from vander.me.

[00:14:38]And he goes through how Postgres committers are selected and chosen for the project in terms of who does it and the process and general criteria. So, check this out if you're interested.

[00:14:51]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 find an audio version of the show as well as a full transcript. Thanks.

[00:15:08]I'll see you next week.