pgjdbc's prepareThreshold and PostgreSQL's Generic Plans: The Performance Trap After the Fifth Execution

Good evening. I regret to inform you that your query plans have a shelf life.

You have a Java application. It uses pgjdbc and HikariCP, as nearly all Java applications do. It has been running in production for months. The queries are parameterized, the indexes are proper, and EXPLAIN ANALYZE shows clean index scans with sub-millisecond execution times.

Then, on a Tuesday afternoon — always a Tuesday — your monitoring lights up. A query that has never taken more than 5 milliseconds is now taking 57 seconds. Not all executions. Just some. The ones where the parameter value happens to be 'pending' instead of 'archived'.

You check the query. It has not changed. You check the indexes. They are still there. You check the table statistics. Current. You run the query manually with the same parameters. 3 milliseconds. You run it again through the application. 57 seconds.

The manual execution is fast because psql does not use prepared statements. It sends the full SQL text with values inlined. PostgreSQL parses, plans, and executes with full knowledge of the actual parameter values. The application execution is slow because pgjdbc has promoted that query to a server-side prepared statement, and PostgreSQL has decided — reasonably, by its own logic — that a generic plan will do.

The problem is not your query. It is not your indexes. It is a two-stage plan caching decision that pgjdbc and PostgreSQL make collaboratively, silently, and — for tables with skewed data distributions — catastrophically.

If you will permit me, I should like to walk through the entire mechanism, from first execution to 57-second disaster, and then through every mitigation available. The walk will be thorough. The problem warrants it.

The two-stage pipeline that nobody explains in full

Most documentation describes prepared statements as a single concept: parse once, execute many times, save CPU. This is true but dangerously incomplete. There are actually two distinct caching decisions, made by two different systems, at two different times. Both must go right for your queries to stay fast.

# The full lifecycle of a parameterized query through pgjdbc + PostgreSQL
#
# Executions 1-4: Client-side parsing
#   pgjdbc sends the full SQL text with parameter values inlined.
#   PostgreSQL parses, plans, and executes with exact values.
#   Plan is tailored to the actual data. Fast.
#
# Execution 5: Server-side PREPARE (prepareThreshold crossed)
#   pgjdbc sends: PARSE "S_1" = "SELECT * FROM orders WHERE status = $1"
#   PostgreSQL stores the prepared statement. No plan cached yet.
#
# Executions 5-9: Custom plans
#   pgjdbc sends: BIND "S_1" WITH ('pending')  /  EXECUTE "S_1"
#   PostgreSQL generates a CUSTOM plan using the actual parameter value.
#   Knows 'pending' matches 200 rows -> Index Scan. Good.
#   Knows 'archived' matches 14M rows -> Seq Scan. Also good.
#
# Execution 10+: Generic plan evaluation
#   PostgreSQL creates a GENERIC plan that ignores parameter values.
#   Uses pg_statistic averages instead. Estimates "typical" row count.
#   Compares generic plan cost to average of recent custom plan costs.
#   If generic plan is not significantly worse: switches to generic plan.
#   (Re-evaluated periodically — reverts if custom plans prove cheaper.)
#
#   Generic plan for "WHERE status = $1":
#     Estimated rows: 2.8M (average across all statuses)
#     Chosen plan: Seq Scan
#     Cost for 'pending' (200 rows): 57 seconds instead of 3ms.

Stage 1: pgjdbc's prepareThreshold. By default, pgjdbc sends the first four executions of a parameterized query as simple queries with values inlined. On the fifth execution, it promotes the query to a server-side prepared statement by sending a PARSE message through the extended query protocol. This threshold is controlled by the prepareThreshold connection property, which defaults to 5.

This is a sensible optimization. One-off queries are not worth preparing. Queries that repeat at least five times probably repeat thousands of times, and the parse+plan savings compound. The pgjdbc maintainers chose this default wisely — it is a reasonable heuristic for the majority of workloads.

Stage 2: PostgreSQL's generic plan evaluation. Once a query is server-side prepared, PostgreSQL tracks its execution. For the first five executions (yes, five again — a coincidence of defaults), it generates a custom plan that uses the actual parameter values for cost estimation. After the fifth execution, it generates a generic plan that uses table statistics instead of actual values. If the generic plan's estimated cost is not significantly worse than the average custom plan cost, PostgreSQL switches to the generic plan for all future executions.

The word "significantly" in the previous sentence is doing considerable work. PostgreSQL uses a heuristic: the generic plan is adopted if its cost is not more than 10% worse than the average custom plan cost. But this comparison uses estimated costs — the planner's predictions, not actual execution times. For skewed data, the planner's predictions for generic plans are systematically wrong, because the planner estimates row counts using column-level averages that obscure the distribution's tails.

The generic plan is faster to produce — no planning overhead at all, since the plan is cached. For tables with uniform data distribution, the generic plan is identical to any custom plan. For tables with skewed distributions, the generic plan can be a disaster.

// HikariCP + pgjdbc — the default configuration most Spring apps ship with.
// This is the configuration that will eventually betray you.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://db.prod:5432/orders");
config.setUsername("app_user");
config.setPassword("...");
config.setMaximumPoolSize(20);

// pgjdbc's prepareThreshold default: 5
// After a query is executed 5 times on a connection, pgjdbc
// promotes it from a named prepared statement to a server-side
// prepared statement. PostgreSQL then caches the plan.
//
// You probably did not set this. It is set for you.
//
// What you may not know: after 5 MORE executions of the
// server-side prepared statement, PostgreSQL itself makes
// a second decision — custom plan or generic plan.
//
// That second decision is where the 57-second queries come from.

