postgres-core-version-matrix

Quick Reference :

PostgreSQL 15, 16, 17 are all currently supported (v15 EOL 2027-11, v16 EOL 2028-11, v17 EOL 2029-11). Every feature, GUC, and syntax form in any other skill in this package MUST be annotated with the earliest supported version that exposes it. This skill is the authoritative cross-reference.

Three-step decision flow when authoring or reviewing PostgreSQL code :

Identify the target PostgreSQL major version (or minimum supported version if running fleet has mixed versions).
Look up each feature in this matrix : if present in all three (v15+v16+v17), no annotation needed. If introduced in v16, annotate v16+. If only in v17, annotate v17+.
If a feature only exists in v17 but the target is v15 or v16, find the fallback pattern listed in references/methods.md (e.g. INSERT ... ON CONFLICT instead of MERGE ... RETURNING).

Detection : SHOW server_version_num; returns a single integer like 170002 (v17.0.2). Use current_setting('server_version_num')::int >= 170000 in conditional SQL. NEVER parse version() string output (format is unstable).

When To Use This Skill :

ALWAYS use this skill when :

A user asks "can I use feature X on PostgreSQL 15 / 16 / 17"
Writing code that may run on multiple PostgreSQL versions
Migrating an application from a previous PostgreSQL version (e.g. v15 to v17)
Annotating any skill content in this package with version markers
A v17 feature (MERGE RETURNING, JSON_TABLE, transaction_timeout, incremental backup, pg_createsubscriber) is being considered

NEVER use this skill for :

Versions earlier than v15 (out of scope per D-015)
Beta / development versions (v18+) until officially released
Third-party extension version mapping (pgvector, PostGIS, pg_partman use their own version axes)

Decision Trees :

Which version to target :

Is target version explicit (e.g. AWS RDS PostgreSQL 16, Supabase = 15) ?
├── YES → use that version. Annotate any feature outside that version.
└── NO  → ask user, then default to v17 (newest GA, longest support window).

Is the fleet mixed (e.g. some prod on v15, some staging on v17) ?
├── YES → write to the LOWEST version. Add `v16+` / `v17+` guarded blocks for opt-in.
└── NO  → write to the target version.

Choose MERGE variant :

Need to UPDATE/INSERT and return affected rows ?
├── v17+ → use MERGE ... RETURNING merge_action(), col1, col2 ...
├── v15+v16 → MERGE has no RETURNING. Use INSERT ... ON CONFLICT ... RETURNING instead.
└── target unclear → use INSERT ... ON CONFLICT ... RETURNING (works on all three).

Need to delete target rows missing from source (full-sync) ?
├── v17+ → MERGE ... WHEN NOT MATCHED BY SOURCE THEN DELETE
└── v15+v16 → manual DELETE WHERE NOT EXISTS in a separate statement.

Choose JSON-to-rows method :

Need to project JSON array into rows-and-columns ?
├── v17+ → JSON_TABLE(jsonb_col, '$.items[*]' COLUMNS (...))
└── v15+v16 → jsonb_to_recordset(jsonb_col) AS x(id int, name text)
              or jsonb_array_elements(jsonb_col) + ->>

Choose backup strategy :

Need page-level incremental backup ?
├── v17+ → set summarize_wal = on; pg_basebackup --incremental=<manifest>;
│           combine with pg_combinebackup before restore.
└── v15+v16 → file-system-level snapshot + WAL archive (PITR),
              or third-party (pgBackRest, Barman) which already do incrementals.

Choose timeout GUC :

Need to limit one statement                → statement_timeout (v15+)
Need to limit lock acquisition             → lock_timeout (v15+)
Need to limit idle-in-transaction sessions → idle_in_transaction_session_timeout (v15+)
Need to limit any idle session             → idle_session_timeout (v15+)
Need to limit WHOLE transaction duration   → transaction_timeout (v17+ ONLY)

Patterns :

Pattern 1 : Detect server version at runtime

ALWAYS : compare server_version_num as integer. NEVER : parse version() text.

