How to Use Partial Indexes in PostgreSQL

Key Insights

Partial indexes only index rows matching a WHERE clause, reducing index size by 50-95% while maintaining query performance for targeted queries
The query planner only uses partial indexes when your query’s WHERE clause logically implies the index’s WHERE condition—mismatches result in full table scans
Partial unique indexes enable conditional uniqueness constraints, like ensuring only one active record per user without blocking inactive duplicates

What Are Partial Indexes?

A partial index in PostgreSQL is an index built on a subset of rows in a table, defined by a WHERE clause. Unlike standard indexes that include every row, partial indexes only index rows that match specific conditions.

Here’s the fundamental difference:

-- Full index: indexes all 10 million rows
CREATE INDEX idx_orders_status ON orders(status);

-- Partial index: only indexes ~100k pending orders
CREATE INDEX idx_orders_pending ON orders(status)
WHERE status = 'pending';

The partial index achieves the same query performance for pending orders while consuming a fraction of the storage and maintenance overhead. If only 1% of your orders are pending, the partial index is 99% smaller than the full index.

Use partial indexes when you consistently query a small, well-defined subset of your data. They’re particularly effective for skewed data distributions where most queries target a minority of rows—active users in a mostly inactive user base, unprocessed items in a queue, or recent records in historical data.

Common Use Cases for Partial Indexes

Indexing Active Records

The most common pattern is indexing only active, current, or undeleted records:

-- User table with soft deletes
CREATE INDEX idx_active_users_email ON users(email)
WHERE deleted_at IS NULL;

-- Subscription table
CREATE INDEX idx_active_subscriptions ON subscriptions(user_id, plan_id)
WHERE status = 'active';

If 95% of your users are active, this partial index is 95% smaller than indexing all users. More importantly, write operations on deleted users don’t update the index.

Indexing Recent Data

For time-series data where queries predominantly target recent records:

-- Only index last 90 days of logs
CREATE INDEX idx_recent_logs ON application_logs(created_at, severity)
WHERE created_at > CURRENT_DATE - INTERVAL '90 days';

-- Recent orders for dashboard queries
CREATE INDEX idx_recent_orders ON orders(customer_id, created_at)
WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

These indexes stay small and fast regardless of total table size. The tradeoff is that queries for older data won’t use the index.

Indexing Non-Null Values

When a column is mostly NULL but you query non-NULL values:

-- Only 2% of products have a discount
CREATE INDEX idx_discounted_products ON products(discount_percentage)
WHERE discount_percentage IS NOT NULL;

-- Only completed orders have a completion timestamp
CREATE INDEX idx_completed_orders ON orders(completed_at)
WHERE completed_at IS NOT NULL;

Measuring the Impact

Compare index sizes to quantify the benefit:

-- Create both indexes
CREATE INDEX idx_orders_full ON orders(status);
CREATE INDEX idx_orders_pending ON orders(status) WHERE status = 'pending';

-- Compare sizes
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'orders'
    AND indexname LIKE 'idx_orders_%';

On a table with 10 million orders (100k pending), you might see:

indexname              | size
-----------------------+-------
idx_orders_full        | 214 MB
idx_orders_pending     | 2208 kB

The partial index is 100x smaller.

Creating and Managing Partial Indexes

Basic Syntax

The WHERE clause follows standard SQL syntax and can reference any columns in the table:

-- Simple equality
CREATE INDEX idx_name ON table(column) WHERE status = 'active';

-- Multiple conditions
CREATE INDEX idx_name ON table(col1, col2)
WHERE status = 'active' AND deleted_at IS NULL;

-- Date ranges
CREATE INDEX idx_name ON table(created_at)
WHERE created_at >= '2024-01-01';

-- Complex expressions
CREATE INDEX idx_name ON table(user_id)
WHERE status IN ('pending', 'processing') AND priority > 5;

Verifying Index Usage

Always verify the query planner uses your partial index:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

Look for your index name in the output:

Index Scan using idx_orders_pending on orders
    (cost=0.29..8.31 rows=1 width=...)
    Index Cond: (status = 'pending'::text)

If you see a “Seq Scan” or different index, the planner isn’t using your partial index.

Managing Partial Indexes

Drop and recreate like any index:

-- Drop
DROP INDEX idx_orders_pending;

-- Recreate with CONCURRENTLY to avoid blocking writes
CREATE INDEX CONCURRENTLY idx_orders_pending ON orders(status)
WHERE status = 'pending';

Use CONCURRENTLY in production to build indexes without locking the table for writes.

Performance Considerations

Index Size and Maintenance

Smaller indexes mean faster writes. Every INSERT, UPDATE, or DELETE that affects indexed columns must update the index. With a partial index, writes to rows outside the WHERE condition don’t touch the index:

-- This UPDATE doesn't touch idx_orders_pending
UPDATE orders SET customer_notes = 'Updated'
WHERE status = 'completed';

-- This UPDATE does touch the index
UPDATE orders SET priority = 1
WHERE status = 'pending';

Query Planner Behavior