DataSource ds = new HikariDataSource(config);

Why PostgreSQL chose this design (and why it is not a bug)

Before blaming PostgreSQL for a design flaw, allow me to present the other side. The generic plan optimization is genuinely good for the majority of workloads.

Consider an application that executes SELECT * FROM users WHERE id = $1 ten thousand times per second. With custom plans, PostgreSQL generates a fresh query plan for each execution. The plan is always the same — an index scan on the primary key — because id has uniform distribution and every value matches exactly one row. The planning overhead is wasted: 0.3ms of CPU, ten thousand times per second, producing an identical plan every single time. That is 3 seconds of CPU per second. On a 4-core machine, you have donated an entire core to redundant planning.

With a generic plan, PostgreSQL plans once, caches the plan, and reuses it. Zero planning overhead on subsequent executions. For uniformly distributed columns — and primary keys, unique indexes, and evenly-distributed foreign keys are all uniformly distributed — the generic plan is always correct.

The problem is specific to columns with skewed distributions. Status columns. Type discriminators. Tenant IDs in multi-tenant databases. Country codes where 60% of traffic comes from one country. Anything where the frequency of one value is orders of magnitude greater than another.

PostgreSQL's planner cannot know, at generic-plan time, which parameter value will be supplied. It estimates row counts using pg_statistic averages. The average selectivity for a column with five values is roughly total_rows / n_distinct. For 14.2 million rows with 5 distinct statuses, that average is 2.84 million rows. Seq Scan is the correct plan for 2.84 million rows. It is catastrophically wrong for 200 rows.

The PostgreSQL developers are aware of this limitation. The plan_cache_mode GUC, introduced in PostgreSQL 12, is the official response. It is not a workaround — it is the intended escape hatch for workloads where generic plans are inappropriate.

The 57-second query: seeing the regression in EXPLAIN output

Here is what this looks like in practice. The table is orders, 14.2 million rows. The status column has five distinct values with extremely skewed distribution: 'pending' has 200 rows, 'archived' has 8.3 million.

-- Let's see this in practice. Table: orders, 14.2M rows.
-- status distribution: 'pending' = 200, 'processing' = 3,400,
-- 'shipped' = 840K, 'delivered' = 5.1M, 'archived' = 8.3M

-- CUSTOM plan (executions 5-9): PostgreSQL uses actual parameter value
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE find_by_status ('pending');

--  Index Scan using idx_orders_status on orders
--    Index Cond: (status = 'pending')
--    Rows Removed by Filter: 0
--    Buffers: shared hit=4
--    Planning Time: 0.12 ms
--    Execution Time: 0.03 ms        <-- 0.03ms. Lovely.

-- GENERIC plan (execution 10+): PostgreSQL ignores parameter value
-- It uses average selectivity from pg_statistic instead.
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE find_by_status ('pending');

--  Seq Scan on orders  (cost=0.00..412859.00 rows=2841600 width=128)
--    Filter: (status = $1)
--    Rows Removed by Filter: 14199800
--    Buffers: shared hit=287412
--    Planning Time: 0.00 ms         <-- no planning needed (cached)
--    Execution Time: 57,241.83 ms   <-- 57 seconds. For 200 rows.

-- Same query. Same parameters. 1,908,061x slower.
-- The only thing that changed: PostgreSQL's plan cache decision.

Read those numbers carefully. The custom plan uses an Index Scan, touches 4 buffer pages, and returns in 0.03 milliseconds. The generic plan uses a Seq Scan, touches 287,412 buffer pages, and returns in 57,241 milliseconds. Same query. Same parameter value. The only difference: PostgreSQL decided that a generic plan would be efficient on average across all possible parameter values.

And on average, it is. When status = 'archived' matches 8.3 million rows, a Seq Scan is genuinely faster than an Index Scan. The generic plan is correct for the common case. It is catastrophic for the rare case. PostgreSQL's cost comparison does not weight by frequency of parameter values — it compares average costs.

The particularly insidious part: the switch from custom to generic plans is invisible. There is no log entry. No notice. No event. The query plan simply changes on the tenth execution, and unless you are monitoring pg_prepared_statements, you will not know until your application starts timing out.

There is also a timing element that makes debugging harder. The switch happens per-connection, not globally. Connection A may have executed the query twelve times and switched to generic plans. Connection B, freshly created by HikariCP, is still on custom plans. The same application, the same query, the same parameter value — different execution times on different connections. If you are load-testing with multiple connections (as you should be), the regression may appear intermittent. It is not intermittent. It is deterministic, but scoped to individual connections.

Knowing your data: identifying skew before it becomes an incident

The best time to find data skew is before PostgreSQL makes a catastrophic plan choice at 3 AM. The evidence is already in your database — pg_stats holds the distribution data that the planner uses.

-- Finding data skew before it finds you.
-- Run this against any table with parameterized WHERE clauses.

-- Step 1: Identify skewed columns
SELECT attname AS column_name,
       n_distinct,
       most_common_vals,
       most_common_freqs
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('status', 'tenant_id', 'country', 'plan_type');

