Conference 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

Speaker

Thomas BOUSSEKEY