The beloved open source database PostgreSQL issued a new version of its namesake database last night with more standard SQL assents and increased performance.
Performance gains can be attributed to improvements in the indexing system, as well as partition. The frequently modified B-tree indices have been optimized and the team claims a 40% reduction in space utilization.
The performance of adding data to partitioned tables through
COPY has also been improved, as well as the critical incorporation of the ability to attach a new partition to a table Without blocking queries.
Other indexing performance strokes of lower overhead costs in generating advance write records and enabling Just-in-time default compilation should speed up the execution of expressions in
WHERE . The feature is not new, it was introduced in PostgreSQL 11 of 2018, but it was left disabled by default.
However, what is new and will have many administrators jumping for joy is the arrival of
REINDEX CONCURRENTLY that can rebuild an index without blocking the writes in the table. For many, and the increase in performance, it will be worth the price of admission alone.
Compatibility with SQL standards has also received recognition at launch with "generated columns" derived from other columns and a "stored" version, which will hide the calculated version on disk. Queries can also be executed on JSON documents and queries
WITH can now be inserted (as long as they are not recursive or referenced more than once at a later part of the query).
There is also a nod to those who struggle with the challenges of internationalization, with non-deterministic collations to allow comparisons between upper and lower case.
Finally, authentication has been enhanced with servers capable of requiring a client to provide a valid SSL certificate, if configured, and client and server. Side encryption has been introduced for authentication through GSSAPI. LDAP servers can also be discovered if PostgreSQL is compiled with OpenLDAP.
For those who prefer things a bit more cloudy, both AWS and Azure continue to persist in PostgreSQL 11. Based on previous performance, we expect version 12 to emerge in the first in about five months, and in the last three months after .
PostgreSQL has been around for quite some time, with the first formal release in 1997. Apart from the almost two-year gap between 9.4 (2014) and 9.5 (2016), it has achieved an emission approximately once a year.
Version 12 has enough strength to show that the old is still worthy of DBA's attention if open source is your thing. Or, worrying for Oracle's tastes, even if it isn't. ®
How to process, discuss, analyze and visualize your data with three complementary tools