-- What to look for:
-- most_common_freqs = {0.58, 0.36, 0.059, 0.0024, 0.000014}
--
-- That 0.58 is the dominant value — 58% of all rows. The 0.000014
-- is the rare value — 0.0014% of rows. The ratio between them: 41,429x.
-- A generic plan that targets the average will choose Seq Scan (because
-- the average row count across all parameter values is dominated by
-- the heavy hitters), and that Seq Scan will be catastrophic for the
-- rare values.
--
-- Rule of thumb: if the ratio between the highest and lowest
-- most_common_freqs exceeds 100x, generic plans are dangerous
-- for that column.

-- Step 2: Quantify the damage potential
SELECT status,
       count(*) AS row_count,
       round(count(*) * 100.0 / sum(count(*)) OVER (), 2) AS pct
FROM orders
GROUP BY status
ORDER BY row_count DESC;

--  status     | row_count  | pct
-- ------------+------------+-------
--  archived   | 8,300,000  | 58.45
--  delivered  | 5,100,000  | 35.92
--  shipped    |   840,000  |  5.92
--  processing |     3,400  |  0.02
--  pending    |       200  |  0.00
--
-- When PostgreSQL generates a generic plan for "WHERE status = $1",
-- it estimates rows using the average selectivity: ~2.8M rows.
-- For 'archived' (8.3M rows), Seq Scan is actually correct.
-- For 'pending' (200 rows), Seq Scan is a 1.9 million x penalty.

I should offer a practical heuristic. Not every skewed column is dangerous. The generic plan problem requires three conditions to align:

• The column is used in a WHERE clause of a parameterized query. Columns used only in SELECT, ORDER BY, or GROUP BY are not affected — the planner does not use parameter values for those decisions.
• The column has significant skew — a ratio of at least 100x between the most common and least common values in most_common_freqs. Below 100x, the generic plan's row estimate is close enough that it typically chooses the same plan as a custom plan.
• Different plans are optimal for different values. If the table is small enough that a Seq Scan is fast regardless, skew does not matter. If the column is not indexed, every plan is a Seq Scan regardless. The danger zone is large tables (millions of rows) with indexes on skewed columns — exactly the scenario where the planner must choose between Index Scan and Seq Scan based on estimated row counts.

The columns most commonly affected, in my experience: status columns (active/archived distributions), type or kind discriminator columns (especially in Single Table Inheritance), tenant_id in multi-tenant schemas (one large tenant, many small ones), country or region columns (geographic concentration), and boolean flags where one value dominates (e.g., is_deleted where 99.9% are false).

Detecting which statements have gone generic

PostgreSQL exposes the evidence you need in two system views. pg_prepared_statements tells you which plans are generic versus custom. pg_stat_statements tells you which queries have suspicious execution time variance.

-- Inspect what PostgreSQL knows about your prepared statements:
SELECT name,
       statement,
       prepare_time,
       parameter_types,
       result_types,
       generic_plans,
       custom_plans
FROM pg_prepared_statements;

--  name | statement                                    | generic_plans | custom_plans
-- ------+----------------------------------------------+---------------+-------------
--  S_1  | SELECT * FROM orders WHERE status = $1       |           847 |            5
--  S_2  | SELECT * FROM users WHERE tenant_id = $1 ... |          1203 |            5
--  S_3  | INSERT INTO audit_log ...                    |             0 |          412

-- S_1 and S_2: generic_plans >> custom_plans.
-- PostgreSQL switched to generic plans after 5 custom plans.
-- If those generic plans chose Seq Scan for skewed columns: trouble.

The pattern to look for: generic_plans much greater than custom_plans, where custom_plans sits at exactly 5. That means PostgreSQL ran five custom plans, deemed the generic plan acceptable, and switched. If the query involves a column with skewed distribution — and pg_stats will show you the frequency distribution — you have found your culprit.

One important caveat: pg_prepared_statements shows only the prepared statements on the current connection. It is a session-level view, not a cluster-level view. If you are connected through psql, you are seeing psql's prepared statements, not your application's. To inspect your application's prepared statements, you need to query from the application's connection — or, more practically, use the monitoring queries against pg_stat_statements which aggregates across all connections.

-- Find which prepared statements have gone generic
-- and whether their plans are suspicious:

-- Step 1: Check generic vs custom plan counts
SELECT name,
       statement,
       generic_plans,
       custom_plans,
       CASE
         WHEN generic_plans > 0 AND custom_plans <= 5
         THEN 'SWITCHED TO GENERIC'
         ELSE 'still custom'
       END AS plan_status
FROM pg_prepared_statements
ORDER BY generic_plans DESC;

-- Step 2: Check if any columns used in WHERE clauses have skewed data
SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'status';

-- If most_common_freqs shows values like &#123;0.58, 0.36, 0.059, 0.0024, 0.000014&#125;
-- that is extreme skew: one value matches 58% of rows, another matches 0.0014%.
-- Generic plans WILL choose the wrong strategy for rare values.

-- Step 3: Check pg_stat_statements for queries that got slower over time
SELECT query,
       calls,
       mean_exec_time,
       max_exec_time,
       stddev_exec_time
FROM pg_stat_statements
WHERE query LIKE '%orders%'
  AND stddev_exec_time > mean_exec_time * 5  -- high variance = plan instability
ORDER BY max_exec_time DESC
LIMIT 20;

-- High stddev_exec_time relative to mean_exec_time is the fingerprint
-- of generic plan regression. The mean looks fine because most executions
-- use common parameter values. The max is astronomical because rare
-- values got the wrong plan.

