PostgreSQL 17 & 18: New Features and Migration Guide

PostgreSQL continues to evolve with each major release, bringing performance improvements, new features, and enhanced developer experience. PostgreSQL 17, released in late 2023, and PostgreSQL 18, expected in 2024, introduce significant capabilities that can transform how you manage and query your data. This tutorial dives deep into the most impactful new features, providing practical examples and a step-by-step migration guide. Whether you're a DBA planning an upgrade or a developer looking to leverage new SQL features, this article will help you understand what's new and how to adopt these changes safely in production. We'll cover incremental backup for faster disaster recovery, MERGE enhancements for upsert operations, SQL/JSON constructor functions for easier JSON handling, and performance improvements like parallel query for MERGE and better vacuum management. Real-world scenarios and debugging tips ensure you can apply these features immediately.

Incremental Backup in PostgreSQL 17

PostgreSQL 17 introduces native incremental backup, a game-changer for large databases. Previously, pg_basebackup only supported full backups, which could be time-consuming and I/O-intensive. Incremental backup allows you to back up only the changes since the last full backup, drastically reducing backup time and storage. The feature is built on the WAL (Write-Ahead Log) and uses a new 'incremental' mode in pg_basebackup. To perform an incremental backup, you first need a full backup as a base. Then you can run: pg_basebackup -D /backup/incremental -Fp -Xs -P --incremental=/backup/full. This creates a backup that contains only the blocks changed since the base backup. Restoring requires the base backup plus all incremental backups. You can also use pg_combinebackup to merge incremental backups into a new full backup. This feature is especially useful for large databases where full backups are impractical daily.

# Create a full backup as base
pg_basebackup -D /backup/full -Fp -Xs -P

# Later, create an incremental backup
pg_basebackup -D /backup/inc1 -Fp -Xs -P --incremental=/backup/full

# Combine incremental into new full backup
pg_combinebackup /backup/full /backup/inc1 -o /backup/new_full

MERGE Enhancements and Parallelism

PostgreSQL 17 improves the MERGE statement (also known as UPSERT) with better performance and new capabilities. MERGE now supports a wider range of conditions and actions, and crucially, it can leverage parallel query execution. This means that large MERGE operations can be split across multiple workers, significantly speeding up bulk upserts. For example, you can now use MERGE to update or insert rows based on a join condition, and the query planner can choose to execute it in parallel if the table is large enough. Additionally, MERGE now supports the 'WHEN NOT MATCHED BY SOURCE' clause, allowing you to handle rows that exist in the target but not in the source. This is useful for synchronization tasks. To enable parallelism, ensure max_parallel_workers_per_gather is set appropriately. Example: MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

-- Enable parallel query for MERGE
SET max_parallel_workers_per_gather = 4;

-- Example MERGE with parallel execution
MERGE INTO employees t
USING new_employees s
ON t.employee_id = s.employee_id
WHEN MATCHED THEN
    UPDATE SET salary = s.salary, last_updated = now()
WHEN NOT MATCHED THEN
    INSERT (employee_id, name, salary, last_updated)
    VALUES (s.employee_id, s.name, s.salary, now());

-- Check parallel workers used
EXPLAIN (ANALYZE, BUFFERS) MERGE INTO employees t ...

SQL/JSON Constructor Functions in PostgreSQL 18

PostgreSQL 18 introduces SQL/JSON constructor functions as specified in the SQL standard. These functions allow you to construct JSON documents directly from relational data using a more declarative syntax. The key functions are JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, and JSON_ARRAYAGG. For example, JSON_OBJECT(key: value, ...) creates a JSON object, while JSON_ARRAY(value1, value2, ...) creates a JSON array. JSON_OBJECTAGG aggregates key-value pairs into a JSON object, and JSON_ARRAYAGG aggregates values into a JSON array. These functions are more expressive than the older row_to_json or json_build_object functions and integrate better with SQL standard compliance. Example: SELECT JSON_OBJECT('name' : e.name, 'salary' : e.salary) FROM employees e; This returns a JSON object for each employee. For aggregation: SELECT department_id, JSON_OBJECTAGG(e.name : e.salary) FROM employees e GROUP BY department_id; This returns a JSON object mapping employee names to salaries per department.

-- Create a JSON object for each employee
SELECT JSON_OBJECT('name' : name, 'salary' : salary) AS employee_json
FROM employees;

