Release date: 2020-09-24
PostgreSQL 13 contains many new features and enhancements, including:
Space savings and performance gains from de-duplication of B-tree index entries
Improved performance for queries that use aggregates or partitioned tables
Better query planning when using extended statistics
Parallelized vacuuming of indexes
Incremental sorting
The above items and other new features of PostgreSQL 13 are explained in more detail in the sections below.
A dump/restore using pg_dumpall or use of pg_upgrade or logical replication is required for those wishing to migrate data from any previous release. See Section 18.6 for general information on migrating to new major releases.
Version 13 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:
Change SIMILAR TO ... ESCAPE NULL to return NULL (Tom Lane) §
This new behavior matches the SQL specification. Previously a null ESCAPE value was taken to mean using the default escape string (a backslash character). This also applies to substring(. The previous behavior has been retained in old views by keeping the original function unchanged.text FROM pattern ESCAPE text)
Make json[b]_to_tsvector() fully check the spelling of its string option (Dominik Czarnota) §
Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro) §
Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:
SELECT round(sum(OLDVALUE/ n::float)) AS newvalue FROM generate_series(1,OLDVALUE) s(n);
Prevent display of auxiliary processes in pg_stat_ssl and pg_stat_gssapi system views (Euler Taveira) §
Queries that join these views to pg_stat_activity and wish to see auxiliary processes will need to use left joins.
Rename various wait events to improve consistency (Fujii Masao, Tom Lane) § § § § §
Fix ALTER FOREIGN TABLE ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao) §
Previously it returned ALTER TABLE; now it returns ALTER FOREIGN TABLE.
Fix ALTER MATERIALIZED VIEW ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao) §
Previously it returned ALTER TABLE; now it returns ALTER MATERIALIZED VIEW.
Rename configuration parameter wal_keep_segments to wal_keep_size (Fujii Masao) §
This determines how much WAL to retain for standby servers. It is specified in megabytes, rather than number of files as with the old parameter. If you previously used wal_keep_segments, the following formula will give you an approximately equivalent setting:
wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB)
Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax (Daniel Gustafsson) §
Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax (Daniel Gustafsson) §
Remove support for "opaque" pseudo-types used by pre-PostgreSQL 7.3 servers (Daniel Gustafsson) §
Remove support for upgrading unpackaged (pre-9.1) extensions (Tom Lane) §
The FROM option of CREATE EXTENSION is no longer supported. Any installations still using unpackaged extensions should upgrade them to a packaged version before updating to PostgreSQL 13.
Remove support for posixrules files in the timezone database (Tom Lane) §
IANA's timezone group has deprecated this feature, meaning that it will gradually disappear from systems' timezone databases over the next few years. Rather than have a behavioral change appear unexpectedly with a timezone data update, we have removed PostgreSQL's support for this feature as of version 13. This affects only the behavior of POSIX-style time zone specifications that lack an explicit daylight savings transition rule; formerly the transition rule could be determined by installing a custom posixrules file, but now it is hard-wired. The recommended fix for any affected installations is to start using a geographical time zone name.
In ltree, when an lquery pattern contains adjacent asterisks with braces, e.g., *{2}.*{3}, properly interpret that as *{5} (Nikita Glukhov) §
Fix pageinspect's bt_metap() to return more appropriate data types that are less likely to overflow (Peter Geoghegan) §
Below you will find a detailed account of the changes between PostgreSQL 13 and the previous major release.
Allow pruning of partitions to happen in more cases (Yuzuko Hosoya, Amit Langote, Álvaro Herrera) § §
Allow partitionwise joins to happen in more cases (Ashutosh Bapat, Etsuro Fujita, Amit Langote, Tom Lane) § §
For example, partitionwise joins can now happen between partitioned tables even when their partition bounds do not match exactly.
Support row-level BEFORE triggers on partitioned tables (Álvaro Herrera) §
However, such a trigger is not allowed to change which partition is the destination.
Allow partitioned tables to be logically replicated via publications (Amit Langote) § §
Previously, partitions had to be replicated individually. Now a partitioned table can be published explicitly, causing all its partitions to be published automatically. Addition/removal of a partition causes it to be likewise added to or removed from the publication. The CREATE PUBLICATION option publish_via_partition_root controls whether changes to partitions are published as their own changes or their parent's.
Allow logical replication into partitioned tables on subscribers (Amit Langote) §
Previously, subscribers could only receive rows into non-partitioned tables.
Allow whole-row variables (that is, table.*) to be used in partitioning expressions (Amit Langote) §
More efficiently store duplicates in B-tree indexes (Anastasia Lubennikova, Peter Geoghegan) §
This allows efficient B-tree indexing of low-cardinality columns by storing duplicate keys only once. Users upgrading with pg_upgrade will need to use REINDEX to make an existing index use this feature.
Allow GiST and SP-GiST indexes on box columns to support ORDER BY queries (Nikita Glukhov) § §box <-> point
Allow GIN indexes to more efficiently handle ! (NOT) clauses in tsquery searches (Nikita Glukhov, Alexander Korotkov, Tom Lane, Julien Rouhaud) §
Allow index operator classes to take parameters (Nikita Glukhov) §
Allow CREATE INDEX to specify the GiST signature length and maximum number of integer ranges (Nikita Glukhov) §
Indexes created on four and eight-byte integer array, tsvector, pg_trgm, ltree, and hstore columns can now control these GiST index parameters, rather than using the defaults.
Prevent indexes that use non-default collations from being added as a table's unique or primary key constraint (Tom Lane) §
The index's collation must match that of the underlying column, but ALTER TABLE previously failed to check this.
Improve the optimizer's selectivity estimation for containment/match operators (Tom Lane) §
Allow setting the statistics target for extended statistics (Tomas Vondra) §
This is controlled with the new command option ALTER STATISTICS ... SET STATISTICS. Previously this was computed based on more general statistics target settings.
Allow use of multiple extended statistics objects in a single query (Tomas Vondra) § §
Allow use of extended statistics objects for OR clauses and IN/ANY constant lists (Pierre Ducroquet, Tomas Vondra) § § §
Allow functions in FROM clauses to be pulled up (inlined) if they evaluate to constants (Alexander Kuzmenkov, Aleksandr Parfenov) § §
Implement incremental sorting (James Coleman, Alexander Korotkov, Tomas Vondra) § §
If an intermediate query result is known to be sorted by one or more leading keys of a required sort ordering, the additional sorting can be done considering only the remaining keys, if the rows are sorted in batches that have equal leading keys.
If necessary, this can be controlled using enable_incremental_sort.
Improve the performance of sorting inet values (Brandur Leach) §
Allow hash aggregation to use disk storage for large aggregation result sets (Jeff Davis) § § §
Previously, hash aggregation was avoided if it was expected to use more than work_mem memory. Now, a hash aggregation plan can be chosen despite that. The hash table will be spilled to disk if it exceeds work_mem times hash_mem_multiplier.
This behavior is normally preferable to the old behavior, in which once hash aggregation had been chosen, the hash table would be kept in memory no matter how large it got — which could be very large if the planner had misestimated. If necessary, behavior similar to that can be obtained by increasing hash_mem_multiplier.
Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum (Laurenz Albe, Darafei Praliaskouski) §
Previously, insert-only activity would trigger auto-analyze but not auto-vacuum, on the grounds that there could not be any dead tuples to remove. However, a vacuum scan has other useful side-effects such as setting page-all-visible bits, which improves the efficiency of index-only scans. Also, allowing an insert-only table to receive periodic vacuuming helps to spread out the work of “freezing” old tuples, so that there is not suddenly a large amount of freezing work to do when the entire table reaches the anti-wraparound threshold all at once.
If necessary, this behavior can be adjusted with the new parameters autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor, or the equivalent table storage options.
Add maintenance_io_concurrency parameter to control I/O concurrency for maintenance operations (Thomas Munro) §
Allow WAL writes to be skipped during a transaction that creates or rewrites a relation, if wal_level is minimal (Kyotaro Horiguchi) §
Relations larger than wal_skip_threshold will have their files fsync'ed rather than generating WAL. Previously this was done only for COPY operations, but the implementation had a bug that could cause data loss during crash recovery.
Improve performance when replaying DROP DATABASE commands when many tablespaces are in use (Fujii Masao) §
Improve performance for truncation of very large relations (Kirk Jamison) §
Improve retrieval of the leading bytes of TOAST'ed values (Binguo Bao, Andrey Borodin) §
Previously, compressed out-of-line TOAST values were fully fetched even when it's known that only some leading bytes are needed. Now, only enough data to produce the result is fetched.
Improve performance of LISTEN/NOTIFY (Martijn van Oosterhout, Tom Lane) § § §
Speed up conversions of integers to text (David Fetter) §
Reduce memory usage for query strings and extension scripts that contain many SQL statements (Amit Langote) §
Allow EXPLAIN, auto_explain, autovacuum, and pg_stat_statements to track WAL usage statistics (Kirill Bychik, Julien Rouhaud) § § §
Allow a sample of SQL statements, rather than all statements, to be logged (Adrien Nayrat) §
A log_statement_sample_rate fraction of those statements taking more than log_min_duration_sample duration will be logged.
Add the backend type to csvlog and optionally log_line_prefix log output (Peter Eisentraut) §
Improve control of prepared statement parameter logging (Alexey Bashtanov, Álvaro Herrera) § §
The GUC setting log_parameter_max_length controls the maximum length of parameter values output during logging of non-error statements, while log_parameter_max_length_on_error does the same for logging of statements with errors. Previously, prepared statement parameters were never logged during errors.
Allow function call backtraces to be logged after errors (Peter Eisentraut, Álvaro Herrera) § §
The new parameter backtrace_functions specifies which C functions should generate backtraces on error.
Make vacuum buffer counters 64-bits wide to avoid overflow (Álvaro Herrera) §
Add leader_pid to pg_stat_activity to report a parallel worker's leader process (Julien Rouhaud) §
Add system view pg_stat_progress_basebackup to report the progress of streaming base backups (Fujii Masao) §
Add system view pg_stat_progress_analyze to report ANALYZE progress (Álvaro Herrera, Tatsuro Yamada, Vinayak Pokale) §
Add system view pg_shmem_allocations to display shared memory usage (Andres Freund, Robert Haas) §
Add system view pg_stat_slru to monitor internal SLRU caches (Tomas Vondra) §
Allow track_activity_query_size to be set as high as 1MB (Vyacheslav Makarov) §
The previous maximum was 100kB.
Report a wait event while creating a DSM segment with posix_fallocate() (Thomas Munro) §
Add wait event VacuumDelay to report on cost-based vacuum delay (Justin Pryzby) §
Add wait events for WAL archive and recovery pause (Fujii Masao) §
The new events are BackupWaitWalArchive and RecoveryPause.
Add wait events RecoveryConflictSnapshot and RecoveryConflictTablespace to monitor recovery conflicts (Masahiko Sawada) §
Improve performance of wait events on BSD-based systems (Thomas Munro) §
Allow only superusers to view the ssl_passphrase_command setting (Insung Moon) §
This was changed as a security precaution.
Change the server's default minimum TLS version for encrypted connections from 1.0 to 1.2 (Peter Eisentraut) §
This choice can be controlled by ssl_min_protocol_version.
Tighten rules on which utility commands are allowed in read-only transaction mode (Robert Haas) §
This change also increases the number of utility commands that can run in parallel queries.
Allow allow_system_table_mods to be changed after server start (Peter Eisentraut) §
Disallow non-superusers from modifying system tables when allow_system_table_mods is set (Peter Eisentraut) §
Previously, if allow_system_table_mods was set at server start, non-superusers could issue INSERT/UPDATE/DELETE commands on system tables.
Enable support for Unix-domain sockets on Windows (Peter Eisentraut) §
Allow streaming replication configuration settings to be changed by reload (Sergei Kornilov) §
Previously, a server restart was required to change primary_conninfo and primary_slot_name.
Allow WAL receivers to use a temporary replication slot when a permanent one is not specified (Peter Eisentraut, Sergei Kornilov) § §
This behavior can be enabled using wal_receiver_create_temp_slot.
Allow WAL storage for replication slots to be limited by max_slot_wal_keep_size (Kyotaro Horiguchi) §
Replication slots that would require exceeding this value are marked invalid.
Allow standby promotion to cancel any requested pause (Fujii Masao) §
Previously, promotion could not happen while the standby was in paused state.
Generate an error if recovery does not reach the specified recovery target (Leif Gunnar Erlandsen, Peter Eisentraut) §
Previously, a standby would promote itself upon reaching the end of WAL, even if the target was not reached.
Allow control over how much memory is used by logical decoding before it is spilled to disk (Tomas Vondra, Dilip Kumar, Amit Kapila) §
This is controlled by logical_decoding_work_mem.
Allow recovery to continue even if invalid pages are referenced by WAL (Fujii Masao) §
This is enabled using ignore_invalid_pages.
Allow VACUUM to process a table's indexes in parallel (Masahiko Sawada, Amit Kapila) §
The new PARALLEL option controls this.
Allow FETCH FIRST to use WITH TIES to return any additional rows that match the last result row (Surafel Temesgen) §
Report planning-time buffer usage in EXPLAIN's BUFFER output (Julien Rouhaud) §
Make CREATE TABLE LIKE propagate a CHECK constraint's NO INHERIT property to the created table (Ildar Musin, Chris Travers) §
When using LOCK TABLE on a partitioned table, do not check permissions on the child tables (Amit Langote) §
Allow OVERRIDING USER VALUE on inserts into identity columns (Dean Rasheed) §
Add ALTER TABLE ... DROP EXPRESSION to allow removing the GENERATED property from a column (Peter Eisentraut) §
Fix bugs in multi-step ALTER TABLE commands (Tom Lane) §
IF NOT EXISTS clauses now work as expected, in that derived actions (such as index creation) do not execute if the column already exists. Also, certain cases of combining related actions into one ALTER TABLE now work when they did not before.
Add ALTER VIEW syntax to rename view columns (Fujii Masao) §
Renaming view columns was already possible, but one had to write ALTER TABLE RENAME COLUMN, which is confusing.
Add ALTER TYPE options to modify a base type's TOAST properties and support functions (Tomas Vondra, Tom Lane) §
Add CREATE DATABASE LOCALE option (Peter Eisentraut) §
This combines the existing options LC_COLLATE and LC_CTYPE into a single option.
Allow DROP DATABASE to disconnect sessions using the target database, allowing the drop to succeed (Pavel Stehule, Amit Kapila) §
This is enabled by the FORCE option.
Add structure member tg_updatedcols to allow C-language update triggers to know which column(s) were updated (Peter Eisentraut) §
Add polymorphic data types for use by functions requiring compatible arguments (Pavel Stehule) §
The new data types are anycompatible, anycompatiblearray, anycompatiblenonarray, and anycompatiblerange.
Add SQL data type xid8 to expose FullTransactionId (Thomas Munro) §
The existing xid data type is only four bytes so it does not provide the transaction epoch.
Add data type regcollation and associated functions, to represent OIDs of collation objects (Julien Rouhaud) §
Use the glibc version in some cases as a collation version identifier (Thomas Munro) §
If the glibc version changes, a warning will be issued about possible corruption of collation-dependent indexes.
Add support for collation versions on Windows (Thomas Munro) §
Allow ROW expressions to have their members extracted with suffix notation (Tom Lane) §
For example, (ROW(4, 5.0)).f1 now returns 4.
Add alternate version of jsonb_set() with improved NULL handling (Andrew Dunstan) §
The new function, jsonb_set_lax(), handles a NULL new value by