
Sub-transactions throttled our Postgres-backed queue
How savepoints quietly throttled our Postgres queue
以下正文同步自 Hacker News 最新,版权归原站所有,已转换为易读排版。
All postsEsc
How savepoints quietly throttled our Postgres queue
A dispiriting performance ceiling
An anatomy of a function call
acquireAndTick
acquire
tick
The unbearable slowness of acquisition
LWLock:MultiXact-ly!
Not the standard failure mode
How we fixed it
Why claimed_until isn't indexed
One claimer per pod
Well, what have you got to show for it?
How savepoints quietly throttled our Postgres queue
Rory Malcolm
October 6, 2026 — 18 min read
At incident.io we are huge fans of Postgres; we've written about it a lot over the years, including how to choose the right indexes and how we're proud of being boring (©The Pet Shop Boys).
We use Postgres as our primary transactional database, which as of today has ~900 tables, and counting! The vast majority of our codebase does something along the following lines: read some data from Postgres, execute some business logic, then write that data back to Postgres.
One of the most complicated uses of Postgres we have in our product is a component of our On-call system we call the “ticker”. The ticker's job is to make sure that every active escalation is in the right state, checking who's acked or nacked, and progressing the escalation to the next level of its path if needed. At the moment, we do this about 15 million times a day.
The ticker's indicator of what escalation needs to be worked and at what point is a Postgres-backed queue, very similar to the shape described by Brandur (Postgres Job Queues & Failure By MVCC), whose work we're also big fans of.
In this post we're going to cover a number of performance pitfalls you can fall into with these systems, and how we solved them. One of the worst possible ways to learn a lot about Postgres internals is by debugging something like this while it's hurting production; we've learned a lot from load testing that can hopefully help you avoid these mistakes.
The end result of running through all of these performance tricks was a 3.5x improvement in our peak escalation capacity at our current size, as well as removing a number of bottlenecks that damped our ‘scaling curve'. We're really excited about these results, and this post will walk you through what we learned.
Recently, we've been doing a lot of load testing as part of the reliability work we're doing across the platform. We want to understand how our system creaks under the most extreme load. When we do this work, we disable a number of protections we run in production, like rate limiting. We want to see what performance looks like without these constraints so we can push the system to its limits, with a view to doing performance work that enables us to raise these limits for customers.
We wanted to be able to fully saturate our database doing useful work: as the number of concurrent workers (and work to be done) rose, we'd reach our eventual CPU ceiling, at which point we could get a bigger CPU. Unfortunately, however, there were bottlenecks in our system that slowed us down before we were able to saturate the database. There was something about our implementation that was causing us to not be able to use all our cores for work all the time. Given turbopuffer's conjecture “Typically when there's a large gap between napkin math and reality, it means there's either a big opportunity for optimisation or a bug in our understanding” (yet to be disproven by Fable), there had to be performance gains available to us that were within reach, so naturally, we went looking for them!
I'm going to be talking about some ticker internals in this post and how they got slow under load, and then how we fixed that, so it's worth us breaking out what these terms mean and what they're doing under the hood.
In the ticker the main ‘work loop' was calling this function on repeat — it has two constituent parts, acquire (which fetches escalations to tick) and tick (which actually ticks them, more on that later).
The acquire function is remarkably simple: the heart of it is a database query that looked not unlike this:
SELECT * FROM escalations WHERE next_due_at <= now() ORDER BY next_due_at ASC, id ASC LIMIT $batch FOR UPDATE SKIP LOCKED;
This query is normally very fast. Let's break it down:
The latency we expect of an acquire function in production is a p50 of 10ms and a p95 of sub-20ms.
Next up, once we have the work, is to tick the escalations we've acquired. The logic was basically as simple as “loop through the escalations we've acquired, and call tick on each one while we hold a lock against the batch”.
We previously (we'll come back to this) ran each tick inside its own subtransaction after claiming the rows to be ticked, using Postgres' SAVEPOINT command (SAVEPOINT). This meant that a single tick transaction failing didn't dirty every tick in the batch.
Ticks are a more business-logic-intensive operation than pure acquisition, and the operation is more ‘branchy' because of this: some ticks need to advance an escalation to the next level of an escalation path, notifying new users and looking up a round-robin policy when doing so; others are ‘no action required'.
This means latency here is a little less regular than acquisition, but we expect the p50 to roughly be ~25ms with a sub-100ms p99.
Anyone that's a seasoned Postgres-backed-queue blog post enjoyer knows where this goes next: under load, acquisition began to slow down.
Under normal load, the ‘seconds per second' of work distributed inside the ticker is about 5:1 in favour of ticking. Under heavy load, acquire began to dominate the workload entirely. Even worse, adding more ticker workers couldn't help us ‘burn through' this; an increase in workers just increased the amount of acquisition we were doing.
But why? Why had we slowed down? To this point?
There's one telling metric every time you see a slowdown like this in a queue: the amount of data you're fetching from shared_buffers for the table you're acquiring from. Our first tests at heavy load peaked with us fetching 136 GiB/s of data from shared_buffers for that table. It's worth saying that in Postgres this metric counts the total size of the pages you're accessing logically, so we're not actually loading that much, but still — this is a problematic number, equivalent to about 6 Blu-rays, or, for the younger readers (we have interns every year — apply!), roughly 160 hours of TikTok, every second.
This all points to there being a specific underlying failure mode, and to understand it, we need to dive into how Postgres stores data on disk.
Postgres stores its row data in tuples; you should read this Crunchy Data post if you're unfamiliar with the concept. One row can have many tuples, each representing that row's data at a particular point in time. The reason for this is that different transactions might need to see different versions of a given row's values, so we can only clean up old tuples when all the transactions that need to see that data have finished.
When acquisition slows down, this usually means some process is blocking this cleanup: dead tuples are accumulating but nothing is pruning those old rows.
If we're going to continue using a Postgres queue (we are, for the next little while — another post on what we did next will come later!), this isn't really something we can get away from. We do a number of things to make sure that we don't get into these situations in the first place, for example, we ban long-running transactions. However, cleanup can be held back by any number of processes, including operational things like read replica replication.
One thing we can do is make each tuple smaller. For us, this meant moving the queue from the escalations table (which contains all information about an escalation) to a table named escalation_jobs, which is the minimal queue representation of the work to be done for a given escalation. On disk, this means that a given escalation_job is anything between ~15x and 60x smaller than an escalation.
During our load tests we started to see a rise in LWLock waits in GCP Cloud SQL's metrics. As we increased load, the percentage of time the database was spending waiting on internal Postgres locks climbed and climbed. Eventually, it began to totally dominate all of the time we were spending in the database.
We thought that this would've been solved by our earlier work: a common issue people hit with this type of workload is LWLock:LockManager waits. Matt Smiley from the GitLab reliability team goes into this in great detail here.
In earlier versions of Postgres, too many relations (indexes, foreign keys) on a single table could cause slowdowns in performance. The older escalations table fit this shape, but moving to escalation_jobs, which didn't, had no effect.
Cloud SQL's Query Insights only shows the class of wait event, not the details of the wait event itself. We could see it was an LWLock, but there are many flavours of LWLock — we needed to be sure we were targeting the right one.
Follow this section along with this visualisation — it'll help you understand what's happening inside the database!
Running a load test again while sampling pg_stat_activity began to show something else: the flavour of LWLock we were specifically hitting was LWLock:MultiXact.
Well, you know how earlier we spoke about how we used subtransactions to tick each escalation? Internally to Postgres, transactions are tracked via a reference to the xid of the transaction that created or locked a given tuple. When you lock a row, Postgres records the locking transaction in that tuple's xmax field. That works perfectly when exactly one transaction holds a lock on a row — but xmax only has room for one identifier.
We're in a bit of a bind here — these rows are locked by more than one transaction (the subtransaction and its parent), but we have no way of indicating that.
To get round it, Postgres maintains a separate ‘multitransaction' infrastructure to handle these scenarios. Multitransactions have their own ID range — separate from the basic transaction ID range. Postgres sets the tuple's xmax to the multitransaction ID (henceforth, MultiXact) and then sets some tuple-level metadata to say that this ID lives in the MultiXact space, not the normal transaction space.
These live in pg_multixact, a pair of on-disk structures, offsets and members, that Postgres reads through a small, dedicated in-memory cache. offsets tells Postgres where to look inside members for a given MultiXact ID, and members holds the actual list of transactions and the locks they hold.
This means that to check who holds a row with a MultiXact, we now need access to this data. If this data changed in flight, that would be a problem for query correctness, so access to it is guarded by, you guessed it, an LWLock!
Now think about what acquire does: SKIP LOCKED means walking past every row another worker is currently ticking, and in our world every one of those rows had a MultiXact in xmax, because every tick ran inside a savepoint. Each row skipped meant a lookup. Each worker we added meant more rows locked, more MultiXacts minted, and more workers queueing to read them, all through the same LWLock-gated cache. That's why adding workers slowed us down: each one just added more lock contention.
The good news about a problem you've caused yourself is that you can stop causing it!
Doing so here is pretty interesting: rather than wrapping each tick in a subtransaction, we split acquiring work from doing it.
Instead of opening a parent transaction up front, we run a query that looks like this:
UPDATE escalation_jobs SET claimed_until = now() + interval '10 seconds' WHERE escalation_id IN ( SELECT escalation_i
正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。