The endpoint that serves our order history was fine for months. Then, during a routine traffic increase, the p95 jumped from 12 ms to 400 ms. CPU on the Postgres primary climbed from 20% to 80%. The query had not changed. The schema had not changed. The data distribution had shifted slightly (a few customers now had tens of thousands of orders instead of hundreds), but the query plan in psql still showed an Index Scan finishing in 2 ms.

The problem was not the query. It was the plan. Our ORM (Prisma, in this case) was using prepared statements. Postgres had compiled a generic plan after five executions, and that generic plan assumed a uniform distribution that no longer existed. For the customers with small order histories, the plan was fine. For the customers with large histories, the plan was a disaster.

This post is about how Postgres plan caching works, the exact threshold where custom plans become generic plans, the data-skew scenario that breaks them, and the three ways to fix it without abandoning prepared statements.

How prepared statements and plan caching work

When a client sends a query like SELECT * FROM orders WHERE user_id = 42, the Postgres backend parses it, rewrites it, plans it, and executes it. Planning is not free. For complex queries with many joins, planning can take milliseconds. For simple queries, it is microseconds, but at thousands of queries per second, even microseconds add up.

Prepared statements split this pipeline. The client sends PREPARE stmt AS SELECT * FROM orders WHERE user_id = $1. Postgres parses and rewrites once, stores the query tree, and returns a statement handle. Later, the client sends EXECUTE stmt(42). Postgres skips parsing and rewriting, generates a plan for the specific parameter (42), and executes it.

The key detail is in the planning step. For the first five executions, Postgres generates a custom plan: it looks at the specific parameter value, checks the statistics for that value, and builds a plan optimized for it. If user_id = 42 matches 5 rows, it uses an index scan. If it matches 500,000 rows, it uses a sequential scan. The plan is tailored to the parameter.

On the sixth execution, Postgres makes a cost-based decision: is the average custom plan cheaper than a single generic plan that works for all parameter values? If the generic plan wins, every subsequent execution uses that same plan regardless of the parameter. The threshold is hardcoded at five custom plans. There is no GUC to change it.

When generic plans fail

Generic plans work well when the data distribution is uniform. If every user_id has roughly the same number of orders, a single plan is fine. But real data is never uniform. A marketplace has power sellers with 100,000 orders and casual buyers with three. A SaaS platform has enterprise tenants with millions of rows and free-tier tenants with dozens.

Here is a reproducible example. Create a table with skewed data:

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  status text NOT NULL,
  total numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Insert 1M rows: 90% belong to user_id = 1, the rest are spread across 1000 users
INSERT INTO orders (user_id, status, total)
SELECT
  CASE WHEN random() < 0.9 THEN 1 ELSE (random() * 1000)::bigint + 2 END,
  CASE WHEN random() < 0.5 THEN 'shipped' ELSE 'pending' END,
  random() * 500
FROM generate_series(1, 1000000);

ANALYZE orders;

Now run a prepared statement that filters by user_id and status:

PREPARE order_query(bigint, text) AS
SELECT * FROM orders
WHERE user_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 20;

Execute it five times for the small user (user_id = 50, which has ~100 rows):

EXECUTE order_query(50, 'shipped');
-- run 5 times

Check the plan for the fifth execution:

EXPLAIN (ANALYZE, BUFFERS) EXECUTE order_query(50, 'shipped');

You will see an Index Scan using idx_orders_user_status with a small cost. The custom plan knows user 50 has few rows, so the index is perfect.

Now execute it for the large user (user_id = 1, which has 900,000 rows):

EXPLAIN (ANALYZE, BUFFERS) EXECUTE order_query(1, 'shipped');

On the sixth execution, Postgres switches to the generic plan. The generic plan was built using the average statistics, or possibly using the statistics from the first parameter values it saw. If it decides an Index Scan is the generic plan, the query for user 1 will scan 450,000 index entries (half are shipped), sort them, and return 20. The execution time will be hundreds of milliseconds. A sequential scan with a filter would have been faster for user 1, but the generic plan does not know that.

