Scaling Indexes with Partitioning

When dealing with truly massive datasets in PostgreSQL, combining indexing and partitioning isn't just an option—it's often a necessity for maintaining performance.

This lesson explores how indexes interact with partitioned tables, and the strategies for creating indexes that scale effectively alongside your data divisions.

A local index is an index that is created on an individual partition of a partitioned table. When you create an index on the parent partitioned table, PostgreSQL automatically creates a separate index for each of its partitions.

• Each local index only covers the data within its specific partition.
• They are implicitly managed as you add or remove partitions.
• This is the most common and often most efficient type of index for partitioned tables.