New and Evolving PostgreSQL Enterprise Features with Recent Releases

PostgreSQL also introduced many other breakthrough features in versions 10 through 11, 12 and 13 that make it a real competitor against Oracle, such as partitioning enhancements, parallel query and logical replication. In this blog post, we will showcase some of the new and notable PostgreSQL features included in more recent versions of PostgreSQL.

Partitioning Features

Declarative Partitioning

Until PostgreSQL version 9.4, there was no actual partitioning in PostgreSQL. It was achieved only through table inheritance, which offered very limited functionality and performance benefits. Much of the functionality is managed manually through triggers or SQL commands. For example, we had to use triggers to direct a row before INSERT to the correct partition. We have to create indexes for each partition separately. PostgreSQL version 10 gave birth to declarative partitioning, but we still had to create constraints and indexes for each partition.

PostgreSQL 11 comes complete with a very impressive set of new Partitioning features to both help improve performance and also to help make partitioned tables more transparent to applications.

Logical replication of partitioned tables

With PostgreSQL 13, they introduced support for logically replicating partitioned tables. Previously, you were forced to replicate partitions to your standbys individually. Now, however, you can automatically replicate all your partitions at the same time

Partitioning Methods

The currently supported partitioning methods are range, list, and hash.

Keys & Indexes

Full support for foreign keys on partitioned tables was added in PostgreSQL 12. PostgreSQL also boasts support for primary keys, indexes, and triggers on partitioned tables.

Default Partition

PostgreSQL allows the creation of a “default” partition for storing data that does not match any of the remaining partitions. Oracle users will love this feature as it is not available in Oracle Database.

Row Movement

UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions.

Partition Pruning

Improve SELECT performance through enhanced partition elimination strategies during query planning and execution. A new method of performing partition elimination has been added. This new algorithm is able to determine matching partitions by looking at the query’s WHERE clause. The previous algorithm checked each partition, in turn, to see if it could match the WHERE clause of the query. This resulted in an additional increase in planning time as the number of partitions grew.

Partition Pruning During Query Execution

As for prepared statements, query parameters are not known before the execution. Query Planner cannot eliminate partitions during the planning phase as parameters are not known. So, the executor does the partition pruning during execution to access only the partitions that match parameters.

Indexing Features

Covering Index

PostgreSQL now allows you to add non-key columns in the btree index. Since queries typically need to retrieve more columns than just the ones they search on, PostgreSQL allows you to create an index in which some columns are just “payload” and are not part of the search key. It helps in doing just index-only scans to retrieve the required rows.

Reindex Concurrently

Starting PostgreSQL 12, it’s possible to rebuild an index with REINDEX CONCURRENTLY without locking the table for read/writes, just like the Oracle REBUILD INDEX command.

Parallel Index Creation

With parallel index creation (Introduced in PostgreSQL 11, currently applicable only for b-tree indexes), indexes can be created faster up to max_parallel_workers value and setting maintenance_work_mem large enough to hold multiple copies of data. Parallel index creation can significantly reduce your index creation time.

Deduplication of Data in B-Tree Indexes

Sometimes there will be duplicate entries in an index. This means that a leaf node in a B-Tree index with at least two index entries in the same index contains the same data for all the index columns. With the addition of deduplication in PostgreSQL 13, you can group these duplicate B-Tree index entries and gather them in a group entry. The benefit of this is saving space and reduced load on disk and RAM, as you won’t have to duplicate column data. Duplicate entries also cause unwanted index bloating.

Authentication

SCRAM-SHA-256

In PostgreSQL 11 came support for SCRAM-SHA-256 password authentication. This method prevents password sniffing on untrusted connections and allows you to store your passwords in a cryptographically hashed form.

Out of all the currently supported password authentication methods, this is the most secure one.

GSSAPI

