macOS Windows

PostgreSQL 17 features many feature improvements, optimizations, additions to the developer experience with JSON TABLE, and enhancement to logical replications to simplify management of workloads and version upgrades.

1. Performance gains

  • PostgreSQL 17 improves performances for NOT NULL constraints, WITH queries, and queries with IN clauses.

  • 2x performance improvements when exporting large rows using COPY

  • MERGE feature updates. PostgreSQL 17 adds more features to MERGE, including a RETURNING clause and the ability to update views.

  • High concurrency workloads performance increase thanks to improvements with write-ahead log processing.

  • New memory structure for vacuum that consumes up to 20x less memory.

2. New features

  • Two new commands: MERGE PARTITIONS and SPLIT PARTITIONS have been added, which allows merging and splitting of partitions respectively.

  • JSON TABLE. Developers can now convert JSON data into a PostgreSQL table. PostgreSQL 17 also supports SQL/JSON constructors JSON, JSON_SCALAR, JSON_SERIALIZE and query functions JSON_EXISTS, JSON_QUERY, JSON_VALUE, supporting many ways to convert JSON data into a native PostgreSQL data type.

  • pg_createsubscriber, a new tool that allows users to create a logical replica from a physical replica, such as a physical standby server.

  • A new TLS option, sslnegotiation lets users perform direct TLS handshakes when using ALPN.

  • pg_basebackup now supports incremental backups and adds a new utility, pg_combinebackup to reconstruct a full backup.

  • pg_dump has a new option, --filter that allows selecting what objects to include when generating a dump file.

  • EXPLAIN now shows time spent for local I/O block and writes, and includes two new options: SERIALIZE and MEMORY.

  • A new view, pg_wait_events, when combined with pg_stat_activity, allow a user to see why an active session is waiting.

  • PostgreSQL 17 now supports using identity columns and exclusion constraints on partitioned tables.