Prepared statements are supposed to make PostgreSQL calmer. Parse once, plan less often, execute many times. For many workloads that is exactly what happens: lower CPU, fewer repeated parse costs, cleaner application code, and more predictable query shapes in pg_stat_statements.
The production failure mode is more subtle. A prepared statement can move from a custom plan that understands the current parameters to a generic plan that must be acceptable for many possible parameters. That generic plan can be fine for the median tenant and terrible for the tenant on fire.
This is why plan-cache bugs feel unfair. Nothing changed in the SQL text. The parameters changed, the data distribution changed, or PostgreSQL decided a reusable plan was cheaper on average. The query that used the right partial index yesterday now scans too much today.
The framework: parameter sensitivity is the real risk
I do not start by asking whether prepared statements are good or bad. I ask whether the query is parameter-sensitive. If a small change in the bind values should lead to a very different plan, cached planning deserves scrutiny.
- A tenant id where one tenant owns half the table and most tenants own almost nothing.
- A status predicate where one value is rare and another is common.
- A time range where recent data fits in cache and historical data does not.
- A partial index that only helps when the parameter implies the predicate.
- A LIMIT query where selectivity determines whether an index scan or bitmap path wins.
Prepared statements are safest when many executions really do deserve the same plan. They become risky when the SQL shape is stable but the correct access path depends on the parameter values.
Custom plans versus generic plans
A custom plan is planned with the current parameter values. A generic plan is planned without assuming specific parameter values. Generic plans save planning work, but they can miss the exact selectivity that made the fast path obvious.
PREPARE tenant_orders(bigint, timestamptz) AS
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
AND created_at >= $2
ORDER BY created_at DESC
LIMIT 100;For a tiny tenant, the best plan may be an index path that finds a few rows quickly. For a huge tenant with a wide time window, a different index or a bitmap path may be better. A generic plan has to pick one compromise.
That compromise is often invisible from application logs because every execution still uses the same SQL text and the same prepared statement name.
Partial indexes make this sharper
Partial indexes are excellent when the predicate is stable and explicit. They are less helpful when the planner cannot prove the prepared statement always matches the partial-index predicate.
CREATE INDEX CONCURRENTLY orders_open_recent_idx
ON orders (tenant_id, created_at DESC)
WHERE status = 'open';This query is easy for the planner to reason about because the status is literal:
SELECT id
FROM orders
WHERE tenant_id = $1
AND status = 'open'
ORDER BY created_at DESC
LIMIT 100;This version is more ambiguous because the status is a parameter:
PREPARE tenant_orders_by_status(bigint, text) AS
SELECT id
FROM orders
WHERE tenant_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 100;If the hot path is always status = 'open', do not hide that fact behind a generic helper that accepts every status. Split the query shape so the database can see the product rule.
How I diagnose it
Start with the same query and compare plans under different parameter values. Do not rely on one EXPLAIN from a friendly tenant.
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE tenant_orders(42, now() - interval '1 day');
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE tenant_orders(9001, now() - interval '90 days');Then force the planning mode in a safe session and compare behavior. This is a diagnostic tool, not a blanket production fix.
SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE tenant_orders(9001, now() - interval '90 days');
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE tenant_orders(9001, now() - interval '90 days');If custom plans are consistently better for a query class, the issue is not PostgreSQL being random. The query is parameter-sensitive and the generic plan is the wrong abstraction.
PgBouncer changes the conversation
Connection pooling affects prepared statements because prepared statements live in database sessions. With session pooling, a client can keep using the same server session. With transaction pooling, a transaction may land on a different server connection next time.
That does not mean PgBouncer and prepared statements cannot coexist, but it does mean the application, driver, and pool mode have to agree. A driver that aggressively prepares statements can create confusing failures or lose the benefit when the pool mode does not preserve session affinity.
For production systems, I want the pooling mode, driver prepare threshold, and query workload documented together. Treating them as separate knobs is how teams end up debugging the wrong layer.
Operational signals to watch
- High variance by tenant: p99 is bad for a subset of tenants while average latency looks fine.
- Rows removed by filter: the plan reads a broad candidate set and filters too late.
- Partial index ignored: the expected partial index is absent from the plan.
- Plan changes after deploy: driver or pool configuration changed prepare behavior.
- Planning time versus execution time: forcing custom plans helps execution but costs more planning CPU.
The pragmatic default
Use prepared statements for stable, frequently executed queries. Be skeptical for tenant-skewed, status-sensitive, or partial-index-heavy query paths. If the product has one hot status, one hot time window, or one special tenant class, give that path its own SQL shape.
The mistake is not using prepared statements. The mistake is assuming a reusable query text always deserves a reusable plan.