Schedule - Nordic PGDay 2025
Leveraging table partitioning for query performance and data archiving
Date: 2025-03-18
Time: 15:10–16:00
Room: Main Room
Level: Intermediate
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.
