Postgres Prepared Statements: When the Plan Cache Helps and When It Hurts

Prepared statements are one of the oldest performance optimizations in client-server SQL databases, and one of the most misunderstood. The textbook description is that they save the parsing and planning cost on repeated execution of the same query shape. This is correct as far as it goes, but it omits the more interesting half of the story: the plan that gets cached may be substantially worse than the plan that would be chosen for a specific execution, and the conditions under which Postgres switches between cached and per-execution planning have non-obvious consequences for production workloads.

What prepared statements actually do

A prepared statement in Postgres has three phases. The PREPARE phase parses the SQL, validates the schema references, and produces an unbound plan with parameter placeholders. The EXECUTE phase binds parameter values and runs the plan. Between PREPARE and EXECUTE the statement lives in the session's prepared statement cache, accessible by the name given to it.

The win is that PREPARE happens once per session per statement name, while EXECUTE can happen thousands of times. Skipping parsing and planning on every execution is a measurable performance improvement for high-throughput OLTP workloads, particularly when the queries are simple and the parsing cost is a meaningful fraction of total query time.

The session-scoped behavior is worth noting: prepared statements do not survive across sessions. Connection poolers that multiplex sessions onto fewer connections (PgBouncer in transaction mode, most notably) interact poorly with prepared statements because the server-side session that holds the prepared statement may not be the same session as the next client request. The standard mitigation is to disable prepared statements at the driver level (most drivers have a flag) or to use PgBouncer's session mode at the cost of much higher connection counts.

The generic plan trap

The interesting half of the prepared statement story is how Postgres chooses between custom plans (planned with knowledge of the specific parameter values) and generic plans (planned without knowledge of parameter values). The choice is not made at PREPARE time; it is made on each EXECUTE based on a heuristic.

The heuristic: for the first five executions, Postgres uses custom plans. Starting at execution six, Postgres considers switching to a generic plan. If the estimated cost of the generic plan is no more than the average cost of the recent custom plans plus a small safety margin, the generic plan is used from that point forward. The intent is that after five executions, Postgres has enough information to know whether the optimal plan is parameter-dependent or not.

The trap: for queries where the optimal plan depends heavily on the parameter value, the generic plan can be substantially worse than the custom plan. The canonical example is a query like SELECT * FROM events WHERE event_type = $1 on a table with a heavily-skewed event_type distribution. For high-cardinality values (rare event types), the right plan is an index scan. For low-cardinality values (common event types), the right plan is a sequential scan with filter. The generic plan, knowing nothing about which value will be passed, chooses something in the middle, typically an index scan, which is correct for rare values but terrible for common ones.

The symptom in production: queries that ran fine for weeks suddenly become 100x slower with no schema change, no data change, and no application change. The cause is that after enough executions, the prepared statement switched to a generic plan, and the generic plan happens to be a bad fit for the parameter values your application sends.

Diagnosing the generic plan trap

The diagnostic is in pg_prepared_statements and in EXPLAIN ANALYZE behavior. The view shows generic_plans and custom_plans counters per prepared statement (Postgres 14+):

SELECT name, statement, generic_plans, custom_plans
FROM pg_prepared_statements;

If generic_plans grows while custom_plans stays at five, you are using generic plans. The EXPLAIN ANALYZE behavior is also distinctive: explaining a prepared statement with EXPLAIN EXECUTE shows different plans depending on whether the planner is in custom or generic mode. The plan shape includes hints like "Filter: (column = $1)" with placeholder parameters in the generic case vs the actual parameter value in the custom case.

The mitigation depends on the access pattern. The simplest fix is to set plan_cache_mode to force_custom_plan for the affected query, either globally for the session or via the application's database connection setup. This forces custom plans on every execution and pays the planning cost every time, which for parameter-skewed queries is a small cost compared to running a bad plan.

A finer-grained option is to use unprepared queries for the parameter-skewed cases and prepared queries for everything else. Most ORMs have a way to express this; psycopg3 in particular has explicit prepare=False per-query control. For high-volume workloads, this can be the right balance.

Where prepared statements help most

The cases where prepared statements provide unambiguous wins are queries where the optimal plan is the same regardless of parameter values. Primary key lookups, queries against columns with uniform value distributions, and queries that always touch a small fraction of the table fit this pattern. For these cases, the planning cost saved on every execution compounds into measurable throughput improvements at scale.