The third query in that monitoring block is the one that will save you the most time. High stddev_exec_time relative to mean_exec_time is the statistical fingerprint of generic plan regression. The mean looks reasonable because most executions hit common parameter values where the generic plan works fine. The max is orders of magnitude higher because rare parameter values got the wrong plan. The EXPLAIN ANALYZE guide covers how to interpret the plans once you have identified the problematic queries.

A word on pg_stat_statements: it normalizes queries by replacing literal values with parameter placeholders. The query SELECT * FROM orders WHERE status = 'pending' and SELECT * FROM orders WHERE status = 'archived' appear as a single entry: SELECT * FROM orders WHERE status = $1. This is exactly what you want for detecting generic plan problems, because the generic plan applies to all parameter values equally. If the aggregated statistics show high variance, the generic plan is harmful for some parameter values, and you do not need to know which ones to fix it — force_custom_plan fixes all of them.

The prepareThreshold settings and what each one actually does

pgjdbc gives you a dial. Here is what each position means, honestly:

Notice the uncomfortable truth in that table: changing prepareThreshold alone does not fix the generic plan problem. It delays it or avoids it by disabling server-side preparation entirely. Setting the threshold higher means PostgreSQL's generic plan evaluation starts later, but it still starts. Setting it to 0 or -1 disables server-side preparation, which eliminates the generic plan risk but also eliminates the 15-30% latency reduction that prepared statements provide.

I encounter teams who set prepareThreshold=0 as a blanket fix, and I understand the appeal. It is simple, it eliminates the entire class of problem, and the overhead is predictable. But it is a concession. You are telling PostgreSQL: "I do not trust your plan caching, so I will pay the planning tax on every single execution to avoid the possibility of a bad cached plan." For high-throughput applications executing thousands of queries per second, that tax is not trivial — it can consume 15-30% of your PostgreSQL CPU budget.

The real fix is not at the pgjdbc layer. It is at the PostgreSQL layer.

plan_cache_mode: the actual fix (PostgreSQL 12+)

PostgreSQL 12 introduced the plan_cache_mode GUC parameter, which gives you direct control over the custom-versus-generic decision. This is the parameter that solves the problem without sacrificing prepared statement performance.

-- PostgreSQL 12+ provides the real fix: plan_cache_mode

-- Option 1: Session-level (affects all prepared statements)
SET plan_cache_mode = 'force_custom_plan';

-- Option 2: Per-transaction
BEGIN;
SET LOCAL plan_cache_mode = 'force_custom_plan';
-- your queries here
COMMIT;

-- Option 3: Per-user default (survives reconnection)
ALTER ROLE app_user SET plan_cache_mode = 'force_custom_plan';

-- The three values:
--   auto (default)         — PostgreSQL decides after 5 custom plans
--   force_custom_plan      — always use actual parameter values
--   force_generic_plan     — always use generic plan (rarely wanted)

-- Cost of force_custom_plan: PostgreSQL must plan every execution.
-- For a 2ms query, planning adds ~0.3-0.8ms.
-- That is a 15-40% overhead — but it is PREDICTABLE overhead.
-- No query will ever jump from 3ms to 57 seconds.

-- The trade-off is clear:
-- force_custom_plan: every query is 15-40% slower, none are catastrophic.
-- auto: most queries are fastest possible, some are 1,908,061x slower.

Setting plan_cache_mode = 'force_custom_plan' tells PostgreSQL to always generate a custom plan using the actual parameter values, even for prepared statements. The parse phase is still cached — you still save the 0.1-0.3ms of SQL parsing. But the plan phase runs fresh every time with knowledge of the actual parameters.

The overhead is real but bounded. Custom planning adds 0.2-0.8ms per execution, depending on query complexity. For a 2ms query, that is 10-40% overhead. For a 50ms query, it is noise. Compare that to the alternative: most queries run 15% faster with generic plans, but some queries run 1,908,061% slower. The expected value calculation is not even close.

For applications where you know exactly which queries have skewed parameters, you can be surgical: set plan_cache_mode in a transaction wrapper around only those queries, and let the rest benefit from generic plan caching. In practice, most teams find that force_custom_plan at the session level is simpler and the overhead is acceptable.

I should note the third value — force_generic_plan — because it has a legitimate, if narrow, use case. If you have a query that is executed thousands of times per second with uniformly distributed parameter values, and profiling shows that planning overhead is a meaningful fraction of total CPU, forcing the generic plan eliminates that overhead entirely. It is a performance optimization for the specific case where you have already verified that the generic plan is correct for all parameter values. In practice, I have seen this used for high-frequency primary key lookups on large tables where the planner always chooses Index Scan regardless. Narrow. But real.

Before PostgreSQL 12: what you can do on older versions

Not everyone has the luxury of running PostgreSQL 12 or later. If you are on PostgreSQL 9.6, 10, or 11 — and a meaningful number of production databases still are — you do not have access to plan_cache_mode. Here are the options that remain.

-- Before PostgreSQL 12: no plan_cache_mode. Your options:

-- Option 1: Periodic DEALLOCATE (blunt but effective)
-- Deallocate specific statements when you detect regression.
DEALLOCATE find_by_status;
-- Forces re-preparation on next use. Counter resets.
-- Not ideal — you lose all cached plans, not just the bad one.

