Schedule - PGConf.EU 2018
Partitioning, back to the core
Date: 2018-10-25
Time: 11:50–12:40
Room: Berlin
Level: Intermediate
In this talk, I will describe my nowadays production architecture using pg_partman trigger-based partitioning technique on PostgreSQL 9.5.
This database stores all sales eventof DECATHLON sport retail company for several years. Containing more than 250 millions sales events and increasing fastly...
In order to take the benefits of the improvement of the last PostgreSQL version regarding partitioning in particular...
I'm studying opportunities of improving the partitioning model of the database, using native partitioning and/or available extensions for trigger based partitioning or tooling.
In this talk, I will: - introduce my database structure - present 9.5 partitioning - explain improvement on partitioning on PostgreSQL11 - analyze possible solutions and focus on several solutions - provide feedbacks regarding these solutions regarding - perfomance impact - side effet of database administration - migration technique
Here is a plan of my presentation:
Partitioning possibilities in 9.5
Trigger based partitioning
Extensions (some of the possibilites)
pg_partman
pg_pathman
PartMgr
Native partitioning in PG 10, (improved PG 11)
How does it works?
Hybrid mode
Mix partitioning solutions together
- Define the possible solution
- Weigh the pros and cons for them
Tooling
A glimpse of PartMgr
https://github.com/slardiere/PartMgr
the database
9.5 structure
- Single axis partitioning
Focus on partitions and surrounding objects
- functionnal maintenance procedures
Impact on database
Re-design our existing partition schemas
From a single storeID-based partitioning to a double-axis partitioning (storeID+time)?
- Add a second partition axis (time).
Performance aspects
- Is there a maximum number of partitions?
Migration procedure
Slides
The following slides have been made available for this session:
