PostgreSQL Partial Index

Key Partial Index Benefits

-- Only index unpaid orders
CREATE INDEX idx_unpaid 
ON orders(id) WHERE paid = false;

-- Index recent records only
CREATE INDEX idx_recent 
ON logs(created_at) WHERE created_at > '2024-01-01';

-- Index only premium users
CREATE INDEX idx_premium 
ON users(name) WHERE plan = 'premium';

-- Index non-deleted items
CREATE INDEX idx_active_products 
ON products(name) WHERE deleted_at IS NULL;

🔹 Creating a Partial Index

Partial indexes use a WHERE clause to specify which rows to index. This is perfect when you frequently query a specific subset of data, like active users or pending orders.

-- Create orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    status VARCHAR(20),
    total DECIMAL(10,2)
);

-- Index only pending orders (most frequently queried)
CREATE INDEX idx_pending_orders 
ON orders(user_id) 
WHERE status = 'pending';

-- This query uses the index
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'pending';

🔹 Partial Index for Active Records

A common use case is indexing only active or non-deleted records. This keeps your index small and fast since deleted records don't need to be indexed.

-- Create users table with soft delete
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100),
    deleted_at TIMESTAMP
);

-- Index only active (non-deleted) users
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE deleted_at IS NULL;

-- Fast lookup for active users
SELECT * FROM users 
WHERE email = '[email protected]' 
AND deleted_at IS NULL;

🔹 Partial Unique Index

Combine partial indexes with uniqueness constraints. This allows unique values only within a subset, like ensuring active users have unique emails but allowing reuse after deletion.

-- Unique email only for active users
CREATE UNIQUE INDEX idx_unique_active_email 
ON users(email) 
WHERE deleted_at IS NULL;

-- First user with this email
INSERT INTO users (email, name) 
VALUES ('[email protected]', 'John');

-- Soft delete the user
UPDATE users SET deleted_at = NOW() 
WHERE email = '[email protected]';

-- Now this works - email can be reused after deletion!
INSERT INTO users (email, name) 
VALUES ('[email protected]', 'John Smith');

🔹 Partial Index with Date Ranges

Index only recent data when older records are rarely queried. This is perfect for logs, events, or time-series data where you mostly query recent entries.

-- Create logs table
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Index only logs from the last 30 days
CREATE INDEX idx_recent_logs 
ON logs(user_id, created_at) 
WHERE created_at > NOW() - INTERVAL '30 days';

-- Fast query for recent logs
SELECT * FROM logs 
WHERE user_id = 123 
AND created_at > NOW() - INTERVAL '30 days';

🔹 Partial Index for Boolean Columns

When a boolean column is mostly one value, index only the minority. For example, if most orders are paid, index only unpaid orders for faster processing.

-- Most orders are paid, few are unpaid
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    paid BOOLEAN DEFAULT false,
    amount DECIMAL(10,2)
);

-- Index only unpaid orders (the minority)
CREATE INDEX idx_unpaid_orders 
ON orders(user_id) 
WHERE paid = false;

-- Fast query for unpaid orders
SELECT * FROM orders 
WHERE user_id = 123 AND paid = false;

🔹 When to Use Partial Indexes

Partial indexes are most effective when you query a specific subset of data frequently. They reduce index size and maintenance while improving query performance on targeted data.