PostgreSQL 13 Many New Features

The PostgreSQL Global Development Team announced the first beta version of PostgreSQL 13 on 2020–05–21, which is currently available for download. Although the details may change, this version contains a preview of all the new features in the final official version of PostgreSQL 13, let us take a quick look!

Feature

PostgreSQL 13 adds many new features to improve performance, while making application development easier.

Improved the handling of duplicate data in B-tree indexes , reducing the size of the index and increasing the search speed, especially when the index contains duplicate values.

Incremental sorting function has been added to speed up sorting operations based on already sorted intermediate result sets.

Queries that contain OR clauses or IN/ANY constant lists can take advantage of extended statistics (created through the CREATE STATISTICS statement) to obtain better execution plans and performance.

You can use disks to implement hash aggregation of large data sets (as part of aggregate queries).

The continuous improvement of the partitioning function includes the direct connection of partitioned tables in more scenarios to improve the overall query performance.

Partitioned tables support BEFORE row-level triggers , and support the overall logical replication of partitioned tables, without the need to publish and subscribe to each partition separately.

Implemented more SQL query functions, such as FETCH FIRST WITH TIES , which can return more data that meets the conditions.

Added .datetime () function to jsonpath query to automatically convert date or time string to the appropriate PostgreSQL date/time data type.

For more convenient random UUID, the built-in function gen_random_uuid() no longer needs to install additional extension plug-ins.

Manageability

The VACUUM command supports parallel processing of indexes, which is one of the most anticipated features of PostgreSQL 13. With the new option PARALLEL or vacuumdb — parallel of the VACUUM command, you can specify the concurrent worker process when cleaning up the index. Note that this option is not compatible with the FULL option.

The reindexdb command adds the — jobs option to specify the number of concurrent threads when reindexing.

Introduced the concept of “ trusted plug-in “, allowing superuser to specify that a certain plug-in can be installed by users into their own database, as long as they have CREATE permission

Supports more database monitoring methods: allows tracking of WAL usage statistics, progress of basic backup of streaming replication , and progress of ANALYZE command execution . pg_basebackup can generate a description list to verify the integrity of the backup (via a new tool pg_verifybackup ). It is allowed to limit the WAL space reserved for replication slots.

pg_dump supports a new option, — include-foreign-data , for exporting data from FDW external servers.

Enhancement of pg_rewind command. In addition to automatic failure recovery, pg_rewind can also configure PostgreSQL standby instances through the — write-recovery-conf option. In addition, pg_rewind can also use the restore_command of the target instance to obtain the required write-ahead log.

Security and Safety

PostgreSQL continues to improve security-related functions and introduces features related to PostgreSQL security deployment:

Several new security parameters have been introduced in the libpq connection library used by psql and many PostgreSQL connection drivers. Added channel_binding connection parameter to allow clients to use SCRAM’s channel binding function. In addition, the client can also use the SSL password parameter to specify a password and authenticate with password-protected TLS. PostgreSQL 13 also added DER encryption authentication.

The PostgreSQL external data wrapper ( postgres_fdw ) has enhanced secure connection capabilities, including connecting to other PostgreSQL servers using authentication-based authorization. In addition, unprivileged users can connect to other PostgreSQL databases through postgres_fdw without providing a password.

Other enhancements

PostgreSQL 13 improves the operating system on Windows systems. Window users running PostgreSQL can use UNIX domain sockets to connect.

PostgreSQL 13 document adds a glossary of terms (glossary), which can help users familiar with PostgreSQL and general database concepts. At the same time, the display of functions and operators in the table has been significantly rearranged to improve the readability of web pages and PDF documents.

The pgbench tool used for performance testing can support partitioning of the accounts table, making performance testing of partitioned tables easier.

psql supports the \warn command, which is similar to the \echo command to output data, except that the result of \warn is printed to stderr. In addition, the — help option contains a link to https://www.postgresql.org.

More features

In addition to the above, PostgreSQL 13 has added a large number of new features and improvements. For a complete list of new features, you can refer to the official release notes.

We will introduce and analyze these new features in the follow-up articles. Welcome to follow

Thanks for reading this post.