PGCon2018 - 2.5

PGCon 2018
The PostgreSQL Conference

Robert Treat
Day Talks - Day 1: Thursday - 2018-05-31
Room DMS 1120
Start time 10:15
Duration 00:45
ID 1241
Event type Lecture
Track DBA
Language used for presentation English

PostgreSQL Partitioning

Slicing and dicing your schema for quicker queries and dishing data

Begining in 8.1, PostgreSQL has offered table partitioning, allowing you to split data across different physical segments for significant performance improvements. Until recently not much had changed, but starting in Postgres 10, and new, more native partitioning system was added into Postgres. While the initial implementation left some things to be desired, many of those issues are being addressed. This talk will cover many of the insider tricks that are used in large setups and explain the trade-offs between the options you now have available.

PostgreSQL table partitioning allows you to scale your data to significantly larger volumes than would otherwise be possible. Getting started with table partitions in and of itself is not complicated, but if you want to get maximum benefits from your setup you can't just follow the documentation. This talk will walk through the ins and outs of table partitioning including:

  • What table partitioning is, and how it is implemented in PostgreSQL.
  • Differences between native and inheritance based partitioning.
  • Migrating from one format to the other.
  • Automated partition maintenance.

Learn how to setup and maintain table partitioning in PostgreSQL with hands-on examples. We'll walk through manipulating SQL and look at tools to automate your partition maintenance.