-- Option 2: Wrapper functions with dynamic SQL
-- The planner cannot use a generic plan for EXECUTE because
-- the query text changes with each call.
CREATE OR REPLACE FUNCTION find_orders_by_status(p_status text)
RETURNS SETOF orders AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT * FROM orders WHERE status = $1'
        USING p_status;
END;
$$ LANGUAGE plpgsql;

-- This forces a fresh plan on every call. Same overhead as
-- force_custom_plan, but without the GUC parameter.
-- Works on PostgreSQL 9.6+.

-- Option 3: Upgrade to PostgreSQL 12.
-- This is not flippant advice. plan_cache_mode was the single
-- most impactful GUC addition for prepared statement safety in
-- PostgreSQL's recent history. If you are on 11 or earlier
-- and experiencing generic plan regressions, the upgrade
-- justifies itself on this feature alone.

The wrapper function approach deserves additional explanation. When you use EXECUTE ... USING inside a PL/pgSQL function, PostgreSQL treats it as dynamic SQL. The query text is constructed at runtime, which means the planner generates a fresh plan for each invocation. This is functionally identical to force_custom_plan, but it requires wrapping your queries in functions.

The drawback is clear: you are adding a function call layer around queries that were previously simple SQL. The application must call SELECT * FROM find_orders_by_status('pending') instead of SELECT * FROM orders WHERE status = $1. If you use an ORM, this means custom query methods or native queries rather than derived queries. It is more code to maintain, and it adds the PL/pgSQL executor overhead (typically 0.05-0.1ms per call).

If you are on PostgreSQL 11 specifically, the upgrade to 12 is straightforward and well-documented. The plan_cache_mode parameter alone justifies it, but PostgreSQL 12 also brought significant other improvements: generated columns, improved partitioning performance, and the removal of recovery.conf in favor of standby.signal. If the upgrade is possible, it is the better path.

Hibernate, JPA, and Spring Data: where the ORM hides the problem

The generic plan regression is particularly treacherous in ORM-heavy applications because the developer never sees the SQL. Hibernate generates parameterized queries for entity operations, Spring Data JPA generates them from method names, and Criteria queries generate them from programmatic specifications. The developer writes Java. pgjdbc sends prepared statements. PostgreSQL adopts generic plans. The entire chain is invisible unless you are specifically looking for it.

// Hibernate / JPA and the generic plan problem.
// Hibernate generates parameterized queries for all entity operations.
// Every findById, every JPQL query, every Criteria query becomes a
// prepared statement through pgjdbc.
//
// The queries most vulnerable to generic plan regression:

// 1. Queries on discriminator columns (Single Table Inheritance)
//    @Entity
//    @Inheritance(strategy = InheritanceType.SINGLE_TABLE)
//    @DiscriminatorColumn(name = "type")
//    public abstract class Notification { ... }
//
//    SELECT * FROM notifications WHERE type = $1
//    If 99% of notifications are 'email' and 0.01% are 'sms',
//    generic plan chooses Seq Scan. SMS lookups: catastrophic.

// 2. Queries with status/state columns
//    entityManager.createQuery(
//        "SELECT o FROM Order o WHERE o.status = :status", Order.class)
//        .setParameter("status", "pending")
//        .getResultList();
//    Same skew problem as the raw SQL example above.

// 3. Multi-tenant queries on tenant_id
//    @Where(clause = "tenant_id = :tenantId")
//    The largest tenant has 60% of the data. The smallest has 0.01%.
//    Generic plan uses average selectivity. Small tenants suffer.

// 4. Spring Data JPA derived queries
//    List<Order> findByStatus(String status);
//    Generates: SELECT * FROM orders WHERE status = $1
//    The developer never sees the SQL. Never thinks about plan caching.
//    The regression appears months after deployment.

// The fix is the same regardless of how the SQL is generated:
// SET plan_cache_mode = 'force_custom_plan' at the session level.
// Hibernate does not need to know about it. pgjdbc does not need
// to know about it. PostgreSQL applies it to all prepared statements
// on the connection.

The Single Table Inheritance case is worth dwelling on. STI is a common pattern in domain models — Notification with subtypes EmailNotification, SmsNotification, PushNotification. Hibernate generates WHERE type = $1 for every subtype query. If 99% of notifications are emails, the generic plan optimizes for the common case and destroys performance for SMS and push lookups. The developer who chose STI — a reasonable ORM-level decision — has unknowingly created a generic plan trap.

Spring Data JPA derived queries (findByStatus, findByTenantId) are equally vulnerable. The method signature List<Order> findByStatus(String status) compiles to SELECT * FROM orders WHERE status = $1. The developer thinks in Java abstractions. PostgreSQL thinks in plan costs. The gap between those two perspectives is where the 57-second query lives.

The remedy is the same regardless of ORM: plan_cache_mode = 'force_custom_plan' at the session level. Hibernate does not need to know. Spring Data does not need to know. pgjdbc does not need to know. The fix lives entirely in the PostgreSQL connection session, which is exactly where it belongs — the database is the system making the plan decision, so the database is where the correction should be applied.

PgBouncer and prepared statements: the compound problem

If you are using PgBouncer in transaction mode — and most PgBouncer deployments use transaction mode, because session mode provides little benefit over direct connections — you face a second layer of prepared statement complexity on top of the generic plan problem.

