Sign in to confirm youā€™re not a bot
This helps protect our community. Learn more

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

Microsoft Developer
589K subscribers
<__slot-el>
<__slot-el>
2K views 10 months ago #PostgreSQL #Database #PosetteConf
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-postgr.... 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 Po ā€¦...more
...more
Partitioning your Postgres tables for 20x better performance | POSETTE 2024
59Likes
2,096Views
Jun 132024
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-postgr.... 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:   / derk-van-veen-database-specialist  

Follow along using the transcript.

Microsoft Developer

589K subscribers