Photo by Hu Chen on Unsplash

Partitioned tables improvements in recent Postgres versions

Split of a huge table is more performant than ever

When building an app, we usually strive for the simplest designs. One of the simplest routes to take when it comes to backend design is to start with relational database. As the apps grow, certain database tables become a bottleneck. And sure, you can offload some of the work to NoSQL database such as MongoDB. Yet certain tables are at risk of staying in the core of the system forever. Not to mention cases where you are forced to stick to the relational database.

Fortunately, relational databases are around for a really long time. There are plenty of options to consider that people used in the past. We will explore one of them — partitioning. It is especially useful if majority of your queries against an ever-growing database queries the well defined subset of data (this subset might differ on query to query basis). To give an example — your app might be concerned just about the sales that happened during the last few days (partition by day) or there is a separate UI for various departments within your company (partition by department ID).

What is partitioning?

Partitioned table is looks just as any other table — yet physically it is split into smaller subtables. This can yield huge performance gains for large tables in queries that touch just a bunch of these partitions. In certain cases this may mean that instead of scanning a table with billions of rows, you may end up scanning just one table with hundred thousands of rows. Of course, one may argue that you can achieve similar things with indexes — and they aren’t totally wrong. It’s just the fact that smaller table have also smaller indexes that are easier to manage at OS level.

Version 10: Declarative partitioning

Partitioned tables aren’t a new feature. You might be tempted to think that they were introduced in version 10 but in fact that’s only true for declarative syntax. Prior to that version you could leverage inheritance (to create table partitions) and on insert trigger (to dispatch events to the right partition) to achieve similar effect. Starting from version 10, Postgres introduced declarative partitioning — a special syntax for defining partitioned tables. It was a good step forward, yet it had its limitations.

Partitioned tables on Postgres 10 are fully functional in a sense that you can define them with either range or list partitioning strategy. Here’s an example (it is based on actors table from dvdrental database)

-- RANGE partitioning
CREATE TABLE actor_partitioned (
  actor_id    INTEGER, 
  first_name  VARCHAR, 
  last_name   VARCHAR, 
  last_update TIMESTAMP WITHOUT TIME ZONE) 
PARTITION BY RANGE(first_name);
-- partition for actors whose name starts with an A
CREATE TABLE actor_partitioned_A 
PARTITION OF actor_partitioned 
FOR VALUES FROM (MINVALUE) TO ('B');
-- partition that contains the rest of actors
CREATE TABLE actor_partitioned_REST 
PARTITION OF actor_partitioned 
FOR VALUES FROM ('B') TO (MAXVALUE);

You don’t have to exhaust the range (or with LIST partitioning, every possible value). It is important to capture the values you expect though. Inserting values that can’t be assigned to any partition will result in an error.

You can’t have overlapping partitions too — Postgres is smart enough to raise an error when you’d like to register a partition like this.

There are a few limitations though that the next Postgres versions try to solve.

1. If you are running tables with large number of partitions, you might have hit the limitation in the past that forced your team to migrate to newer Postgres version. Version 10 could handle a few hundred partitions efficiently and this number grew by the order of magnitude with the upcoming Postgres releases.
2. In the example above we defined partition for the rest of actors . We didn’t really care about their properties, we could think of this partition as a default partition — a partition in which records that are not matching any other partition should end up.
3. Adding / removing partitions requires lock on the table and it can only be done sequentially.
4. Lack of foreign keys support to partitioned tables.

Version 11: Default partition, hash partitioning and more

Version 11 brought many improvements that improved usability of partitioned tables. Starting from now you can use the following syntax to configure a default partition

CREATE TABLE actor_partitioned_rest 
PARTITION OF actor_partitioned 
DEFAULT;

It also added another method of partitioning called HASH partitioning. Instead of assigning concrete column values to partition (list partitioning) or value ranges (range partitioning), one may rely on value hash. Here’s how you would define it

