Fri. May 29th, 2020

PostgreSQL 13 beta 1 released: Overall Performance Improvements

4 min read

PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. It can handle workloads ranging from small single-machine applications to large Internet-facing applications (or for data warehousing) with many concurrent users; on macOS Server, PostgreSQL is the default database; and it is also available for Microsoft Windows and Linux (supplied in most distributions).

PostgreSQL is ACID-compliant and transactional. PostgreSQL has updatable views and materialized views, triggers, foreign keys; supports functions and stored procedures, and other expandability.

PostgreSQL is developed by the PostgreSQL Global Development Group, a diverse group of many companies and individual contributors. It is free and open-source, released under the terms of the PostgreSQL License, a permissive software license. _Wiki

PostgreSQL

Changelog Postgresql 13 beta 1

Functionality

There are many new features in PostgreSQL 13 that help improve the overall performance of PostgreSQL while making it even easier to develop applications.

B-tree indexes, the standard index of PostgreSQL, received improvements for how they handle duplicate data. These enhancements help to shrink index size and improve lookup speed, particularly for indexes that contain repeated values.

PostgreSQL 13 adds incremental sorting, which accelerates sorting data when data that is sorted from earlier parts of a query are already sorted. Additionally, queries with OR clauses or IN/ANY constant lists can use extended statistics (created via CREATE STATISTICS), which can lead to better planning and performance gains. PostgreSQL 13 can now use disk storage for hash aggregation (used as part of aggregate queries) with large aggregation sets.

There are more improvements added to PostgreSQL’s partitioning functionality in this release, including an increased number of cases where a join directly between partitioned tables can occur, which can improve overall query execution time. Partitioned tables now support BEFORE row-level triggers, and a partitioned table can now be fully replicated via logical replication without having to publish individual partitions.

PostgreSQL 13 brings more convenience to writing queries with features like FETCH FIRST WITH TIES, which returns any additional rows that match the last row. There is also the addition of the .datetime() function for jsonpath queries, which will automatically convert a date-like or time-like string to the appropriate PostgreSQL date/time datatype. It is also even easier now to generate random UUIDs, as the gen_random_uuid() function can be used without having to enable any extensions.

Administration

One of the most anticipated features of PostgreSQL 13 is the ability for the VACUUM command to process indexes in parallel. This functionality can be accessed using the new PARALLEL option on the VACUUM command (or --parallel on vacuumdb), which allows you to specify the number of parallel workers to use for vacuuming indexes. Note that this does not work with the FULL option.

The reindexdb command has also added parallelism with the new --jobs flag, which lets you specify the number of concurrent sessions to use when reindexing a database.

PostgreSQL 13 introduces the concept of a “trusted extension”, which allows for a superuser to specify extensions that a user can install in their database so long as they have a CREATE privilege.

This release includes more ways to monitor activity within a PostgreSQL database: PostgreSQL 13 can now track WAL usage statistics and the progress of streaming base backups, and the progress of an ANALYZE command. pg_basebackup can also generate a manifest that can be used to verify the integrity of a backup using a new tool called pg_verifybackup. It is also now possible to limit the amount of WAL space reserved by replication slots.

A new flag for pg_dump--include-foreign-data, includes data from servers referenced by foreign data wrappers in the dump output.

The pg_rewind command also has improvements in PostgreSQL 13. In addition to pg_rewind automatically performing crash recovery, you can now use it to configure standby PostgreSQL instances using the --write-recovery-conf flag. pg_rewind can also use the restore_command of the target instance to fetch needed write-ahead logs.

Security

PostgreSQL continues to improve on its security capabilities in this latest release, introducing several features to help further deploy PostgreSQL safely.

libpq, the connection library that powers psql and many PostgreSQL connection drivers, includes several new parameters to help secure connections. PostgreSQL 13 introduces the channel_binding connection parameters, which lets a client specify that they want to require the channel binding functionality as part of SCRAM. Additionally, a client that is using a password protected TLS certificate can now specify its password using the sslpassword parameter. PostgreSQL 13 also adds support for DER encoded certificates.

The PostgreSQL foreign data wrapper (postgres_fdw) also received several enhancements to how it can secure connections, including the ability to use certificate-based authentication to connect to other PostgreSQL clusters. Additionally, unprivileged accounts can now connect to another PostgreSQL database via the postgres_fdw without using a password.

Other Highlights

PostgreSQL 13 continues to improve operability on Windows, as now users who run PostgreSQL on Windows now have the option to connect over UNIX domain sockets.

The PostgreSQL 13 documentation adds a glossary of terms to help people familiarize themselves with both PostgreSQL and general database concepts. This coincides with a significant rework in the display of functions and operators in tables, which helps to improve readability both on the web and in the PDF documentation.

The pgbench utility, used for performance testing, now supports the ability to partition its “accounts” table, making it easier to benchmark workloads that contain partitions.

psql now includes the \warn command that is similar to the \echo command in terms of outputting data, except \warn sends it to stderr. And in case you need additional guidance on any of the PostgreSQL commands, the --help flag now includes a link to https://www.postgresql.org.

Download