Protect your shared DB with "chained pages"

TL;DR

❌ Don't fan out one Pub/Sub message per row when a single human click of a button can touch tens of thousands of rows on a shared Postgres cluster.

❌ Don't raise the "go do the work" event outside the DB transaction that made the work necessary.

✅ Use chained pages: one event processes one page of rows, then raises the next event carrying a cursor. The chain ends on a short page, one with fewer rows than the page size.

✅ Raise the first event inside your DB transaction so the DB update and the domino work are both performed, or none of it is done.

Intro

"Chained pages" is not an industry term. It's the name I’ve given to a combination of a few well-known patterns (see Related Patterns below) while working on an internal ticket. The shape is useful enough that I want it written down before I forget why I did it this way.

What’s the backstory?

Registered agent contacts are immutable history. When a customer service agent edits a provider's office contact (think ZenBusiness' Texas office), our backend API doesn't overwrite the row. We added a new end-point POST /v1/contacts/:id/supersede which demotes the old contact to on_file and inserts a brand-new primary contact with a brand-new uuid.

The issue is, now every subscription in that jurisdiction for has an assignment row pointing at the old contact. Each of those needs a REPLACE: deactivate the old assignment and insert a new one pointing at the new contact. Why? Because we want to preserve history and be able to answer CS questions like who was the RA contact in 2026? What was the RA address two years ago?

This demotion creates a problem however. A customer service agent looks at an internal tool and notices that the address isn’t correct (the latest address). Our customers log into their dashboard and see an old address that isn’t correct. This is a UX issue which may result in higher calls to customer service – costing the company money 💰

All subscriptions to RA get an accompanying assignment record. Assignments are immutable history too, so we couldn’t just update the assigned_uuid via UPDATE ... SET assigned_uuid = ....

When we measured prod on Oct 16, 2026, ~3400 assignments in Alabama and ~1,400 in Mississippi were still pointing at the old contact.

Bear in mind once the new contact is inserted, all new subscriptions will automatically pick up the new primary contact in the table. It is the existing customers who see incorrect data 😅

We originally were going to provide a “fix it” button to CS on the Admin UI to repoint the contact for a business. But in a state like TX with almost 50K active subscriptions, who wants click a button fifty thousand times?!

How big can one click get?

The worst case today is ZenBusiness in TX with ~50K active assignments. A REPLACE is two writes per row (deactivate + insert), plus two more writes to a outbox table so internal domain events can be raised (RA_ASSIGNMENT_ENDED for the old row and RA_ASSIGNMENT_CREATED for the new one). That's four writes per row, so one supersede could mean about 200K row writes on a cluster shared by other databases.

The Technical Problem

My first instinct was the classic fan-out: one event per subscription, let the subscriber pods chew through them.

Two problems with that:

• The fan-out IS the burst. Our events go through the magical PGED service I talked about in my last post. So 50K events means 50K outbox INSERTs, all at once, on the same shared cluster.
• Concurrency becomes whatever the config says today. Our pub/sub subscribers have no flowControl set (see Google's own docs on the topic), so that's the default maxMessages: 1000 per pod × 10 pods in prod: up to 10,000 messages in flight. At the database, the only thing holding that back is the knex pool (we don't override it, so it's the plugin default of 7 connections per pod, 70 in total).

Are we solutionizing for a problem that isn't a problem?

This section is for anyone new to software engineering, because the question matters more than the answer.

I asked myself exactly this before writing a single line of code. Chained pages is more moving parts than "loop over the rows". A new event, a handler that raises itself, a cursor, a guard. If the database can take the hit anyway, why bother?

Short answer: the database could have taken it. If the shared cluster were the backend API's own database, I would have been much more relaxed about a one-off burst.

But it isn't ours. It's shared by N databases. That's what gave me pause. The things we were actually protecting are:

• Blast radius. One CS click on an admin screen shouldn't be able to spike load for N other databases that have nothing to do with registered agents. The other teams on that cluster never signed up for our burst.
• Lock time. Long transactions holding locks on assignment while customers are trying to buy things.
• Recoverability. If the transaction dies halfway through, what's left, and how do we restart it? With chained pages, the cursor tells you, and re-raising the first event is safe.

None of these are "the DB will fall over". They're "we're good neighbors on shared infrastructure, and we can explain what happens when something goes wrong".

So, the habit worth stealing: ask the question out loud, measure, then write down why you did it. "We did it just coz" is not a reason. "The DB can't take it" wasn't true here either. "The cluster is shared and we want bounded, recoverable load" is a reason you can defend in a PR review.

The Solution: Chained Pages

One event, one page. The current event raises the next one until we’re finished with the chain.

The important bits:

• Keyset pagination, never OFFSET. Offset re-reads every earlier row on each page, so page 47 reads 47,000 rows to return 1,000. It also counts positions rather than rows, so a subscription cancelled mid-run shifts everything after it and the next page silently skips one. A cursor (uuid > lastUuid) costs the same on every page and can't skip rows.
• Skipping finished rows makes it safe. A row already on the new contact is read and skipped, never rewritten, so a redelivered page, a double-click or a restart from cursor: null all just skip finished work.
• Exactly one message in flight per run. Load is serial by construction. No flow-control setting, pod count or autoscaler can make it burst.
• The cursor IS the progress. Want to know how far along it is? Look at the last cursor.

Choose fan-out when each item is expensive, independent and benefits from parallelism (e.g. calling a slow third party). Choose chained pages when each item is a cheap DB write and the real constraint is not hurting the database.

What if the chain runs twice?

A double-click, a page redelivered after it already raised the next one, or a manual re-run can all start a second chain for the same jurisdiction. The data stays correct. The run ids don't protect it, since they're only for tracing. Two other things do:

• One after the other: the second chain finds every row already on the new contact and skips it. In dev, the re-run wrote nothing.
• At the same moment: both chains read the same assignment before either writes. The first closes it with UPDATE … WHERE status = 'active' and holds the row lock. The second waits, re-checks the row once the first commits, finds it no longer active and updates nothing. Its insert then hits the unique index that allows one active assignment per business and jurisdiction, and it rolls back before raising any events.

Lessons Learned

• Same pool ≠ same connection. A transaction lives on one connection. If your library grabs its own, it's not in your transaction, no matter how the pools are wired.
• Keep the design simple. Pinning a DB connection or passing the transaction around would have worked, but passing the existing connection was a one-line option.
• Ask whether the problem is real. The DB could take it. Blast radius and recoverability on a shared cluster were the actual reasons, and they're better reasons.
• Let the database be the last line of defense. The skip check handles the common case. The row lock and a unique index handle the race, so a duplicate chain can waste work but can't corrupt data.
• Measure before doing anything. Keep your decisions grounded in data rather than vibes.

Related Patterns

None of this is new. Chained pages is just these stuck together:

• Job chaining: a job enqueues the next job from its own handler. See Salesforce, Control Processes with Queueable Apex.
• Query cursors for long jobs: carry a resumption point so a later task picks up where the last one stopped. See Google App Engine, NDB Queries.
• Keyset pagination (the seek method): Markus Winand, We need tool support for keyset pagination.
• Transactional outbox: Chris Richardson, Pattern: Transactional outbox.