The slow kind of broken

Databases lean before they fall

The outages people plan for are loud. The process dies, the disk fills, the connection is refused, something turns red. Those are the easy ones, because something told you.

The failures that actually cost you a weekend are the other kind. Queries get a little slower every hour. Lock waits creep from two milliseconds to twenty to two hundred. Autovacuum runs constantly and reclaims nothing. Nothing errors. No alert fires, because no threshold was crossed - the threshold is just slowly moving toward you.

Most of these have the same root, and it is not the one people reach for. This release is about making that root visible, and about setting the two or three defaults that stop it becoming a bad week.

One open transaction

Postgres never overwrites a row. An UPDATE writes a new version and marks the old one; a DELETE only marks. The old versions stay because some transaction that started earlier might still be entitled to see them.

VACUUM cleans them up, and VACUUM has exactly one rule: it may only remove a row version invisible to every open transaction. So the oldest open transaction sets a floor, and nothing newer than that floor can be reclaimed anywhere in the database.

That means a single session that ran BEGIN and then stopped - a code path that returns early, an ORM holding a transaction across an outbound HTTP call, a psql window someone left open - holds the floor in place while the rest of your application keeps writing. Dead rows pile up behind it. Sequential scans read them and throw them away. Index scans follow entries pointing at rows nobody can see, paying a page read each time for nothing. The table grows, so less of it fits in cache, so everything gets slower.

The moment that transaction ends, the floor moves and the backlog clears. The problem does not taper off and it does not degrade gracefully. It holds, and then it is gone.

Every cell now sets idle_in_transaction_session_timeout to 10 minutes. It only kills sessions sitting idle inside a transaction, so a query that legitimately runs for an hour, a pg_dump, or an in-flight import is never touched. No correct application depends on idling inside a transaction for ten minutes.

We deliberately did not set the other three, and the reasoning matters more than the setting:

• statement_timeout - a long analytical query and a runaway query are indistinguishable from where we stand. Only you know which is which.
• lock_timeout - a schema migration waiting behind a long-running read is correct behaviour. Capping it fleet-wide would break migrations that work today.
• transaction_timeout - it kills active transactions too, so it would cut legitimate long imports and bulk jobs.

All three are worth setting; they are just yours to set. Timeouts shows how to do it per role with ALTER ROLE, which survives the pooled endpoint - unlike a session-level SET, which lands on whichever server connection you happened to borrow.

And there is a new Long-running transaction advisory in your dashboard. This sits next to the existing Vacuum advisory on purpose: the vacuum one counts dead rows, which is the symptom. This one names the session holding the floor, which is the cause. If you see both, fix this one and the other resolves itself.

The 64 you did not know about

This is the one we most wanted to ship, because until now it was completely invisible to us and almost impossible for you to diagnose.

Every Postgres backend caches the IDs of the subtransactions inside its current transaction. The cache holds 64. Past that it overflows to an on-disk structure, and here is the part that surprises people: while any one backend is overflowed, every other query in the database starts consulting that structure too, once per row it examines. Unrelated queries. Unrelated tables. Different connections.

The incident shape is distinctive. Throughput collapses. It stays collapsed for exactly as long as the offending transaction is open. Then it returns to normal the instant that transaction commits. Nothing errors, and no individual query looks slow in isolation, because every query got slower by the same mechanism.

What makes it worth knowing is that you almost certainly create subtransactions without typing SAVEPOINT. Every PL/pgSQL BEGIN ... EXCEPTION block is one, created each time the block is entered - so a function that catches unique_violation inside a loop creates one per row. So does a nested transaction in most ORMs. A thousand-row import loop with per-row error handling overflows the cache sixteen times over.

There is a new Nested transaction overhead advisory when a backend has been overflowed for a sustained period, and Subtransactions has the two queries that tell you which backend and whether it is reaching disk. The usual fix is ON CONFLICT instead of an exception block in a loop, which is not a micro-optimisation here - it is a different order of magnitude.

Memory, and the number actually worth alerting on

High memory usage in Postgres is mostly a sign it is doing its job. Memory is workspace: pages cached, sorts and hashes given room. An instance sitting at high memory with a warm cache is an instance answering queries from RAM.

So we did not add an alert on memory usage. We added one on OOM kills - the kernel actually killing a process inside your cell - read from the cgroup and surfaced as an advisory. That is the memory event that always means something went wrong.

It is deliberately an advisory rather than an email. If an instance is killed hard and stays down, the existing Database unreachable alert already notifies you through every channel. This new one exists to answer the follow-up question - why did it go down - which only matters once you are already looking.

A gap in our own recovery point

While reviewing the WAL path we found something we did not like, so it goes in here rather than in a changelog nobody reads.

