Prepared Statements — Custom Plan vs Generic Plan.

⚖️The two plans Postgres can give you

CUSTOM PLAN

• Re-planned on every EXECUTE, with your actual parameter values
• Sees "tenant_id = 7" and knows 7 is rare → Index Scan
• Costs planning time each call (~0.1–2ms on a complex join)

GENERIC PLAN

• Planned ONCE with $1 as an unknown → reused forever
• Estimates selectivity as an average: rows / n_distinct
• Zero planning cost, but blind to your value

🔀How Postgres picks

Executions 1–5: always custom. It records the average custom cost.

From execution 6: if estimated generic cost <= average custom cost, it locks in the generic plan and stops re-planning.

That's the cliff. Nothing in your app changed — the planner just stopped looking at your values.

💣Where it bites: skewed columns

tenant_id has 2 values: tenant 1 = 499,500 rows, tenant 7 = 500 rows.

Custom plan for tenant 7: Index Scan, 500 rows, 3ms.

Generic plan: 500000 / 2 = 250,000 estimated rows → Seq Scan, 400ms.

Same for status='failed' (0.1% of rows), soft-delete flags, LIKE 'abc%', and anything behind a partial index. High skew + prepared statement = a latency cliff on call #6.

🔎 Are you even using prepared statements?

You probably are and don't know it:

• asyncpg — prepares everything by default
• JDBC/pgjdbc — switches at prepareThreshold=5 (same cliff)
• node-postgres — only when you pass name: 'my_query'
• Django/Rails — mostly unnamed/simple protocol, so usually not

Check live:

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

(generic_plans/custom_plans available PG14+.) If generic_plans is climbing on a skewed query, that's your suspect.

🛠️The fix (one line)

SET plan_cache_mode = force_custom_plan;      -- PG12+

Make it stick for the app user:

ALTER ROLE app_user SET plan_cache_mode = force_custom_plan;

You're trading ~1ms of planning for not falling off a 100x cliff. On skewed OLTP queries that is almost always the right trade. force_generic_plan is the opposite lever — useful for simple PK lookups at very high QPS.

⚠️Two gotchas practitioners hit

1. EXPLAIN ANALYZE SELECT ... WHERE tenant_id = 7 will NEVER reproduce the bug — that's a literal, so it's always a custom plan. You must EXPLAIN the prepared path: PREPARE it, then EXECUTE 6+ times. A plan showing "$1" instead of a value is the generic one.
2. PgBouncer in transaction pooling mode used to break named prepared statements entirely; 1.21+ supports them at the protocol level. If you're on an older PgBouncer, you may have no prepared statements at all — and a re-plan tax on every single query instead.

🧪Your exercise

CREATE TABLE events(id bigserial, tenant_id int);
INSERT INTO events(tenant_id)
  SELECT CASE WHEN i % 1000 = 0 THEN 7 ELSE 1 END
  FROM generate_series(1,500000) i;
CREATE INDEX ON events(tenant_id);
ANALYZE events;
PREPARE q(int) AS SELECT count(*) FROM events WHERE tenant_id = $1;
EXPLAIN (ANALYZE) EXECUTE q(7);   -- now run this 6 times

Question: on which execution does the node flip from Index Scan to Seq Scan, and what does the "rows=" estimate change to? Then re-run after SET plan_cache_mode = force_custom_plan and confirm it stays fast.