Replacing our Postgres queue at 45 million polls a day

At its worst, one of our Postgres tables held about 99,000 live trigger rows while dead tuples, including its TOAST data, reached 872 million.

The live rows were polling triggers waiting for their next run. The dead tuples were old versions left behind by writes, still taking up space because vacuum hadn't caught up. Every second, a scheduler asked which triggers were due. Answering that question meant walking an index full of dead entries.

We moved the scheduling decision out of Postgres. Temporal workflows now hold the timers and dispatch polls in batches. Postgres still stores durable trigger state, but it no longer has to find the next poll to run.

A table that doubled as a queue

Composio Triggers let an agent react to events in other apps, such as a new email or a calendar change. When a provider can push events to us, we use webhooks. When it can't, we poll. Every minute or two, for every active polling trigger, we ask the provider what changed.

That added up to about 45 million polls a day. We were also building toward a billion tool calls a month, so the scheduler needed room to grow.

The first version used a Postgres table as the queue. A scheduler woke up every second, selected up to 800 due rows, and handed them to a processor. When a poll finished, we deleted its row and inserted a new one for the next run.

We expected it to last about six months. It ran for more than a year, and it got harder to live with the longer it ran.

The MVCC doom loop

Postgres uses multi-version concurrency control, or MVCC, to let transactions read and write concurrently. A transaction sees a consistent snapshot, so Postgres cannot overwrite a row version another transaction might still need. An UPDATE creates a new version, while a DELETE marks the existing version as deleted. Vacuum can reclaim an obsolete version only after no transaction can still see it.

That is a good trade for many workloads. Our queue rewrote every row every minute or two.

Each poll made four writes to its row, one insert, two updates, and one delete. The row was wide, with large encrypted columns stored out of line in TOAST, and it had six indexes. A heap-only tuple update, or HOT update, can avoid new index entries when the indexed values do not change and the new tuple fits on the same heap page. Only 18% of our updates qualified. The rest required index maintenance too.

Two details made the churn feed on itself.

First, a long-running transaction holding an old snapshot could prevent vacuum from reclaiming versions across the database. An idle session with an open transaction could keep the queue's dead tuples around even if that session had nothing to do with polling.

Second, the due-work query read exactly where the dead entries accumulated. It asked for the oldest fire times. Rows that had already fired were deleted and reinserted with later times, leaving the older end of the index full of dead entries.

Churn created dead versions faster than vacuum could reclaim them. Scheduler ticks slowed as they walked past those versions. Slower ticks held transactions and snapshots open longer, delaying cleanup further. A vacuum pass could take two to three days, and the dead tuples returned within hours.

Tuning autovacuum moved the tipping point without removing the loop. The combination of constant row churn and due-time scans was the problem. Because the queue lived on our primary database, its bad days affected everything else using that database.

Why we batched timers instead of sharding Postgres

Smaller tables would scan faster, but splitting them within one database would still leave them sharing a vacuum horizon. Separate databases would avoid that coupling at the cost of operating more databases, moving rows during resharding, and assigning shards to workers ourselves.

SQS and Kafka avoid Postgres dead tuples, but delayed delivery was another requirement. Our timers can run up to 60 days out. SQS message delays stop at 15 minutes. A Temporal workflow for every trigger would give us durable timers, but still leave us dispatching a separate workflow's poll on every fire.

EventBridge Scheduler feeding SQS was the most tempting alternative. At roughly 48 million polls a day, we were handling about 556 fires a second. That design would mean an invocation and an SQS message for every fire, plus a schedule update for every re-arm. Against the 1,000-invocations-per-second default quota we evaluated, there was room to start, and AWS can raise quotas. But the upstream work would still grow with every fire.

SQS consumers can receive messages in batches only after each fire has already been scheduled, invoked, and enqueued. We wanted to batch earlier, where the timers live.

A shard holding a few thousand timers can hand off everything due within the same few seconds as one unit. At 556 timers a second, full batches of 800 would require about 0.7 batch workflow starts a second. Our actual batches contain about 200 to 250 timers, so we start two or three batch workflows a second instead of dispatching every timer separately.

How timer shards work

The interface is small. A timer has an ID, a fire time, and a type:

type Timer struct {
    Id     string
    FireAt time.Time
    Type   TimerType
}

