How to Create Index on Partitioned Table in PostgreSQL

In PostgreSQL, you create an index on a partitioned table by defining the index on the parent table using CREATE INDEX. This automatically creates matching indexes on all partitions. You can also create indexes directly on individual partitions if needed.

Syntax

The basic syntax to create an index on a partitioned table is:

• CREATE INDEX index_name ON parent_table (column_name); - creates an index on the parent partitioned table.
• This command automatically creates indexes on all existing partitions.
• You can also create indexes on individual partitions using CREATE INDEX on the partition table name.

sql

CREATE INDEX index_name ON parent_table (column_name);

Example

This example shows how to create a partitioned table, add partitions, and create an index on the parent table which applies to all partitions.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE INDEX idx_sales_date ON sales (sale_date);

Output

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX

Common Pitfalls

Common mistakes when creating indexes on partitioned tables include:

• Trying to create an index only on the parent table without specifying columns correctly.
• Not creating indexes on new partitions if created after the parent index.
• Assuming indexes on the parent table automatically cover all future partitions without creating indexes on new partitions.

To avoid these, always create indexes on the parent table first, and create indexes on new partitions as they are added.

sql

/* Wrong: Creating index only on a partition, not parent */
CREATE INDEX idx_sales_2023_date ON sales_2023 (sale_date);

/* Right: Create index on parent to cover all partitions */
CREATE INDEX idx_sales_date ON sales (sale_date);

Quick Reference

Command	Description
CREATE INDEX idx_name ON parent_table (column);	Creates index on partitioned table and all partitions
CREATE INDEX idx_name ON partition_table (column);	Creates index on a single partition
DROP INDEX idx_name;	Drops index from parent or partition
ALTER TABLE parent_table ATTACH PARTITION new_partition;	Add new partition (create index separately if needed)

Key Takeaways

Create indexes on the parent partitioned table to automatically index all partitions.

New partitions added after index creation need their own indexes if not inherited automatically.

You can create indexes on individual partitions for special cases.

Always specify columns correctly when creating indexes on partitioned tables.

Check indexes on partitions after adding new partitions to maintain performance.