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.

Creating a local index is straightforward. You simply create the index on the parent partitioned table. PostgreSQL handles the creation of individual indexes for each child partition automatically.

Try running this example:

Local indexes shine when queries involve the partition key. Here's why:

• Partition Pruning: PostgreSQL's query planner can quickly identify and scan only the relevant partitions based on your query's `WHERE` clause.
• Smaller Indexes: Each local index is smaller, containing only a subset of the data, leading to faster lookups within a partition.
• Reduced I/O: Less data needs to be read from disk, improving query speed.

A global index, unlike a local index, spans across all partitions of a partitioned table. It's a single, monolithic index that acts much like an index on a regular, non-partitioned table.

• It's not tied to any specific partition.
• Updates to any partition affect the single global index.
• They are less common for general query optimization on partitioned tables.

To create a global index, you typically use the ONLY keyword when specifying the parent table. This tells PostgreSQL to create the index directly on the parent, not implicitly on each child.

Global indexes are often used for enforcing unique constraints across the entire partitioned table on columns that are not the partition key.

Try running this example:

While global indexes have their uses, they come with trade-offs:

• Maintenance Overhead: Inserts, updates, or deletes to any partition require updates to the single global index, which can be slower.
• No Partition Pruning Benefit: Queries using a global index don't benefit from partition pruning on the index itself, as it spans all data.
• Use Cases: Best for unique constraints on non-partition key columns, or queries that frequently access non-partition key columns across the entire dataset.

Choosing between local and global indexes depends on your workload:

• Local Indexes: Ideal when queries frequently filter data using the partition key (e.g., date ranges, specific categories). They leverage partition pruning for speed.
• Global Indexes: Consider for enforcing unique constraints on columns that are not the partition key, or for queries that frequently access non-partition key columns across the entire table.

Most often, local indexes are the preferred choice for performance on partitioned tables.

Imagine an orders table partitioned by order_date.

• A local index on order_date would be highly efficient for queries like WHERE order_date BETWEEN X AND Y because of partition pruning.
• A global index on customer_id would be efficient for WHERE customer_id = Z across all orders, especially if you need to find all orders for a customer regardless of date.

The key is aligning your index strategy with your most common query patterns.

Consider a large sales table partitioned by sale_date. Which index type is generally more efficient for queries like SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31' AND product_id = 123;?

In this lesson, we explored how indexes interact with partitioned tables. You learned about:

• Local indexes: Created per partition, ideal for queries using the partition key, benefiting from partition pruning.
• Global indexes: Span all partitions, useful for unique constraints or queries on non-partition key columns across the whole table, but with more maintenance overhead.

Mastering the interplay between partitioning and indexing is key to unlocking high performance in large-scale PostgreSQL databases.