Skip to content

PLANETSCALE 2026-04-11

Read original ↗

PlanetScale — Keeping a Postgres queue healthy

Summary

PlanetScale's Simeon Griggs revisits Brandur Leach's 2015 "Postgres Job Queues & Failure By MVCC" finding on modern Postgres (18), then shows that modern Postgres has raised the floor but not removed the ceiling: FOR UPDATE SKIP LOCKED + B-tree bottom-up deletion lift Postgres-backed queues from a 15-minute death spiral to "survives longer with visible dead-tuple accumulation," but the underlying MVCC-horizon-pinning failure mode is unchanged. In a mixed-workload Postgres cluster (queue table + analytics queries + application transactions), long-running or continuously overlapping transactions pin the MVCC horizon, preventing vacuum from reclaiming dead tuples faster than the queue creates them. Timeouts (statement_timeout, idle_in_transaction_session_timeout, transaction_timeout) are blunt — they target individual-query duration but cannot limit workload-class concurrency. The article's product pitch is Traffic Control (part of PlanetScale's Insights extension): tag queries with action=analytics via SQLCommenter, apply a Resource Budget capping the concurrent-worker count for that workload class, and vacuum windows reappear between analytics queries. At 800 jobs/sec with 3 overlapping 120-second analytics queries, Traffic Control disabled produced a death spiral (155k-job backlog, 300ms+ lock time, 383k dead tuples, VACUUM blocked); Traffic Control enabled (max 1 concurrent action=analytics worker) kept the queue stable (0 backlog, 2ms lock time, dead tuples cycling 0–23k, VACUUM runs between queries, 15 analytics queries completed over 15 min).

Key takeaways

  1. Queue tables have a unique shape: most rows are transient. Inserted, read once, deleted — table size stays roughly constant while cumulative throughput is enormous. Every delete creates a dead tuple that the executor still walks on index scans ordered by run_at. An index-scan queue is especially vulnerable because the B-tree accumulates references to dead heap tuples, forcing the scan to traverse entries that point to rows no longer visible. (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy)

  2. FOR UPDATE SKIP LOCKED doesn't fix MVCC bloat. Griggs reran Brandur's 2015 degradation benchmark with the 2026-era best practices (SELECT ... FOR UPDATE SKIP LOCKED + batch of 10 jobs/txn) on Postgres 18 vs the original recursive-CTE pattern. The degradation curves were "almost identical" — both approaches scan the same B-tree index and encounter the same dead tuples. Lock time climbed from 1.3–3.0 ms at baseline to 9–29 ms (peak 180 ms at 24k dead tuples) — dead-tuple growth and MVCC dynamics are the load-bearing variables, not the lock-acquisition API. (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy)

  3. The MVCC horizon is pinned by the oldest active transaction anywhere on the cluster. Postgres will not vacuum away any dead tuple that might still be visible to an active transaction. One 2-minute analytics transaction pins the horizon for 2 minutes. More dangerously, three 40-second queries staggered 20 seconds apart keep the horizon pinned continuously — no individual query trips statement_timeout, but vacuum sees the same effect as one unending transaction. (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy)

  4. Postgres timeouts cannot target workload classes. statement_timeout (PG 7.3+) kills individual SQL statements; idle_in_transaction_session_timeout (PG 9.6+) kills sessions idle inside a transaction; transaction_timeout (PG 17+) kills any transaction exceeding a duration. All three are blunt instruments: they act on time, not concurrency, and they can't distinguish a high-priority transactional update from a low-priority analytics report. For the continuously overlapping failure mode, timeouts are impotent. (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy)

  5. What's actually needed: a workload-class resource budget. Tag queries (PlanetScale uses SQLCommenter tags like action=analytics), then cap the number of concurrent workers that can be executing that tag at any moment. Queries exceeding the cap are blocked and retried later rather than killed outright — the total work is preserved, only its instantaneous concurrency is smoothed. PlanetScale's Traffic Control implements this with three dials: server share + burst limit, per-query limit (seconds of full-server usage), and maximum concurrent workers (% of max_worker_processes). (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy)

  6. The quiet failure mode matters more than the loud one. The 2015 version produced a visible death spiral within 15 min. Modern Postgres produces a "quietly degraded equilibrium where lock times creep up, jobs slow down, and no alert fires" — the dead tuple count, not the queue depth, is the leading indicator. In a mixed-workload cluster this is the normal operating condition, not an edge case. (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy)

  7. Retry logic is mandatory if you throttle. When Traffic Control blocks a query, the application must retry at an appropriate time — the system is not doing less work, it's smoothing the rate at which work is performed. Traffic Control without retry logic in the caller just moves the failure from "database dies" to "queries fail." (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy)

