PostgreSQL 16: New Features Every DBA Should Know

PostgreSQL 16 landed in September 2023 and brought a set of changes that shift how DBAs approach replication, I/O diagnostics, and query performance at scale. The headline feature — logical replication from standby servers — fundamentally changes CDC and HA architectures that teams have worked around for years. But pg_stat_io, SQL/JSON constructors, COPY improvements, and a refactored monitoring stack deserve equal attention before you plan your next upgrade window. If you are running PG15 or earlier in production, this guide covers the changes that will have the most operational impact on your day-to-day work.

Parallel Query Improvements

PostgreSQL 16 continues to refine the parallel query infrastructure that has been evolving since PG10. The most practical improvements for DBAs are in how parallel hash joins handle memory pressure and how the planner estimates costs for large aggregate operations.

Parallel Hash Join Batching

PG16 improves how parallel hash joins handle batching when the hash table does not fit in work_mem. Earlier versions would sometimes fall back to a single-process hash join when batch overflow occurred. PG16 keeps the parallel execution path alive through batching, which means workloads with large hash joins and moderate work_mem settings see fewer unexpected performance regressions during data growth.

Aggregate Parallelism Improvements

Partial aggregate pushdown has been improved for several aggregate types. If your OLAP queries combine GROUP BY with window functions over large scans, re-run EXPLAIN (ANALYZE, BUFFERS) after upgrading — you may see the planner choose a parallel partial aggregate path where it previously chose a serial one.

max_parallel_workers_per_gather

The default for max_parallel_workers_per_gather remains 2, but PG16 improves the cost model that decides when parallelism is worth activating. Queries that were borderline serial in PG15 may go parallel automatically in PG16 on the same hardware. Audit your slow query log after upgrade — parallel plan regressions are rare but do occur on heavily skewed data distributions.

Logical Replication Enhancements

This is the biggest architectural change in PostgreSQL 16. Logical replication from standby servers, two-phase commit support, and the new pg_createsubscriber utility collectively remove three of the most common operational complaints about PG logical replication.

Logical Replication from Standby Servers

Before PG16, every logical replication subscriber had to connect directly to the primary server. This meant that CDC pipelines, downstream analytics replicas, and cross-region subscribers all added WAL sender load to the primary. In PG16, a physical standby can act as a logical replication source — it decodes WAL and publishes changes downstream just as the primary would.

This is a significant architecture change for teams running Debezium or similar CDC tools against high-traffic primaries. You can now offload the logical decoding work to a read replica without any change to how downstream consumers receive data.

-- PG16: Logical replication from standby
-- The standby can now publish changes to downstream subscribers
CREATE PUBLICATION standby_pub FOR ALL TABLES;

-- Create subscriber pointing to the standby
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=standby-host dbname=myapp user=repuser'
  PUBLICATION standby_pub;

-- pg_createsubscriber: convert physical replica to logical subscriber
pg_createsubscriber -d myapp -P standby_pub -s standby-host

Row Filtering in Initial Snapshot

PG15 introduced row filters for ongoing logical replication (only replicate rows matching a WHERE clause). PG16 extends this to the initial table sync snapshot. Previously, the initial copy phase would transfer all rows even if a row filter was defined, relying on the subscriber to discard unwanted rows. In PG16, the filter is applied during the initial snapshot, which reduces network transfer and subscriber-side processing for large partially-replicated tables.

Two-Phase Commit Support

PG16 adds two-phase commit (2PC) support for logical replication. When a subscriber is configured with two_phase = on, prepared transactions are replicated and held in the prepared state on the subscriber until the originating COMMIT PREPARED arrives. This matters for distributed transaction patterns where you need strict consistency guarantees across publisher and subscriber, and for integrations with XA transaction coordinators.

pg_createsubscriber Utility

The new pg_createsubscriber command-line utility automates the process of converting an existing physical standby into a logical replication subscriber. This was previously a multi-step manual process involving stopping the standby, reconfiguring recovery, and running CREATE SUBSCRIPTION. The utility handles the coordination automatically, significantly reducing the risk of misconfiguration during the transition.