GSSAPI allows secure authentication, as well as automatic single-sign-on for systems that support it. This authentication method relies on a GSSAPI-compatible security library. The data sent over the database connection will be unencrypted unless SSL is used; however the authentication itself is secure. GSSAPI with Kerberos authentication is possible with PostgreSQL according to industry standards. When Kerberos is used, a standard principal is used in the format “servicename/hostname@realm”. Any principal included in the keytab used by the server will be accepted by the PostgreSQL server.

LDAP

Operating similarly to password authentication, this authentication method uses LDAP as the verification method. It’s only used to validate username and password pairs, hence the user must exist in the database already for the authentication to work. LDAP authentication operates in either a simple bind mode or what’s called search+bind. Search+bind allows you to use other identifiers compared to the simple bind mode, which only allows the distinguished name, domain name, or email to be used.

Certificate

The certificate authentication method uses SSL certificates to authenticate. Consequently, it’s only available for SSL connections. With certificate authentication, no password is required. The client simply needs to provide a valid and trusted certificate to be able to authenticate. The certificate’s common name will be matched with the database user name, and if a match is found, the client will be logged in.

Other notable features

Parallel Vacuum

With the release of PostgreSQL 13, improvements to the VACUUM command were implemented. The improvement in question is the added parameter option PARALLEL. With PARALLEL, you can perform index vacuum and index cleanup phases of VACUUM. This allows you to parallel vacuum multiple indexes corresponding to a single table.

Parallel Query

Most Reports queries, which generally scan a lot of data, suffer from performance degradation due to needing to scan or aggregate data from many rows even with an index scan. These queries can use only one CPU until 9.4 and run serially.

With parallel query (which offers parallel sequential scan, merge join, hash join, aggregate and other parallel query plan features), these queries can take advantage of multiple workers, and performance can improve from 2x to 10x as per documentation.

Check out this post to learn when to use parallel queries.

Native Logical Replication

PostgreSQL introduced native logical replication in version 10 to provide a more flexible replication option, unlike streaming replication and more like Oracle Streams, to replicate specific tables, columns, or rows. This can be used to split between multiple databases or consolidate from multiple databases. And can also replicate between different major versions of PostgreSQL.

Stored Procedures with Embedded Transactions

Yet another addition that makes PostgreSQL compatible for migrations from Oracle database.

We don’t have any transaction control inside the pgsql program (DO block or function) in version 9.4, and have to use a workaround like dblink to start and commit/rollback transactions.

With stored procedures, which premiered in version 11, we can now implement transaction control within any pgsql structure like while loop, for loop, or if else statement. Although stored procedures are similar to functions, they have to be invoked by CALL command and can work as independent programs.

Generated Columns

Like Oracle database, generated columns in PostgreSQL can store the data automatically computed from other columns within the row. This speeds up queries by not having to compute the value during query execution, and instead, the generated column value is computed on INSERT or UPDATE to the row.

JIT Compilation

PostgreSQL 11, 12 and 13 all support Just-in-Time (JIT) compilation, which was added back in 2018. JIT compilation is the process of turning an interpreted program evaluation into a native program. With JIT compilation, this process can be done at run time. An advantage of JIT is the possibility of generating expression-specific functions that the CPU can natively execute. In turn, the process gives you a speedup.

Page Checksums

Page checksums is a feature that helps you verify the integrity of data stored to disk. Previously, you could only enable page checksums during the initialization of a PostgreSQL cluster. PostgreSQL 12 introduced the ability to enable or disable page checksums in an offline cluster via the pg_checksums command.

Summary

As you can see from this article, PostgreSQL is an ever-evolving database system that brings many powerful features to the table. Every new version adds new exciting features that make it a real competitor to other databases such as Oracle. Its partitioning and indexing features get many new updates that add onto its already expansive toolbox.

If you are looking for a managed solution for your PostgreSQL database, feel free to check out our ScaleGrid for PostgreSQL service. We provide a free 30-day trial that allows you to use all our management features with no restrictions. Pricing starts at just $10/month and gives you access to a fully managed enterprise-grade PostgreSQL hosting solution.