PostgreSQL 13 makes life easier with improvements to indexing, sorting, and vacuuming. Read on to learn how it makes your datasets cleaner and queries faster.
Auri Poso
|RSS FeedTechnical Copywriter at Aiven
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
We have great news for administrators of Aiven for PostgreSQL and all application developers using it: Your life just got easier with PostgreSQL 13.
The new version of Postgres, one of the all-time greats of open source databases, includes housekeeping operations that keep your database size more manageable.
In this post, we’ll cover the most important improvements and how you can reap the benefits of them.
Duplicate entries - which occur when you index non-unique data but also when updating rows with unique indexes - are found in B-tree indexes more commonly than most people think. This results in larger indexes than needed, which slows down performance and increases storage costs.
PostgreSQL 13 solves this problem by merging duplicate key values in B-tree indexes into a single posting list tuple so that the column key values only appear once. In indexes where duplicate values often occur, this can reduce index size dramatically. It also increases query throughput and makes routine vacuuming faster.
And speaking of vacuuming…
This is the feature we’ve all been waiting for!
The VACUUM command removes rows that are no longer visible due to updates or deletions, among other things. In previous versions, vacuuming could take a long time for large tables with multiple indexes. In Postgres 13, processing times are drastically shorter now that a worker can be allocated to each index and they can be vacuumed in parallel with a single vacuum process.
Partitioning enables you to accelerate queries, improve bulk loads and deletion, and manage large tables with ease, and Postgres 13 ships with several partitioning enhancements that give you more ways to split tables. Partition pruning is now allowed in a wider variety of cases and partitions can more often be directly joined, improving query performance.
One of PostgreSQL 13’s most prominent new features is incremental sorting. When a group of data sets is already sorted by some column(s), the result can be used as a basis for further sorting later, thereby reducing the volume of data that needs to be sorted with each query. Of course, the efficiency improvement depends on the data in question, so PostgreSQL 13 optimizer heuristically decides whether to use incremental sorting or not.
For a full list of version improvements, check out PostgreSQL's release notes.
You’ll need to upgrade to PostgreSQL 13 to take advantage of these features. If you already use Aiven for Postgres, you can easily run an in-place upgrade to migrate to the newest version.
Not using Aiven for PostgreSQL yet? Try out the latest PostgreSQL version with our free 30-day no-commitment trial. Get starting by signing up and choosing version 13 when creating your Postgres service. Alternatively, you can also use our Terraform tooling, REST API or CLI for service creation. To find out more about PostgreSQL, read our Introduction to PostgreSQL
PostgreSQL 13 is packed with next-level features that enable you to improve query performance, rapidly deduplicate data, vacuum indexes in parallel, and sort data more effectively. Head over to the Aiven console and get the upgrade started, so you can enjoy these improvements.
Not using Aiven services yet? Sign up now for your free trial at https://console.aiven.io/signup!
In the meantime, make sure you follow our changelog and blog RSS feeds or our LinkedIn and Twitter accounts to stay up-to-date with product and feature-related news.
