PostgreSQL 19 Beta Introduces SQL/PGQ, Enhanced Maintenance, and Query Optimization Features

PostgreSQL 19 is currently in beta, with general availability expected around September or October 2026. This release introduces several noteworthy features that enhance the database's capabilities in graph querying, data maintenance, conflict resolution, and query optimization. One of the headline features is the implementation of SQL/PGQ, the property-graph component of the SQL:2023 standard. Users can define a property graph over existing tables and query it using pattern matching, simplifying complex join operations. For example, a social graph can be declared over person and follows tables, enabling multi-hop queries such as friends-of-friends without explicit self-joins. The graph queries are internally rewritten into standard relational queries, ensuring compatibility with existing planner optimizations and indexes. However, variable-length path patterns are not yet supported. Maintenance operations receive improvements with the introduction of the REPACK command, which unifies and replaces VACUUM FULL and CLUSTER. REPACK supports a CONCURRENTLY option that allows table rebuilding without acquiring an ACCESS EXCLUSIVE lock, enabling ongoing reads and writes during the operation. Although this feature was reverted shortly after beta 3 due to design issues, it is expected to appear in PostgreSQL 20. The new FOR PORTION OF clause on UPDATE and DELETE statements allows modifications to specific sub-periods of range columns, automatically splitting or trimming rows as needed. This is particularly useful for temporal data management. Conflict handling during inserts is enhanced with ON CONFLICT DO SELECT, which returns existing conflicting rows without modifying them. This addition fills a gap where previously conflicting rows were omitted from results, making it easier to distinguish between inserted and existing data. The clause also supports locking options to help manage concurrent access. Window functions such as lead(), lag(), first_value(), last_value(), and nth_value() now support the SQL-standard IGNORE NULLS clause, simplifying queries that need to skip null values. Query planning sees several enhancements. The pg_plan_advice module introduces a way to record and constrain query plans without overriding the planner, providing feedback on whether advice was applied. Additionally, the planner can now push partial aggregates below joins to reduce the number of rows processed, improving performance for certain grouped queries. The planner also optimizes NOT IN subqueries by converting them to anti-joins when nullability conditions are met. EXPLAIN output gains new options, including IO reporting that details asynchronous I/O operations, and enhanced information for Memoize nodes showing planner estimates. COPY commands now support JSON output, multi-line headers, error handling that sets invalid fields to NULL, and direct copying of partitioned tables. Other notable improvements include new range operators that return multiple ranges when subtracting non-contiguous intervals, expanded jsonpath string functions, additional encoding alphabets for encode()/decode(), and support for ALTER CONSTRAINT NOT ENFORCED on CHECK constraints. Operational defaults have changed as well: lz4 compression is used when available, max_locks_per_transaction has doubled, log_lock_waits is enabled by default, and standard_conforming_strings is now forced on. Some legacy behaviors have been removed or altered, so reviewing migration notes is advised before upgrading. Overall, PostgreSQL 19 focuses on expanding SQL standards compliance, improving maintenance operations, enhancing conflict resolution semantics, and providing deeper insight into query planning. These changes aim to make PostgreSQL more powerful and easier to manage for complex workloads.