Release of the PostgreSQL 19 DBMS

After a year of development, a new stable branch of the PostgreSQL database management system, version 19, has been released. Updates for this new branch will be provided for five years until November 2031. Support for PostgreSQL 14.x, the oldest of the supported branches, will end this month. The release was published nearly two months later than originally scheduled due to an unusual flow of bug reports identified through code analysis using AI tools.

Key innovations:

  • The implementation of the asynchronous input/output subsystem has been improved, allowing for increased I/O throughput and the elimination of delays. In the universal AIO implementation (io_method=worker), there is now the capability for automatic scaling of the number of active I/O handler processes based on load. Parameters such as io_min_workers, io_max_workers, io_worker_idle_timeout, and io_worker_launch_interval have been proposed to determine the minimum and maximum number of handler processes, as well as the inactivity timeout before termination and the delay before starting processes.

    Additionally, the prefetching scheduler used in AIO has been optimized to enhance the performance of queries requiring large amounts of data to be loaded from disk. This optimization is achieved by adaptively determining the allowable number of prefetch operations based on the nature and size of the request (for example, for scanning large tables and indexes, more data is now preloaded from the disk). An option "IO" has been added to the "EXPLAIN ANALYZE" command for analyzing the execution of I/O operations in asynchronous mode.

  • The pg_plan_advice and pg_stash_advice extensions have been added to provide the query planner with hint parameters in the mini-language 'plan advice', allowing influence over the selection of execution plans, fixing selected plans, and experimenting with different plans. To obtain the list of parameters chosen by the planner during query execution, the EXPLAIN command has been enhanced with the 'PLAN_ADVICE' option, and to set your parameters before query execution, you can use the expression 'SET pg_plan_advice.advice = ‘a string with a list of parameters' '. The pg_stash_advice extension complements pg_plan_advice with the ability to bind parameters to query identifiers, so they do not need to be added before each query. EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id; … Generated Plan Advice: JOIN_ORDER(f d) HASH_JOIN(d) SEQ_SCAN(f d) NO_GATHER(f d) SET pg_plan_advice.advice = ‘JOIN_ORDER(f d) NO_GATHER(f d)’;
  • The automatic vacuuming process (autovacuum) now supports the parallel use of multiple workers during index cleanup. The number of workers can be configured through the autovacuum_max_parallel_workers option. Additionally, new options autovacuum_vacuum_score_weight, autovacuum_vacuum_insert_score_weight, and autovacuum_analyze_score_weight have been added to set the priority of table cleanup.
  • A new cleanup strategy has been implemented, allowing for a reduction in future workload by updating the visibility map of pages while scanning tables during SELECT queries, without having to wait for the VACUUM command to initiate.
  • A new SQL command 'REPACK' has been introduced for restructuring tables with lower overhead costs. The REPACK command completely rewrites all content of the table into a new file on disk, allowing the operating system to reclaim disk space occupied by unused blocks. Unlike the 'VACUUM FULL' command, which imposes a hard lock during rewriting, the REPACK command offers a 'CONCURRENTLY' option, allowing the table to be used by other transactions during repacking. Compared to the pg_repack extension, REPACK is noticeably faster as it does not add additional triggers to the table, but it does not support the handling of fully partitioned tables.
  • The performance of INSERT operations has been increased up to two times for tables where records are checked against foreign keys (Foreign Key). This acceleration was achieved by utilizing an index for checking the foreign key, allowing for the avoidance of a resource-intensive internal query to the table.
  • The query planner now recognizes Anti-Join operations (defining rows from the first table for which there are no matches in the second table) in complex queries with 'LEFT JOIN' and queries with 'NOT IN' expressions to engage specific optimizations. The application of incremental sorting has been expanded. An 'eager aggregation' optimization has been added, moving aggregation operations to a stage before table merging to reduce the number of rows processed during the merge. The parallel execution of sequential scan operations (parallel seq scan) has been accelerated. The constructs 'IS DISTINCT FROM' and 'IS NOT DISTINCT FROM' are now reduced to expressions with the operators '' and '=', if both operands cannot take on a NULL value.
  • The efficiency of delivering notifications using the NOTIFY command in multi-channel notification configurations has been improved. A map of channels is now kept in shared memory, allowing for direct addressing only to backends subscribed to the specified channel.
  • Support for the expression ON CONFLICT DO SELECT … RETURNING has been added to the INSERT command, returning records in the event of their inability to be added via INSERT, in addition to the expressions ON CONFLICT DO NOTHING/UPDATE, which silently ignore conflicts on insertion or perform an update instead of an insertion.
  • Support for outputting data in JSON format has been added to the COPY command.
  • Methods for processing strings such as lower(), upper(), replace(), split_part(), and btrim() have been added to the SQL/JSON language.
  • Support for the expression 'IGNORE NULLS' has been added to window functions lead(), lag(), first_value(), last_value(), and nth_value() to skip NULL values.
  • The 'WAIT FOR LSN' command has been added, which, during asynchronous replication, allows for suspending a SELECT query on the replicated server until changes from the primary server are applied to prevent stale data output. server, corresponding to a specific LSN position (Log Sequence Number) in the WAL log.
  • The random() function now supports date and timestamp types for generating random dates and times within a specified range.
  • It is now possible to create functions for event triggers in PL/Python.
  • Support for TLS extension SNI (Server Name Indication) has been added to handle TLS certificates associated with hostnames that clients request when establishing an encrypted connection over a single IP address. server The password_expiration_warning_threshold parameter has been added to the postgresql.conf configuration file, allowing users to set up warnings for upcoming password expiration.
  • Warnings are now enabled by default when using the MD5 algorithm for authentication, which has been deprecated. A parameter, md5_password_warnings, has been added to disable these warnings.
  • Views have been added: ‘pg_stat_lock’ for viewing accumulated statistics on locks related to lock types; ‘pg_stat_recovery’ for obtaining information about the status of recovery operations; ‘pg_stat_autovacuum_scores’ for determining the processing priority of tables in autovacuum. A column ‘stats_reset’ has been added to many system views to indicate the last time counters were cleared. In the views ‘pg_stat_progress_vacuum’ and ‘pg_stat_progress_analyze’, a column ‘started_by’ has been added to show the initiator of the operation. In the pg_stat_progress_vacuum view, a column ‘mode’ has been added that reflects the cleanup mode used.
  • The ability to specify minimum log message verbosity levels (log_min_messages) related to process types has been added.
  • The statistics displayed after VACUUM and ANALYZE operations, as well as in the ‘pg_stat_wal’ view, now include details about the size of full pages written to the WAL log, which simplifies the analysis of reasons for excessive WAL growth (it can help understand whether the growth is related to the use of the full_page_writes parameter).
  • Support for replicating sequence values (SEQUENCE) in the logical replication mechanism has been implemented, which can be published using the ‘CREATE PUBLICATION’ command with the expression ‘FOR ALL SEQUENCES’.
  • The syntax ‘CREATE PUBLICATION … EXCEPT’ has been added to replicate all tables except those explicitly specified.
  • Added the syntax 'CREATE PUBLICATION … EXCEPT' to replicate all tables except those explicitly nominated.
  • The expression "CREATE SUBSCRIPTION … SERVER" has been added, allowing the creation of logical replication subscriptions by obtaining connection settings from an external server.
  • The ability to enable logical replication on the fly without restarting the server has been added, even if the "wal_level" parameter is set to "replica" instead of "logical". To determine the actual logging level not tied to the wal_level setting in the configuration file, the parameter "effective_wal_level" has been added.
  • In the Foreign Data Wrapper (postgres_fdw) mechanism for connecting external tables, capabilities for passing array operations to a remote server have been implemented, and the option "import_stats" has been added to import statistics for tables from the external server when executing the ANALYZE command to enhance the efficiency of local query execution planning.
  • JIT compilation is disabled by default, as it mainly provides a significant performance gain for heavy analytical queries, while in regular OLTP workloads it may lead to performance drops due to errors in predicting the resources needed for query execution.
  • To improve the performance of compression and decompression of TOAST tables, the parameter "default_toast_compression" has been set to "lz4".
  • Support for authentication using the RADIUS protocol has been removed.
  • In the command "vacuumdb —analyze-only", the analysis of partitioned tables is enabled by default.

Due to issues identified during the final testing phase, certain features presented in the beta version are not included in this release, such as the syntax "GROUP BY ALL", support for non-text output formats in pg_dumpall, SQL/PGQ for graph queries, enabling checksum support on the fly without restart and reinitialization, the expression "FOR PORTION OF" in UPDATE and DELETE, the expressions "ALTER TABLE … MERGE PARTITIONS" and "ALTER TABLE … SPLIT PARTITIONS" for reorganizing partitioned tables, setting LC_COLLATE to "C" for the postmaster process, and the new functions pg_get_role_ddl(), pg_get_tablespace_ddl(), and pg_get_database_ddl(). This news is sponsored by Hi-Square, the developer of the Low Code platform XSQUARE on PostgreSQL (advertising, LLC Hi-Square, TIN 7718990614).

Source: opennet.ru

Buy reliable website hosting with DDoS protection, VPS VDS servers 🔥 Buy reliable website hosting with DDoS protection, VPS VDS servers | ProHoster