Welcome to the 4th blog post of PG19 Hacktober!!
pg_stat_statements is one of the PostgreSQL’s primary tool for tracking query performance statistics. However, cursor-heavy applications that use varying FETCH sizes can generate a large number of statistics entries, causing the monitoring extension itself to become a performance bottleneck through entry churn.
In PostgreSQL 18, I encountered this firsthand: an application retrieving data through explicit cursors with different batch sizes generated thousands of low-value entries. The issue wasn’t the underlying query execution, but the overhead of pg_stat_statements maintaining separate statistics for different FETCH sizes.
PostgreSQL 19 addresses this by grouping FETCH statements with different fetch sizes into unified statistics entries. Based on recent discussions and proposals on the pgsql-hackers mailing list, this post walks through the PostgreSQL 18 baseline, reproduces the issue, and demonstrates how PostgreSQL 19 improves cursor tracking.
Understanding the Problem: Cursor Bloat in pg_stat_statements
To understand why these changes matter, let’s first examine how pg_stat_statements handles cursor-related SQL in PostgreSQL 18 and earlier.
The Three Core Issues:
Issue 1: FETCH/MOVE statements don’t aggregate properlyFETCH 10 c1 and FETCH 20 c1 appear as separate entries because each unique fetch size generates a different queryId. In workloads with varying fetch sizes, this creates an explosion of entries with limited diagnostic value.
Issue 2: FETCH/CLOSE can’t be traced back to the underlying query
The DECLARE CURSOR statement may have already been evicted from pg_stat_statements by the time you examine the stats. A cursor name like c1 could be reused for entirely different queries across transactions, making the FETCH stats meaningless.
Issue 3: DECLARE CURSOR doesn’t expose execution stats for the underlying SQL
You must set pg_stat_statements.track = 'all' to see the underlying query, which adds overhead. There’s also a bug where the toplevel column is incorrectly marked as true for the nested query.
What changed: PG18 (Baseline) vs PG19 (Improved)
Here’s a high-level comparison of cursor handling behavior between the two versions:
| Feature | PostgreSQL 18 | PostgreSQL 19 |
|---|---|---|
| FETCH size normalization | Each fetch size creates a separate entry | Fetch sizes are normalized to $1 |
FETCH ALL vs FETCH N | FETCH ALL and numeric fetches can be mixed | FETCH ALL remains a distinct entry |
FETCH direction keywords | FIRST, LAST, and BACKWARD can be conflated | Each direction gets a distinct queryId |
queryId for FETCH variants | Based on the literal SQL text | Based on fetch direction and cursor name |
| Entry count for cursor workloads | Grows with unique fetch sizes | More stable: cursor × direction |
| Generic/Custom plan tracking | No visibility | generic_plan_calls / custom_plan_calls available |
Deep Dive: FETCH Normalization in PostgreSQL 19
The key change in PostgreSQL 19 is the introduction of a new FetchDirectionKeywords enum and a direction_keyword field in FetchStmt. This allows the query jumbling infrastructure to:
- Ignore the numeric fetch size via query_jumble_ignore on howMany
- Distinguish direction keywords (FIRST, LAST, FORWARD ALL, BACKWARD ALL, etc.) via the new enum field that is included in the jumble
- Preserve cursor name distinction — FETCH $1 cursor_a and FETCH $1 cursor_b remain separate entries
Test Cases: PG18 vs PG19 Side-by-Side
Test case in PG18
Setup: enable the extension and reset statistics
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
----------------------------------
2026-10-04 07:53:08.914186+05:30
(1 row)Declare two cursors :
postgres=# BEGIN;
DECLARE cs1 CURSOR FOR SELECT * FROM pg_class;
DECLARE cs2 CURSOR FOR SELECT * FROM pg_class;
BEGIN
DECLARE CURSOR
DECLARE CURSORRun FETCH with different sizes and directions
Just displaying 5 records from query output to minimize screen space
FETCH 2 FROM cs1;
FETCH 3 FROM cs1;
FETCH 2 FROM cs2;
FETCH FORWARD ALL FROM cs1;
FETCH FIRST FROM cs1;
FETCH LAST FROM cs1;
FETCH BACKWARD 2 FROM cs1;Close the cursors and reuse a cursor name
postgres=*#CLOSE cs1;
CLOSE cs2;
CLOSE CURSOR
CLOSE CURSOR
postgres=*#
postgres=*# DECLARE cs1 CURSOR FOR SELECT * FROM pg_attribute;
DECLARE CURSORInspect pg_stat_statements
postgres=*# SELECT calls, rows, query, toplevel
FROM pg_stat_statements
ORDER BY query COLLATE "C";
COMMIT;
calls | rows | query | toplevel
-------+------+---------------------------------------------------+----------
1 | 0 | BEGIN | t
1 | 0 | CLOSE cs1 | t
1 | 0 | CLOSE cs2 | t
1 | 0 | DECLARE cs1 CURSOR FOR SELECT * FROM pg_attribute | t
1 | 0 | DECLARE cs1 CURSOR FOR SELECT * FROM pg_class | t
1 | 0 | DECLARE cs2 CURSOR FOR SELECT * FROM pg_class | t
2 | 2 | FETCH 2 cs1 | t
1 | 2 | FETCH 2 cs2 | t
1 | 2 | FETCH 3 cs1 | t
1 | 2 | FETCH BACKWARD 2 cs1 | t
1 | 1 | FETCH FIRST cs1 | t
1 | 20| FETCH FORWARD ALL cs1 | t
1 | 1 | FETCH LAST cs1 | t
0 | 0 | SELECT calls, rows, query, toplevel +| t
| | FROM pg_stat_statements +|
| | ORDER BY query COLLATE "C" |
1 | 1 | SELECT pg_stat_statements_reset() | t
(15 rows)
COMMIT
Notice:
FETCH 2 FROM cs1andFETCH 3 FROM cs1are tracked as two separate entries. Every new batch size adds another row.
Test case in PG19
Setup: enable the extension and reset statistics
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
----------------------------------
2026-10-04 07:53:14.050953+05:30Declare two cursors
postgres=# BEGIN;
DECLARE cs1 CURSOR FOR SELECT * FROM pg_class;
DECLARE cs2 CURSOR FOR SELECT * FROM pg_class;
BEGIN
DECLARE CURSOR
DECLARE CURSORRun FETCH with different sizes and directions
Just displaying 2 records from query output to minimize screen space
FETCH 2 FROM cs1;
FETCH 3 FROM cs1;
FETCH 2 FROM cs2;
FETCH FORWARD ALL FROM cs1;
FETCH FIRST FROM cs1;
FETCH LAST FROM cs1;
FETCH BACKWARD 2 FROM cs1;Close the cursors and reuse a cursor name & Inspect pg_stat_statements
postgres=*# CLOSE cs1;
CLOSE cs2;
DECLARE cs1 CURSOR FOR SELECT * FROM pg_attribute;
CLOSE CURSOR
CLOSE CURSOR
DECLARE CURSOR
postgres=*#
postgres=*# SELECT calls, rows, query, toplevel
FROM pg_stat_statements
ORDER BY query COLLATE "C";
COMMIT;
calls | rows | query | toplevel
-------+------+---------------------------------------------------+----------
1 | 0 | BEGIN | t
1 | 0 | CLOSE cs1 | t
1 | 0 | CLOSE cs2 | t
1 | 0 | DECLARE cs1 CURSOR FOR SELECT * FROM pg_attribute | t
1 | 0 | DECLARE cs1 CURSOR FOR SELECT * FROM pg_class | t
1 | 0 | DECLARE cs2 CURSOR FOR SELECT * FROM pg_class | t
3 | 40 | FETCH $1 cs1 | t
1 | 10 | FETCH $1 cs2 | t
1 | 5 | FETCH BACKWARD $1 cs1 | t
1 | 1 | FETCH FIRST cs1 | t
1 | 391 | FETCH FORWARD ALL cs1 | t
1 | 1 | FETCH LAST cs1 | t
1 | 1 | SELECT pg_stat_statements_reset() | t
(13 rows)
COMMIT
postgres=#Key Observations from the Test :
- Batch Aggregation:
FETCH 2 FROM cs1andFETCH 3 FROM cs1collapsed into a singleFETCH $1 FROM cs1entry withcalls = 2androws = 5. - Cursor Isolation:
FETCH $1 FROM cs1andFETCH $1 FROM cs2remain separate entries. - Directional Clarity:
FETCH FIRST,FETCH LAST,FETCH FORWARD ALL, andFETCH BACKWARD $1each maintain distinct identifiers.
Plan Cache Tracking: generic_plan_calls & custom_plan_calls
A separate but equally important improvement proposed for PostgreSQL 19 is the addition of plan cache tracking columns to pg_stat_statements. Currently, pg_prepared_statements shows generic vs. custom plan counts, but this information is backend-local and resets when the session ends.
Test case pg18:
pg_stat_statements
A second major feature in PostgreSQL 19 is the addition of plan cache metrics to pg_stat_statements.
Previously, pg_prepared_statements exposed generic vs. custom plan counts, but that view is session-local and discarded when connections terminate. PostgreSQL 19 tracks cumulative plan types globally across all backends.
postgres=# SELECT query, calls,
generic_plan_calls,
custom_plan_calls,
mean_exec_time
FROM pg_stat_statements;
ERROR: column "generic_plan_calls" does not exist
LINE 2: generic_plan_calls,
^Check for plan type columns (the column "generic_plan_calls" does not exist error
Test case pg19
pg_stat_statements
New columns track cumulative plan type usage across all sessions and over time.
postgres=# SELECT
query,
calls,
generic_plan_calls,
custom_plan_calls,
mean_exec_time
FROM public.pg_stat_statements;
query
| calls | generic_plan_calls | custom_plan_calls | mean_exec_time
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
----------+-------+--------------------+-------------------+---------------------
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.re
lforcerowsecurity, $1 AS relhasoids, c.relispartition, pg_catalog.array_to_string(c.reloptions || arra
y(select $2 || x from pg_catalog.unnest(tc.reloptions) x), $3)
+| 1 | 0 | 0 | 0.150401
, c.reltablespace, CASE WHEN c.reloftype = $4 THEN $5 ELSE c.reloftype::pg_catalog.regtype::pg_catalo
g.text END, c.relpersistence, c.relreplident, am.amname
+| | | |
FROM pg_catalog.pg_class c
+| | | |
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
+| | | |
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
+| | | |
WHERE c.oid = $6
| | | |
FETCH BACKWARD $1 FROM cs1
| 1 | 0 | 0 | 0.004445
DECLARE cs1 CURSOR FOR SELECT * FROM pg_attribute
| 1 | 0 | 0 | 0.629976
SELECT
+| 2 | 0 | 0 | 0.29092799999999996
query,
+| | | |
calls,
+| | | |
mean_plan_time,
+| | | |
mean_exec_time
+| | | |
FROM public.pg_stat_statements
+| | | |
ORDER BY query COLLATE "C"
| | | |
DECLARE cs1 CURSOR FOR SELECT * FROM pg_class
| 1 | 0 | 0 | 0.602923
SELECT a.attname,
+| 1 | 0 | 0 | 0.241821
pg_catalog.format_type(a.atttypid, a.atttypmod),
+| | | |
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, $1)
+| | | |
FROM pg_catalog.pg_attrdef d
+| | | |
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
+| | | |
a.attnotnull,
+| | | |
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
+| | | |
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attco
llation,
+| | | |
a.attidentity,
+| | | |
a.attgenerated
+| | | |
FROM pg_catalog.pg_attribute a
+| | | |
WHERE a.attrelid = $2 AND a.attnum > $3 AND NOT a.attisdropped
+| | | |
ORDER BY a.attnum
| | | |
SELECT query, calls, mean_exec_time
+| 1 | 0 | 0 | 0.291333
FROM public.pg_stat_statements
| | | |
FETCH FORWARD ALL FROM cs1
| 1 | 0 | 0 | 0.089086
DECLARE cs2 CURSOR FOR SELECT * FROM pg_class
| 1 | 0 | 0 | 0.09583
SELECT c.oid,
+| 1 | 0 | 0 | 0.163007
n.nspname,
+| | | |
c.relname
+| | | |
FROM pg_catalog.pg_class c
+| | | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+| | | |
WHERE c.relname OPERATOR(pg_catalog.~) $1 COLLATE pg_catalog.default
+| | | |
AND pg_catalog.pg_table_is_visible(c.oid)
+| | | |
ORDER BY 2, 3
| | | |
SELECT r.rulename, trim(trailing $1 from pg_catalog.pg_get_ruledef(r.oid, $2))
+| 1 | 0 | 0 | 0.00866
FROM pg_catalog.pg_rewrite r
+| | | |
WHERE r.ev_class = $3 AND r.rulename != $4 ORDER BY 1
| | | |
SELECT column_name, data_type
+| 1 | 0 | 0 | 3.511374
FROM information_schema.columns
+| | | |
WHERE table_schema = $1
+| | | |
AND table_name = $2
+| | | |
ORDER BY ordinal_position
| | | |
SET search_path = $1
| 2 | 0 | 0 | 0.028064
BEGIN
| 1 | 0 | 0 | 0.002691
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
+| 1 | 0 | 0 | 8.061893
EXECUTE customer_lookup(3)
| | | |
FETCH FIRST FROM cs1
| 1 | 0 | 0 | 0.05834
SELECT c.relname, $1::pg_catalog.text FROM pg_catalog.pg_class c WHERE c.relkind IN ($2 /*, ... */) A
ND (c.relname) LIKE $3 AND pg_catalog.pg_table_is_visible(c.oid)
+| 4 | 0 | 0 | 0.12684875
UNION ALL
+| | | |
SELECT $4::pg_catalog.text, n.nspname FROM pg_catalog.pg_namespace n WHERE n.nspname LIKE $5
+| | | |
LIMIT $6
| | | |
FETCH $1 FROM cs2
| 1 | 0 | 0 | 0.012943
SELECT calls, rows, query, toplevel
+| 1 | 0 | 0 | 0.169362
FROM pg_stat_statements
+| | | |
ORDER BY query COLLATE "C"
| | | |
SELECT c.relname, $1::pg_catalog.text FROM pg_catalog.pg_class c WHERE (c.relname) LIKE $2 AND pg_cat
alog.pg_table_is_visible(c.oid)
+| 2 | 0 | 0 | 0.247738
UNION ALL
+| | | |
SELECT $3::pg_catalog.text, n.nspname FROM pg_catalog.pg_namespace n WHERE n.nspname LIKE $4
+| | | |
LIMIT $5
| | | |
CLOSE cs2
| 1 | 0 | 0 | 0.010461
SELECT
+| 1 | 0 | 0 | 0.247576
query,
+| | | |
calls,
+| | | |
generic_plan_calls,
+| | | |
custom_plan_calls,
+| | | |
mean_exec_time
+| | | |
FROM public.pg_stat_statements
| | | |
COMMIT
| 1 | 0 | 0 | 0.001927
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Descr
iption"
+| 4 | 0 | 0 | 0.11283725
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOI
N pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = $1::pg_catalog.regclass
+| | | |
ORDER BY 1
| | | |
FETCH $1 FROM cs1
| 2 | 0 | 0 | 0.033471
SELECT a.attname,
+| 1 | 0 | 0 | 1.265511
pg_catalog.format_type(a.atttypid, a.atttypmod),
+| | | |
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, $1)
+| | | |
FROM pg_catalog.pg_attrdef d
+| | | |
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
+| | | |
a.attnotnull,
+| | | |
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
+| | | |
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attco
llation,
+| | | |
a.attidentity,
+| | | |
a.attgenerated,
+| | | |
a.attstorage,
+| | | |
pg_catalog.col_description(a.attrelid, a.attnum)
+| | | |
FROM pg_catalog.pg_attribute a
+| | | |
WHERE a.attrelid = $2 AND a.attnum > $3 AND NOT a.attisdropped
+| | | |
ORDER BY a.attnum
| | | |
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.re
lforcerowsecurity, $1 AS relhasoids, c.relispartition, $2, c.reltablespace, CASE WHEN c.reloftype = $3
THEN $4 ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident,
am.amname+| 1 | 0 | 0 | 0.033865
FROM pg_catalog.pg_class c
+| | | |
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
+| | | |
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
+| | | |
WHERE c.oid = $5
| | | |
SELECT pg_stat_statements_reset()
| 1 | 0 | 0 | 0.159595
FETCH LAST FROM cs1
| 1 | 0 | 0 | 0.082378
CLOSE cs1
| 1 | 0 | 0 | 0.025972
SELECT c.oid,
+| 1 | 0 | 0 | 0.190116
n.nspname,
+| | | |
c.relname
+| | | |
FROM pg_catalog.pg_class c
+| | | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+| | | |
WHERE c.relname OPERATOR(pg_catalog.~) $1 COLLATE pg_catalog.default
+| | | |
AND n.nspname OPERATOR(pg_catalog.~) $2 COLLATE pg_catalog.default
+| | | |
ORDER BY 2, 3
| | | |
SET plan_cache_mode = $1
| 2 | 0 | 0 | 0.0499165
SELECT pg_catalog.pg_get_viewdef($1::pg_catalog.oid, $2)
| 1 | 0 | 0 | 1.5422
select version()
| 1 | 0 | 0 | 0.01872
SELECT
+| 1 | 0 | 0 | 0.234409
query,
+| | | |
calls,
+| | | |
plans,
+| | | |
generic_plan_calls,
+| | | |
custom_plan_calls,
+| | | |
mean_plan_time,
+| | | |
mean_exec_time
+| | | |
FROM public.pg_stat_statements
+| | | |
WHERE query LIKE $1
+| | | |
ORDER BY query
| | | |
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
+| 2 | 0 | 0 | 217.091218
EXECUTE customer_lookup(1)
| | | |
(36 rows)Generate generic and custom plans with a prepared statement (the demo I suggested, since your current output is all zeros.
Why This Matters?
Detecting plan cache thrashing: If a query shows high custom_plan_calls but zero generic_plan_calls, PostgreSQL is regenerating plans every time — possibly due to parameter sensitivity. This is a common performance antipattern in prepared statement workloads.
Identifying suboptimal generic plans: A query with high generic_plan_calls and high mean_exec_time may be suffering from a poor generic plan that ignores parameter-specific statistics.
Cumulative, server-wide tracking: Unlike pg_prepared_statements (which is session-local), these counters aggregate across all backends, giving DBAs a true picture of plan cache behavior over time.
Implementation Detail
Rather than adding two new booleans to CachedPlan, the patch repurposes the existing is_reusable boolean as an enum that tracks plan states: “generic reused”, “generic first time”, and “custom”. This keeps the CachedPlan structure lean while providing the needed information to pg_stat_statements.
Here is the youtube video:
Conclusion:
Till today, PostgreSQL 19 beta 4 makes pg_stat_statements much better for cursor-heavy workloads, and adds a new way to observe plan cache behavior.
1. FETCH normalization cuts entry bloat. In PG18, every distinct fetch size (FETCH 2, FETCH 3, …) produced its own entry. PG19 normalizes the size to $1, so these collapse into one entry per cursor. In the test, FETCH 2 FROM cs1 and FETCH 3 FROM cs1 merged into FETCH $1 cs1 with 3 calls in total, and the output shrank from 15 rows to 13. Entry count now scales with cursor × direction rather than with the number of unique batch sizes.
2. Diagnostic detail is preserved where it matters.
- Different cursors (
cs1vscs2) stay separate. - Direction keywords (
FIRST,LAST,FORWARD ALL,BACKWARD $1) each keep their own distinct entry.
3. Plan cache visibility is new. PG19 adds generic_plan_calls and custom_plan_calls to pg_stat_statements (PG18 raises a “column does not exist” error). Unlike the session-local pg_prepared_statements, these counters are cumulative across all backends. They help DBAs to:
- Spot plan cache thrashing (high custom calls, zero generic calls).
- Identify poor generic plans (high generic calls with high mean execution time).
If you run applications that use explicit cursors with varying batch sizes, upgrading to PG19 should reduce statistics churn and make the monitoring data cleaner and more useful. The plan-type counters give an additional server-wide view of prepared-statement behavior.
In the PG19 plan-cache demo, all counters showed zeros, because no prepared statements had been run in a way that generated generic or custom plans. The post itself notes a better demo is needed. The plan-tracking feature is also described as a proposal based on pgsql-hackers discussions, so its final form could change.
Thank you for reading the blog!!