Practical Partitioning with PostgreSQL (in Production!)

📚 PostgreSQL Mistakes and How to Avoid Them: https://mng.bz/vKd4

Follow me on:

🦣 https://fosstodon.org/@vyruss

🦋 https://bsky.app/profile/vyruss.org

https://www.linkedin.com/in/vyruss/

Has your table become too large to handle? Have you thought about chopping it up into smaller pieces that are easier to query and maintain? What if it's in constant use?

An introduction to the problems that can arise and how PostgreSQL's partitioning features can help, followed by a real-world scenario of partitioning an existing huge table on a live system.

Talk from Postgres Vision 2021. Slides: https://vyruss.org/computing/slides/pgvision2021_practical_partitioning_in_production_with_pg.pdf

0:00 Introduction

0:53 What is partitioning?

1:49 Partitioning in PostgreSQL

3:08 Declarative Partitioning

5:06 PostgreSQL limits

6:51 What partitioning can help with

10:48 What partitioning is not

12:03 Dimensioning

14:42 Partitioning method

16:45 Partition Key selection

19:35 Sub-partitioning

21:22 Partitioning by multiple columns

23:03 What Postgres does not do

24:21 Partitioning a live production system

25:11 The situation

26:21 Hidden performance pitfall

30:50 The plan

32:32 Create empty partitioned table & indices

32:48 Create partition for new incoming data

33:08 Attach old table as a partition

39:28 To conclude...