PostgreSQL Optimization: Using Partial and Covering Indexes to Reduce Storage by 90% and Speed Up Queries – ITFROMZERO

Table of Contents

Context: When Traditional Indexes Become a Burden

In database management, we often face a paradox: the more we want fast queries, the more we tend to create indexes. However, excessive indexing slows down INSERT/UPDATE operations while causing storage to balloon rapidly. After over 6 months of operating an e-commerce system with an orders table reaching 150 million records, I realized that standard B-Tree indexes were no longer efficient enough.

Previously, I used to create indexes on entire columns out of habit from MySQL. But with PostgreSQL, index customization is much more flexible. Two “golden” techniques that helped me solve performance issues are Partial Indexes and Covering Indexes.

Real-world issues I faced:

Resource waste: Indexing old, rarely queried data (like canceled orders from 3 years ago).
I/O Bottleneck: The database finds the key in the index but still needs an extra step to access the main table (Heap) to retrieve data, causing delays.

Partial Indexes: Indexing Only What You Truly Need

Concept and Benefits

A Partial Index allows you to scope the data to be indexed using a WHERE clause. Instead of indexing all 150 million rows, you can focus on only the 1-2% of data that is actually “hot”.

This technique is extremely effective in two scenarios:

Skewed Data: Suppose a users table has 10 million rows but only 1% are unverified. If you only need to find this group to send reminder emails, just index the unverified users.
Eliminating NULL values: Saves massive amounts of space if a column contains mostly empty data.

Practical Implementation

Consider a tasks table. I’m only interested in tasks currently being processed (processing), while finished tasks (finished) are rarely touched.

-- Only index processing tasks to save memory
CREATE INDEX idx_tasks_processing 
ON tasks (created_at) 
WHERE status = 'processing';

When running a query, PostgreSQL will automatically use this index:

SELECT * FROM tasks 
WHERE status = 'processing' 
ORDER BY created_at DESC;

Important note: Your SQL statement must include the WHERE status = 'processing' condition. If missing, the Postgres Planner will ignore the index and switch to a Full Table Scan (Seq Scan).

Covering Indexes: Achieving the Index-Only Scan State

The Power of the INCLUDE Keyword

Typically, an index only stores key columns. When you query a column not included in the index, the database must perform a “Heap Fetch” to retrieve data from the original table. This step consumes I/O resources and slows down response times.

Covering Index (supported since PostgreSQL 11) allows you to attach additional data to the index via the INCLUDE keyword. The goal is to achieve an Index-Only Scan: retrieving everything directly from the index without touching the main table.

Practical Example

I once optimized a log system with a very high query frequency for user_id and action_code based on time.

-- Covering Index with INCLUDE
CREATE INDEX idx_logs_time_covering 
ON logs (created_at) 
INCLUDE (user_id, action_code);

Why is this approach smart? In idx_logs_time_covering:

created_at is used for sorting and searching (Search Key).
user_id and action_code are just “passengers” (Payload).

Since the payload isn’t used for sorting, the index remains more compact than if all three columns were primary keys. INSERT speed is also significantly faster.

Combining Both: A Case Study in Reducing Storage by 90%

In a shipping management project, I combined both to retrieve information for orders currently being shipped:

CREATE INDEX idx_orders_shipping_fast_track
ON orders (customer_id)
INCLUDE (total_amount, shipping_address)
WHERE status = 'shipping';

The results were surprising. Index size dropped from 12GB to 800MB. Query speed plummeted from 500ms to under 10ms. These are actual numbers I measured in a production environment after 6 months of stable operation.

Monitoring and Measuring Effectiveness

Don’t assume the index will be used immediately. I always use EXPLAIN ANALYZE to verify the Planner’s decisions.

EXPLAIN ANALYZE 
SELECT total_amount FROM orders 
WHERE status = 'shipping' AND customer_id = 12345;

If you see the Index Only Scan line, you’ve succeeded. Additionally, regularly check the pg_stat_user_indexes view. If an index has idx_scan at zero after a week, don’t hesitate to “decommission” it to free up resources.

SELECT relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE schemaname = 'public';

Database optimization is a continuous journey. Understanding Partial and Covering Indexes will help you solve complex performance puzzles without the cost of hardware upgrades.