PostgreSQL 19 Beta Introduces Property Graphs, Improved Maintenance, and Query Enhancements

PostgreSQL 19 is now in beta, with general availability expected around September or October 2026. This release introduces several notable features that enhance graph querying, table maintenance, and query optimization. One of the headline additions is support for SQL/PGQ, the property graph extension of SQL:2023. Users can now define property graphs over existing tables and query them using pattern matching, simplifying complex join operations. For example, a social graph can be created over person and follows tables, allowing multi-hop queries like friends-of-friends without explicit self-joins. The graph queries are internally rewritten into standard relational queries, ensuring compatibility with existing planner and indexing mechanisms. However, variable-length path patterns are not yet supported. Maintenance commands see improvements with the introduction of the REPACK command, which unifies and replaces VACUUM FULL and CLUSTER. REPACK supports a CONCURRENTLY option that rebuilds tables without requiring an ACCESS EXCLUSIVE lock, allowing reads and writes to continue during the operation. Although this feature was reverted shortly after beta 3 due to design issues and will not ship in version 19, it is expected in PostgreSQL 20. The new FOR PORTION OF clause on UPDATE and DELETE allows modifications to specific subranges within range columns, automatically splitting or trimming rows as needed. This is particularly useful for temporal data management. Conflict handling during INSERT is enhanced with ON CONFLICT DO SELECT, which returns existing conflicting rows without modifying them, addressing a previous limitation where conflicting rows were omitted from results. This clause also supports locking to differentiate inserted from existing rows. Window functions such as lead() and lag() now support the SQL-standard IGNORE NULLS clause, simplifying operations like filling gaps in sparse data. Query planning gains new tools including the pg_plan_advice module, which allows users to record and constrain query plans without overriding the planner entirely. This feature provides feedback on whether advice is applied or ignored, improving plan tuning. The planner also introduces eager aggregation, pushing partial aggregates below joins to reduce intermediate row counts and improve performance when grouping by join keys. Optimization of NOT IN (subquery) queries is improved by proving the absence of NULLs, enabling the planner to use more efficient anti-join strategies when columns are declared NOT NULL. EXPLAIN gains new output options to report asynchronous I/O activity and detailed Memoize node statistics, offering deeper insight into query execution. COPY commands are enhanced to support JSON output, multi-line headers, error handling that converts bad data to NULLs, and direct copying from partitioned tables. Additional improvements include new range operators returning multiple ranges, expanded jsonpath string functions, new encoding alphabets for base64url and base32hex, and various utility and client enhancements. Several default behaviors have changed, such as enabling lz4 compression when available, doubling max_locks_per_transaction, and enabling log_lock_waits by default. Some features like REPACK CONCURRENTLY were reverted and deferred to future releases. Overall, PostgreSQL 19 focuses on expanding graph query capabilities, improving maintenance operations to reduce downtime, enhancing query planner flexibility, and refining data import/export processes. These changes reflect ongoing efforts to balance performance, usability, and operational efficiency in one of the leading open-source database systems.