The PostgreSQL 14 end-of-life audit

The date, and the part of it that is not about security

PostgreSQL 14 has its final minor release on 12 November 2026. After that the community publishes no more fixes for it, including security fixes, and the version is out of support for good. If you are reading this after that date, nothing below needs rewriting; the audit is the same audit, and only the answer to “how long have we got” has changed.

The security argument is the one that gets budget approved and it is also the least interesting thing about this upgrade, because it says nothing about what the work involves. A fleet that moves from 14 to 17 or 18 crosses four majors of change to the statistics interface in a single step, and the parts of that change which fail loudly are not the parts that cost you. The parts that cost you are the ones where a query keeps returning rows and the rows no longer mean what the dashboard thinks they mean.

So this page is an audit rather than an argument. It covers what to establish about a 14 cluster before anyone schedules a window, and what happens to the monitoring on the way out.

What a 14 cluster can tell you about itself, and how much of it to believe

Start with the thing that is specific to this version and that no later one shares. On PostgreSQL 14 the statistics you read out of pg_stat_user_tables, pg_stat_database and the rest do not come from the server processes that produced them. Each backend sends counter deltas over a datagram socket to a separate collector process, the collector accumulates them and writes them to a file, and a session that reads a statistics view asks the collector for a fresh copy of that file. The reader gets whatever was written, with a tolerance of half a second, and a datagram that was dropped under load is simply gone.

That is not a theoretical concern and it is easy to see. Insert a thousand rows, let the counters settle, reset them, and read them back in the very next statement.

CREATE TABLE settlement (id integer PRIMARY KEY, amount numeric);
INSERT INTO settlement SELECT g, g * 2.25 FROM generate_series(1, 1000) AS g;
SELECT pg_sleep(2);
SELECT n_tup_ins AS before_the_reset FROM pg_stat_user_tables WHERE relname = 'settlement';
SELECT pg_stat_reset_single_table_counters('settlement'::regclass);
SELECT n_tup_ins AS immediately_after FROM pg_stat_user_tables WHERE relname = 'settlement';
SELECT pg_sleep(1);
SELECT n_tup_ins AS one_second_later FROM pg_stat_user_tables WHERE relname = 'settlement';

CREATE TABLE
INSERT 0 1000
 pg_sleep 
----------
 
(1 row)

 before_the_reset 
------------------
             1000
(1 row)

 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

 immediately_after 
-------------------
              1000
(1 row)

 pg_sleep 
----------
 
(1 row)

 one_second_later 
------------------
                0
(1 row)

The reset happened. The read that followed it did not see it, and a second later the same read did. Nothing is broken here: the reset is a message to the collector like any other, and the view is served from a file the collector had already written. Every counter you scrape off a 14 cluster arrives through that same path, which is why a per-second rate computed over a five-second scrape interval on this version has a genuine jitter in it that no exporter can remove.

PostgreSQL 15 replaced the collector with shared memory. Backends write their counters into a shared segment and readers read them directly, so the socket, the file and the dropped datagrams all stop existing. The same release added a setting controlling whether a transaction sees one frozen snapshot of the counters or the live values, and the presence of that setting is the cleanest single test of which side of the boundary a cluster is on.

SHOW stats_fetch_consistency;

ERROR:  unrecognized configuration parameter "stats_fetch_consistency"

On 18 the same sequence as above reads back what you would expect a counter reset to read back.

CREATE TABLE settlement (id integer PRIMARY KEY, amount numeric);
INSERT INTO settlement SELECT g, g * 2.25 FROM generate_series(1, 1000) AS g;
SELECT pg_sleep(2);
SHOW stats_fetch_consistency;
SELECT n_tup_ins AS before_the_reset FROM pg_stat_user_tables WHERE relname = 'settlement';
SELECT pg_stat_reset_single_table_counters('settlement'::regclass);
SELECT n_tup_ins AS immediately_after FROM pg_stat_user_tables WHERE relname = 'settlement';

CREATE TABLE
INSERT 0 1000
 pg_sleep 
----------
 
(1 row)

 stats_fetch_consistency 
-------------------------
 cache
(1 row)

 before_the_reset 
------------------
             1000
(1 row)

 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

 immediately_after 
-------------------
                 0
(1 row)

This matters for the audit in a direction people usually get backwards. It is not a reason to distrust the numbers you have been collecting off 14 for years; the jitter is small and it averages out. It is a reason to be careful about the comparison you are about to make, because the first week after the upgrade is the week somebody says a metric changed, and some of what changed is the fidelity of the measurement rather than the behaviour of the database.

The inventory, run twice

The rest of the audit is mechanical and it is worth doing with a query rather than a memory. Every column your monitoring names either exists on the version you are moving to or does not, and a throwaway container of the target version will tell you which in a few seconds.

CREATE EXTENSION pg_stat_statements;

CREATE EXTENSION

SELECT v.view_name, v.col,
       (a.attname IS NOT NULL) AS still_there
FROM (VALUES
        ('pg_stat_bgwriter',           'checkpoints_timed'),
        ('pg_stat_bgwriter',           'buffers_backend'),
        ('pg_stat_wal',                'wal_write_time'),
        ('pg_stat_wal',                'wal_sync_time'),
        ('pg_stat_progress_vacuum',    'max_dead_tuples'),
        ('pg_backend_memory_contexts', 'parent'),
        ('pg_stat_statements',         'blk_read_time')
     ) AS v(view_name, col)
