Reading Older PostgreSQL Projects: Key Changes from 13 to 17

Version note: The examples in this post were run with the official Docker images on PostgreSQL 13.23, 14.25, 15.20, 16.16 and 17.12, executing the same SQL file on every version.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, recently inherited an old supplier integration as well. Its database runs on PostgreSQL 14, and its repo contains these three pieces: a script line that starts the nightly backup, the query behind a monitoring dashboard, and the staging table of the import job. The application connects with its own role, not as a superuser.

SELECT pg_start_backup('nightly');                       /* backup script */
SELECT checkpoints_timed FROM pg_stat_bgwriter;          /* dashboard */
CREATE TABLE public.probe_scratch (id int);              /* import job, as the application role */

The team will move the integration to a freshly installed PostgreSQL 17 server and recreate the schema with migrations. How many of the three lines still work unchanged on 17?

ERROR:  function pg_start_backup(unknown) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
ERROR:  column "checkpoints_timed" does not exist
ERROR:  permission denied for schema public

None. All three worked on 14; two changed in 15 and one in 17. This is what you run into when you inherit an old project or upgrade across several versions: things that were removed, renamed or added between releases. This post summarizes the important changes of every version from 14 to 17, together with what they mean when you see them in old code. The source is each version's official release notes; every item was tested by running the SQL file below on all five versions.

The Support Calendar and the End of PostgreSQL 14

PostgreSQL ships one major version a year, and each major version is supported with minor releases (security and bug fixes) for five years. The calendar, from the versioning policy page on postgresql.org:

14.25 came out on 12 November 2026 together with 18.7 and was the last release of 14; the same announcement says 52 security vulnerabilities were fixed. Any vulnerability found in 14 from now on will not be fixed. 13 has been out of support for a year. A system on either of these versions belongs at the top of the upgrade list. Choosing 18 instead of 17 as the target pushes the next mandatory upgrade out by another year; the pg_upgrade notes from post 14 apply here too.

The Test Setup

db/15-versions.sql is a small file that runs on an empty server: a two-row books table, a price update table and a one-line probe for each feature. We ran the same file on every version in a throwaway container:

foreach ($v in 13, 14, 15, 16, 17) {
    docker run -d --name pg-old -e POSTGRES_PASSWORD=<password> postgres:$v-alpine
    Start-Sleep 5
    docker cp db/15-versions.sql pg-old:/tmp/15-versions.sql
    docker exec pg-old psql -U postgres -X -f /tmp/15-versions.sql > "15-$v.out" 2>&1
    docker rm -f pg-old
}

A summary of the results; each cell shows what you get on that version:

The table holds two kinds of rows: new features (a syntax error on older versions) and things that were removed or moved (an error on newer versions). The second kind is what breaks an upgrade; the first kind explains why old code sometimes takes a roundabout route.

PostgreSQL 14: Multiranges, Pipeline Mode and the SCRAM Default

14 gave every range type a multirange counterpart, for keeping non-adjacent ranges in a single value. A fragmented calendar like the campaign periods in post 04 now fits in one column:

SELECT datemultirange(daterange('2026-11-01', '2026-11-10'), daterange('2026-11-20', '2026-12-01')) AS campaign_days;

                   campaign_days
---------------------------------------------------
 {[2026-11-01,2026-11-10),[2026-11-20,2026-12-01)}

On 13 the same line gives function datemultirange(daterange, daterange) does not exist. If you see a daterange[] column and application code that merges ranges in old code, this is why. Npgsql has mapped multiranges to NpgsqlRange<T>[] since version 6.

The pipeline mode that 14 added to libpq lets a client send several queries without waiting for each answer. This matters for clients built on libpq; Npgsql implements the protocol itself and already supported batching (NpgsqlBatch from post 11). One default changed too: password_encryption went from md5 to scram-sha-256 in 14. Roles carried over from 13 may still have md5 passwords; the check query from post 14 shows them.

PostgreSQL 15: MERGE, the public Schema and the Backup Functions

The most visible addition in 15 is the standard MERGE; post 04 compared it with ON CONFLICT. Seeing ON CONFLICT or "first UPDATE, then INSERT if no row" instead of MERGE in old code is normal; most of the time there is no need to rewrite it, because ON CONFLICT is safer under concurrent writes.

The change that really matters during an upgrade is the public schema: from 15 on, the right of everyone to create objects there was removed and the schema is owned by the database owner. That is the third error in the guess. The release notes recommend revoking this privilege by hand in existing databases, because pg_upgrade carries the old privilege over. We tried a pg_upgrade from 14 to 18 (db/15-ops/public-schema.sh): the privilege on public stayed {postgres=UC/postgres,=UC/postgres} and the application role could create a table. Because template1 is carried over as well, we got the same result even in a new database created after the upgrade. So the same application can work on the upgraded server and fail on a freshly installed test environment.

On the backup side 15 removed the long-deprecated exclusive backup mode and renamed pg_start_backup()/pg_stop_backup() to pg_backup_start()/pg_backup_stop(). That is the first error in the guess. If you find a hand-written backup script, move it to pg_basebackup as in post 14 or to a backup tool. Two other additions: UNIQUE NULLS NOT DISTINCT, a unique constraint that counts NULLs as duplicates too (it used to take partial indexes and coalesce tricks), and column lists and WHERE filters in logical replication publications. The details are in the Veritabanı Performansı ve Scaling series; one note: if a filter column is not part of the replica identity, UPDATE and DELETE on that table are refused, which we also saw during the test.

