Partial indexes let you stop scanning the whole table.
Here’s a CockroachDB cluster running a simple key-value workload. We’ve got a table users with a city column and an is_active boolean.
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
city STRING,
is_active BOOL DEFAULT true,
created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO users (name, city, is_active) VALUES
('Alice', 'New York', true),
('Bob', 'London', false),
('Charlie', 'New York', true),
('David', 'Paris', true),
('Eve', 'London', true);
Now, let’s say we frequently query for active users in "New York". Without a partial index, CockroachDB would have to scan all rows in the users table, check if city is 'New York', and then check if is_active is true.
-- This query can be slow without a partial index
SELECT id, name FROM users WHERE city = 'New York' AND is_active = true;
A standard index on (city, is_active) would speed this up, but it still includes rows where is_active is false, which we don’t care about for this specific query.
A partial index, however, only includes rows that satisfy a specific condition. We can create an index that only contains active users:
CREATE INDEX active_users_in_new_york
ON users (city)
STORING (name)
WHERE is_active = true AND city = 'New York';
Now, when we run our query:
SELECT id, name FROM users WHERE city = 'New York' AND is_active = true;
CockroachDB can use active_users_in_new_york. This index is significantly smaller than a full index on (city, is_active) because it only stores entries for users who are both active and in New York. The STORING (name) clause is crucial here: it adds the name column to the index, allowing the query to be fully covered by the index, meaning CockroachDB doesn’t need to go back to the main table to fetch name. This is called a "covering index."
The magic of partial indexes lies in their selectivity. By pre-filtering data at the index creation stage based on your query predicates, you drastically reduce the amount of data the database needs to inspect. For queries that target a specific subset of your data, a partial index can be orders of magnitude faster and more storage-efficient than a general-purpose index. Think of it as a highly specialized filing system where only the most relevant documents are stored.
The WHERE clause in the CREATE INDEX statement is the key. It acts as a filter, dictating which rows are included in the index. This filter is applied during index maintenance, ensuring that only qualifying rows are ever added or updated within the index itself.
Consider a scenario with a large orders table where you frequently query for status = 'shipped' and delivery_date IS NOT NULL. A partial index would look like this:
CREATE INDEX shipped_orders_with_delivery
ON orders (order_date)
STORING (order_id, customer_id)
WHERE status = 'shipped' AND delivery_date IS NOT NULL;
This index only contains entries for orders that have already been shipped and have a recorded delivery date. If your query is SELECT order_id FROM orders WHERE status = 'shipped' AND delivery_date IS NOT NULL ORDER BY order_date DESC, CockroachDB will use this index, avoiding a full table scan and potentially even a secondary lookup for order_id and customer_id if they are also stored.
The STORING clause is not strictly required for a partial index to function, but it’s essential for achieving index-only scans (covering indexes). If a query can be satisfied entirely by the data within the index (including the STORING columns), CockroachDB won’t need to perform a lookup into the main table data, which is a significant performance win.
The most common pitfall is creating a partial index that is too broad, defeating its purpose. The WHERE clause must be specific enough to significantly reduce the number of rows indexed. For example, WHERE is_active = true might still be too broad if 90% of your users are active. The goal is to index only the tail of the data distribution that your queries care about.
The next step is often dealing with complex joins involving tables that have these specialized, partial indexes.