Systems / concepts / patterns extracted

Systems: PostgreSQL, PlanetScale for Postgres, PlanetScale Insights, Traffic Control.

Concepts: Postgres MVCC + HOT updates (this source reinforces the MVCC half — dead tuples, vacuum, horizon), MVCC horizon (new canonical wiki concept), Dead tuples & vacuum (new), Postgres autovacuum (new), SELECT FOR UPDATE SKIP LOCKED (new), Postgres queue table (new), Write churn (new).

Patterns: Postgres queue on same database as application (new; Brandur-era caveats explicitly re-stated on modern Postgres), Workload-class resource budget (new; PlanetScale's Traffic Control is the canonical wiki instance).

Operational numbers

  • Original 2015 Brandur test bench: Ruby + Que v0.x gem on Postgres 9.4; put the database into a "death spiral" within 15 minutes at low workload.
  • 2026 Postgres 18 re-run (recursive CTE, matching 2015 shape) on PS-5 cluster ($5/mo starter tier):
  • Baseline lock time: 2–3 ms
  • End lock time (typical): 10–34 ms
  • Worst spike: 84.5 ms at 33k dead tuples
  • Queue depth: 0–100 oscillating
  • Dead tuples at end: 42,400
  • Throughput: ~89 jobs/sec
  • 2026 Postgres 18 re-run (SKIP LOCKED + batch of 10/txn):
  • Baseline lock time: 1.3–3.0 ms
  • End lock time (typical): 9–29 ms
  • Worst spike: 180 ms at 24k dead tuples
  • Queue depth: 0 (mostly)
  • Dead tuples at end: 42,450
  • Throughput: ~50 jobs/sec (producer-limited at 50/sec; not under-pressure — test not designed to compare throughput between the two shapes fairly)
  • Traffic Control stress test — 800 jobs/sec producer, 3 concurrent overlapping 120-sec action=analytics queries, 15-minute run:
  • Traffic Control disabled: 155,000-job backlog, 300+ ms lock time, 383,000 dead tuples at end, 3 concurrent analytics queries, VACUUM blocked (horizon always pinned) — death spiral.
  • Traffic Control enabled (analytics cap = 1 worker, 25% of max_worker_processes): 0 jobs backlog, 2 ms lock time, dead tuples cycling 0–23,000, 1 analytics query at a time + 2 retrying, VACUUM running normally in windows between queries, 15 analytics queries completed in 15 min — completely stable.
  • Postgres timeout history: statement_timeout introduced in PG 7.3, idle_in_transaction_session_timeout in 9.6, transaction_timeout in 17.0.
  • Autovacuum defaults: autovacuum_naptime = 1 minute between launcher checks; table-level trigger is autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples.

Caveats

  • Product-marketing post with architectural content — straddles the Tier-3 borderline. Scope test clears: the MVCC / vacuum / horizon / queue-table analysis is a canonical clear-in on database-internals grounds; the Traffic Control section is the product pitch but describes a real, named, novel mechanism (not available in upstream Postgres) with measured outcomes. Include.
  • No head-to-head with external queue systems (Oban, pgboss, GoodJob, SQS, Kafka) — the post argues Postgres queues are still workable if you control mixed-workload concurrency, not that they beat purpose-built queues.
  • Workers assumed sub-millisecond — the examples assume workers do the job work fast and hold the transaction open only briefly. Long-duration worker transactions are a separate failure mode the post doesn't deeply analyze; the MVCC-horizon argument applies to them too.
  • Traffic Control availability — exclusive to PlanetScale Postgres. Upstream Postgres does not have an equivalent. Self-hosted Postgres users can approximate via pgbouncer pool-mode limits + application-side rate-limiting + retry logic, but that's not a direct substitute for query-tagged class-level concurrency caps.
  • idle_in_transaction_session_timeout DOES catch the original 2015 long-runner (it's an idle transaction). Griggs explicitly switched the demo to overlapping, continuously-active analytics queries to show a failure mode timeouts can't catch.
  • Throughput discrepancy (89/sec CTE vs 50/sec SKIP LOCKED) is not a lock-strategy comparison — both implementations are producer-rate-limited at 50 jobs/sec; CTE workers outpace the producer while batched SKIP-LOCKED workers drain the queue and spend time in backoff sleep.
  • B-tree bottom-up deletion improvements (Postgres 14+) and scan-driven removal of dead index tuples are mentioned as contributing factors to the improved 2026 baseline, but not quantified on their own.

Source

Last updated · 319 distilled / 1,201 read