Derk van Veen

POSETTE 2024 Talk

Partitioning your Postgres tables for 20X better performance

local CEST (Livestream 4)

Over the last two years we mastered the skill of creating and maintaining partitions in PostgreSQL and shared our work with the Postgres database community: https://github.com/Adyen/adyen-postgres-partitioning.

But we forgot a very important detail. Until now we have been partitioning individual Postgres tables in the most optimal way, taking locking into account. But in a relational database you JOIN a lot of tables together. And we did not optimise our strategy for joining, resulting in suboptimal database performance—and even an almost performance incident.

In this session I will share our latest insights with respect to Postgres partitioning, optimal partitioning strategies, and how to use check constraints to help the Postgres query optimiser create the best plans.

With the improved strategy and denormalising a few Postgres tables, we have been able to make queries up to 50 times faster. We have been totally blown away by the results this far.