In modern application development, database performance can make or break user experience.
When working with large datasets, database indexing plays a crucial role in improving query performance. But sometimes, you don’t want to index every row — only a subset that matters for your queries. That’s where partial indexes shine.
Here, at Ipsator, we’ve successfully implemented partial indexing across multiple projects, achieving significant performance improvements while reducing storage costs. This guide explores what partial indexes are, when to use them, and how to implement them in PostgreSQL and MySQL.
What Are Partial Indexes?
A partial index is an index built on a subset of table rows that satisfy a specific condition. Instead of indexing every row in a table, you define a WHERE clause that determines which rows get indexed.
Why Partial Indexes Are Useful:
Reduce index size
Improve query performance
Reduce index maintenance overhead
Allow condition-based uniqueness
Partial indexes are especially powerful when only 5–30% of a table’s rows are frequently queried.
PostgreSQL has native support for partial indexes with a simple, elegant syntax.
Basic Syntax
CREATE INDEX index_name
ON table_name (column1, column2, ...)
WHERE condition;1. Status-Based Filtering
The most common use case involves tables with status columns where you primarily query for CONFIRMED, PENDING, CANCELLED, and FAILED records:
CREATE TABLE bookings (
id SERIAL PRIMARY KEY,
customer_id BIGINT,
status VARCHAR(20),
booking_date TIMESTAMP,
amount_payable NUMERIC(10, 2)
);
Now, suppose most of your queries filter only on confirmed bookings:
SELECT * FROM bookings WHERE status = 'CONFIRMED';
Instead of creating a full index on the status column, you can create a partial index only for confirmed ones
CREATE INDEX idx_active_bookings
ON bookings (booking_date, customer_id)
WHERE status = 'CONFIRMED';
Benefits
Reduces index size
Improves performance for targeted queries
Saves index update cost for other statuses
2. Conditional Uniqueness
Sometimes, you want unique constraints only for a subset of rows.
Example: A seat must be unique only when the booking is confirmed, but duplicates are fine in the PENDING or FAILED state.
CREATE TABLE booking_seats (
id SERIAL PRIMARY KEY,
booking_id BIGINT,
booking_seat_status VARCHAR(20),
created_at TIMESTAMP,
seat_id BIGINT
);
Ensure that a seat is assigned only once for confirmed bookings:
CREATE UNIQUE INDEX idx_seat_id_confirmed_booking_seat
ON booking_seats (seat_id)
WHERE booking_seat_status = 'CONFIRMED';
Prevents two confirmed bookings from using the same seat.
Still allows:
PENDING reservations (multiple users can select the same seat but not finalise)
FAILED attempts
CANCELLED bookings
This pattern is common in ticketing, seat allocation, and reservation systems.
3. Soft Deletes
Most apps use soft deletes for auditability — they use deleted_at, is_deleted flags.
But when querying, you typically fetch only active (non-deleted) users.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
mobile VARCHAR(10),
username VARCHAR(100),
deleted_at TIMESTAMP NULL
);
Index only active users
CREATE INDEX idx_active_users
ON users (mobile)
WHERE deleted_at IS NULL;
Benefits
Most applications never query deleted users.
No need to index irrelevant rows.
Partial indexes are not always beneficial. Avoid them when:
The WHERE clause matches most rows (>70–80%)
The filter condition changes frequently
Writes heavily outweigh reads (partial indexes add write overhead)
You need the index for many types of queries, not just filtered ones
Rule of thumb:
Partial indexes are best when the filtered subset is small and stable.
MySQL does not natively support partial indexes, at least not the way PostgreSQL does. However, there are several workarounds that can achieve similar results.
-- Create bookings table
CREATE TABLE bookings (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
status VARCHAR(20),
booking_date DATETIME,
amount_payable DECIMAL(10,2),
INDEX idx_customer (customer_id)
) ENGINE=InnoDB;
Workaround 1: Generated Column + Index
ALTER TABLE bookings
ADD pending_flag TINYINT
AS (status = 'PENDING') STORED,
ADD INDEX idx_pending (pending_flag, customer_id);
This is the more reliable and optimizer-friendly approach for filtering queries. This technique improves readability and separates logic from the index definition.
The generated column pending_flag evaluates to 1 only when the row meets the condition
For all other rows, the value becomes 0, making them less relevant for the index lookup
More readable than using CASE in the index
Note that, unlike PostgreSQL, this method indexes all rows (zeros and ones), so it does not reduce storage size, but it significantly speeds up lookups.
-- Query using the index
SELECT *
FROM bookings
WHERE pending_flag = 1
AND customer_id = 12345;
Workaround 2: Functional Index Using CASE Expression (MySQL 8+)
This method uses MySQL’s support for functional indexes to index only a subset of rows.
It works well, especially for conditional uniqueness.
-- Emulate partial index using functional index
-- Rule: Ensure a customer can have only ONE 'PENDING' booking at a time
CREATE UNIQUE INDEX idx_pending_bookings
ON bookings ((CASE
WHEN status = 'PENDING'
THEN customer_id
ELSE NULL
END));
Limitation
MySQL’s optimizer does not always use this functional index for filtering queries because:
The query does not reference the exact expression used in the index.
MySQL does not automatically infer that status = ‘PENDING’ is equivalent to the CASE expression inside the index.
Best use case: Enforcing conditional uniqueness
Partial indexing is a powerful tool for optimizing database performance, especially in large-scale systems. While PostgreSQL offers native support, MySQL users can still achieve similar benefits using functional indexes, generated columns, and partitioning. At Ipsator, leveraging these strategies has consistently improved performance, reduced load, and optimized query response times across mission-critical applications.
Want to read more about how we think and build at Ipsator?