-- WORKS on v15, v16, v17
SELECT current_setting('server_version_num')::int >= 160000 AS at_least_v16;

DO $$
BEGIN
  IF current_setting('server_version_num')::int >= 170000 THEN
    -- run v17-only DDL such as ALTER SYSTEM SET transaction_timeout
    PERFORM 1;
  END IF;
END$$;

WHY : server_version_num is a stable 6-digit integer (MMmm00 for major M, minor m). version() returns a human-readable string whose format has changed between releases.

Pattern 2 : Portable UPSERT (works on v15, v16, v17)

ALWAYS : use INSERT ... ON CONFLICT ... DO UPDATE ... RETURNING for portable UPSERT-with-RETURNING. NEVER : use MERGE ... RETURNING if any target server is v15 or v16.

INSERT INTO accounts (id, balance)
VALUES (1, 100)
ON CONFLICT (id) DO UPDATE
  SET balance = accounts.balance + EXCLUDED.balance
RETURNING id, balance;

WHY : MERGE was introduced in v15 but RETURNING on MERGE only landed in v17. INSERT ... ON CONFLICT ... RETURNING has been stable since v9.5.

Pattern 3 : Version-guarded transaction_timeout

ALWAYS : guard transaction_timeout with a version check. NEVER : set transaction_timeout unconditionally in shared config that targets v15 or v16.

DO $$
BEGIN
  IF current_setting('server_version_num')::int >= 170000 THEN
    EXECUTE 'SET LOCAL transaction_timeout = ''30s''';
  ELSE
    -- v15+v16 fallback : combine statement_timeout + idle_in_transaction_session_timeout
    EXECUTE 'SET LOCAL statement_timeout = ''30s''';
    EXECUTE 'SET LOCAL idle_in_transaction_session_timeout = ''30s''';
  END IF;
END$$;

WHY : transaction_timeout (v17+) limits the wall-clock lifetime of the entire transaction, including idle-in-transaction time AND every statement. On v15+v16 only the separate statement-level and idle-in-tx-level GUCs exist; combining them approximates but does not equal transaction_timeout.

Pattern 4 : security_invoker views (v15+)

ALWAYS : add WITH (security_invoker = true) when row-level security policies on underlying tables must apply to the view's caller, not its owner. NEVER : use SECURITY INVOKER views on v14 or earlier (not supported).

-- v15+ : view executes RLS as caller, not view owner
CREATE VIEW active_orders WITH (security_invoker = true) AS
  SELECT * FROM orders WHERE archived_at IS NULL;

WHY : Before v15, views always executed with the owner's privileges, meaning RLS policies on base tables checked the view owner, not the querying user. v15+ makes this configurable per view.

Pattern 5 : public schema lockdown (v15+, new databases only)

ALWAYS : when creating a database on v15+, expect the public schema to no longer grant CREATE to PUBLIC. NEVER : assume any non-superuser role can create objects in public of a freshly-created v15+ database without an explicit GRANT.

-- v15+ new database : public has USAGE for PUBLIC but NOT CREATE
-- to allow app role to create objects there :
GRANT CREATE ON SCHEMA public TO app_user;

-- preferred pattern : give each app its own schema and revoke from public entirely
CREATE SCHEMA app AUTHORIZATION app_user;

WHY : CVE-2018-1058 mitigation. Pre-v15 default allowed any role to create objects in public, enabling search-path-based privilege escalation. v15+ default removes the CREATE grant for new databases. Pre-existing databases keep prior permissions on pg_upgrade.

Pattern 6 : Logical replication from standby (v16+)

ALWAYS : on v16+, set up logical decoding directly on a hot standby to offload publisher load. NEVER : attempt logical decoding on a v15 standby (returns ERROR: logical decoding requires a non-hot-standby).

-- v16+ : on the primary, ensure WAL records carry standby snapshot info
SELECT pg_log_standby_snapshot();

-- on the standby
SELECT pg_create_logical_replication_slot('slot_on_standby', 'pgoutput');

