A query is fast in a SQL console and slow in the application. Adding an index does not help consistently. Restarting the application briefly improves latency. Before blaming connection pooling or storage, investigate whether the application and the console are executing different plans.
Prepared statements introduce a decision about reuse. A custom plan can use the actual parameter values. A generic plan trades that information for avoiding repeated planning. Neither is universally better.
The performance contract belongs to the distribution of production parameters, not to one successful query execution.
How tenant skew changes the decision #
Imagine a hypothetical orders table where one tenant owns half the rows and most tenants own a few hundred. An index scan may suit a small tenant; reading much of the table may suit the largest. A reusable plan must operate without knowing which tenant the next execution will name.
Under PostgreSQL 18’s automatic policy, the first five parameterized executions use custom plans. PostgreSQL then compares a generic plan’s estimated cost with the average estimated custom cost. It does not simply switch permanently after five requests, and the comparison is not a benchmark of observed latency. See the PREPARE documentation.
The operational consequence is important: the first requests handled by a fresh connection can influence what happens later. A pool also contains multiple sessions, so two apparently identical requests can encounter different preparation histories.
Reproduce the application path #
Start with the exact statement text, parameter types, PostgreSQL version, driver preparation settings, role, and search path. Capture representative small, medium, and large tenants. Do not put sensitive parameter values into unrestricted logs.
In a staging database with representative distributions, compare both modes. This example assumes an existing orders table and intentionally tests a read:
PREPARE tenant_orders(bigint) AS
SELECT id, created_at, total
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC
LIMIT 100;
BEGIN;
SET LOCAL plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE tenant_orders(42);
ROLLBACK;
BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE tenant_orders(42);
ROLLBACK;Repeat with different tenants and cache conditions. A LIMIT can materially change which plan wins; do not replace the real query with a count and assume the comparison still applies.
Read work, not just elapsed time #
Compare estimated and actual rows, loops, rows removed by filtering, buffer hits and reads, sorts, and temporary I/O. PostgreSQL’s EXPLAIN guide explains these measurements. ANALYZE executes the statement; this is not a harmless way to inspect a production mutation.
Planning overhead matters for cheap, high-frequency queries. Execution work dominates an expensive report. Saving a small amount of planning CPU is a poor exchange for scanning millions of unnecessary rows, but forcing every tiny lookup to replan may waste capacity.
Inspect generic and custom execution counters through pg_prepared_statements in the relevant session. An administrator’s separate connection cannot show every application’s prepared statements through that session-local view.
Choose the narrowest justified change #
| Evidence | Candidate response | Cost to measure |
|---|---|---|
| inaccurate row estimates in both modes | refresh and improve statistics | analyze overhead and estimate stability |
| custom mode consistently wins for one statement | scoped custom planning | additional planning CPU |
| different workloads hidden behind one query | split query shapes | more application paths |
| missing useful access path | evaluate an index | write amplification and storage |
| no meaningful difference | investigate locks, I/O, pooling | avoid an unrelated planner change |
Do not globally force custom plans after examining one endpoint. Also do not disable parameter binding: SQL injection prevention and generic-plan reuse are separate concerns.
For a scoped setting, use SET LOCAL inside a transaction. A session-level setting returned to a connection pool can silently affect unrelated callers. Confirm what the driver and pool support before changing preparation behavior.
A rollout that can disprove the hypothesis #
Canary the change on the affected endpoint. Compare tenant cohorts rather than only fleet-wide p95. Track planning time, execution time, database CPU, buffer work, pool wait, and error rate. Hold query shape and workload mix stable enough to interpret the result.
Include a cold-connection test, because warm-up behavior is the suspected trigger. Include large and small tenants, because an aggregate improvement can hide a severe regression for one cohort. Define rollback as a configuration change with a known owner, not an emergency database restart.
The architecture decision #
Prepared statements are a useful optimization, not a promise of uniform performance. Make parameter skew part of performance testing and preserve evidence about the actual execution path.
The leadership question is not whether to “use prepared statements.” It is whether one reusable plan fits the workload distribution—and how narrowly the system can respond when it does not.