PostgreSQL 18 Release Notes: New Features and Improvements

PostgreSQL 18 represents a significant milestone in the evolution of the world's most advanced open-source relational database. Released on September 25, 2025, this version brings substantial performance improvements through a new asynchronous I/O subsystem, enhanced developer features including virtual generated columns and temporal constraints, and improved upgrade capabilities.

Release Timeline

• Beta 1: May 2025
• Release Candidate 1: September 2025
• General Availability: September 25, 2025
• First Minor Update (18.1): November 13, 2025
• End of Life: Approximately September 2030 (5 years from GA)

Major New Features in PostgreSQL 18

1. Asynchronous I/O Subsystem

PostgreSQL 18 introduces a groundbreaking asynchronous I/O (AIO) subsystem that delivers up to 3× performance improvements when reading from storage.

Key Benefits:

• Up to 3× faster sequential scans
• Improved performance for bitmap heap scans
• Faster VACUUM operations
• Better utilization of modern storage hardware

Impact: The AIO subsystem fundamentally changes how PostgreSQL interacts with storage, allowing multiple I/O operations to proceed concurrently rather than blocking on each read. This is particularly beneficial for workloads involving large table scans and analytical queries.

Configuration:

# postgresql.conf - Enhanced defaults for modern hardware
max_io_concurrency = 32  # Increased default
maintenance_io_concurrency = 32  # Increased default

2. Virtual Generated Columns

PostgreSQL 18 makes virtual generated columns the default, computing values at query time rather than storing them.

Key Benefits:

• Reduced storage requirements
• No write overhead during INSERT/UPDATE
• Always up-to-date computed values
• Better for frequently changing base columns

Example:

-- Virtual generated column (default in PG 18)
CREATE TABLE products (
    price DECIMAL(10,2),
    tax_rate DECIMAL(5,4),
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);

