No video of the event yet, sorry!

Introduced in PostgreSQL 10, partitioned tables are becoming a main stream feature of PostgreSQL. When combined with FDW esp. postgres_fdw, these two technologies form a base for distributed query processing in a sharded setup.

Partitioned table distribute the data into smaller chunks or when coupled with FDW they distribute the data across multiple servers. PostgreSQL's query optimizer takes advantage of this distribution to speed up queries involving partitioned table. It plans queries so that the basic SQL operations like join, aggregation, filtering, sorting are broken down to work at the level of partitions instead of partitioned table. This allows these operations to be performed in-memory, in parallel if the partitions are local. If partitions are foreign, the operations can be pushed down to the foreign server. Either way the goal is to make query efficient.

In this talk we will cover these optimizations and reflect on what problems lie ahead. But PostgreSQL's planner requires a lot of memory when dealing with a large number of partitions. To a large extent this is because PostgreSQL's partitions are really tables, which require to be planned separately. The solution is to use efficient data structures or to reduce the amount of partition specific handling we need to do. We will close the discussion with some thoughts on those line.

Date:

2020 November 19 14:50 CST

Duration:

40 min

Room:

Virtual - English Sub-Conference B

Conference:

CHINA 2020 And PGConf.Asia 2020

Language:

Track:

Performance

Difficulty:

Medium