PostgreSQL 16: SQL/JSON Constructors and Logical Replication from a Standby

16 brought the SQL standard's JSON constructors: JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, JSON_ARRAYAGG and the IS JSON check:

SELECT JSON_OBJECT('isbn': isbn, 'title': title) AS doc FROM books WHERE book_id = 1;
SELECT '{"pages": 412}' IS JSON OBJECT AS is_json;

                     doc
----------------------------------------------
 {"isbn" : "9780441013593", "title" : "Dune"}

 is_json
---------
 t

In old code you will see json_build_object and json_agg instead; both still work and there is no need to change them. IS JSON makes it easier to write a CHECK constraint that validates JSON in text columns. On the operations side 16 allowed logical decoding on a standby server: reading from a replica for logical replication or CDC without loading the primary. The pg_stat_io view also arrived in 16; you can answer the I/O questions from post 01 with it.

PostgreSQL 17: JSON_TABLE, MERGE RETURNING, VACUUM and the Builtin Collation

17 completed the SQL/JSON side with JSON_TABLE (we used it in post 05) and added RETURNING to MERGE. merge_action() tells you which action changed each row:

MERGE INTO books b USING price_updates u ON b.book_id = u.book_id
    WHEN MATCHED THEN UPDATE SET price = u.price + 1
    RETURNING merge_action(), b.book_id, b.price;

 merge_action | book_id | price
--------------+---------+-------
 UPDATE       |       2 | 42.50
 UPDATE       |       3 | 13.00

Conversions written with jsonb_to_recordset or jsonb_array_elements in old code do the job of JSON_TABLE; if they work, leave them alone. The incremental backup we saw in post 14 also came with 17.

An invisible but important change is VACUUM's memory: 17 switched to a new data structure for the dead tuple list, uses less memory, and the limit that used to stop silently at 1 GB when maintenance_work_mem was set higher is gone. For the big-table VACUUMs from post 08 this means indexes are cleaned in fewer passes. The builtin collation provider from post 03 is also 17's: C.UTF-8, independent of the operating system's libc and with a sort order that does not change between versions.

What breaks in 17 is monitoring queries: the checkpoint counters moved from pg_stat_bgwriter to the new pg_stat_checkpointer view (we used this view in post 01). That is why the dashboard query in the guess broke; the new name of checkpoints_timed is pg_stat_checkpointer.num_timed. Try the ready-made dashboards of Grafana or any other monitoring tool against the new version before the upgrade.

What It Means When You See It in Old Code

serial and bigserial: the approach before IDENTITY (post 03). They work, but the sequence's ownership and privileges are managed separately; use GENERATED ... AS IDENTITY for new tables.

CREATE EXTENSION "uuid-ossp", or pgcrypto only for UUIDs: gen_random_uuid() has been in core since 13, and 18 adds uuidv7(). The core_uuid line of the test returned t on every version from 13 on.

GRANT CREATE ON SCHEMA public, pg_start_backup, the checkpoint columns of pg_stat_bgwriter: the three breaks above. The wal_keep_segments setting: it became wal_keep_size in 13; if you find it in an old configuration file, the server will not start.

md5 passwords and the md5 method in pg_hba.conf: SCRAM has been the default since 14, and md5 is deprecated in 18 (post 14).

Production Notes

If you skip several versions, read the "Migration to Version N" section of the release notes of every version in between; that is where the incompatibilities are collected. A probe file like the one in this post checks the functions and monitoring queries your application uses against the target version in a few minutes.

Try the upgrade on a copy first with pg_upgrade --check, then run the application's tests and the monitoring queries against the new version. For a system leaving 14, the first thing to look at is the backup scripts and the privileges on the public schema.

Do not forget driver versions: an old Npgsql may not know the new server's types (such as multiranges). Keep the driver up to date before upgrading the server.

Common Pitfalls

Reading only the "new features" list and skipping what was removed: upgrades are broken by what was removed or renamed, not by what was added.

Building the test environment from scratch while production moves with pg_upgrade: as the public schema example shows, the two environments behave differently. If you can, build the test environment the same way.

Saying "it works, don't touch it" about an unsupported version: 14 no longer gets security fixes; the next critical vulnerability will stay open.

A Small Experiment

★☆☆ Add SELECT 'Dune' IS JSON and SELECT pg_backup_start('nightly') to 15-versions.sql; see what each version returns.

★★☆ Add three queries from Bookstore's own reports to the probe file and run it on 13–17. In which version do they first work?

★★★ Run public-schema.sh, then apply REVOKE CREATE ON SCHEMA public FROM PUBLIC to bookstore and template1 in the upgraded cluster. What does the application role get now in a newly created database? Why must you not forget to fix template1?

In the next post we look at the new features of PostgreSQL 18, this series' base version, all together: asynchronous I/O, skip scan, uuidv7(), virtual generated columns, RETURNING OLD/NEW and temporal constraints, each with a "how did we do this in 17" comparison.