The planner only uses a partial index when it can prove your query’s WHERE clause implies the index’s WHERE condition. This is strict logical implication:

CREATE INDEX idx_high_priority ON tasks(assigned_to)
WHERE priority >= 7;

-- Uses the index: priority = 10 implies priority >= 7
SELECT * FROM tasks WHERE priority = 10 AND assigned_to = 123;

-- Doesn't use the index: priority >= 5 doesn't imply priority >= 7
SELECT * FROM tasks WHERE priority >= 5 AND assigned_to = 123;

Benchmark Example

Compare query performance with realistic data:

-- Setup: 10M rows, 100k pending
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Full index
CREATE INDEX idx_full ON orders(status, created_at);

-- Partial index
CREATE INDEX idx_partial ON orders(status, created_at)
WHERE status = 'pending';

-- Benchmark
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;

Results with partial index:

Planning Time: 0.123 ms
Execution Time: 1.456 ms
Shared Buffers: 24 hits

Results with full index:

Planning Time: 0.145 ms
Execution Time: 1.512 ms
Shared Buffers: 156 hits

Similar execution times, but the partial index reads far fewer buffers and consumes 100x less storage.

Advanced Patterns and Best Practices

Partial Unique Indexes

Enforce uniqueness only for specific rows:

-- Only one active session per user
CREATE UNIQUE INDEX idx_active_sessions ON user_sessions(user_id)
WHERE ended_at IS NULL;

-- Only one primary email per user
CREATE UNIQUE INDEX idx_primary_emails ON user_emails(user_id)
WHERE is_primary = true;

This allows multiple inactive sessions or non-primary emails per user while enforcing the business rule for active/primary records.

Expression-Based WHERE Clauses

Combine partial indexes with functional conditions:

-- Index only long comments
CREATE INDEX idx_long_comments ON comments(post_id)
WHERE LENGTH(body) > 500;

-- Index only business hours activity
CREATE INDEX idx_business_hours ON events(user_id, created_at)
WHERE EXTRACT(HOUR FROM created_at) BETWEEN 9 AND 17;

-- Index only valid email addresses
CREATE INDEX idx_valid_emails ON users(email)
WHERE email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';

Multiple Complementary Partial Indexes

Create several partial indexes that together cover all cases:

-- Separate indexes for each status
CREATE INDEX idx_orders_pending ON orders(customer_id, created_at)
WHERE status = 'pending';

CREATE INDEX idx_orders_processing ON orders(customer_id, created_at)
WHERE status = 'processing';

CREATE INDEX idx_orders_completed ON orders(customer_id, created_at)
WHERE status = 'completed';

This works well when different statuses have vastly different row counts and query patterns.

Combining with Other Index Types

Partial indexes work with any index type:

-- Partial GIN index for full-text search on published posts
CREATE INDEX idx_published_posts_search ON posts USING GIN(to_tsvector('english', body))
WHERE published_at IS NOT NULL;

-- Partial GiST index for geospatial queries on active locations
CREATE INDEX idx_active_locations ON stores USING GIST(location)
WHERE status = 'active';

Common Pitfalls and Troubleshooting

WHERE Clause Mismatches

The most common issue is query conditions that don’t match the index:

CREATE INDEX idx_active_users ON users(email)
WHERE active = true;

-- Uses the index ✓
SELECT * FROM users WHERE active = true AND email = 'user@example.com';

-- Doesn't use the index ✗ (active IS NOT FALSE doesn't imply active = true)
SELECT * FROM users WHERE active IS NOT FALSE AND email = 'user@example.com';

-- Doesn't use the index ✗ (no active condition)
SELECT * FROM users WHERE email = 'user@example.com';

Always include the exact index condition in your queries.

Type Mismatches

Ensure data types match exactly:

CREATE INDEX idx_status ON orders(customer_id)
WHERE status = 'pending';

-- Doesn't use the index if status is an enum type
-- Use: WHERE status = 'pending'::order_status_enum

Parameter vs. Constant Conditions

The planner handles constants and parameters differently:

CREATE INDEX idx_recent ON logs(severity)
WHERE created_at > CURRENT_DATE - INTERVAL '7 days';

-- Uses the index (constant evaluation)
SELECT * FROM logs
WHERE created_at > CURRENT_DATE - INTERVAL '7 days'
    AND severity = 'ERROR';

-- Might not use the index (parameter)
PREPARE stmt AS
SELECT * FROM logs
WHERE created_at > $1 AND severity = 'ERROR';

For time-based partial indexes, use constant expressions rather than parameters when possible.

Monitoring Index Usage

Identify unused partial indexes:

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexname LIKE 'idx_%'
    AND idx_scan < 100
ORDER BY pg_relation_size(indexrelid) DESC;

Drop indexes with zero scans after sufficient observation time. Partial indexes should show clear usage patterns aligned with their WHERE conditions.

Partial indexes are a powerful optimization for queries targeting specific data subsets. Start with your most common filtered queries, measure index sizes before and after, and always verify planner usage with EXPLAIN. When used correctly, they deliver full index performance at a fraction of the cost.