PostgreSQL Updates: What’s New in the Latest Release?

Introduction
Key Features in PostgreSQL 16
How to Upgrade to PostgreSQL 16
Conclusion
References

Key Features in PostgreSQL 16

The Long-Awaited `MERGE` Command

For years, PostgreSQL users have relied on INSERT ... ON CONFLICT for “upsert” (insert-or-update) operations. While powerful, it lacks support for more complex workflows—like updating some rows, deleting others, and inserting new ones in a single statement. Enter MERGE, a SQL-standard command finally introduced in PostgreSQL 16.

What it does:
MERGE lets you conditionally perform INSERT, UPDATE, or DELETE operations on a target table based on matching rows in a source table or query. This simplifies logic for syncing data between tables (e.g., merging a staging table into a production table).

Example Use Case:
Suppose you have a production_orders table and a staging_orders table with daily updates. You want to:

Update existing orders in production_orders if the status in staging_orders is “shipped.”
Delete orders in production_orders if they’re marked “cancelled” in staging_orders.
Insert new orders from staging_orders that don’t exist in production_orders.

With MERGE, this can be done in one query:

MERGE INTO production_orders AS target
USING staging_orders AS source
ON (target.order_id = source.order_id)
WHEN MATCHED AND source.status = 'shipped' THEN
  UPDATE SET status = 'shipped', ship_date = source.ship_date
WHEN MATCHED AND source.status = 'cancelled' THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, status, order_date)
  VALUES (source.order_id, source.customer_id, source.status, source.order_date);

Why it matters:
MERGE reduces the need for complex transaction blocks or application-side logic, minimizing round-trips to the database and reducing error risks.

Enhanced Logical Replication: Row Filtering & Column Lists

Logical replication (which replicates changes at the row level, not the disk level) has been a PostgreSQL staple since v10, but it lacked flexibility. PostgreSQL 16 addresses this with two game-changing additions: row filtering and column lists.

Row Filtering

Now you can replicate only specific rows from a table by adding a WHERE clause to a publication. This is critical for multi-tenant systems, where you might replicate only data for a specific tenant to a read replica.

Example:
Replicate only active users from a users table to a replica:

-- Create a publication with row filtering
CREATE PUBLICATION active_users_pub FOR TABLE users
WHERE (is_active = true);

-- On the subscriber, create a subscription to this publication
CREATE SUBSCRIPTION active_users_sub
CONNECTION 'host=primary dbname=app user=repl'
PUBLICATION active_users_pub;

Column Lists

You can now specify which columns to replicate, avoiding unnecessary data transfer (e.g., excluding sensitive columns like password_hash).

Example:
Replicate only id, name, and email from the users table:

CREATE PUBLICATION user_public_pub FOR TABLE users (id, name, email);

Why it matters:
These features reduce replication overhead, improve security (by limiting sensitive data), and make logical replication viable for more use cases—like data warehousing or regional read replicas.

Performance Boosts: Merge Joins, Parallel Query, and BRIN Indexes

PostgreSQL 16 delivers significant performance gains across the board, with optimizations for large-scale workloads.

Merge Join Optimization

Merge joins are efficient for joining large, sorted datasets, but PostgreSQL historically underutilized them. In v16, the query planner now prioritizes merge joins for scenarios like:

Joins on indexed, sorted columns (e.g., ORDER BY date).
Joins involving CTEs or subqueries with sorted outputs.

Impact: Faster joins for data warehousing and analytics workloads, where large tables are joined on time or ID columns.

Parallel Query Improvements

Parallel query (using multiple CPU cores to speed up operations) now supports more workloads:

Parallel CREATE INDEX: For B-tree indexes on large tables, parallel index creation reduces build time by leveraging multiple cores.
Parallel Vacuum: Vacuuming large tables (especially partitioned ones) now parallelizes across segments, reducing I/O contention.

Example:
Create a B-tree index in parallel (default behavior for large tables in v16):

-- Uses parallel workers if table size exceeds parallel_setup_cost threshold
CREATE INDEX idx_orders_date ON orders (order_date);

BRIN Index Enhancements

Block Range Indexes (BRIN) are lightweight indexes ideal for large tables with ordered, range-based data (e.g., time-series metrics). PostgreSQL 16 improves BRIN scan speeds by up to 2x for tables with millions of rows, making them even more competitive with B-trees for range queries.

Use Case: A table with 100M+ sensor readings, where you frequently query by timestamp. A BRIN index here will use 100x less storage than a B-tree and scan faster for time-range filters.

Strengthened Security: Password Policies and Granular Access Control

Security remains a priority, and PostgreSQL 16 adds tools to enforce stricter access controls.

Password Rotation Policies

PostgreSQL 16 introduces password expiration and rotation via the pg_password_policy extension (shipped by default). Admins can enforce:

Password expiration (e.g., 90 days).
Minimum password length and complexity.
Prevention of password reuse.

Example:
Enforce a 90-day password expiration for all users:

