PostgreSQL 17 Released: Boosting Performance and Enhancing Developer Experience

Why PostgreSQL 17 Stands Out

Key Features of PostgreSQL 17

1. MERGE Command with RETURNING Support

Streamlining Conditional Data Modifications

Example:

CREATE TABLE hero (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  hero_name TEXT UNIQUE
);

-- Insert a new hero or update an existing one based on hero_name
MERGE INTO hero h
USING (VALUES ('Dahunsi', 'Ajeet', 'Adesoji')) v(first_name, last_name, hero_name)
ON h.hero_name = v.hero_name
WHEN MATCHED THEN
  UPDATE SET first_name = v.first_name, last_name = v.last_name
WHEN NOT MATCHED THEN
  INSERT (first_name, last_name, hero_name)
  VALUES (v.first_name, v.last_name, v.hero_name)
RETURNING merge_action(), *;

merge_action | id | first_name | last_name | hero_name
-------------|----|------------|-----------|-----------
UPDATE       | 1  | Dahunsi      | Ajeet    | Adesoji

2. Enhanced JSON Functions

Simplifying Management of Semi-Structured Data

Example:

SELECT *
FROM json_table(
'[
  {"product": "Laptop", "details": {"price": 1200, "stock": 25}},
  {"product": "Smartphone", "details": {"price": 800, "stock": 50}},
  {"product": "Tablet", "details": {"price": 500, "stock": 40}}
]',
'$[*]'
COLUMNS (
  product_name TEXT PATH '$.product',
  price INT PATH '$.details.price',
  stock INT PATH '$.details.stock'
)
) AS jt;

Output:

sql

product_name | price | stock
-------------|-------|-------
Laptop       | 1200  | 25
Smartphone   | 800   | 50
Tablet       | 500   | 40

3. Improved Performance

Accelerating Queries and Operations

• Vacuum Process Enhancements: A new internal memory structure reduces memory consumption by up to 20x, speeding up vacuum operations and freeing up shared resources.
• I/O Layer Optimizations: High concurrency workloads benefit from up to 2x better write throughput due to improved write-ahead log (WAL) processing. The new streaming I/O interface accelerates sequential scans and the ANALYZE command.
• Query Execution Boosts: Enhanced support for SIMD (Single Instruction/Multiple Data) operations, including AVX-512 for the bit_count function, and optimizations for B-tree indexes and parallel BRIN index builds speed up query execution.

Contributions by Neon Engineers

• EXPLAIN (SERIALIZE): Provides insights into query serialization costs for better performance tuning.
• BRIN Parallel Index Builds: Supports parallel construction of BRIN(Block Range INdex) indexes, reducing build times on large datasets.
• B-Tree Performance Improvements: Optimizes queries with large IN lists, enhancing performance for queries involving extensive value lists.

Logical Replication Enhancements

Advanced Security and Operational Features

• New TLS Option (sslnegotiation): Facilitates direct TLS handshakes using ALPN, improving secure connection performance.
• pg_maintain Role: A new role for managing maintenance operations, improving role-based security management.
• Incremental Backups and Enhanced pg_dump: Supports incremental backups, while pg_dump now includes a –filter option for selective data dumping.
• Source code improvements: Neon Engineers contributed to various optimizations and refactorings in the Postgres codebase, enhancing the overall stability and performance of the system​—e.g. by removing AIX support and the configure options –disable-thread-safety and –with-CC. (Heikki Linnakangas) .
• Improvements in libpq/psql: Postgres 17 also comes with various enhancements to libpq and psql. These improvements mean a more robust and feature-rich developer experience when interacting with Postgres from the command line or via applications. (Tristan Partin, Tom Lane)
• Event Triggers and AMCheck Validation: Adds granular control over session-level operations and thorough validation checks for B-tree indexes, enhancing database integrity and reliability.



Get Started with PostgreSQL 17

PostgreSQL 17 is available for download now. To explore the full list of new features and enhancements, check out the PostgreSQL 17 Release Notes.

Useful Links:

• Download PostgreSQL 17
• Release Notes
• Press Kit
• Security Page
• Neon Engineers
• Versioning policy

Conclusion

With the release of PostgreSQL 17, database administrators, developers, and organizations gain access to powerful new features and performance enhancements. Whether it’s the MERGE command, advanced JSON support, or improved logical replication, PostgreSQL 17 delivers exceptional value, making it the database of choice for modern applications.