Going down the rabbit hole of Postgres 18 features
9 hours ago
- #Performance
- #PostgreSQL
- #Database
- PostgreSQL 18 introduces Async IO infrastructure for better performance and lower resource utilization, especially beneficial for network-attached storage.
- OAuth 2.0 support is added, enabling secure authentication via SSO providers and OAuth workflows.
- UUIDv7 support is introduced, offering sortable UUIDs with embedded timestamps for better index locality and compression.
- RETURNING clause enhancements allow explicit return of old/new values, simplifying code for updates and inserts.
- Virtual generated columns (VIRTUAL) are now the default, computed on read like views, improving storage efficiency.
- Temporal database improvements include WITHOUT OVERLAPS for PK/unique constraints and PERIOD for foreign keys.
- NOT NULL constraints can now be added as NOT VALID, allowing non-blocking schema changes.
- Performance optimizations include B-tree skip scans for multi-column indexes and self-join elimination.
- Observability enhancements include EXPLAIN improvements, per-process statistics, and lock failure logging.
- New utility functions like array_reverse(), array_sort(), and casefold() are introduced for better handling of arrays and case-insensitive operations.