PostgreSQL Partitioning: Slicing and Dicing for Performance and Easier Maintenance | POSETTE 2024

Video of a conference talk about PostgreSQL partitioning presented by Ryan Booz at POSETTE: An Event for Postgres 2024. As your database grows, the performance and maintenance of large tables can become challenging. Fear not! PostgreSQL has the right tool for the job: declarative table partitioning. This talk will discuss the benefits of partitioning in PostgreSQL, including improved performance and simplified maintenance.

After introducing the benefits of table partitioning, we’ll take a whirlwind tour through the range, list, and hash partitioning types available in PostgreSQL, highlight common use cases and trade-offs. From there, we’ll dive right into practical examples of declarative partitioning and discuss some key considerations and best practices when designing the schema for partitioning.

By the end of this session, you will be able to create partitioned tables in PostgreSQL that improve the performance and maintenance of your application, allowing you to scale with more control than ever before.

Ryan is an Advocate at Redgate focusing on PostgreSQL. Ryan has been working as a PostgreSQL advocate, developer, DBA and product manager for more than 20 years, primarily working with time-series data on PostgreSQL and the Microsoft Data Platform.

Ryan is a long-time DBA, starting with MySQL and Postgres in the late 90s. He spent more than 15 years working with SQL Server before returning to PostgreSQL full-time in 2018. He’s at the top of his game when he's learning something new about the data platform or teaching others about the technology he loves.

Chapters:
00:00 - Intro
01:22 - The what & why of partitioning
03:57 - What are partitions
05:09 - Declarative partitioning
09:55 - Data retention & archiving
11:37 - Three types of partitions in Postgres
15:37 - Creating & maintaining partitions
24:36 - SQL tips & best practices

📕 Everything you need to know about POSETTE: An Event for Postgres 2024 can be found at: https://aka.ms/posette

✅ Learn more:
Watch more POSETTE talks: https://aka.ms/posette-playlist-2024

📌 Let’s connect:
X – @PosetteConf https://x.com/PosetteConf
Mastodon - @posetteconf https://mastodon.social/@posetteconf
Threads – @posetteconf https://www.threads.net/@posetteconf

🔔 Subscribe to the POSETTE News: https://aka.ms/posette-subscribe

#PosetteConf #PostgreSQL #Database

Presenters:
Ryan Booz, Developer Advocate at Redgate

Connect:
Ryan Booz:   / ryanbooz  ,   / ryanbooz