postgresql 13 что нового
PostgreSQL 13 Released!
The PostgreSQL Global Development Group today announced the release of PostgreSQL 13, the latest version of the world’s most advanced open source database.
PostgreSQL 13 includes significant improvements to its indexing and lookup system that benefit large databases, including space savings and performance gains for indexes, faster response times for queries that use aggregates or partitions, better query planning when using enhanced statistics, and more.
Along with highly requested features like parallelized vacuuming and incremental sorting, PostgreSQL 13 provides a better data management experience for workloads big and small, with optimizations for daily administration, more conveniences for application developers, and security enhancements.
«PostgreSQL 13 showcases the collaboration and dedication of our global community in furthering the abilities of the world’s most advanced open source relational database,» said Peter Eisentraut, a PostgreSQL Core Team member. «The innovations that each release brings along with its reputation for reliability and stability is the reason why more people choose to use PostgreSQL for their applications.»
PostgreSQL, an innovative data management system known for its reliability and robustness, benefits from over 25 years of open source development from a global developer community and has become the preferred open source relational database for organizations of all sizes.
Continued Performance Gains
Building on work from the previous PostgreSQL release, PostgreSQL 13 can efficiently handle duplicate data in B-tree indexes, the standard database index. This lowers the overall space usage that B-tree indexes require while improving overall query performance.
PostgreSQL 13 introduces incremental sorting, where sorted data from an earlier step in a query can accelerate sorting at a later step. Additionally, PostgreSQL can now use the extended statistics system (accessed via CREATE STATISTICS ) to create improved plans for queries with OR clauses and IN / ANY lookups over lists.
In PostgreSQL 13, more types of aggregate and grouping set queries can leverage PostgreSQL’s efficient hash aggregation functionality, as queries with large aggregates do not have to fit entirely into memory. Queries with partitioned tables have received a performance boost, as there are now more cases where partitions can be pruned and where partitions can be directly joined.
Administration Optimizations
Vacuuming is an essential part of PostgreSQL administration, enabling the database to reclaim storage space after rows are updated and deleted. This process can also present administrative challenges, though prior PostgreSQL releases have done work to ease the overhead of vacuuming.
PostgreSQL 13 continues to improve the vacuuming system with the introduction of parallelized vacuum for indexes. In addition to the vacuuming performance benefits it offers, the use of this new feature can be tuned to specific workloads as administrators can select the number of parallel workers to run. In addition to these performance benefits, data inserts can now trigger the autovacuum process.
Replication slots, which are used to prevent write-ahead logs (WAL) from being removed before they are received by a replica, can be tuned in PostgreSQL 13 to specify the max number of WAL files to retain and help avoid out-of-disk errors.
Application Development Conveniences
PostgreSQL’s partitioning system is more flexible, as partitioned tables fully support logical replication and BEFORE row-level triggers.
The FETCH FIRST syntax in PostgreSQL 13 is now expanded to include the WITH TIES clause. When specified, WITH TIES includes any rows that, based on the ORDER BY clause, «tie» with the last row in the result set.
Security Enhancements
For applications that require secure authentication methods, PostgreSQL 13 allows for clients to require channel binding when using SCRAM authentication, and the PostgreSQL foreign data wrapper ( postgres_fdw ) can now use certificate-based authentication.
About PostgreSQL
PostgreSQL is the world’s most advanced open source database, with a global community of thousands of users, contributors, companies and organizations. Built on over 30 years of engineering, starting at the University of California, Berkeley, PostgreSQL has continued with an unmatched pace of development. PostgreSQL’s mature feature set not only matches top proprietary database systems, but exceeds them in advanced database features, extensibility, security, and stability.
Release Notes
PostgreSQL 13.0
E.6. Release 13
Release date: 2020-09-24
E.6.1. Overview
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
The above items and other new features of PostgreSQL 13 are explained in more detail in the sections below.
E.6.2. Migration to Version 13
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:
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:
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)
Rename configuration parameter wal_keep_segments to wal_keep_size (Fujii Masao)
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)
E.6.3. Changes
Below you will find a detailed account of the changes between PostgreSQL 13 and the previous major release.
E.6.3.1. Server
E.6.3.1.1. Partitioning
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.
E.6.3.1.2. Indexes
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 box point queries (Nikita Glukhov)
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.
E.6.3.1.3. Optimizer
Improve the optimizer’s selectivity estimation for containment/match operators (Tom Lane)
Allow setting the statistics target for extended statistics (Tomas Vondra)
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)
E.6.3.1.4. General Performance
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.
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)
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)
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)
E.6.3.1.5. Monitoring
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)
E.6.3.1.6. System Views
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.
E.6.3.1.7. Wait Events
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)
The new events are BackupWaitWalArchive and RecoveryPause.
Add wait events RecoveryConflictSnapshot and RecoveryConflictTablespace to monitor recovery conflicts (Masahiko Sawada)
E.6.3.1.8. Authentication
Allow only superusers to view the ssl_passphrase_command setting (Insung Moon)
This was changed as a security precaution.
This choice can be controlled by ssl_min_protocol_version.
E.6.3.1.9. Server Configuration
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)
E.6.3.2. Streaming Replication and Recovery
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.
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)
Allow control over how much memory is used by logical decoding before it is spilled to disk (Tomas Vondra, Dilip Kumar, Amit Kapila)
Allow recovery to continue even if invalid pages are referenced by WAL (Fujii Masao)
E.6.3.3. Utility Commands
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)
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)
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)
E.6.3.4. Data Types
Add polymorphic data types for use by functions requiring compatible arguments (Pavel Stehule)
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.
E.6.3.5. Functions
Add alternate version of jsonb_set() with improved NULL handling (Andrew Dunstan)
Add SQL functions NORMALIZE () to normalize Unicode strings, and IS NORMALIZED to check for normalization (Peter Eisentraut)
Add min() and max() aggregates for pg_lsn (Fabrízio de Royes Mello)
These are particularly useful in monitoring queries.
Allow to_date() and to_timestamp() to recognize non-English month/day names (Juan José Santamaría Flecha, Tom Lane)
The names recognized are the same as those output by to_char() with the same format patterns.
Add datetime format patterns FF1 – FF6 to specify input or output of 1 to 6 fractional-second digits (Alexander Korotkov, Nikita Glukhov, Teodor Sigaev, Oleg Bartunov)
Add function gen_random_uuid() to generate version-4 UUIDs (Peter Eisentraut)
Add greatest-common-denominator ( gcd ) and least-common-multiple ( lcm ) functions (Vik Fearing)
Improve the performance and accuracy of the numeric type’s square root ( sqrt ) and natural log ( ln ) functions (Dean Rasheed)
Add function min_scale() that returns the number of digits to the right of the decimal point that are required to represent a numeric value with full accuracy (Pavel Stehule)
Add function trim_scale() to reduce the scale of a numeric value by removing trailing zeros (Pavel Stehule)
Add commutators of distance operators (Nikita Glukhov)
For example, previously only point line was supported, now line point works too.
Create xid8 versions of all transaction ID functions (Thomas Munro)
Allow get_bit() and set_bit() to set bits beyond the first 256MB of a bytea value (Movead Li)
Allow advisory-lock functions to be used in some parallel operations (Tom Lane)
Add the ability to remove an object’s dependency on an extension (Álvaro Herrera)
E.6.3.6. PL/pgSQL
Improve performance of simple PL/pgSQL expressions (Tom Lane, Amit Langote)
Improve performance of PL/pgSQL functions that use immutable expressions (Konstantin Knizhnik)
E.6.3.7. Client Interfaces
Allow libpq clients to require channel binding for encrypted connections (Jeff Davis)
Add libpq connection parameters to control the minimum and maximum TLS version allowed for an encrypted connection (Daniel Gustafsson)
Allow use of passwords to unlock client certificates (Craig Ringer, Andrew Dunstan)
This is enabled by libpq’s sslpassword connection parameter.
Fix ecpg ‘s EXEC SQL elif directive to work correctly (Tom Lane)
E.6.3.8. Client Applications
E.6.3.8.1. psql
Add transaction status ( %x ) to psql ‘s default prompts (Vik Fearing)
Allow the secondary psql prompt to be blank but the same width as the primary prompt (Thomas Munro)
Allow psql ‘s \g and \gx commands to change \pset output options for the duration of that single command (Tom Lane)
Add psql commands to display operator classes and operator families (Sergey Cherkashin, Nikita Glukhov, Alexander Korotkov)
Show table persistence in psql ‘s \dt+ and related commands (David Fetter)
In verbose mode, the table/index/view shows if the object is permanent, temporary, or unlogged.
Improve output of psql ‘s \d for TOAST tables (Justin Pryzby)
Fix redisplay after psql ‘s \e command (Tom Lane)
Add \warn command to psql (David Fetter)
This is like \echo except that the text is sent to stderr instead of stdout.
E.6.3.8.2. pgbench
Allow pgbench to partition its “ accounts ” table (Fabien Coelho)
This allows performance testing of partitioning.
Allow pgbench to generate its initial data server-side, rather than client-side (Fabien Coelho)
E.6.3.9. Server Applications
Generate backup manifests for base backups, and verify them (Robert Haas)
A new tool pg_verifybackup can verify backups.
Have pg_basebackup estimate the total backup size by default (Fujii Masao)
Add an option to pg_rewind to configure standbys (Paul Guo, Jimmy Yih, Ashwin Agrawal)
Allow pg_rewind to use the target cluster’s restore_command to retrieve needed WAL (Alexey Kondratov)
Have pg_rewind automatically run crash recovery before rewinding (Paul Guo, Jimmy Yih, Ashwin Agrawal)
Allow vacuum commands run by vacuumdb to operate in parallel mode (Masahiko Sawada)
Allow reindexdb to operate in parallel (Julien Rouhaud)
Allow dropdb to disconnect sessions using the target database, allowing the drop to succeed (Pavel Stehule)
E.6.3.10. Documentation
Add a glossary to the documentation (Corey Huinker, Jürgen Purtz, Roger Harkavy, Álvaro Herrera)
Reformat tables containing function and operator information for better clarity (Tom Lane)
Upgrade to use DocBook 4.5 (Peter Eisentraut)
E.6.3.11. Source Code
Add support for building on Visual Studio 2019 (Haribabu Kommi)
Add build support for MSYS2 (Peter Eisentraut)
Add compare_exchange and fetch_add assembly language code for Power PC compilers (Noah Misch)
Update Snowball stemmer dictionaries used by full text search (Panagiotis Mavrogiorgos)
This adds Greek stemming and improves Danish and French stemming.
Remove support for Windows 2000 (Michael Paquier)
Remove support for Python versions 2.5.X and earlier (Peter Eisentraut)
Remove support for OpenSSL 0.9.8 and 1.0.0 (Michael Paquier)
These were needed for compatibility with some version-zero C functions, but those are no longer supported.
Pass the query string to planner hook functions (Pascal Legrand, Julien Rouhaud)
Add TRUNCATE command hook (Yuli Khodorkovskiy)
Allow building with no predefined Unix-domain socket directory (Peter Eisentraut)
Reduce the probability of SysV resource key collision on Unix platforms (Tom Lane)
Use operating system functions to reliably erase memory that contains sensitive information (Peter Eisentraut)
For example, this is used for clearing passwords stored in memory.
Add headerscheck script to test C header-file compatibility (Tom Lane)
Implement internal lists as arrays, rather than a chain of cells (Tom Lane)
This improves performance for queries that access many objects.
Change the API for TS_execute() (Tom Lane, Pavel Borisov)
TS_execute callbacks must now provide ternary (yes/no/maybe) logic. Calculating NOT queries accurately is now the default.
E.6.3.12. Additional Modules
Allow extensions to be specified as trusted (Tom Lane)
Such extensions can be installed in a database by users with database-level CREATE privileges, even if they are not superusers. This change also removes the pg_pltemplate system catalog.
Allow non-superusers to connect to postgres_fdw foreign servers without using a password (Craig Ringer)
Specifically, allow a superuser to set password_required to false for a user mapping. Care must still be taken to prevent non-superusers from using superuser credentials to connect to the foreign server.
Allow postgres_fdw to use certificate authentication (Craig Ringer)
Different users can use different certificates.
Allow sepgsql to control access to the TRUNCATE command (Yuli Khodorkovskiy)
Allow pg_stat_statements to optionally track the planning time of statements (Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao)
Previously only execution time was tracked.
Overhaul ltree’s lquery syntax to treat NOT (!) more logically (Filip Rembialkowski, Tom Lane, Nikita Glukhov)
Also allow non-* queries to use a numeric range (<>) of matches.
Add support for binary I/O of ltree, lquery, and ltxtquery types (Nino Floris)
Add an option to dict_int to ignore the sign of integers (Jeff Janes)
Add adminpack function pg_file_sync() to allow fsync’ing a file (Fujii Masao)
Add pageinspect functions to output t_infomask / t_infomask2 values in human-readable format (Craig Ringer, Sawada Masahiko, Michael Paquier)
Add B-tree index de-duplication processing columns to pageinspect output (Peter Geoghegan)
E.6.4. Acknowledgments
The following individuals (in alphabetical order) have contributed to this release as patch authors, committers, reviewers, testers, or reporters of issues.