Postgres ships a WAL segment to the archive when it fills - 16 MB - or at a shutdown checkpoint. Sleeping cells were always fine, because scale-to-zero checkpoints and stops, and the shutdown checkpoint is archived. But a cell that stays awake with a low write rate - a staging project, an internal tool, anything trickling a few kilobytes an hour - could sit on a partially filled segment for hours.

That gap, not the backup schedule, was that cell's real recovery point. Every archiving cell now sets archive_timeout, which forces a segment switch on a bounded interval regardless of how little was written. We picked 15 minutes deliberately: it bounds the exposure by an order of magnitude while keeping the worst case at roughly four extra objects per hour per awake cell, which is a cost we would rather pay than describe an RPO we were not actually delivering.

Prose is not a guard

Our MCP tools have always told the model to check its WHERE clause and to create a restore point before anything destructive. That text is good, and it is not a guard.

A model that skips an instruction - or one steered by text that arrived inside a query result, which our own tool descriptions explicitly warn is untrusted - does damage no restore point was taken for. An UPDATE with no WHERE rewrites every row in the table, and the previous values are simply gone.

So the control plane now refuses the shapes with no plausible intent behind them: an UPDATE or DELETE with no top-level WHERE, and any TRUNCATE. The refusal names the remedy, because the caller reading it is usually a model that can correct itself.

Two details we got wrong on the first pass and are happier for having caught:

EXPLAIN is not execution. EXPLAIN DELETE FROM users plans the statement and runs nothing, so refusing it is pure friction. EXPLAIN ANALYZE DELETE FROM users genuinely deletes. The guard now tells them apart.

An agent in a browser is still an agent. The opt-out for this guard is a per-call-site parameter, and the first version baked it into the shared API client - which would have quietly handed the bypass to the dashboard's in-browser agent tools alongside the human SQL console. It is now passed explicitly, in one place: the console, where a person typed the statement themselves. Nothing else gets it. The CLI is guarded by default too, with --allow-unqualified-writes when you mean it, because a CLI is as likely to be inside a script as under a person.

What an index costs, and what it buys

The index advisor has always been evidence-based: it reads the predicates your queries actually ran, and measures each candidate by building it as a hypothetical index the planner can see but which is never written to disk. That told you what an index would cost to store.

It now also tells you what it would buy. We plan the statement each candidate came from twice - once as it is, once with the hypothetical index present - and report the planner's estimated cost reduction. That is the number that turns a list of candidates into a recommendation.

The mechanism is a nice piece of Postgres: statement text is stored with parameters replaced by $1, which plain EXPLAIN refuses to plan. EXPLAIN (GENERIC_PLAN) exists precisely for this, and it is available on every major version we run. Candidates are measured one at a time with a reset in between, so a saving is never credited to the wrong index.

One reading note that we surface in the API rather than hiding: an absent reduction is not zero. Absent means we could not measure it. Zero means we measured, and the index would not help - so do not build it.

Eight pages on using Postgres well

Our guides have always covered operating CapyDB - backups, previews, cutovers, upgrades. They had nothing on using Postgres well, which is a strange gap for a Postgres company.

Postgres in practice is eight new pages, written from the failures above rather than from a feature list:

• Timeouts and Long-running transactions
• Subtransactions
• Job queues - including why your queue probably got slow because of something that is not your queue
• Large tables - the ceilings, and why deleting rows is the expensive way to remove data
• Multi-tenancy - shared schema, schema per tenant, database per tenant, cell per tenant, and what each actually costs
• Row Level Security - including the one-character change that makes policies several times faster
• Reducing data transfer

The connection pooling guide also gained the arithmetic behind the pool numbers, and a section on session state left behind on a pooled server connection - the one where perfectly correct code fails with cannot execute INSERT in a read-only transaction because somebody else set a GUC and did not unset it. Because every project has its own instance and its own pooler, that can never reach another project's database. It can still ruin your afternoon, so it is written down.

You may benchmark us, and publish it

Our terms of service now say so explicitly. Measure a project you own, document the method well enough that someone else could reproduce it - plan, region, Postgres version, client location, workload, exact commands - and represent the configuration, results and cost accurately. That is the whole condition. No permission, no prior notice, no review.

We hold ourselves to the same standard, which meant fixing something in our own harness first.

Our benchmarks were closed-loop: a fixed number of clients, each waiting for its previous query before issuing the next. That is a fine way to compare behaviour at a known concurrency, and it systematically flatters tail latency. When the database stalls, the client stalls with it, so the requests that would have queued are never issued and never measured. The p99 comes out looking healthy through exactly the incident a user would describe as an outage.

The harness now also runs open-loop, issuing at a fixed arrival rate the way real traffic does, and reports service time plus queue wait - what a caller actually experienced, rather than what the server saw once it finally got the request. Those are the numbers we will quote when we make a tail-latency claim.

---

None of this is a feature you will go and use. It is the set of things that decide whether a database gets quietly worse for three weeks or tells you on day one. That seemed worth a release, and worth writing down.