-- Load the extension
CREATE EXTENSION pg_password_policy;

-- Create a policy
CREATE POLICY password_expiry_policy
ON pg_authid
AS RESTRICTIVE
FOR ALL
USING (current_date - (pg_authid.valid_until) > 90);

Granular Privilege Management

PostgreSQL 16 adds GRANT ... WITH GRANT OPTION for column-level privileges, letting admins delegate access to specific columns without exposing entire tables.

Example:
Allow a reporting role to read order_date and total but not customer_ssn:

GRANT SELECT (order_date, total) ON orders TO reporting WITH GRANT OPTION;

JSONB Improvements: Faster Queries and New Functions

JSONB (binary JSON) is PostgreSQL’s go-to for storing flexible, schema-less data. v16 enhances JSONB with new functions and performance tweaks.

`jsonb_path_query_first`

Extract the first matching result from a JSON path query, instead of returning all matches. Useful for finding the “latest” or “highest” value in a JSON array.

Example:
Find the first active user in a JSON array:

SELECT jsonb_path_query_first(
  '[{"name": "Alice", "active": true}, {"name": "Bob", "active": false}]',
  '$[*] ? (@.active == true)'
);
-- Returns: {"name": "Alice", "active": true}

Faster JSONB Lookups

Internal optimizations reduce JSONB key lookup time by 10-15% for large objects, benefiting applications that heavily use JSONB for dynamic data (e.g., e-commerce product attributes).

Developer Experience: Better Error Messages and PL/pgSQL Enhancements

PostgreSQL 16 makes debugging easier with more informative error messages and PL/pgSQL improvements.

Context-Rich Error Messages

Errors now include:

Line numbers in the original query (e.g., ERROR at line 5: column "nonexistent" does not exist).
Stack traces for PL/pgSQL functions, showing where in the function the error occurred.

Example:
A typo in a query now highlights the exact line:

SELECT id, name, emal FROM users; -- "emal" is a typo
-- ERROR: column "emal" does not exist at character 15
-- LINE 1: SELECT id, name, emal FROM users;
--                     ^

PL/pgSQL: `DEFAULT` Parameters and `NULL` Checks

PL/pgSQL functions now support DEFAULT values for parameters, simplifying function calls:

CREATE OR REPLACE FUNCTION get_orders(
  status text DEFAULT 'pending' -- Default value
) RETURNS SETOF orders AS $$
BEGIN
  RETURN QUERY SELECT * FROM orders WHERE order_status = status;
END;
$$ LANGUAGE plpgsql;

-- Call without specifying status (uses 'pending')
SELECT * FROM get_orders();

Additionally, IS [NOT] NULL checks are now optimized in PL/pgSQL, reducing runtime overhead for conditional logic.

Monitoring & Observability: `pg_stat_io` for I/O Insights

Understanding I/O patterns is critical for optimizing database performance. PostgreSQL 16 introduces pg_stat_io, a new system view that tracks I/O activity (reads, writes, syncs) at the relation level (tables, indexes, toast tables).

Example:
Identify which tables are causing the most write I/O:

SELECT 
  relname AS table_name,
  writes AS total_writes,
  write_time AS total_write_time_ms
FROM pg_stat_io
WHERE relkind = 'r' -- Focus on regular tables
ORDER BY writes DESC
LIMIT 5;

Use Case: Diagnose slow queries by correlating high I/O on specific tables with application workloads (e.g., a frequently updated cart table in an e-commerce app).

How to Upgrade to PostgreSQL 16

Upgrading is straightforward with pg_upgrade, which minimizes downtime by migrating data files directly. Follow these steps:

Backup Your Database: Use pg_dump or pg_basebackup to create a backup.
Install PostgreSQL 16: Use your package manager (e.g., apt, yum) or compile from source.

Run pg_upgrade:

# Initialize a new PostgreSQL 16 cluster
initdb -D /var/lib/postgresql/16/main

# Upgrade from 15 to 16 (adjust paths as needed)
pg_upgrade \
  -b /usr/lib/postgresql/15/bin \
  -B /usr/lib/postgresql/16/bin \
  -d /var/lib/postgresql/15/main \
  -D /var/lib/postgresql/16/main

Test & Restart: Verify the upgrade with pg_ctl start -D /var/lib/postgresql/16/main, then test applications for compatibility.

Note: Check the PostgreSQL 16 release notes for deprecated features (e.g., pg_stat_statements.track_planning is now pg_stat_statements.track).

Conclusion

PostgreSQL 16 is a landmark release, with features that cater to developers, DBAs, and data engineers alike. From the MERGE command to logical replication flexibility, performance boosts, and enhanced monitoring, it solidifies PostgreSQL’s position as a leading open-source database for modern applications.

Whether you’re building a high-scale SaaS platform, a data warehouse, or a real-time application, upgrading to PostgreSQL 16 will unlock faster performance, better security, and a smoother developer experience.

Table of Contents