You can verify which plan type is being used by checking the plan_cache_mode behavior or by observing the plan output. In Postgres 12+, EXPLAIN on a prepared statement shows whether the plan is generic. Look for identical plans across different parameter values. If the plan does not change when the parameter changes dramatically, you are looking at a generic plan.

Detecting plan instability in production

The first symptom is usually a latency spike that correlates with a specific parameter value, not with overall load. Your p50 is flat, but your p99 has a tail. If you have pg_stat_statements enabled, look for queries with a high standard deviation in execution time:

SELECT
  query,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
  ROUND(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_statements
WHERE stddev_exec_time > mean_exec_time * 2
  AND calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 10;

A query with a mean of 5 ms and a standard deviation of 50 ms is a prime candidate for plan instability. The fast executions are the small-parameter cases. The slow executions are the large-parameter cases hitting the wrong generic plan.

The second diagnostic is to capture the actual plan for a slow execution. If your application logs slow queries, grab one and run it manually in psql with the same parameter values. If psql is fast but production is slow, the difference is often the plan type. psql without prepared statements always uses custom plans.

You can also force a custom plan for a single execution to confirm the hypothesis:

SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE user_id = 1 AND status = 'shipped'
ORDER BY created_at DESC LIMIT 20;
RESET plan_cache_mode;

If the custom plan is fast and the generic plan is slow, you have found the culprit.

Fix 1: Force custom plans for the specific query

Postgres 12 introduced plan_cache_mode, which controls whether prepared statements use generic or custom plans. The default is auto (switch after five executions). You can set it to force_custom_plan for a session or a transaction:

SET LOCAL plan_cache_mode = force_custom_plan;

In application code, you can set this before running the problematic query. With Node.js pg, you can send it as a preamble:

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function getOrders(userId, status) {
  const client = await pool.connect();
  try {
    await client.query('SET LOCAL plan_cache_mode = force_custom_plan');
    const result = await client.query(
      `SELECT * FROM orders
       WHERE user_id = $1 AND status = $2
       ORDER BY created_at DESC LIMIT 20`,
      [userId, status]
    );
    return result.rows;
  } finally {
    client.release();
  }
}

The trade-off is planning overhead. Every execution now plans from scratch. For simple queries, this is negligible (microseconds). For complex queries with ten joins, it can add milliseconds. Measure before you deploy. If the query is simple and the parameter skew is severe, custom plans are almost always the right choice.

Fix 2: Rewrite the query to be plan-agnostic

Sometimes you can rewrite the query so that the same plan is optimal for all parameter values. The most common technique is to add a LIMIT or CTE that constrains the planner’s options.

For our skewed orders example, the generic index scan is bad for user 1 because the index returns hundreds of thousands of rows before filtering by status. If we restructure the query to use a subquery that the planner can reason about more consistently, we can sometimes get a better generic plan:

WITH user_orders AS (
  SELECT * FROM orders
  WHERE user_id = $1
  ORDER BY created_at DESC
  LIMIT 1000
)
SELECT * FROM user_orders
WHERE status = $2
ORDER BY created_at DESC
LIMIT 20;

The LIMIT 1000 inside the CTE gives the planner a bounded cost for the inner scan. Even for user 1, scanning 1000 rows by user_id and then filtering by status is predictable. The generic plan is now an index scan on idx_orders_user_id with a bounded cost, and it performs acceptably for both small and large users.

This is not a silver bullet. The LIMIT 1000 is a business decision: are there cases where the first 1000 rows contain fewer than 20 matching rows? If so, the query is now wrong. But for many real-world cases (“show the latest 20 shipped orders”), the last 1000 orders almost certainly contain 20 shipped ones.

Another rewrite technique is to make the query so selective that only one plan makes sense. If you add a date range that is always present:

SELECT * FROM orders
WHERE user_id = $1
  AND status = $2
  AND created_at > now() - interval '90 days'
ORDER BY created_at DESC
LIMIT 20;

Even for user 1, the 90-day window may contain only a few thousand rows. The generic plan can safely choose an index scan because the date predicate bounds the cost regardless of user_id.

Fix 3: Partition or shard by the skew dimension

If the skew is structural (one tenant has 1000× the data of another), the real fix is often at the schema level. Postgres declarative partitioning by user_id range or hash can isolate the large user’s data into its own partition. The planner then generates per-partition plans, and the generic plan for the small-user partition is correct.

For multi-tenant SaaS, partitioning by tenant_id is a common pattern. The query becomes:

SELECT * FROM orders
WHERE tenant_id = $1 AND user_id = $2 AND status = $3
ORDER BY created_at DESC LIMIT 20;

With a partition key on tenant_id, each tenant’s data is in a separate physical partition. The generic plan is generated per-partition, so a small tenant gets the right plan and a large tenant gets a different plan. Partition pruning happens at planning time, so the overhead is minimal.

This is the most invasive fix, but it is the right one if your data skew is tenant-level and growing. It also makes VACUUM, REINDEX, and archival easier because you can operate on individual partitions.

The ORM problem

Most ORMs use prepared statements by default. Prisma, Sequelize, TypeORM, and Drizzle all parameterize queries to prevent SQL injection and reduce parsing overhead. They do not expose plan_cache_mode in their connection configuration. You have to drop to raw SQL or a custom transaction block to set it.

If you are using an ORM and seeing plan instability, your options are:

1. Use a raw query for the affected endpoint. Set plan_cache_mode = force_custom_plan in the same transaction.
2. Configure the ORM to not use prepared statements for that query. Some ORMs allow prepareThreshold=0 at the driver level, which disables server-side prepared statements entirely. This is a blunt instrument but effective if the problem is widespread.
3. Fix the schema or query so generic plans are safe. This is the cleanest solution if you can find a rewrite.

With Node.js pg, you can disable prepared statements globally by setting prepareThreshold: 0 on the pool:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Disable server-side prepared statements
  prepareThreshold: 0,
});