-- Stored generated column (explicit)
CREATE TABLE users (
    first_name TEXT,
    last_name TEXT,
    full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

3. Temporal Constraints with WITHOUT OVERLAPS

PostgreSQL 18 introduces temporal constraints that prevent overlapping time periods in your data.

New Capability: Support for WITHOUT OVERLAPS and PERIOD clauses in PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.

Use Cases:

• Room booking systems (prevent double-bookings)
• Employee shift scheduling
• Resource allocation tracking
• Temporal data management

Example:

-- Prevent overlapping room reservations
CREATE TABLE reservations (
    room_id INT,
    reservation_period tstzrange,
    guest_name TEXT,
    CONSTRAINT no_overlap PRIMARY KEY (room_id, reservation_period WITHOUT OVERLAPS)
);

-- This will succeed
INSERT INTO reservations VALUES (101, '[2025-12-01, 2025-12-05)', 'Alice');

-- This will fail - overlapping period
INSERT INTO reservations VALUES (101, '[2025-12-03, 2025-12-07)', 'Bob');

4. UUID Version 7 Support

PostgreSQL 18 adds native support for UUID version 7, which includes timestamp ordering for better index performance.

New Function: The uuidv7() function generates timestamp-ordered UUIDs that perform significantly better in B-tree indexes.

Advantages over UUIDv4:

• Better index locality (reduced page splits)
• Improved insert performance
• Natural chronological ordering
• Reduced index bloat

Example:

-- Create table with UUIDv7 primary key
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    event_type TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- UUIDs are naturally ordered by creation time
INSERT INTO events (event_type) VALUES ('login'), ('logout'), ('purchase');

SELECT id FROM events ORDER BY id;
-- Results are chronologically ordered

5. Enhanced RETURNING Clause

PostgreSQL 18 allows access to both OLD and NEW values in RETURNING clauses for INSERT, UPDATE, DELETE, and MERGE statements.

New Capability: Track both the previous and new values of modified rows in a single query.

Example:

-- Update prices and see both old and new values
UPDATE products
SET price = price * 1.1
WHERE category = 'electronics'
RETURNING
    id,
    OLD.price AS old_price,
    NEW.price AS new_price,
    NEW.price - OLD.price AS price_increase;

-- Merge with full audit trail
MERGE INTO inventory i
USING shipments s ON i.product_id = s.product_id
WHEN MATCHED THEN
    UPDATE SET quantity = i.quantity + s.quantity
RETURNING
    NEW.product_id,
    OLD.quantity AS previous_quantity,
    NEW.quantity AS current_quantity;

6. Performance Improvements

Query Planner Enhancements

• Skip scans on multicolumn B-tree indexes for better DISTINCT queries
• Self-join elimination optimization
• Enhanced OR condition optimization
• Improved statistics preservation during pg_upgrade

Parallel Query Improvements

• Parallel GIN index builds for faster index creation
• Enhanced parallel hash joins with reduced memory usage
• Improved GROUP BY performance with parallel processing

Index Performance

• Skip scan support for multicolumn indexes
• Better B-tree index utilization
• Enhanced index-only scan efficiency

7. pg_upgrade Improvements

PostgreSQL 18 significantly improves the upgrade experience with faster processing and better post-upgrade performance.

Key Improvements:

• Statistics preservation: Planner statistics now persist through major version upgrades, eliminating the post-upgrade performance degradation period
• Parallel processing: pg_upgrade checks now run in parallel for faster validation
• Swap flag: New --swap option for faster migrations

Example:

# Faster upgrade with parallel checks
pg_upgrade \
    -b /usr/lib/postgresql/17/bin \
    -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17/data \
    -D /var/lib/postgresql/18/data \
    --check \
    --jobs=4

# Use swap mode for faster migration
pg_upgrade --swap \
    -b /usr/lib/postgresql/17/bin \
    -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17/data \
    -D /var/lib/postgresql/18/data

8. Security Enhancements

New Security Features:

• OAuth 2.0 authentication support for modern authentication flows
• SCRAM passthrough authentication for remote connections
• SHA-2 encryption support in pgcrypto for password hashing
• Data checksums enabled by default in initdb (new installations)
• MD5 password authentication deprecated (use SCRAM-SHA-256)

Example:

-- Create role with SCRAM-SHA-256 authentication
CREATE ROLE app_user WITH
    LOGIN
    PASSWORD 'secure_password'
    CONNECTION LIMIT 50;

-- MD5 is now deprecated - will warn or fail based on configuration
-- Use SCRAM-SHA-256 instead (default in PostgreSQL 18)

Configuration:

# postgresql.conf
password_encryption = 'scram-sha-256'  # Default in PG 18

9. Enhanced Unicode and Text Processing

New Features:

• PG_UNICODE_FAST collation for improved Unicode handling
• casefold() function for case-insensitive comparisons
• LIKE with nondeterministic collations support

Example:

-- Case-insensitive comparison using casefold()
SELECT * FROM users
WHERE casefold(username) = casefold('JohnDoe');

-- LIKE now works with nondeterministic collations
CREATE COLLATION case_insensitive (
    provider = icu,
    locale = 'und-u-ks-level2',
    deterministic = false
);

SELECT * FROM products
WHERE name LIKE '%widget%' COLLATE case_insensitive;

10. Monitoring and Observability

Enhanced EXPLAIN Output:

• Buffer access statistics showing read patterns
• Index lookup counts for better query analysis
• More detailed timing information

Additional Statistics:

• Enhanced VACUUM statistics in system views
• Expanded I/O statistics tracking
• Better insight into query performance bottlenecks

Example Usage:

-- Enhanced EXPLAIN with buffer statistics
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id > 1000;

-- View detailed I/O statistics
SELECT * FROM pg_stat_io;

-- Monitor vacuum operations
SELECT * FROM pg_stat_progress_vacuum;

11. Enhanced COPY Command

PostgreSQL 18 improves the COPY command with better error handling and data import capabilities.

New Features:

• Error handling: Better control over how errors are handled during bulk imports
• Rejection limits: Set thresholds for acceptable error rates
• Improved performance: Faster bulk data loading

Example:

-- COPY with error handling
COPY products FROM '/data/products.csv'
WITH (FORMAT csv, HEADER true);

-- Import with logging of problematic rows
COPY events FROM '/data/events.csv'
WITH (FORMAT csv, ON_ERROR ignore);

12. PostgreSQL Wire Protocol 3.2

PostgreSQL 18 introduces version 3.2 of the wire protocol, bringing improvements to client-server communication.

Benefits:

• More efficient data transfer
• Better error reporting
• Enhanced prepared statement handling
• Improved authentication flows
• Backward compatible with older clients

Performance Benchmarks

Query Performance

Real-world improvements from PostgreSQL 18:

• Up to 3× faster sequential scans (AIO subsystem)
• 2-3× faster DISTINCT queries on multicolumn indexes (skip scans)
• 20-40% faster complex analytical queries
• 15-25% improvement in OLTP workloads
• Reduced memory usage for hash joins and GROUP BY operations

I/O Performance

• Up to 3× improvement when reading from storage (AIO)
• Faster bitmap heap scans
• Improved VACUUM performance
• Better utilization of modern NVMe storage

Index Performance

• Skip scan optimization for multicolumn indexes
• Parallel GIN index builds
• Enhanced B-tree index efficiency
• Better index-only scan performance

Deprecated and Removed Features

Deprecated Features

1. MD5 Password Authentication

   • MD5 authentication is now deprecated
   • Use SCRAM-SHA-256 instead (default in PostgreSQL 18)
   • Migration path: update password_encryption setting

   # postgresql.conf
   password_encryption = 'scram-sha-256'  # Use this instead of md5
   

2. Legacy Backup Methods

   • Exclusive backup mode (use pg_basebackup or non-exclusive API)
   • Will be removed in future versions

Breaking Changes

1. VACUUM and ANALYZE Behavior

   • Changed behavior with partitioned tables
   • Review automation scripts that use VACUUM/ANALYZE

2. COPY Special Markers

   • Modified handling of special markers in COPY operations
   • Test data import/export workflows

3. Python 2 Support Removed

   • PL/Python now requires Python 3.7 or later
   • Update Python-based stored procedures

Upgrade Considerations

Pre-Upgrade Checklist

1. Review Compatibility

   • Check deprecated feature usage
   • Review custom extensions compatibility
   • Test application queries

2. Backup Strategy

   • Perform full database backup
   • Test restore procedures
   • Document rollback plan

3. Testing Environment

   • Set up staging environment with PostgreSQL 18
   • Run application test suite
   • Perform load testing

Upgrade Methods

Method 1: pg_upgrade (Recommended)

# Stop PostgreSQL 17
pg_ctl stop -D /var/lib/postgresql/17/data

# Run pg_upgrade
pg_upgrade \
    -b /usr/lib/postgresql/17/bin \
    -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17/data \
    -D /var/lib/postgresql/18/data \
    --check

# Perform actual upgrade
pg_upgrade \
    -b /usr/lib/postgresql/17/bin \
    -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17/data \
    -D /var/lib/postgresql/18/data

# Start PostgreSQL 18
pg_ctl start -D /var/lib/postgresql/18/data

Method 2: Logical Replication

-- On PostgreSQL 17 (source)
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- On PostgreSQL 18 (target)
CREATE SUBSCRIPTION upgrade_sub
CONNECTION 'host=source_host dbname=mydb'
PUBLICATION upgrade_pub;

Method 3: Dump and Restore

# Dump from PostgreSQL 17
pg_dumpall -h old_server > dump.sql

# Restore to PostgreSQL 18
psql -h new_server -f dump.sql

Post-Upgrade Tasks

1. Update Statistics

VACUUM ANALYZE;

2. Reindex if Necessary

REINDEX DATABASE mydb;

3. Update Extensions

ALTER EXTENSION extension_name UPDATE;

4. Monitor Performance

• Check slow query logs
• Monitor resource utilization
• Validate application performance

Configuration Changes

New Configuration Parameters

# I/O Concurrency (increased defaults for modern hardware)
max_io_concurrency = 32  # Increased from 1
maintenance_io_concurrency = 32  # Increased from 10

# Data Integrity (new installations only)
data_checksums = on  # Enabled by default in initdb

# Security
password_encryption = 'scram-sha-256'  # Default (was md5)

# Performance tuning
enable_parallel_gin_build = on  # New parallel GIN index builds

# Monitoring
track_io_timing = on
track_wal_io_timing = on

Modified Parameters

• max_io_concurrency: Default increased to better utilize modern storage
• maintenance_io_concurrency: Default increased for better index build performance
• password_encryption: Now defaults to scram-sha-256 instead of md5

Important Configuration Notes

• Data checksums: Enabled by default for new installations created with initdb
• Existing databases: Upgrading does NOT automatically enable checksums (requires rebuild)

Platform Support

Supported Operating Systems

• Linux: RHEL 8+, Ubuntu 20.04+, Debian 11+
• Windows: Windows Server 2019+, Windows 10+
• macOS: macOS 11+ (Big Sur and later)
• FreeBSD: FreeBSD 13+

Supported Compilers

• GCC 7.0+
• Clang 6.0+
• MSVC 2019+

Extension Compatibility

Compatible Extensions

Most popular PostgreSQL extensions have been updated for PostgreSQL 18 compatibility:

• pgvector: Full support for vector similarity search
• PostGIS: Version 3.5+ compatible with PostgreSQL 18
• TimescaleDB: Version 2.18+ optimized for PostgreSQL 18 features
• pg_stat_statements: Enhanced with additional tracking capabilities
• pg_partman: Compatible with new partition features
• Citus: Distributed PostgreSQL support available
• pgcrypto: Enhanced with SHA-2 support

Extension Notes

• Check extension documentation for version-specific requirements
• Some extensions may require updates for full PostgreSQL 18 feature support
• Test extensions in staging before production deployment
• Generated columns now supported in logical replication (benefits extensions using replication)

Migration from Older Versions

From PostgreSQL 14 and Earlier

Critical Changes:

• Review authentication method changes
• Update custom functions for new syntax
• Check procedural language compatibility

Recommended Path:

• Upgrade to PostgreSQL 15 or 16 first
• Test thoroughly in staging
• Then upgrade to PostgreSQL 18

From PostgreSQL 15-17

Minimal Changes Required:

• Review new features for optimization opportunities
• Update configuration for new parameters
• Test logical replication if used

Installing PostgreSQL 18

Package Installation

# Install on Ubuntu/Debian
sudo apt-get update
sudo apt-get install postgresql-18

# Install on RHEL/CentOS/Fedora
sudo dnf install postgresql18-server

# Initialize database (new installations)
sudo postgresql-18-setup initdb
sudo systemctl enable postgresql-18
sudo systemctl start postgresql-18

Docker Installation

# Pull PostgreSQL 18 image
docker pull postgres:18

# Run PostgreSQL 18 container
docker run --name postgres18 \
  -e POSTGRES_PASSWORD=mypassword \
  -p 5432:5432 \
  -d postgres:18

# Or use with a specific minor version
docker pull postgres:18.1

Community Resources

Official Documentation

• Release Notes: https://www.postgresql.org/docs/18/release.html
• What's New: https://www.postgresql.org/docs/18/release-18.html
• Migration Guide: https://www.postgresql.org/docs/18/migration.html

Community Support

• Mailing Lists: pgsql-announce, pgsql-general
• IRC: #postgresql on Libera.Chat
• Slack: PostgreSQL Community Slack

Bug Reporting

• Bug Tracker: https://www.postgresql.org/support/submitbug/
• Security Issues: security@postgresql.org

Best Practices for Adoption

1. Plan Ahead

• Review release notes thoroughly
• Identify features beneficial to your workload
• Plan testing timeline

2. Test Extensively

• Set up dedicated test environment
• Run comprehensive test suite
• Perform load testing
• Test backup and recovery procedures

3. Gradual Rollout

• Upgrade non-critical systems first
• Monitor performance and stability
• Document issues and resolutions
• Plan production upgrade during maintenance window

4. Stay Informed

• Subscribe to PostgreSQL mailing lists
• Monitor community forums
• Follow PostgreSQL blog
• Attend PostgreSQL conferences

Performance Tuning for PostgreSQL 18

Recommended Settings

# Memory settings
shared_buffers = 25% of RAM
effective_cache_size = 75% of RAM
work_mem = 4MB to 64MB (depending on workload)
maintenance_work_mem = 1GB to 2GB

# Parallel processing
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 16

# WAL settings
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
wal_compression = on

# Checkpoint tuning
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

Related Topics

• PostgreSQL Security Best Practices
• PostgreSQL Row Level Security
• PostgreSQL Vulnerability List
• PostgreSQL vs MySQL

What's Next: PostgreSQL 19

Development of PostgreSQL 19 is already underway, expected for release in late 2026. Early development discussions include:

• Further performance improvements building on the AIO subsystem
• Additional SQL standard compliance features
• Enhanced parallel query capabilities
• Continued security enhancements

Summary

PostgreSQL 18, released September 25, 2025, represents a major leap forward for the database:

Top Features:

1. Asynchronous I/O - Up to 3× performance improvement for storage operations
2. Virtual Generated Columns - Compute values on-demand without storage overhead
3. Temporal Constraints - Prevent overlapping periods with WITHOUT OVERLAPS
4. UUID v7 - Timestamp-ordered UUIDs for better index performance
5. Enhanced RETURNING - Access both OLD and NEW values in modifications
6. pg_upgrade Improvements - Statistics preservation and parallel processing
7. OAuth 2.0 - Modern authentication support
8. Skip Scans - Faster DISTINCT queries on multicolumn indexes

Key Benefits:

• Significantly faster for analytical and OLTP workloads
• Better upgrade experience with preserved statistics
• Enhanced security with OAuth and deprecated MD5
• Improved developer productivity with new SQL features
• Better observability and monitoring capabilities

PostgreSQL 18 is recommended for all new deployments and provides compelling reasons to upgrade from earlier versions.

Further Reading

• PostgreSQL 18 Documentation: Official Docs
• PostgreSQL 18 Release Notes: Release 18
• PostgreSQL Wiki: PostgreSQL 18 Features
• PostgreSQL Planet: Community Blogs
• PostgreSQL Mailing Lists: Archives