The Database Index Type You Have Never Used but Should

Most engineers know three types of database indexes: B-tree (the default), hash, and maybe GIN for full-text search. They create an index on a column and assume the database will figure out the rest.

There are two index types that almost no one uses despite being available in Postgres since version 8.0. Both are strictly better than standard B-tree indexes in specific scenarios that are extremely common.

Partial Indexes

A partial index only indexes rows that satisfy a WHERE condition. The entire index is smaller, faster to scan, and faster to update.

The classic use case: a orders table with 50 million rows, but you almost always query for status = 'pending' and there are only 500 pending orders at any given time.

Standard index:

CREATE INDEX idx_orders_status ON orders(status);
-- Indexes all 50 million rows
-- Size: ~3GB
-- Scan time: proportional to all orders with a given status

Partial index:

CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Indexes only 500 rows
-- Size: ~15KB
-- Scan time: microseconds

The query that benefits:

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 20;
-- With partial index: index scan on 500 rows, sorted, done
-- Without: full index scan on potentially millions of rows

The partial index is also faster to maintain. Every insert, update, and delete that touches the status column requires updating the standard index for all rows. The partial index only updates when a row enters or leaves the pending status.

Where Partial Indexes Win

The pattern is: if your important queries always filter on the same high-cardinality condition, make that condition part of the index definition, not a post-index filter.

Expression Indexes

Expression indexes index the result of a function applied to a column. They enable index scans on queries that use expressions or functions on indexed columns.

The common failure mode:

-- You have an index on email
CREATE INDEX idx_users_email ON users(email);

-- This query uses the index
SELECT * FROM users WHERE email = '[email protected]';

-- This query does NOT use the index - Postgres cannot use a B-tree
-- index when the column is wrapped in a function
SELECT * FROM users WHERE lower(email) = lower('[email protected]');

The second query does a sequential scan even though an index exists. The solution:

CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Now this query uses the index
SELECT * FROM users WHERE lower(email) = lower('[email protected]');

Common Expression Index Patterns

Case-Insensitive Email Lookups

CREATE UNIQUE INDEX idx_users_email_ci ON users(lower(email));
-- Combined: unique constraint + case-insensitive lookup

Date-Based Queries on Timestamps

-- Index the date portion of a timestamp
CREATE INDEX idx_events_date ON events(date_trunc('day', created_at));

-- Query uses the index
SELECT * FROM events
WHERE date_trunc('day', created_at) = '2025-01-15';

JSONB Field Extraction

-- Index a specific JSON field
CREATE INDEX idx_configs_env ON configs((data->>'environment'));

-- Query uses the index
SELECT * FROM configs WHERE data->>'environment' = 'production';

Computed Status Flags

-- Index whether a subscription is expired
CREATE INDEX idx_subscriptions_expired
ON subscriptions((expires_at < NOW()))
WHERE expires_at < NOW();

-- Combines expression index with partial index

The Covering Index Pattern

One more underused type: covering indexes include extra columns to avoid a heap lookup entirely.

-- Standard index on user_id
CREATE INDEX idx_orders_user ON orders(user_id);

-- Query requires heap lookup to get status and total
SELECT status, total FROM orders WHERE user_id = 123;

-- Covering index - includes status and total
CREATE INDEX idx_orders_user_covering ON orders(user_id) INCLUDE (status, total);

-- Now the query is satisfied entirely from the index - no heap lookup

Covering indexes are most valuable for read-heavy, frequently-executed queries. The tradeoff is increased index size and write overhead.

How to Audit for Missing Partial Indexes

Run this query on your Postgres database to find tables with selective column filters that might benefit from partial indexes:

SELECT
    schemaname,
    tablename,
    attname AS column_name,
    n_distinct,
    correlation
FROM pg_stats
WHERE n_distinct > 0 AND n_distinct < 20
    AND tablename IN (
        SELECT relname FROM pg_stat_user_tables
        WHERE seq_scan > 1000 AND n_live_tup > 100000
    )
ORDER BY n_distinct;

This finds large tables with sequential scans and columns with low cardinality - the classic signal that a partial index would help.

Bottom Line

Partial indexes and expression indexes are production-ready, battle-tested features of every major relational database. They are ignored because the default index works and the need for optimization is not obvious until queries are slow.

Audit your most-queried tables for soft delete columns, status fields with common values, and queries that wrap indexed columns in functions. In most production databases with significant query volume, you will find multiple opportunities where these index types cut query time by an order of magnitude.