Database Deep Dives

PostgreSQL 17, released in September 2024, delivers significant improvements to MERGE statement capabilities, logical replication, performance, and JSON handling. For engineering teams running PostgreSQL in production, understanding what changed — and how to leverage it — is essential for 2026 planning.

PostgreSQL 17: What Changed and Why It Matters

PostgreSQL 17 is a major release that continues the project's tradition of significant feature additions alongside robust performance improvements. The headline features for enterprise engineering teams are the expanded MERGE statement (now with RETURNING clause and unconditional WHEN NOT MATCHED BY SOURCE), major logical replication enhancements (including replication slot failover), a new streaming I/O subsystem, and improvements to VACUUM and query planning.

MERGE Enhancements in PostgreSQL 17

The SQL MERGE statement (ISO/IEC 9075-2:2003) was added to PostgreSQL in version 15 but had significant limitations compared to the standard. PostgreSQL 17 closes most of those gaps with two major additions: MERGE ... RETURNING and WHEN NOT MATCHED BY SOURCE.

MERGE ... RETURNING

PostgreSQL 17 allows MERGE statements to return rows affected by the operation, similar to how INSERT, UPDATE, and DELETE support RETURNING. This is extremely useful for audit logging, returning generated IDs, and pipeline processing:

MERGE INTO customers AS target
USING staging_customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
  UPDATE SET
    email = source.email,
    updated_at = NOW()
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, created_at)
  VALUES (source.customer_id, source.email, NOW())
RETURNING merge_action(), target.customer_id, target.email;

The merge_action() function (new in PostgreSQL 17) returns 'INSERT', 'UPDATE', or 'DELETE' for each row processed, enabling precise audit trail construction without additional queries.

WHEN NOT MATCHED BY SOURCE

This clause, finally added in PostgreSQL 17, allows MERGE to handle rows in the target table that have no corresponding row in the source — enabling true UPSERT-and-delete patterns in a single statement:

MERGE INTO products AS target
USING latest_product_feed AS source
ON target.sku = source.sku
WHEN MATCHED AND source.active = TRUE THEN
  UPDATE SET price = source.price, stock = source.stock
WHEN MATCHED AND source.active = FALSE THEN
  DELETE
WHEN NOT MATCHED BY TARGET THEN
  INSERT (sku, price, stock) VALUES (source.sku, source.price, source.stock)
WHEN NOT MATCHED BY SOURCE THEN
  -- Row exists in products but not in feed: mark as discontinued
  UPDATE SET discontinued = TRUE, updated_at = NOW();

This capability was previously only available in SQL Server and enabled complex ETL operations in a single atomic transaction — something PostgreSQL users had to implement with multiple CTE statements and subqueries.

Logical Replication Improvements

Logical replication is a critical capability for PostgreSQL architectures requiring zero-downtime major version upgrades, selective table replication, and change data capture (CDC) pipelines. PostgreSQL 17 delivers several important improvements:

Performance: Streaming I/O and VACUUM

PostgreSQL 17 introduces a new streaming I/O subsystem that significantly improves performance for sequential workloads — full table scans, VACUUM operations, and bulk data loads. The new system uses asynchronous I/O under the hood (via io_uring on Linux, and asynchronous reads on other platforms) to pipeline I/O operations, reducing the time that sequential scans spend waiting for disk.

VACUUM performance improvements in v17 are particularly valuable for high-write OLTP workloads. The new dead tuple tracking algorithm reduces the WAL written during VACUUM operations and improves the efficiency of visibility map updates, resulting in lower I/O overhead during autovacuum cycles.

JSON and SQL/JSON Improvements

PostgreSQL 17 continues to close the gap between PostgreSQL's JSON capabilities and the SQL/JSON standard (ISO/IEC 9075-2:2016). New in v17:

• JSON_TABLE() — Convert JSON data to relational rows/columns using JSON path expressions, similar to XMLTable. Useful for querying API responses and semi-structured data stored in JSONB columns.
• SQL/JSON constructor functions — JSON(), JSON_SCALAR(), JSON_SERIALIZE() for standards-compliant JSON construction.
• SQL/JSON query functions — JSON_EXISTS(), JSON_VALUE(), JSON_QUERY() as alternatives to PostgreSQL's @> and ->> operators, improving portability across database platforms.

-- Using JSON_TABLE to query nested JSON arrays
SELECT jt.order_id, jt.item_name, jt.quantity
FROM orders,
JSON_TABLE(
  order_data,
  '$.items[*]' COLUMNS (
    order_id FOR ORDINALITY,
    item_name TEXT PATH '$.name',
    quantity INT PATH '$.qty'
  )
) AS jt
WHERE jt.quantity > 5;

Upgrade and Migration Guidance