-- PgBouncer and prepared statements: the other trap.
--
-- PgBouncer in TRANSACTION mode reassigns server connections between
-- transactions. Prepared statements are bound to server connections.
-- When your next transaction lands on a different server connection,
-- your prepared statement does not exist there.
--
-- The symptoms:
--   ERROR: prepared statement "S_1" does not exist
--
-- The common "fix": prepareThreshold=0 (disable prepared statements)
-- The cost: you lose 15-30% of query performance.

-- PgBouncer 1.21+ (released 2023) added protocol-level prepared
-- statement support. It tracks prepared statements across server
-- connections and re-prepares them transparently. This is a
-- significant improvement, but it has limitations:
--
-- 1. Memory overhead: PgBouncer must cache statement metadata
--    for every client connection × every prepared statement.
-- 2. Re-preparation latency: when a statement migrates to a new
--    server connection, the first execution incurs parse overhead.
-- 3. The generic plan problem remains: PgBouncer passes PARSE/BIND/
--    EXECUTE through to PostgreSQL. PostgreSQL still makes the
--    custom-vs-generic decision. PgBouncer has no visibility into
--    plan quality.
--
-- If you are using PgBouncer in transaction mode AND have skewed
-- data, you face a compound problem:
--   - prepareThreshold=0 avoids PgBouncer errors but costs performance
--   - prepareThreshold=5 triggers PgBouncer errors OR generic plan risk
--   - plan_cache_mode=force_custom_plan fixes the plan problem but
--     must be SET on every transaction (PgBouncer resets session state)
--
-- The practical solution for PgBouncer + skewed data:
-- SET plan_cache_mode in every transaction:
BEGIN;
SET LOCAL plan_cache_mode = 'force_custom_plan';
SELECT * FROM orders WHERE status = $1;
COMMIT;
-- Or use PgBouncer's connect_query to set it per-connection:
-- connect_query = SET plan_cache_mode = 'force_custom_plan'

The fundamental tension: PgBouncer in transaction mode multiplexes many client connections across fewer server connections, reassigning server connections between transactions. Prepared statements are bound to server connections. When your next transaction lands on a different server connection, the prepared statements from your previous transaction do not exist.

Many teams solve this by setting prepareThreshold=0, which disables server-side preparation entirely. This avoids the "prepared statement does not exist" errors and also, as a side effect, eliminates the generic plan problem. But you lose the 15-30% performance benefit of cached parsing.

PgBouncer 1.21 added protocol-level prepared statement support, which is a significant step forward. But it does not address the generic plan regression — PgBouncer transparently passes statements to PostgreSQL, which still makes the custom-vs-generic decision independently. You need both PgBouncer's prepared statement tracking and plan_cache_mode = 'force_custom_plan' to fully address both problems.

In transaction mode, session-level SET commands do not persist between transactions (because the server connection may change). You must use SET LOCAL inside each transaction, or configure PgBouncer's connect_query parameter to set it when server connections are initialized. The connect_query approach is cleaner — it applies once per server connection and persists for the lifetime of that connection.

Spring Boot configuration: putting it all together

For the majority of Java applications that reach this page, the stack is Spring Boot, HikariCP, and pgjdbc. Here is how to configure each layer correctly.

# Spring Boot / HikariCP configuration for pgjdbc plan safety
# application.yml

spring:
  datasource:
    url: jdbc:postgresql://db.prod:5432/orders
    username: app_user
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 5000

      # Option A: Disable server-side prepared statements entirely.
      # Safest for skewed data, costs 15-30% in planning overhead.
      # data-source-properties:
      #   prepareThreshold: 0

      # Option B: Keep prepared statements, force custom plans.
      # Best of both worlds — plan caching without generic plan risk.
      connection-init-sql: SET plan_cache_mode = 'force_custom_plan'

      # Option C: Higher threshold — delays the problem, doesn't fix it.
      # data-source-properties:
      #   prepareThreshold: 25
      #   preparedStatementCacheQueries: 512

# For multi-tenant apps using search_path switching,
# DO NOT use: connection-init-sql: DISCARD ALL
# Instead, use: connection-init-sql: SET plan_cache_mode = 'force_custom_plan'
# And set search_path explicitly per request.

The connection-init-sql approach is the most practical for most teams. It runs once when HikariCP creates or validates a connection, setting plan_cache_mode for the session. Every prepared statement on that connection will use custom plans. No application code changes. No JDBC URL modifications.

You can also pass it through the JDBC URL directly, which avoids the HikariCP configuration layer entirely:

// pgjdbc URL parameters that affect prepared statement behavior:

// Recommended for most production Java applications:
String url = "jdbc:postgresql://db.prod:5432/orders"
    + "?prepareThreshold=5"            // default; server-side after 5 executions
    + "&preparedStatementCacheQueries=256"  // max cached statements per connection
    + "&preparedStatementCacheSizeMiB=5";   // max memory for cached statements

// For applications with known data skew:
String url = "jdbc:postgresql://db.prod:5432/orders"
    + "?prepareThreshold=0";           // disable server-side prep entirely

// Nuclear option — combines both defenses:
String url = "jdbc:postgresql://db.prod:5432/orders"
    + "?prepareThreshold=5"
    + "&options=-c plan_cache_mode=force_custom_plan";
// The 'options' parameter passes startup GUC settings to PostgreSQL.
// This sets plan_cache_mode for the session without needing SET commands.

