Advanced Indexing Strategies: Composite and Unique Indexes

Previously in this course, we covered the fundamentals of B-tree structures in Understanding Indexes: A Guide to Database Optimization. While a simple index on a single column works wonders for basic lookups, real-world store applications often require filtering by multiple criteria. This lesson adds composite indexes and unique indexes to your toolkit, helping you optimize query performance for multi-column conditions.

Scaling Beyond Single Columns with Composite Indexes

A composite index is an index on two or more columns of a table. Think of it like a library catalog: if you search by "Author" and then "Title," it’s much faster than searching through all books by an author and manually checking titles.

In our store application, we often query orders based on a customer's ID and the date the order was placed. Without a composite index, PostgreSQL might use one index and then filter the results manually, which is inefficient for large datasets.

Worked Example: Creating a Composite Index

Suppose we frequently run this query:

SQL
SELECT * FROM orders 
WHERE customer_id = 123 
AND order_date = '2023-10-27';

A standard index on customer_id will narrow the search, but if a customer has thousands of orders, the database still has to scan those rows to find the specific date. We create a composite index to solve this:

SQL
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, order_date);

Key Principle: The order of columns in a composite index matters. PostgreSQL can use this index for a query that filters by customer_id alone, or by both customer_id and order_date. However, it cannot efficiently use this index if you only filter by order_date because the index is sorted primarily by customer_id. Always place the most selective or most frequently used column first.

Ensuring Data Integrity with Unique Indexes

We previously looked at Applying NOT NULL and UNIQUE Constraints, but it’s worth noting that a unique constraint in PostgreSQL is actually implemented using a unique index.

A unique index ensures that no two rows have the same value (or combination of values) in the indexed columns. This is essential for business logic, such as ensuring a user cannot have two active shopping carts.

SQL
-- Creating a unique index on a single column
CREATE UNIQUE INDEX idx_unique_email ON customers (email);

-- Creating a unique index on multiple columns
-- This prevents a customer from having two orders with the same external reference code
CREATE UNIQUE INDEX idx_unique_customer_order_ref 
ON orders (customer_id, reference_code);

Understanding Index Overhead

Every index you add to your database comes with a cost. While indexes significantly speed up SELECT operations, they slow down INSERT, UPDATE, and DELETE operations. This is because every time you modify the table, PostgreSQL must also update the index structure to keep it in sync.

When considering Index Maintenance and Trade-offs: Optimizing for Performance, remember these rules of thumb:

1. Don't over-index: Only create indexes for columns you actually filter or join on.
2. Measure, don't guess: If you have a table with 10 rows, an index is overkill. Use indexes when you have enough data that a full table scan becomes noticeable.
3. The "Write" tax: If your table is "write-heavy" (constantly receiving new data), keep your index count to a minimum to avoid performance degradation.

Hands-on Exercise

To advance our store project, let's optimize the order_items table, which links orders to products.

1. Create a composite index on order_items to speed up lookups for a specific order's items:

   SQL
   CREATE INDEX idx_order_items_order_id ON order_items (order_id);
   

2. If you frequently check for specific products within an order, refine it:

   SQL
   DROP INDEX idx_order_items_order_id;
   CREATE INDEX idx_order_items_order_product 
   ON order_items (order_id, product_id);
   

3. Reflect: Why would you choose a composite index over two separate indexes here?

Common Pitfalls

• Redundant Indexes: Creating an index on (col1, col2) and another on (col1) is often redundant. The composite index already covers the prefix.
• Indexing low-cardinality columns: Indexing a boolean column (e.g., is_active) often doesn't help because the index doesn't filter out enough data to be faster than a sequential scan.
• Ignoring Column Order: As mentioned, (col1, col2) is not the same as (col2, col1). Always analyze your query patterns before deciding the column sequence.

FAQ

Q: Can I have a composite index with three columns? A: Yes, you can include multiple columns in a single index. However, keep in mind that the index size increases with every column added.

Q: Does a unique index behave differently than a normal index? A: Performance-wise, they are similar, but a unique index imposes a constraint that prevents duplicate data, which is vital for maintaining Database Documentation Standards: Mastering SQL Comments and data consistency.

Q: When should I delete an index? A: If you find an index is rarely used by your queries, remove it to reduce the overhead on your write operations.

Recap

• Composite indexes allow for efficient multi-column filtering.
• Unique indexes enforce data integrity by preventing duplicates.
• Indexes come with a performance tax on write operations; balance your read needs against your write volume.
• Column order is the most critical factor when designing a composite index.

Up next: We will explore how to manage unique identifiers and gaps in your data using PostgreSQL sequences.