OLTP workloads with high QPS and short-running queries also benefit, because parsing and planning are a larger fraction of total query time for queries that execute in milliseconds. Analytical workloads with longer-running queries benefit less because the planning time is a smaller fraction of total cost.

The cases where prepared statements hurt are queries with parameter-skewed plans, queries against tables with substantial statistics changes (where the cached plan goes stale), and connection-pooled workloads where prepared statements do not survive across pooled connections.

Connection pooling interaction

The connection pooling interaction deserves its own paragraph because it bites teams that adopt PgBouncer for connection scaling. PgBouncer in transaction mode multiplexes client sessions onto server sessions, returning the server session to the pool after each transaction. Prepared statements live in the server session's local cache, so the next client transaction may land on a different server session that does not have the prepared statement.

The behaviors that result range from confusing (prepared statement not found errors) to silently-wrong (the wrong prepared statement is used because of name collision). Modern drivers handle this with either explicit session pinning, prepared statement disable flags, or PgBouncer-aware caching. Postgres 17 added named protocol-level prepared statements that PgBouncer can track, which is a substantial improvement, but adoption is still spreading.

The PgBouncer-friendly approaches are: disable prepared statements entirely (lowest performance but always works), use PgBouncer session mode (higher connection counts but full feature support), or use a driver that handles prepared statements with PgBouncer awareness (newest drivers, narrower deployment).

Application-side considerations

Most application frameworks use prepared statements implicitly. Django ORM, SQLAlchemy, ActiveRecord, and similar frameworks all prepare statements behind the scenes for every parameterized query. The application developer rarely sees PREPARE or EXECUTE explicitly. This means the generic plan trap can appear without any code that mentions prepared statements at all.

The mitigation at the application level is to be aware of which queries are parameter-skewed and to disable prepared statements (or force custom plans) for those. Most frameworks expose this at the per-query level, though the syntax varies. The skew-detection step is the harder part: it requires either query profiling to identify queries with high p99 latency that correlate with specific parameter values, or domain knowledge of which parameter values produce dramatically different plans.

Our four products are SQLite-based, where prepared statements are even more important because SQLite's parser is simpler and the relative cost of parsing is higher. SQLite's prepared statement model is simpler than Postgres in that there is no plan cache to go stale, but the connection-scoping is similar. Our Postgres migration plan includes per-query prepared statement decisions for the highest-volume endpoints.

Configuration knobs

The relevant Postgres configuration knobs are mostly per-session via SET LOCAL or per-query via prepare/execute commands rather than postgresql.conf settings. The exception is plan_cache_mode which can be set in postgresql.conf as a default and overridden per session.

Values: auto (default, uses the five-execution heuristic), force_custom_plan (always replan), force_generic_plan (always use generic plan). The auto behavior is right for most workloads. force_custom_plan is right when you know your queries are parameter-skewed. force_generic_plan is rarely the right choice but can help for analytics workloads where the planning overhead is high relative to execution and the queries are not parameter-skewed.

The number of executions before considering the generic plan (currently five) is not configurable. This is a fixed constant in the Postgres source and is sometimes proposed for tuning but not currently exposed.

What prepared statements do not help with

Prepared statements save planning cost, not execution cost. They do not help with slow queries that are slow because of bad plans, missing indexes, or large result sets. They do not help with statement-level concurrency issues. They do not help with deadlocks or lock contention. They are a narrow optimization for the parsing-and-planning fraction of query time.

For queries where execution dominates, prepared statements have negligible impact either way. The decision tree for a slow query should not start with whether prepared statements are being used; it should start with EXPLAIN ANALYZE and the diagnostic queries we have covered in prior posts about pg_stat_statements and pg_stat_user_tables.

The pattern

Prepared statements are a good example of an optimization that is correct in the general case but has narrow failure modes that produce large performance regressions. The general case (skip parsing, save time) is solid. The failure mode (generic plan trap on parameter-skewed queries) is rare in absolute terms but disproportionately consequential when it bites. The diagnostic and mitigation tools exist but require knowing they exist before the incident, which is why we are writing this post.

The deeper observation about Postgres performance is that the defaults are well-tuned for the median case and have specific failure modes for the tails. Knowing the failure modes is most of what production database operations requires. The Postgres documentation is excellent but covers each feature in isolation; the interactions between features (prepared statements plus skewed parameters plus connection pooling, for example) are mostly learned from incidents.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) keep the lights on.