Phân vùng bảng Postgres của bạn để có hiệu suất tốt hơn 20 lần | 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/