LEFT JOIN pg_attribute a
       ON a.attrelid = to_regclass(v.view_name)
      AND a.attname  = v.col
      AND a.attnum > 0
ORDER BY 1, 2;

That block ran on both servers. On 14 every answer is the one you would hope for:

         view_name          |        col        | still_there 
----------------------------+-------------------+-------------
 pg_backend_memory_contexts | parent            | t
 pg_stat_bgwriter           | buffers_backend   | t
 pg_stat_bgwriter           | checkpoints_timed | t
 pg_stat_progress_vacuum    | max_dead_tuples   | t
 pg_stat_statements         | blk_read_time     | t
 pg_stat_wal                | wal_sync_time     | t
 pg_stat_wal                | wal_write_time    | t
(7 rows)

On 18, none of them is:

         view_name          |        col        | still_there 
----------------------------+-------------------+-------------
 pg_backend_memory_contexts | parent            | f
 pg_stat_bgwriter           | buffers_backend   | f
 pg_stat_bgwriter           | checkpoints_timed | f
 pg_stat_progress_vacuum    | max_dead_tuples   | f
 pg_stat_statements         | blk_read_time     | f
 pg_stat_wal                | wal_sync_time     | f
 pg_stat_wal                | wal_write_time    | f
(7 rows)

Seven columns on the left, seven answers on the right, and the shape of the answer on 18 is the shape of the upgrade. Point that query at whichever major you are actually moving to, with whichever column list your own configuration files contain, and you have the work list. Getting the list is the easy part: the strings live in dashboard definitions, alert rule files, the cron job that emails a weekly report, and the query somebody pasted into a runbook years ago.

Four of those seven are worth knowing individually, because the failure modes differ.

• The checkpoint counters left pg_stat_bgwriter in 17 for a view of their own, renamed on the way, and the two columns counting backend writes were deleted rather than moved. A query naming any of them errors.
• The write-ahead log view lost its write and sync columns in 18, which is the change most likely to blank a panel on that upgrade. The timing it used to carry is still collected, in a different view, against a different set of rows.
• Vacuum’s progress view changed the unit it reports dead tuples in, from a count of tuples to a count of bytes, in 17. The old column names are gone, so this one errors rather than lying.
• The block timing columns in the statements extension were renamed in 17 to distinguish shared buffers from local ones. This is the one most likely to be missed, because the extension is versioned separately from the server and the rename arrives with the new server regardless.

The join key you already have, and the three views you may not be using

The other half of the audit runs in the opposite direction. PostgreSQL 14 introduced a set of monitoring surfaces that a fleet which upgraded to 14 and then stopped reading release notes has never switched on, and several of them are worth having during the upgrade itself rather than after it.

The query identifier is the one to start with. From 14 the server computes it in core, exposes it in the activity view, in verbose plans and optionally in every log line, which is what lets a session you can see right now be matched to its row in the statements extension without matching on text. If you are about to compare query behaviour either side of an upgrade, that is the key you want the before-picture keyed on, and it is also the key whose values do not survive the move, which is a thing to know in advance rather than discover.

Three views arrived in the same release and are read far less often than they deserve. Write-ahead log generation became visible as records, full-page images and bytes, which turns “the disks are busy” into a number attributable to a workload. Replication slot activity became visible as spill and stream counters, which is how you find out that a logical subscriber is being fed from disk rather than memory. And bulk copy progress became observable while it runs, which matters on the upgrade night itself if any part of your cutover involves a large load.

Two more are diagnostic rather than routine. A backend’s memory contexts can be read from SQL instead of a debugger, which is the difference between answering “why is this connection holding a gigabyte” in a minute and answering it in an afternoon. And lock rows record when the wait began, so a blocking tree can carry durations rather than a snapshot.

What to check before anyone books the window

• Confirm the target major has every column your collection layer names, using the inventory query above against a container of that exact minor rather than against documentation.
• Decide what happens to the baseline. Query identifiers change across the upgrade, and the statistics counters reset with the new cluster, so anything expressed as “compared to last month” needs a stated restart point rather than a silent one.
• Check which of your alert rules fire on a rate and would therefore not fire on absent data. A collector that loses one query goes quiet rather than loud, and a rule written as a threshold over a rate evaluates against nothing without complaining.

The last of those is the one that turns a clean upgrade into a bad week. An error at the database is the good case, because somebody reads it. The expensive case is the column that still exists, still returns a number, and counts something slightly different from what it counted before.

What it costs to run the audit

Nothing on this page needs a restart, an extension you do not already have, or a maintenance window. The inventory query reads the catalog. The statistics check reads counters the server maintains whether or not anyone looks. The only real cost is the throwaway container of the target version, which is a few minutes and a few hundred megabytes, and which is also the only way to answer the question honestly rather than from a changelog.

The one thing worth doing on the 14 cluster before the window, and that cannot be done afterwards, is capturing the before-picture: a snapshot of the statements extension keyed by identifier, a snapshot of per-table counters, and the current settings. All three become unreadable in their old form once the cluster has moved, and a comparison you did not capture is a comparison you cannot make.

For the shorter version of the inventory step, the upgrade path tool takes the view and column names your monitoring reads and returns the changes between 14 and your target that touch them, with the ones that return nothing rather than erroring listed first.