Sorting out performance bottlenecks

One of the key focuses of this release is improved sorting performance – a critical aspect of any Relational Database Management System (RDBMS). By using generation memory contexts and reducing function call overhead, PostgreSQL 15.2 significantly reduces the memory consumption and boosts the speed of in-memory sorts.

But why is sorting so important? Well, it’s used for plenty of database operations, including:

• ORDER BY clauses in SQL statements
• Queries using GROUP BY
• WINDOW FUNCTIONS with PARTITION BY
• Aggregate functions combined with ORDER BY
• Queries using MERGE JOIN
• UNION queries and those using DISTINCT

FDWs get a performance boost

Foreign Data Wrappers (FDWs), which allow PostgreSQL to interact with external data sources, have also received a significant performance upgrade. With PostgreSQL 15.2, FDWs can now commit in parallel across all foreign servers involved in a local transaction. This translates to faster and more efficient operations for distributed PostgreSQL clusters.

Partitioning power-up

PostgreSQL 10, released all the way back in 2017, introduced the declarative PARTITIONING feature for tables. Since then, this functionality has been steadily enhanced and expanded, and PostgreSQL 15.2 continues the trend.

This release further improves PARTITIONING performance by optimizing queries that target only a few partitions and by reducing the need for sorting in certain scenarios. As a quick reminder, PostgreSQL supports the following forms of partitioning:

• List Partitioning: Divide your data based on distinct values within a column. For example, partitioning by REGION.
• Range Partitioning: Partition your data based on ranges within a column, such as partitioning INVOICES by INVOICE_DATE.
• Hash Partitioning: Introduced in PostgreSQL 11, this method distributes rows based on the hash value of the partition key.

PostgreSQL 15.2 also automatically creates indexes on child tables for default partitions and introduces PARTITIONWISE AGGREGATION. This allows the query planner to push down aggregations to the partition level, resulting in faster queries through improved parallelism and lock handling.

Faster recovery, reduced vacuuming, and more!

PostgreSQL 15.2 introduces a range of optimizations to streamline database operations and minimize resource consumption:

• Speedier recovery and replay: By pre-fetching necessary file contents from the write-ahead log (WAL), PostgreSQL 15.2 accelerates recovery and replay operations.
• Smarter vacuuming: Vacuuming is essential for reclaiming space and maintaining database health. PostgreSQL 15.2 introduces a more efficient approach, reducing vacuuming overhead for certain workloads. Before PostgreSQL v15, vacuum was using the FreezeLimit value cutoff to freeze tuples. Now this value can be set to the oldest extant XID remaining in the table.

New features to explore in PostgreSQL 15.2

Beyond performance enhancements and optimizations, PostgreSQL 15.2 also has a bunch of exciting new features in store:

• MERGE command: Streamline data modification operations with the new MERGE command, which handles inserts, updates, and deletes in a single transaction.
• Enhanced logical replication: Enjoy greater control and efficiency in logical replication with selective table publication, row filter conditions, and support for prepared transactions.
• Advanced compression: Reduce storage footprint and improve backup speeds with support for Zstandard (Zstd) compression for pg_basebackup and WAL archiving for full page writes (optionally via loadable modules).
• Structured server logs: Gain deeper insights into database activity with structured server logs in JSON format.
• Shared memory statistics: Reduce I/O overhead and boost performance by storing statistics in shared memory. Previous statistics are restored after shutdown and restart, and extended statistics can be recorded for a parent with all its children, resulting in improved queries.

A glimpse into the future: PostgreSQL 16

While we’re still exploring the improvements and new features of PostgreSQL 15.2, the open-source community is already hard at work. The next major release, PostgreSQL 16 will be released in late 2023.

Here’s a sneak peek at what’s (probably) in store:

• Enhanced logical replication: Parallel apply, replication of sequences, standby-based logical replication, and support for DDL and LOB replication.
• Improved SQL/JSON compliance: Better adherence to SQL/JSON standards.
• Asynchronous I/O and direct I/O: Enhanced performance through asynchronous data pre-fetching and the ability to bypass the OS cache.
• Transparent data encryption (TDE): Enhanced security with transparent data encryption at rest of particular columns.
• Hash indexes: Improved indexing options with the addition of hash indexes.
• Streamlined vacuuming: Reduced WAL volume and more efficient vacuuming processes.
• Incremental materialized views: Enhanced performance and efficiency for materialized views.

Zebanza: your guide to the PostgreSQL savanna

Even in the wild, elephants and zebras are great at working together… so it should be no surprise that we’re the perfect partners to help you migrate to PostgreSQL 15.2 and beyond!