The options=-c plan_cache_mode=force_custom_plan approach sets the GUC at connection startup through the PostgreSQL protocol's options field. It is the most portable approach — it works regardless of your connection pool or framework. If you use Quarkus with Agroal instead of Spring with HikariCP, or if you use a custom JDBC wrapper, the URL parameter works everywhere.

A note on which approach to choose: if you have a single Spring Boot application connecting directly to PostgreSQL, connection-init-sql is the simplest. If you have multiple applications or frameworks sharing the same database, ALTER ROLE app_user SET plan_cache_mode = 'force_custom_plan' is the most centralized — it applies to every connection from that role, regardless of client configuration. If you want the setting to be visible in your application configuration (for audit, documentation, or deployment tooling), the JDBC URL parameter makes it explicit.

The DISCARD ALL trap with multi-tenant search_path switching

There is one more failure mode that deserves dedicated attention, because it is common in Java applications and the interaction with prepared statements is not obvious.

// The multi-tenant DISCARD ALL gotcha.
//
// Many Java applications switch search_path per tenant:
//
//   SET search_path TO tenant_acme, public;
//   -- run queries
//   SET search_path TO tenant_globex, public;
//   -- run queries
//
// When using connection pooling, you might reset state between tenants:

// HikariCP connectionInitSql — runs on every connection checkout:
config.setConnectionInitSql("DISCARD ALL");

// DISCARD ALL resets EVERYTHING on the connection:
//   - search_path        (back to default)
//   - prepared statements (all deallocated)
//   - temporary tables    (dropped)
//   - session variables   (reset)
//   - advisory locks      (released)
//
// The prepared statement impact:
//   1. pgjdbc's client-side counter thinks the statement is still prepared
//   2. PostgreSQL has deallocated it
//   3. Next EXECUTE fails: "prepared statement S_1 does not exist"
//
// pgjdbc recovers by re-preparing, but:
//   - The re-prepare resets the generic/custom plan counter
//   - You never accumulate enough executions for plan caching
//   - Every checkout = cold start for every prepared statement
//   - The 15-30% prepared statement advantage evaporates entirely
//
// Better alternatives:
//   DISCARD PLANS;        -- only resets cached plans, keeps prep stmts
//   RESET search_path;    -- only resets search_path
//   SET search_path TO ...; -- just set what you need directly

Multi-tenant applications that use schema-per-tenant often set search_path at the beginning of each request. When connections are returned to the pool, the previous tenant's search_path must be cleared. The temptation is to use DISCARD ALL — it resets everything, guaranteeing a clean connection.

The problem: DISCARD ALL also deallocates every prepared statement on the connection. pgjdbc will re-prepare them on next use, but the re-preparation resets PostgreSQL's custom/generic plan counter. If DISCARD ALL runs on every connection checkout, no prepared statement ever accumulates enough executions to stay cached. You pay the parse overhead on every checkout, the plan overhead on every execution, and the generic plan problem never manifests — but only because you have eliminated plan caching entirely, accidentally.

The symptoms are subtle. You will not see errors. You will not see 57-second queries. You will see uniformly elevated latency across all queries — perhaps 20-40% higher than expected — with no obvious cause. pg_prepared_statements will show low generic_plans and low custom_plans counts for every statement, because they keep getting deallocated before they can accumulate.

There is a certain dark irony here: DISCARD ALL prevents the generic plan catastrophe by preventing plan caching entirely. Some teams discover this accidentally and conclude their database is "slow." They add read replicas, they increase instance size, they add Redis caching in front of queries that PostgreSQL could serve in 2ms if only the prepared statement cache were allowed to function. The root cause — a three-word SQL command in a connection pool configuration — is invisible because it does not produce errors. It produces absence. The absence of the performance that prepared statements were designed to provide.

The fix: replace DISCARD ALL with targeted resets. RESET search_path or an explicit SET search_path TO ... is sufficient for tenant isolation without destroying prepared statement state. If you also need to reset plan_cache_mode or other session variables, reset them individually. The connection pooling guide covers additional strategies for managing per-connection state in pooled environments.

The honest counterpoint: when generic plans are exactly right

I have spent considerable ink describing how generic plans can be catastrophic, and I should be equally forthcoming about when they are correct and beneficial. A waiter who overstates his case is no waiter at all.

Uniformly distributed columns. Primary keys, auto-incrementing IDs, UUIDs, and most foreign keys have uniform distribution. Every value matches roughly the same number of rows. The generic plan's row estimate matches any custom plan's estimate. There is no skew. There is no regression. The generic plan saves 0.2-0.8ms of planning overhead on every execution, and for a high-throughput application executing 10,000 queries per second, that saving is 2-8 seconds of CPU per second. On a 4-core machine, that is meaningful.

Small tables. If your table has 10,000 rows and fits in memory, even a "wrong" Seq Scan completes in under a millisecond. The generic plan cannot cause a catastrophic regression because there is no catastrophe available — the worst-case plan is still fast. For tables under 100,000 rows, generic plan regression is rarely noticeable in practice.

CPU-bound workloads. If your PostgreSQL server is CPU-constrained (not I/O-constrained, which is more common), the planning overhead of force_custom_plan is a real cost. For simple queries (single-table, single-predicate), planning is cheap. For complex queries (multi-join, subqueries, CTEs), planning can take 1-5ms. If you have verified that your workload has no skewed columns and your monitoring shows no execution time variance, the generic plan optimization is working as designed and force_custom_plan would be a net cost.

