Partitioning your Postgres tables for 20x better performance | POSETTE 2024

Video of a conference talk about partitioning your Postgres tables presented by Derk van Veen at POSETTE: An Event for Postgres 2024. 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. 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. Chapters: 00:00 Intro 00:36 Partitioning and LEGO 02:55 Introduction and Agenda 07:39 Single table partitioning 14:21 Design flaws 21:08 Target setup 📕 Everything you need to know about POSETTE: An Event for Postgres 2024 can be found at: https://aka.ms/posette ✅ Learn more: Watch more POSETTE talks: https://aka.ms/posette-playlist-2024 📌 Let’s connect: X – @PosetteConf https://x.com/PosetteConf Mastodon - @posetteconf https://mastodon.social/@posetteconf Threads – @posetteconf https://www.threads.net/@posetteconf 🔔 Subscribe to the POSETTE News: https://aka.ms/posette-subscribe #PosetteConf #PostgreSQL #Database Presenters: Derk van Veen, Database specialist at Adyen Connect: Derk van Veen: https://www.linkedin.com/in/derk-van-veen-database-specialist/