Callers arm timers with StartTimers(ctx, timers...). Calling it with an existing Id moves that timer, so re-arming is another call to the same API. There is no cancel operation. Each timer type names a processor that receives a batch of IDs.

Each shard is a Temporal workflow holding a min-heap of timers ordered by fire time. Callers hash the timer ID onto a consistent-hash ring to choose a shard. New timers arrive as durable Temporal signals.

Temporal persists workflow state, manages shard ownership, and replays a failed worker's workflows elsewhere. The timer-dispatch path does not query a database.

When timers become due, the shard moves them into a second heap of pending timers. It flushes that heap when it has a full batch or its oldest timer has waited MaxLag, which is 10 seconds for polling. The shard can sleep until the earliest fire time plus MaxLag, collecting and flushing work in one wake-up. In our measurements, fire lag is spread between zero and ten seconds and averages about five.

Each flush starts one batch poller workflow. It claims the trigger rows, polls providers, broadcasts new events, and commits the resulting state. Trigger rows still live in Postgres, now in a separate worker database. Claim and commit access individual records by key, so that database behaves like a key-value store. Commit writes the row, then re-arms the timer.

Scaling without direct coordination

A shard carries its heap forward with Temporal's Continue-As-New. Temporal caps that payload at 2 MB, enough for about 20,000 of our timers. We cap shards at 5,000 timers for headroom.

Shards write timer counts to a small utilization table. A controller workflow reads it every 30 seconds. When average utilization passes 80%, it increases the shard count to target 60% utilization. The controller and shards do not communicate directly, so either can restart independently.

Adding shards changes the ring. We use consistent hashing with bounded loads to limit how many timers move, and most movement is lazy. A polling timer lands on its new shard the next time it re-arms. An overloaded shard forwards long-horizon timers that will not re-arm for weeks.

What a timer promises

Moving scheduling out of a transactional database changes the guarantees processors need to handle:

• A timer fires at least once. Rebalancing can briefly leave the same timer on two shards.
• In normal operation, a timer fires no later than FireAt plus MaxLag. Provider latency and retries add to that delay.
• Processors check the trigger's durable row before acting and are idempotent, making duplicate or stale fires harmless.
• Each hand-off from a shard to a batch poller is a Temporal checkpoint. A worker crash does not drop timers, and replay rebuilds the shard's heap.

What changed after cutover

We cut over in September. Poll volume stayed roughly flat, at about 45 million polls a day before and 48 million after.

We made two changes together. Temporal took over deciding when polls run, and polling state moved from our primary Postgres database to a separate worker Postgres database. The worker database already handled other traffic. Its writes increased as it took on polling, while primary-database writes fell.

The before window is September 8 to 17. The after window is September 20 to 24. The roughly 700 million dead-tuple peak in this comparison is from that window, separate from the earlier 872 million peak.

Total row writes across both databases rose slightly, but the polling writes became much cheaper. Heap writes on the trigger table roughly halved. Dead tuples now roughly match live rows, and the worker database fits in memory. Primary-database CPU fell too, though less dramatically because that database serves plenty besides polling.

The old table had an index on next fire time for the due-work query. Every poll deleted and reinserted its row, forcing new index entries. The new table is never searched by due time. Claim sets a lease, and commit rewrites the poll state. In our measurements, 100% of updates to the trigger table were HOT.

TOAST writes went up by about a third. Every commit writes freshly encrypted poll state, and Postgres rewrites all of a value's TOAST chunks when it changes. Skipping that write when the poll cursor has not changed is next on the list.

We also designed the polling-state table so it could move to a horizontally scalable key-value store such as DynamoDB if needed.

Other things that need a clock

The shard workflow knows nothing about polling. Scheduled callbacks, our second use case, fire an HTTP request at a chosen time. Some keep-alive refreshes are armed 30 to 60 days out. Adding callbacks required a new timer type with its own batch size and MaxLag, without changing the shard workflow. Webhook subscription refreshes are next.

There are costs to this design. Without Temporal, we would have to build durability, shard ownership, and failover ourselves. Batching adds up to MaxLag of latency. At a few thousand timers, a plain Postgres table can work fine.

For our workload, separating scheduling from durable state removed the expensive due-time scans and let us batch before dispatch. Postgres kept the work it handled cheaply, primary-key lookups and HOT updates.

We could have sharded the database. Sharding time turned out to be less work.