How Postgres Switched to a Generic Plan on the Sixth Execution and Timed Out Our Biggest Tenant's Reports
← Back
November 12, 2026Database9 min read

How Postgres Switched to a Generic Plan on the Sixth Execution and Timed Out Our Biggest Tenant's Reports

Published November 12, 20269 min read

13:52 UTC. Alert: reports-api p99 at 31 seconds, which is suspicious because the statement timeout is 30. Support has two tickets from the same customer, our largest, saying the monthly revenue report spins and then errors. The service was migrated to a new database driver at 13:05. The migration PR was reviewed by three people and changed no SQL.


the scramble

First theory: the driver migration broke something. On-call rolls reports-api back to the old driver at 14:01. Latency drops to normal within a minute. That looks like proof, so the team starts reading the migration diff for a bug. There isn't one. The queries are byte-for-byte identical, the pool sizes match, the timeouts match.

At 14:20 the rollback is re-applied, because a rollback can't stay in place while the diff is clean. The first twenty minutes are fine. Then the same customer's report times out again. Smaller customers never complain. Nobody has a theory for why only one tenant is affected, and the dashboard for the endpoint shows no difference between tenants because it aggregates across all of them.

Second theory: the table is bloated or the stats are stale. Someone runs ANALYZE orders by hand at 14:41. It takes 9 seconds, changes nothing, and the next report for the big tenant still times out. Third theory: lock contention from the nightly billing job. pg_locks is empty of anything interesting, and the billing job doesn't run until 02:00 anyway.


the hunt

The detail that fits no theory is the delay. The report works right after a deploy, breaks later, and breaks for one tenant only. When a query gets slower with time instead of with input, there is usually a cache involved. So the next step is to look at what the slow backend is running right now:

catching the slow report in flight
SELECT pid, now() - query_start AS running, state, left(query, 90)
FROM pg_stat_activity
WHERE application_name = 'reports-api'
  AND now() - query_start > interval '5 seconds';

  pid  |    running    | state  |                            left
-------+---------------+--------+--------------------------------------------------------------
 28117 | 00:00:22.41   | active | SELECT o.id, o.total, i.sku, i.qty FROM orders o JOIN order_...

Nothing unusual in the text. The old driver sent the same text with the parameters interpolated into it on the client. The new driver, asyncpg, prepares each statement on the server and sends only the parameter values. That is the real difference between the two builds, and the diff never showed it because it lives inside the driver, not in the repo. Reproducing in psql with a prepared statement and the big tenant's id:

reproducing the sixth-execution flip in psql
PREPARE tenant_report (int, timestamptz) AS
  SELECT o.id, o.total, i.sku, i.qty
  FROM orders o JOIN order_items i ON i.order_id = o.id
  WHERE o.tenant_id = $1 AND o.created_at >= $2;

-- executions 1 to 5, big tenant (id 4417)
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE tenant_report(4417, '2026-10-01');
--  Hash Join ... Seq Scan on order_items ...   Execution Time: 612 ms

-- execution 6
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE tenant_report(4417, '2026-10-01');
--  Nested Loop
--    -> Index Scan using orders_tenant_created_idx on orders o
--         Index Cond: (tenant_id = $1) AND (created_at >= $2)
--         (rows=710 estimated, rows=712044 actual)
--    -> Index Scan using order_items_order_id_idx on order_items i
--         (loops=712044)
--  Execution Time: 41207 ms

Executions one through five used the big tenant's real value, so the planner saw the real skew and picked a hash join. On the sixth, Postgres switched to a cached generic plan. That plan is built without knowing the parameter, so for tenant_id = $1 it uses the average rows per tenant: 4.4 million orders over 6,200 tenants is about 710. A nested loop with 710 probes is the right plan for 710 rows. It is the wrong plan for the one tenant that holds 1.7 million of those 4.4 million rows, 712,044 of them inside the report window.

This also explains every earlier observation. The rollback "worked" because the old driver never used server-side prepared statements, so every query was planned with its real parameters. The re-apply worked for twenty minutes because each pooled connection needs five executions before the switch. Small tenants never noticed because the generic plan is correct for them. ANALYZE couldn't help because the statistics were accurate. The plan was built to ignore them.


the find

Postgres chooses between custom and generic plans under plan_cache_mode = auto. For the first five executions of a prepared statement it plans with the actual parameters and records the average cost. From the sixth it builds a generic plan, and if the generic plan's estimated cost isn't worse than that average, it keeps using it. The comparison is on estimates. Nothing checks whether the estimate was right for the value being passed now.

 exec 1-5  : custom plan, real $1 = 4417   -> hash join,   ~600 ms
 exec 6    : generic plan, $1 unknown      -> est. 710 rows
             generic cost (est) <= avg custom cost?
                yes                        -> cache generic plan
 exec 7+   : generic plan, $1 = 4417       -> nested loop, 712,044 probes, 41 s

the fix

Two options existed. Turning off the driver's statement cache would restore the old behavior but costs a parse and plan round trip on every call for the other 300 queries the service runs. The narrower fix is to tell Postgres to stop switching, and to do it for the reporting role only, since the OLTP paths benefit from cached plans:

pin the reporting role to custom plans
ALTER ROLE reports_api SET plan_cache_mode = 'force_custom_plan';

-- confirm on a fresh connection
SHOW plan_cache_mode;
--  force_custom_plan

Setting it on the role instead of in the driver's server_settings matters here, because the connections go through PgBouncer in transaction mode, which doesn't forward arbitrary startup parameters. The role-level default applies on every new server connection no matter how it was opened. Existing pooled connections kept the old setting until they were recycled, so the fix also needed a RECONNECT on the pooler. The big tenant's report ran in 604 ms on the next attempt, and on the 6th, 60th and 600th after that.

the aftermath

3 h 14 min From the driver migration to the role-level fix, including one rollback and one re-apply
604 ms to 41 s Big-tenant report runtime before and after the plan flip
712,044 Index probes in the nested loop, against 710 the planner expected
11 Failed report exports for the largest customer, all on the same 30-second timeout
  • Swapping drivers can change query plans while the diff shows zero SQL. Whether parameters travel inside the query text or beside it decides what the planner gets to see.
  • Any query where one parameter value covers a large share of a table needs a test with that value, run at least seven times. Five clean runs prove nothing about the sixth.
  • The slow-query alert now groups by tenant id. The aggregate dashboard averaged 41-second reports for one customer into a p50 that looked fine.
  • A rollback that fixes things for exactly one connection lifetime is a strong hint that state is accumulating per connection. Plan caches are per backend, so this pattern points straight at them.

The planner did what the docs say it does. The generic plan was right for 6,199 of 6,200 tenants, and its estimate was honest. What failed was our assumption that a query which got through review, tests and its first five production runs would keep the plan it started with.

Share this
← All Posts9 min read