Derk van Veen: Beyond the How: Why Table Partitioning Truly Matters

Описание: Table partitioning has become a well-known technique in the PostgreSQL world. There have been talks 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 is all about the "why" and will set you up for success when you are doing partitioning!

Table partitioning starts with finding the leading figure in your data. Only when you find your leading figure can you partition all tables in conjunction with each other. And when all tables are partitioned around that leading figure, you have to align all the partition boundaries as well! Aligning partitions around your leading figure is crucial for basically all the good that partitioning can offer you. They open the door for:

At least not terrible performance
Timely partitioning maintenance
Out-of-the-box PII compliance
Trivial data cleanup
Transparent data tiering, moving data to a second storage tier without the application even being aware

I am especially enthusiastic about how easy data tiering becomes after all the tough groundwork we covered over the years.

When partitioning is done wrong, as I did it in the past, performance can and will be terrible. When done in the right way, you can use enable_partitionwise_join and enable_partitionwise_aggregate to make query performance with partitions really work. For me, the difference in performance was a factor of 70 with respect to query run times!

All the applied strategies presented have been battle tested on our biggest, multiple +100TBs, and most pressured databases. As we are processing financial data they are compliant with the strict financial regulations and are fully based on out of the box and open source technologies.

After this presentation, you will understand all the boundary conditions for building a high-performing, flexible and easy to manage partitioning plan. As a bonus, you get an easier environment to manage, faster vacuums, out-of-the-box PII data compliance, and a very natural way of data cleanup and/or tiering. You and your databases will be ready for whatever data volumes the future will throw at you!