-- HASH partitioning
CREATE TABLE actor_partitioned (
  actor_id    INTEGER, 
  first_name  VARCHAR, 
  last_name   VARCHAR, 
  last_update TIMESTAMP WITHOUT TIME ZONE) 
PARTITION BY HASH(first_name);
-- partitions
CREATE TABLE actor_partitioned_0 
PARTITION OF actor_partitioned 
FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE actor_partitioned_1 
PARTITION OF actor_partitioned 
FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE actor_partitioned_2
PARTITION OF actor_partitioned 
FOR VALUES WITH (MODULUS 3, REMAINDER 2);

Note that hash partitioned tables cannot have default partition.

Another interesting use case (that was actually enabled since version 10 but with greatly improved usability since version 11) is partitioned table across multiple machines. This gives a lot of extra optimization options — users may have certain hardware requirements for some partitions. The major improvement in this regard is the option to insert values to table with foreign partitions (that’s a name of a partition that is stored on a different host).

To configure such partitions you need a few things

1. Create local tables in remote Postgres instances — these are regular tables.
2. Configure server so that the user can connect to remote instance. It will use foreign data wrapper extension.

CREATE EXTENSION IF NOT EXISTS postgres_fdw; 
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO [user]; 
CREATE SERVER shard02 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname '[remote db]', host '[remote host]', port '[remote port]'); 
-- be aware: passing a plaintext password here is not ideal and is likely not something you want. GSSAPI auth can also be used but setting this up is out of scope of this article
CREATE USER MAPPING FOR [user] SERVER shard02 OPTIONS (user '[user]', password '*****'); 
-- now you can define foreign partition as
CREATE FOREIGN TABLE actor_partitioned_rest 
PARTITION OF actor_partitioned 
FOR VALUES FROM ('B') TO (MAXVALUE) 
SERVER shard02;

And now you can query, insert, delete and join to the partitioned table as you would usually do.

Version 12: No exclusive lock partition attach and foreign keys

Since this version of Postgres, you can add foreign key that targets partitioned table. This is particularly useful to enforce referential integrity for OLAP use cases with partitioned table.

Another big improvement is that attaching partitions doesn’t require an ACCESS EXCLUSIVE lock. The SHARE UPDATE EXCLUSIVE is enough. The former is needed for detaching partitions though.

Users may observe a boost in performance for partition pruning and COPY command against partitioned tables. Especially in cases when both tables have the same partitioning schema — Postgres will perform partition-to-partition join.

Version 13: Partition-wise joins, publications

At version 13, joins between partitioned tables is optimized also in cases when partition bounds don’t match exactly.

It is also possible to create publications for partitioned tables. Publications are a means of logical replication in Postgres — a subscriber usually takes a snapshot of table state and then, in real-time, applies changes to its replica according to published events. This is logical replication in a sense that each record corresponds to a specific operation. Postgres also has support for physical replication that can be used for full database replication (as this process just copies WAL binary blocks). Here, the subscriber can be more flexible and disregard the events they are not interested in.

Version 14: Full execution-time partition pruning

Finally, the latest version addresses performance bottlenecks with regards to updates and deletes. Up to this point, only append and merge appendnodes supported execution time pruning and now modify table nodes were included.

Detaching partitions still require an ACCESS EXCLUSIVE lock but at least it can be done concurrently.

We mentioned the option to use foreign partitions with partitioned table. postgres_fdw was improved to perform foreign table scans in parallel. More improvements to enable better sharding support are yet to come and I am looking forward to them!

Conclusion

Postgres is a mature database yet actively developed — and we examined it focusing on partitioned tables. People often underestimate the value of updating software — by not following the release notes, we may not be aware some of the workaround we did in the past may no longer be needed (like table inheritance to achieve partitioning) or that we can, almost for free, get a significant performance boost. If your team is using partitioned tables heavily, yet you didn’t update Postgres for quite some time already, you might be struggling with stability or scalability problems that are no longer relevant.