PostgreSQL 17: The Novelties That Show Promise

javi
Código SQL en pantalla con sintaxis resaltada sobre fondo oscuro

PostgreSQL 17 beta 1 came out on May 23, 2024, with GA planned for September. It’s an iterative release but with several noticeable features: major improvements in vacuum, logical replication with failover, SQL standard JSON_TABLE, and many I/O optimisations. This article covers what’s worth starting to test and why.

Faster, Memory-Cheaper Vacuum

Vacuum has historically caused pain: locks, memory usage, slowness. PG17 attacks this:

  • New dead-tuple storage algorithm: up to 20x less memory.
  • maintenance_work_mem no longer limits as before — intrinsically more efficient.
  • Improved parallel index cleanup.
  • Vacuum post-failover: resumes from where it left off.

On huge tables, this can reduce vacuum from hours to minutes.

Logical Replication with Failover

In PG16 (with improvements discussed in previous article), a common case fails: if primary falls and a standby promotes, subscribers lose state.

PG17 introduces slot synchronization:

  • Logical slots replicate to standbys.
  • After failover, subscriber can reconnect to new primary without full re-sync.

This completes the logical-replication vision as HA-viable infrastructure.

JSON_TABLE

PG17 finally adds JSON_TABLE (SQL:2023 standard). Convert JSON to inline relational table:

SELECT t.*
FROM orders o,
     JSON_TABLE(o.data, '$.items[*]' COLUMNS (
         item_id INT PATH '$.id',
         name TEXT PATH '$.name',
         price NUMERIC PATH '$.price'
     )) AS t;

Before you had to use jsonb_array_elements + extractions — verbose. Now SQL-standard syntax.

Improved COPY

COPY ... FROM has:

  • ON_ERROR ignore: continue past row-parsing error.
  • Improved performance for bulk loads.

For heavy ETL, skip-errors-without-abort capability is gold.

Improved MERGE

MERGE (SQL standard upsert) more robust:

  • Supports triggers correctly.
  • RETURNING clause.
  • Better optimizer.

Allows replacing old INSERT ... ON CONFLICT patterns.

Planner Improvements

  • More aggressive subquery pull-up.
  • Improved parallel hash join.
  • More stable query plans in borderline cases.

Preliminary benchmarks show 5-15% typical-query improvements vs PG16.

Asynchronous I/O

Introduction of async I/O for certain operations:

  • Sequential scans.
  • Index scans.
  • Bitmap heap scans.

Potentially major improvement on modern NVMe hardware. Activatable via config.

New Incremental Backup

pg_basebackup with incremental mode:

pg_basebackup -D backup --incremental=/backup/prev_manifest

Reduces backup time and size. Combined with pgBackRest or WAL-G, more efficient flows.

Extensions

  • pg_stat_io stabilized (introduced PG16).
  • pg_buffercache with more detailed stats.
  • auto_explain with JSON output.

Better out-of-box observability.

Breaking Changes

  • Some old-syntax deprecations.
  • pg_hba.conf parsing changes — review before upgrade.
  • Slightly different defaults in some pragmas.

Nothing dramatic but release-notes reading merited.

Performance Benchmarks

Early numbers (beta):

  • INSERT throughput: +5-10% vs PG16.
  • Logical replication: +30-50% with improved parallel apply.
  • Vacuum on large tables: up to 20x more memory efficient.
  • Complex analytical queries: +10-15% via planner.

For massive production, gains can be significant.

When to Upgrade

For a production cluster:

  • Wait for GA (September 2024) + 1-2 minor (17.1, 17.2).
  • Beta in staging/dev worthwhile now.
  • New projects 2024 H2+: start with 17.

pg_upgrade Without Drama

PG17 maintains pg_upgrade compat from PG15+. Normal flow:

pg_upgrade -b /old/bin -B /new/bin \
  -d /old/data -D /new/data \
  --link

With --link, upgrade in minutes (no data copy, just relinks).

Roadmap: What’s Coming

Lookahead to PG18+:

  • Vectorized execution (experimental).
  • Columnar storage via TableAM expansion.
  • Distributed query with improved FDW.
  • Continuous JIT improvements.

PostgreSQL keeps evolving strongly without losing backwards compatibility.

Extensions to Follow

With PG17:

  • pgvector: 0.7+ compatible.
  • TimescaleDB: PG17 support following GA.
  • Citus: distributed PostgreSQL, upcoming support.
  • PostGIS: traditionally quick to update.

Extension ecosystem adapts in months.

Cloud Ecosystem

Providers:

  • AWS RDS / Aurora: Postgres 17 available after GA + validation.
  • GCP Cloud SQL: similar.
  • Azure Database: similar.
  • Supabase, Neon, Railway, Fly.io: tend to adopt fast.

For managed production, waiting 3-6 months post-GA is prudent.

Conclusion

PostgreSQL 17 is an iterative release but with impactful features: more efficient vacuum, complete failover-enabled logical replication, standard JSON_TABLE, async I/O. For teams operating serious Postgres, worth testing in staging soon and planning upgrade for late 2024 or early 2025. No breaking features but many practical dividends. PostgreSQL continues demonstrating consistent evolution is solid strategy — release after release, accumulates advantages over alternatives.

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

Entradas relacionadas