pg_stat_io: New I/O Statistics View

PostgreSQL 16 introduces pg_stat_io, a system view that provides per-backend-type, per-object, per-context I/O statistics. Before this view existed, understanding whether autovacuum was competing with OLTP queries for I/O required either strace, iostat with process attribution, or commercial monitoring agents. pg_stat_io makes this a standard SQL query.

-- New in PG16: per-backend I/O statistics
SELECT backend_type, object, context,
       reads, read_time,
       writes, write_time,
       hits, evictions, reuses,
       fsyncs, fsync_time
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY reads DESC;

-- Find processes doing the most disk reads
SELECT backend_type, reads, read_time,
  round(read_time / nullif(reads, 0), 3) AS avg_read_ms
FROM pg_stat_io
WHERE reads > 0
ORDER BY read_time DESC;

The context column distinguishes between normal (standard buffer pool operations), vacuum (autovacuum and manual VACUUM), and bulkread/bulkwrite (sequential scan and bulk operations that bypass the ring buffer differently). This lets you answer questions like "how many buffer pool evictions is autovacuum causing per minute?" without any external tooling.

The cumulative stats are reset by pg_stat_reset() along with other pg_stat views, so existing reset procedures work without modification.

SQL/JSON Improvements

PostgreSQL 16 adds the SQL standard JSON constructors and predicates that have been in MySQL and SQL Server for years. These are not merely convenience wrappers — JSON_TABLE in particular enables a class of ETL patterns that previously required PL/pgSQL or application-level processing.

-- PG16: New JSON constructors and predicates
SELECT JSON_OBJECT('name': 'Alice', 'age': 30);
SELECT JSON_ARRAY(1, 2, 3, NULL ABSENT ON NULL);

-- JSON_TABLE for converting JSON to rows
SELECT *
FROM JSON_TABLE(
  '[{"name":"Alice","dept":"Engineering"},{"name":"Bob","dept":"Sales"}]',
  '$[*]'
  COLUMNS (
    name TEXT PATH '$.name',
    dept TEXT PATH '$.dept'
  )
) AS jt;

-- IS JSON predicate
SELECT '{"valid": true}' IS JSON OBJECT;  -- true
SELECT '[1,2,3]' IS JSON ARRAY;            -- true
SELECT 'not json' IS JSON;                 -- false

The IS JSON predicate family is particularly useful for CHECK constraints on text columns that store JSON, or for WHERE clause validation when processing unstructured input. The ABSENT ON NULL option in JSON_ARRAY and JSON_OBJECT gives you clean control over null handling without wrapper functions.

Security and Access Control Changes

pg_hba.conf Improvements

PostgreSQL 16 adds new connection-type keywords in pg_hba.conf that give administrators explicit control over encrypted vs. unencrypted connections at the rule level. The new hostssl and hostnossl entries have existed since PG9.2, but PG16 adds better error messaging when rules conflict and improves handling of clientcert options.

# PG16: connection-type specific rules
# TYPE   DATABASE  USER      ADDRESS       METHOD
local    all       postgres                peer
host     all       all       127.0.0.1/32  scram-sha-256
# New: hostnossl rule to explicitly reject unencrypted
hostnossl  all     all       0.0.0.0/0     reject
hostssl    all     all       0.0.0.0/0     scram-sha-256

The pattern above — explicitly rejecting unencrypted connections from any address while permitting SSL connections with SCRAM-SHA-256 — is now the recommended baseline for internet-accessible PostgreSQL instances. Previously achieving this required firewall rules or careful ordering of host entries.

GRANT on Schemas and Sequences

PG16 adds pg_read_all_data and pg_write_all_data predefined roles that grant SELECT and INSERT/UPDATE/DELETE on all tables, views, and sequences without needing explicit per-object grants. This simplifies provisioning read-only reporting users and ETL service accounts that need broad access without superuser privileges.

Performance and Configuration Changes