WHY : Logical decoding was previously only supported on primaries. v16 extended decoding state to be reachable via WAL, so a standby can run subscriptions and reduce primary load.

Pattern 7 : COPY DEFAULT (v16+)

ALWAYS : on v16+, use COPY ... WITH (DEFAULT '\\D') to map a placeholder token in CSV to the column's default expression. NEVER : use this on v15 (silently treated as literal text, results in invalid value).

-- v16+ : '\D' rows pick up column DEFAULTs
COPY orders (id, created_at, status) FROM STDIN WITH (FORMAT csv, DEFAULT '\D');
1,2025-01-01,paid
2,\D,paid
3,2025-01-03,\D
\.

WHY : Before v16, COPY required every row to supply explicit values (or NULL). Importing externally produced CSV that wanted "use the DB default" required pre-processing. v16's COPY DEFAULT removes that step.

Pattern 8 : Incremental backup (v17+)

ALWAYS : on v17+, enable summarize_wal BEFORE taking the baseline, otherwise incrementals cannot be assembled. NEVER : restore incremental backups directly; ALWAYS combine with pg_combinebackup first.

# v17+ ONLY. On primary, in postgresql.conf :
# summarize_wal = on
# wal_summary_keep_time = '7d'

# Baseline full backup
pg_basebackup -D /backup/full -Fp -X stream -c fast

# Incremental against the previous manifest
pg_basebackup --incremental=/backup/full/backup_manifest -D /backup/incr1 -Fp

# Combine before restore (offline)
pg_combinebackup -o /restore /backup/full /backup/incr1

WHY : v17 native incremental backup tracks changed blocks via WAL summary files. Each pg_basebackup --incremental only copies blocks modified since the previous manifest. Without pg_combinebackup the incremental directory is not a valid data directory.

Pattern 9 : pg_createsubscriber (v17+)

ALWAYS : prefer pg_createsubscriber over manual logical-slot + initial-copy when converting a physical standby into a logical subscriber on v17+. NEVER : run pg_createsubscriber against a primary; it operates on a standby.

# v17+ ONLY. Cleanly converts an existing physical standby into a logical subscriber.
pg_createsubscriber \
  -D /var/lib/postgresql/17/standby \
  --publisher-server='host=primary port=5432 dbname=postgres user=replicator' \
  --subscriber-server='host=standby port=5432 dbname=postgres user=postgres' \
  --database=app

WHY : Pre-v17, converting a physical standby into a logical subscriber required a manual sequence (promote, dump+restore-or-snapshot, create subscription, manage initial-sync). pg_createsubscriber automates this in one command.

Anti-Patterns :

(Full content in references/anti-patterns.md)

MERGE ... RETURNING on v15 or v16 : SQLSTATE 42601 syntax error. See references/anti-patterns.md.
JSON_TABLE on v15 or v16 : SQLSTATE 42883 function does not exist. See references/anti-patterns.md.
SET transaction_timeout on v15 or v16 : SQLSTATE 22023 unrecognized configuration parameter. See references/anti-patterns.md.
Parsing version() text instead of server_version_num : breaks silently on minor release upgrades. See references/anti-patterns.md.
Assuming public schema is writable on v15+ new DBs : SQLSTATE 42501 insufficient privilege on first CREATE TABLE. See references/anti-patterns.md.
Restoring a v17 incremental backup directory directly without pg_combinebackup : incomplete data directory, server fails to start.
Logical decoding on a v15 standby : ERROR: logical decoding requires a non-hot-standby (functionality only added in v16).
pg_dump --filter= on v15 : SQLSTATE not applicable, but pg_dump exits with unrecognized option --filter. v16+ only.

Reference Links :

references/methods.md : Full per-version feature table, GUC introductions per version, fallback patterns for each v16+/v17+ feature, and version-detection helpers.
references/examples.md : Working code samples per pattern, all version-annotated, runnable against the corresponding PostgreSQL major version.
references/anti-patterns.md : Anti-patterns with cause, symptom, SQLSTATE where applicable, and version-specific fixes.