PostgreSQL 16: Changes That Affect Day-to-Day Work

javi
Pantalla con consultas SQL y resultados en terminal

PostgreSQL 16 was released on 14 September 2023, keeping the community’s annual cadence. Although none of its features is revolutionary on its own, the set reflects the project’s maturity: each release polishes specific areas users felt as friction. We cover the new features that really change day-to-day production work.

Logical Replication From Standby

The most awaited feature for teams with large clusters. Until Postgres 15, logical replication (CDC, change data capture) could only be done from the primary — adding load to the most critical node.

Postgres 16 lets you establish logical subscriptions with a standby as source. Practical implications:

  • ETL and CDC pipelines that don’t add load to the primary.
  • Cross-region replication from a local standby instead of crossing continents from the primary.
  • Fan-out topologies where one standby feeds several logical consumers.

For teams with an overloaded primary and several Kafka/Debezium consumers, this change significantly reduces pressure on the primary.

Performance Improvements in Parallel Queries

Postgres has improved parallelism in every recent release; in 16 highlights:

  • Parallel FULL OUTER JOIN. Previously serial; now can use parallel workers. Drastic improvement in analytical queries.
  • JOIN with more complex filters now supports parallelism where it previously didn’t.
  • Better window function planning, especially when combined with aggregations.

For OLAP or complex report queries, the change is noticeable without touching your code.

pg_stat_io: The New Window Into I/O

One of the most useful observability additions. The pg_stat_io view breaks down Postgres I/O by operation type (read, write, extend, fsync) and context (normal, vacuum, bulk read/write).

SELECT backend_type, object, context, reads, writes, hits
FROM pg_stat_io
ORDER BY reads DESC;

For the first time you can answer with data: who is consuming I/O — VACUUM, bulk loads, or normal queries? Does my shared buffers have enough hit ratio? Is there excessive extend (table extension) activity?

It’s a fundamental improvement for performance diagnosis that previously required external tools or manual instrumentation.

New SQL Functions

Syntax improvements that save application code:

  • Standard SQL/JSON constructors (JSON_OBJECT, JSON_ARRAY, IS JSON). More portable across DBs than older Postgres-specific functions.
  • array_sample() and array_shuffle(). Useful for sampling without constructing elaborate queries.
  • Better regex support in COLLATE patterns — more expressive custom sortings.

Physical Replication Improvements

For more operational clusters:

  • Parallel pg_basebackup. Faster initial backups on large clusters.
  • LZ4 and Zstandard compression integrated in pg_dump and pg_restore. Previously had to combine with external pipes.
  • Improved pg_stat_replication statistics — more detail on lag and state of each replica.

Small changes individually, but they add up in daily operations.

Administrative Functions

Improvements to day-to-day administration:

  • Improved pg_locks with granularity over previously opaque lock types.
  • VACUUM with more detailed progress feedback via pg_stat_progress_vacuum.
  • More robust logical decoding in face of interruptions — cleaner recovery after failure.

Less Visible But Important Changes

Some important internal changes for advanced cases:

  • io_method experimental parameter for testing io_uring on Linux. Promises significant I/O throughput improvements.
  • Improved CREATE TABLE LIKE with more options for what to inherit (constraints, indexes, statistics).
  • Large-transaction compression in logical replication — less bandwidth in logical replication.

When to Migrate

PostgreSQL 16 is a solid release. For production:

  • If you’re on 15: migration is relatively easy, no disruptive changes. Worth planning in the next 6 months to benefit from pg_stat_io and standby logical replication.
  • If you’re on 14 or earlier: prioritise migrating to 15 or 16 per your operational calendar. Older versions approach end-of-life.
  • If you’re on 13: 13 loses support in November 2025. Start planning.

For new 2023 projects, start directly on 16. No reason to launch on an earlier version.

Things to Verify Before Migrating

Operational lessons for a clean migration:

  • Extensions. Verify each extension you use (PostGIS, pgvector, TimescaleDB, etc.) has a 16-compatible version.
  • Drivers. Old client drivers may have issues with default SCRAM authentication.
  • Backups: test pg_dump from 16 toward a lower version before committing (backward compatibility works but worth validating).
  • Existing logical replication: subscriber/publisher migration requires coordination with counterparts.

Conclusion

PostgreSQL 16 doesn’t transform how you use the database; it improves specific points any Postgres operator in production will appreciate. Logical replication from standby, pg_stat_io, and parallelism improvements are the three news items that justify planning migration from 14 or 15. For teams on 13 or earlier, end-of-life timing also adds urgency.

Follow us on jacar.es for more on PostgreSQL, database operations, and data architectures.

Entradas relacionadas