Understanding Index Behavior in PostgreSQL Partitioned Tables

PostgreSQL's partitioning feature becomes more powerful with each release, and understanding how indexes work with partitioned tables is crucial for optimal performance. Let's explore some interesting behaviors through practical examples.

Basic Setup and Index Inheritance

Consider a simple partitioned table structure:

CREATE TABLE t (id int, c1 text, c2 text) PARTITION BY RANGE(id); 

Scenario 1: Creating Indexes Before Partition Attachment

When you create an index on a table before attaching it as a partition, the index remains independent:

CREATE TABLE t_p1 (id int, c1 text, c2 text);
CREATE INDEX idx_t_p1_c1 ON t_p1(c1);
ALTER TABLE t ATTACH PARTITION t_p1 FOR VALUES FROM (1) TO (100);

The index idx_t_p1_c1 continues to exist on the partition after attachment.

Scenario 2: Creating Indexes on Parent Table

When you create an index on the parent table:

CREATE INDEX t_c1_idx ON t(c1);

PostgreSQL automatically:

• Creates a partitioned index on the parent table
• Creates corresponding indexes on all existing partitions
• Names the automatically created indexes systematically For example, when we had partition t_p2, PostgreSQL automatically created t_p2_c1_idx.

Scenario 3: Adding New Partitions

The behavior differs based on how you create new partitions:

1. Using PARTITION OF syntax:

CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (100) TO (200);

The new partition automatically gets matching indexes for any indexes on the parent table.

2. Using ATTACH PARTITION with existing table:

CREATE TABLE t_p3 (id int, c1 text, c2 text);
CREATE INDEX idx_t_p3_c1 ON t_p3(c1);
ALTER TABLE t ATTACH PARTITION t_p3 FOR VALUES FROM (200) TO (300);

The existing index on the attached partition remains, and it functions as the local index for the parent's partitioned index.

Scenario 4: Attaching Plain Table Without Pre-existing Index

When attaching a table that doesn't have any indexes to a parent table that has indexes, PostgreSQL automatically creates matching indexes:

CREATE TABLE t_p4 (id int, c1 text, c2 text);
ALTER TABLE t ATTACH PARTITION t_p4 FOR VALUES FROM (300) TO (400);

After attachment, examining the partition shows that PostgreSQL automatically created the index t_p4_c1_idx on the partition to match the parent table's index structure. This demonstrates that:

• PostgreSQL ensures index consistency across all partitions
• The naming convention follows the pattern: {partition_name}_{parent_index_suffix}
• The index is created automatically during the attachment process