The 80/20 assessment. In my experience, approximately 80% of production databases have at least one query affected by generic plan regression, but only 5-10% of queries in those databases are actually vulnerable. The remaining 90-95% of queries benefit from generic plans or are unaffected by them. This is why session-level force_custom_plan is a pragmatic choice rather than the theoretically optimal one — it trades a small, predictable overhead on the 90% for safety on the 10%. For most teams, that trade is obviously worth it. For teams with extremely CPU-sensitive workloads, per-transaction targeting is the more precise instrument.

Choosing your fix: the decision guide

I have presented several mitigation strategies, and the number of options can itself be a source of confusion. Allow me to reduce the decision to its essentials.

# Decision guide: which fix is right for your application?
#
# Q1: Are you on PostgreSQL 12+?
#   YES → Use plan_cache_mode = 'force_custom_plan' (see below)
#   NO  → Go to Q2
#
# Q2: Can you upgrade to PostgreSQL 12+?
#   YES → Do that. plan_cache_mode is worth the upgrade alone.
#   NO  → Use prepareThreshold=0 or wrapper functions (see "Pre-12" section)
#
# Q3: Do you know WHICH queries have skewed parameters?
#   YES → force_custom_plan per-transaction for those queries only
#   NO  → force_custom_plan at the session level (safest default)
#
# Q4: Are you using PgBouncer in transaction mode?
#   YES → SET LOCAL plan_cache_mode in each transaction, OR
#         use PgBouncer connect_query, OR use Gold Lapel instead
#   NO  → Session-level SET or connection-init-sql is sufficient
#
# Q5: Is CPU the bottleneck on your PostgreSQL server?
#   YES → force_custom_plan adds 15-40% planning overhead.
#         Consider targeting only skewed queries.
#   NO  → force_custom_plan globally. The overhead is negligible
#         relative to I/O and network latency.
#
# The honest summary:
#   Most applications should SET plan_cache_mode = 'force_custom_plan'
#   at the session level and stop worrying about it. The CPU cost
#   is real but the alternative — random 57-second queries at 3 AM —
#   is not a trade-off anyone should accept.

For the vast majority of Java applications reading this page, the answer is: add connection-init-sql: SET plan_cache_mode = 'force_custom_plan' to your HikariCP configuration and move on with your life. The overhead is bounded. The protection is comprehensive. The configuration change takes thirty seconds.

The exception is if you are on PostgreSQL 11 or earlier, in which case prepareThreshold=0 is the simplest safe default, and an upgrade to PostgreSQL 12+ should be on your roadmap for this and many other reasons.

The other exception is if you are running a CPU-constrained, high-throughput workload where you have profiled the planning overhead and determined it is material. In that case, target force_custom_plan per-transaction around only the queries with skewed parameters, and let the rest benefit from generic plan caching. This requires more discipline and more knowledge of your data, but it is the most efficient approach.

Where Gold Lapel handles this at the proxy layer

I will be brief, since you arrived here looking for a fix, not an advertisement.

The core problem is that prepared statement plan caching is a server-side decision that application-side configuration can only partially influence. prepareThreshold controls when statements are prepared. plan_cache_mode controls whether generic plans are used. But neither gives you visibility into which specific queries have regressed, or the ability to react dynamically when a plan goes wrong.

# How Gold Lapel handles this at the proxy layer:
#
# 1. Prepared statement promotion with LRU cache (1,024 slots per connection)
#    GL observes query traffic and promotes frequently-executed
#    queries to server-side prepared statements automatically.
#    Your application doesn't need to configure prepareThreshold.
#
# 2. Plan stability monitoring
#    GL tracks execution times per prepared statement per parameter
#    pattern. When it detects a plan regression (execution time
#    jumps by 10x+ for a specific parameter value), it can force
#    re-planning with the actual parameter.
#
# 3. Connection-aware statement management
#    Prepared statements are bound to backend connections.
#    GL maintains the mapping between statements and connections
#    across pool cycling — no DISCARD ALL, no lost prep stmts.
#
# 4. The search_path problem disappears
#    GL manages search_path at the proxy layer. Tenant isolation
#    doesn't require connection-level state resets.
#
# Net effect: your Java application sends ordinary JDBC queries.
# GL decides what to prepare, manages plan stability, and handles
# the connection lifecycle. No pgjdbc tuning. No plan_cache_mode.
# No 57-second surprises.

Gold Lapel operates between your Java application and PostgreSQL, handling prepared statement promotion through an LRU cache of 1,024 slots per connection. It observes query traffic, promotes frequently-executed queries automatically, and — critically — maintains the statement-to-connection mapping across pool cycling. No DISCARD ALL. No lost prepared statements. No counter resets.

For the generic plan problem specifically, Gold Lapel tracks execution times per statement and can detect when a plan regression occurs. The proxy knows what plan_cache_mode and prepareThreshold cannot: whether a specific prepared statement is actually performing well for the traffic it receives.

Change the JDBC connection string from your PostgreSQL host to Gold Lapel's port. Keep your existing HikariCP configuration, your existing queries, your existing prepareThreshold. The proxy handles the rest.

But if you have read this far and prefer to manage it yourself — plan_cache_mode = 'force_custom_plan', applied at the session level, will serve you well. It is a one-line configuration change, and it eliminates a class of failure that has no business appearing in a Tuesday afternoon incident review. I would rather you solve this problem correctly without Gold Lapel than solve it poorly with anything.