-- Aggregate employees per department into a JSON object
SELECT department_id,
       JSON_OBJECTAGG(name : salary) AS employee_map
FROM employees
GROUP BY department_id;

-- Create a JSON array of names
SELECT JSON_ARRAY(name) AS name_array
FROM employees;

-- Aggregate all employee names into a JSON array per department
SELECT department_id,
       JSON_ARRAYAGG(name) AS employee_names
FROM employees
GROUP BY department_id;

Improved Vacuum Performance and Autovacuum Tuning

Both PostgreSQL 17 and 18 bring improvements to vacuum operations, reducing bloat and improving performance. PostgreSQL 17 introduces a new 'vacuum buffer' management that reduces I/O during vacuum. PostgreSQL 18 further enhances autovacuum with better detection of tables that need vacuum and more efficient index cleanup. The key change is that vacuum can now skip unnecessary index scans when no dead tuples exist in the index. Additionally, the autovacuum launcher now uses a more sophisticated algorithm to prioritize tables with high bloat. To take advantage, ensure autovacuum is enabled and tune parameters like autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold. For example, for a large table, you might set: ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.01); This triggers vacuum when 1% of rows are dead, rather than the default 20%.

-- Check current autovacuum settings
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_vacuum_threshold;

-- Tune autovacuum for a specific table
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE large_table SET (autovacuum_vacuum_threshold = 1000);

-- Monitor vacuum activity
SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'large_table';

-- Manually vacuum if needed
VACUUM (VERBOSE, ANALYZE) large_table;

Migration Steps from PostgreSQL 16 to 17/18

Migrating to PostgreSQL 17 or 18 requires careful planning. The recommended approach is to use pg_upgrade for in-place upgrades, which is fast and minimizes downtime. Alternatively, you can use logical replication to migrate with near-zero downtime. Before upgrading, check for deprecated features and compatibility. PostgreSQL 17 removes some old functions and changes default settings. For example, the 'sql_inheritance' GUC is removed. Use the pg_upgrade tool with the --check option to identify issues. Steps: 1. Backup your database. 2. Install new PostgreSQL binaries. 3. Run pg_upgrade --check. 4. If no issues, run pg_upgrade. 5. Run vacuumdb --all --analyze-in-stages. 6. Update statistics. For logical replication, set up a publisher on the old server and subscriber on the new server, then switch over. Always test in a staging environment first.

# Step 1: Backup (optional but recommended)
pg_dumpall > backup.sql

# Step 2: Install new PostgreSQL 17 binaries (e.g., using apt)
sudo apt install postgresql-17

# Step 3: Stop old server
sudo systemctl stop postgresql

# Step 4: Run pg_upgrade check
/usr/lib/postgresql/17/bin/pg_upgrade \
  -b /usr/lib/postgresql/16/bin \
  -B /usr/lib/postgresql/17/bin \
  -d /var/lib/postgresql/16/main \
  -D /var/lib/postgresql/17/main \
  --check

# Step 5: If check passes, run upgrade
/usr/lib/postgresql/17/bin/pg_upgrade \
  -b /usr/lib/postgresql/16/bin \
  -B /usr/lib/postgresql/17/bin \
  -d /var/lib/postgresql/16/main \
  -D /var/lib/postgresql/17/main

# Step 6: Start new server
sudo systemctl start postgresql@17-main

# Step 7: Analyze new cluster
/usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages

Performance Improvements: Parallel Query and More

PostgreSQL 17 and 18 include several performance enhancements beyond those already mentioned. PostgreSQL 17 improves parallel query for queries involving aggregates and window functions. It also introduces 'incremental sort' which can speed up queries with multiple ORDER BY columns. PostgreSQL 18 further optimizes the query planner for better join order selection and introduces 'adaptive' parallelism where the number of workers can be adjusted dynamically. Additionally, there are improvements in memory management for sorting and hash joins. To leverage these, ensure your queries are written in a way that allows parallelism (e.g., avoid functions marked PARALLEL UNSAFE). Use EXPLAIN to check if parallel workers are being used. Example: SET max_parallel_workers_per_gather = 4; EXPLAIN (ANALYZE, BUFFERS) SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; This may show a parallel plan.

-- Enable parallel query
SET max_parallel_workers_per_gather = 4;

-- Example query that benefits from parallelism
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Check if parallel workers were used
SELECT * FROM pg_stat_activity WHERE query LIKE '%employees%';