vacuum_buffer_usage_limit

PG16 introduces vacuum_buffer_usage_limit, a new parameter that directly controls how much of the buffer pool vacuum and analyze operations can consume. Previously, vacuum_cost_delay and vacuum_cost_limit throttled vacuum indirectly through I/O cost accounting, but did not prevent vacuum from evicting hot pages from shared_buffers. The new parameter caps the buffer footprint directly.

# PG16: vacuum_buffer_usage_limit (replaces vacuum_cost_delay for buffer management)
vacuum_buffer_usage_limit = 256kB  # Default — limit buffer consumption during vacuum

On systems where autovacuum was evicting frequently-accessed index pages from the buffer pool, increasing this value allows vacuum to run more efficiently; decreasing it protects the buffer pool for OLTP. The default of 256kB is conservative — production systems with large tables often benefit from raising this to 2MB or 4MB to reduce vacuum runtime at the cost of slightly higher buffer churn.

COPY Improvements

Two COPY improvements in PG16 have significant operational value for data engineering teams.

-- PG16: COPY with DEFAULT keyword support
COPY orders (id, status, amount) FROM STDIN WITH (DEFAULT '*');
-- Rows with '*' in a column use the column's DEFAULT value

-- ON_ERROR ignore option to skip bad rows
COPY orders FROM '/tmp/orders.csv' WITH (FORMAT csv, ON_ERROR ignore);
-- Previously would fail entire COPY on first bad row

ON_ERROR ignore is the change most data engineers have wanted for years. Before PG16, a single malformed row would abort an entire COPY operation, requiring pre-processing to validate input or catch-and-retry logic in application code. With ON_ERROR ignore, bad rows are skipped and the operation continues, with skipped row counts visible in the command output. The DEFAULT keyword support eliminates the need for sentinel values or pre-processing steps to inject default column values during bulk loads.

pg_stat_checkpointer

PG16 separates checkpoint statistics out of pg_stat_bgwriter into a dedicated pg_stat_checkpointer view. This makes dashboards and alerting rules cleaner — checkpoint lag and bgwriter buffer allocation are distinct operational concerns that were awkward to monitor from a single view.

-- pg_stat_bgwriter: new stats in PG16
SELECT buffers_clean, maxwritten_clean, buffers_alloc,
       checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;

-- pg_stat_checkpointer: new dedicated view (separated from bgwriter)
SELECT num_timed, num_requested, write_time, sync_time, buffers_written
FROM pg_stat_checkpointer;

If you have existing monitoring that reads checkpoint timing from pg_stat_bgwriter, update those queries to use pg_stat_checkpointer after upgrading. The old columns remain in pg_stat_bgwriter in PG16 for backward compatibility but are expected to be deprecated in a future version.

Working with JusDB on PostgreSQL Upgrades

Upgrading a production PostgreSQL instance from PG15 to PG16 involves more than running pg_upgrade. The wal_level requirement for logical replication from standbys means you need to plan your upgrade sequence carefully: primary first, then standbys, with a maintenance window for the primary restart when changing wal_level. If you are running streaming replication, the new pg_createsubscriber utility can convert one of your physical standbys to a logical subscriber post-upgrade — enabling zero-downtime CDC migration without a second full base backup.

JusDB works with engineering teams to plan and execute major PostgreSQL version upgrades with minimal downtime. Our approach uses logical replication to cut over between versions without taking application offline — the same technique described in this post, applied to the upgrade process itself. We also help teams instrument pg_stat_io, pg_stat_checkpointer, and the new vacuum parameters into existing Prometheus/Grafana stacks so you leave the upgrade with better observability than you started with.

If you are planning a PG16 upgrade or want help evaluating whether logical replication from standbys fits your CDC architecture, get in touch or review our PostgreSQL services.

Related Reading

• PostgreSQL 17: New Features for DBAs
• Zero-Downtime PostgreSQL Major Version Upgrade Using Logical Replication
• PostgreSQL Logical Replication: Publications, Subscriptions, and Conflict Resolution