Leveraging table partitioning for query performance and data archiving | POSETTE 2025

Video of a conference talk about leveraging table partitioning for query performance and data archiving. Table partitioning has become a known technique in the PostgreSQL world. There has been a talk about it at most of the conferences I visited last year. But often we talk about the technicalities of partitioning and not why we partition. This talk will be about why table partitioning can be done. We use it for PII compliance, improving query performance by using enable_partitionwise_join and enable_partitionwise_aggregate and transparent data archival with respect to the application. Especially the data archival part is something I am really proud of. All these techniques may sound easy, but there is a lot of ground to cover before it works to perfection. Think about finding your leading figure, denormalise tables and aligning partition boundaries. It can be a lot of work but we managed to improve query performance over 50 times for some queries! And with all the partitioning in place we can start moving data out of our primary database into a secondary archival system without the application being aware of it. Now our mean PostgreSQL database becomes smaller, easier to manage and even faster. Derk van Veen is a database specialist at Adyen. Adyen has some very large PostgreSQL databases under continuous high pressure. An important part of his job is to find creative solutions for tough problems. Whenever he, or his team, finds interesting solutions he tries to share the lessons learned with the PostgreSQL community. ► Video bookmarks: ⏩ 00:00 Introduction ⏩ 02:42 3 Reasons to Partition ⏩ 04:31 3 Types of Partitioning ⏩ 07:41 Date Range Partitioning ⏩ 10:32 Integer Partitioning ⏩ 13:28 Joining Tables ⏩ 16:17 Align your partitioning around a leading figure ⏩ 19:15 UUIDv7 (Derk’s new favorite data type) ⏩ 21:29 Data Lifecycle Management ⏩ 24:26 Summary 📕 Everything you need to know about POSETTE: An Event for Postgres 2025 can be fo