PostgreSQL 19 Officially Released: Adds Concurrent REPACK, Supports Query Plan Stability Control
IT之家 November 21 news: On November 19 local time, the open-source relational database PostgreSQL 19 was officially released. This latest major version brings updates in areas including online table maintenance, query plan stability, logical replication, asynchronous I/O, and SQL features. The official release also provides installers, tools, and source code for Windows, Linux, macOS, and other platforms.

PostgreSQL 19 adds the REPACK command, which can reclaim dead space in data tables and reorganize table data based on indexes. Among these, REPACK (CONCURRENTLY) supports completing reorganization without blocking table read and write operations, reducing the impact on business operations during the execution of the traditional VACUUM FULL and CLUSTER commands. The above old commands are still retained for compatibility with existing applications.
For query performance management, the new version introduces the pg_plan_advice extension, allowing users to control and stabilize the query planner's execution plans, and also to try different execution plans through dedicated syntax. The accompanying pg_stash_advice extension can automatically save and apply relevant advice based on query identifiers, addressing some slow query issues without modifying applications.
In terms of database maintenance, PostgreSQL 19 supports cleaning dead space in indexes through parallel worker processes, and adds the autovacuum_max_parallel_workers parameter to control the degree of parallelism. At the same time, the autovacuum mechanism introduces a new scoring system to determine which data tables to prioritize. The new version also adds monitoring views such as pg_stat_lock, pg_stat_recovery, and pg_stat_autovacuum_scores, making it easier for administrators to keep track of locks, recovery status, and the operation of autovacuum tasks.
In terms of distributed data processing, the new version improves the logical replication initialization process: when a user creates the first logical replication slot, if the previous WAL level was replica, the system can automatically raise the effective WAL level to logical without restarting the server. In addition, PostgreSQL 19 supports logical replication of sequence values and allows data tables that do not need to be published to be excluded through the EXCEPT clause of CREATE PUBLICATION.
To address data consistency issues in primary-standby architectures, the new version adds the WAIT FOR LSN command. Applications can wait on a standby server for a specified write-ahead log (WAL) log sequence number to finish replaying before performing read operations, thereby achieving so-called Read-your-writes consistency and ensuring that data just written on the primary server can be read.
In terms of developer features, PostgreSQL 19 adds INSERT ... ON CONFLICT DO SELECT, which can return existing data through RETURNING when an insert conflict occurs, simplifying the common Get or Create operation. In addition, the COPY command now supports outputting data in JSON format, including output as a single JSON array; the SQL / JSON path language also adds string processing methods such as lower(), upper(), replace(), split_part(), and btrim().
In terms of window functions, lead(), lag(), first_value(), last_value(), and nth_value() now support IGNORE NULLS, allowing null values to be skipped in calculations. The random() function adds the ability to generate random dates and timestamps within a specified range.
In terms of performance optimization, PostgreSQL 19 improves the foreign key check mechanism, and in official tests bulk insert performance nearly doubled. The query planner also adds several optimizations, including converting some NOT IN queries into more efficient anti-joins when no NULL values exist, and performing aggregation calculations earlier before join operations.
The new version continues to refine the asynchronous I/O subsystem introduced in PostgreSQL 18. When io_method is set to the default worker, the system can automatically adjust the number of I/O worker processes within the range specified by the io_min_workers and io_max_workers parameters, replacing the previous io_workers parameter.
Other updates include server-side support for TLS Server Name Indication (SNI), allowing the same PostgreSQL server to provide different TLS certificates based on the hostname the client connects with; a new password expiration warning parameter; a warning issued after successful MD5 password authentication, and removal of RADIUS authentication support. In addition, JIT just-in-time compilation is disabled by default, and when LZ4 support is enabled at compile time, the default TOAST compression method is changed to LZ4.
IT之家 note: To upgrade from a previous major version to PostgreSQL 19, users need to complete the migration using methods such as pg_upgrade or pg_dumpall, and cannot install it directly as an ordinary minor version update.
Related Articles
- First among domestic operators: China Mobile's self-developed "Dayun Haishan Database" passes security and reliability evaluation, compatible with domestic CPU chips / operating systems
- Stack Overflow releases 2023 annual report: JavaScript tops the list, 83% of people have used ChatGPT
- Database popularity ranking for May released: PostgreSQL grows slowly, Redis drops one place