This forces the driver to send the full query text every time. Postgres still parses and plans every query, but it never caches a generic plan. The overhead is higher, but you eliminate the entire class of plan instability bugs. Use this as a temporary measure while you fix the underlying query or schema.

Monitoring plan cache behavior

Postgres does not expose a direct view for “how many generic plans are cached,” but you can infer it from pg_prepared_statements:

SELECT
  name,
  statement,
  parameter_types,
  from_sql
FROM pg_prepared_statements;

This shows currently prepared statements in your session. For connection-pooled applications, each connection has its own cache, so the same query may be prepared dozens of times across the pool. There is no global view.

A practical monitoring approach is to add a canary query to your health check. Run the problematic query with a known-bad parameter (the large user) and assert that it finishes in under 100 ms. If it spikes, alert immediately. This catches plan instability before it affects real users.

A practical takeaway

Plan instability is not a Postgres bug. It is a consequence of optimizing for the average case when your data is not average. The five-execution threshold for generic plans is invisible until it bites you, and by then your p99 is already ruined.

The diagnostic chain is simple: if a query is fast in psql but slow in production, and the slowness correlates with specific parameter values, suspect a generic plan. Confirm with SET plan_cache_mode = force_custom_plan. Fix with a session-level override, a query rewrite, or schema partitioning. Do not reach for a bigger instance. The CPU is not the bottleneck. The plan is.

A note from Yojji

Diagnosing plan instability requires reading pg_stat_statements standard deviations, reproducing skewed data distributions in staging, and deciding whether a query rewrite or a schema partition is the right long-term fix. That kind of database engineering (treating the planner as a system to be understood, not a black box to be scaled around) is what Yojji’s backend teams do when they build full-cycle products for clients who need their Postgres to stay fast as their data grows unevenly.