PGCon2017 - 20180510
PGCon 2017
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Amit Langote |
|
Ashutosh Bapat |
| Schedule | |
|---|---|
| Day | Talks - Day 2 - 2017-05-26 |
| Room | DMS 1120 |
| Start time | 16:00 |
| Duration | 00:45 |
| Info | |
| ID | 1047 |
| Event type | Lecture |
| Track | New Features |
| Language used for presentation | English |
Partition and conquer large data with PostgreSQL 10
Declarative partitioning comes to PostgreSQL
For some time now, PostgreSQL users have been implementing partitioning using table inheritance, which has failed to match the usability and performance of the partitioning features in other DBMSes. The new declarative partitioning feature, which will be available in PostgreSQL 10 changes that. It paves the way for implementing query optimization techniques specially designed for partitioned data. The talk introduces new partitioning methods and discusses query optimization techniques with performance numbers.
Before declarative partitioning was developed in PostgreSQL, one could use table inheritance to partition data, CHECK constraints to specify the partition conditions, and rules and/or triggers to route the rows to the right partition. While that has worked fine for a relatively small number of partitions, performance and ease-of-use with a large number of partitions is unsatisfactory. Current approach to deduce partition specifications from a set of arbitrary CHECK constraints is complex and is not efficient. Hence optimizations like plan-time partition-pruning do not scale beyond a few hundred partitions and advanced techniques like partition-wise join and partition-wise aggregation are not implemented.
Declarative partitioning, a long awaited feature, will be available in PostgreSQL 10. It makes it easy to create and maintain partitions without requiring to create and manage constraints, rules and/or triggers. It makes partition specification representation much more direct, leaving nothing to be deduced, thus making above-mentioned query optimizations feasible and scalable.
In this talk we will introduce salient features of declarative partitioning. We will also cover high-level design for above mentioned query optimization techniques, along with some performance figures demonstrating the scalability they achieve.