Partial Index for Active Rows Only

Most tables have a small set of rows that the application asks about all day, and a large pile of finished rows that nobody reads. Open carts, unpaid invoices, pending jobs, active accounts. A normal index covers all of them, and the big pile makes it large and slow to maintain. A partial index covers only the rows that match a condition, so it is small, and the queries that need it get fast. This page builds one, measures it, shows a query that cannot use it, and uses the same idea to enforce a rule that a normal unique index cannot.

Step 1: a table where 2% of the rows are active

CREATE TABLE carts AS
SELECT g AS cart_id,
       (g % 5000) + 1 AS customer_id,
       CASE WHEN g > 19600 THEN 'open'
            WHEN g % 2 = 0 THEN 'checked_out'
            ELSE 'abandoned' END AS status,
       DATE '2026-01-01' + (g % 300) AS created_at
FROM generate_series(1, 20000) AS g;
ALTER TABLE carts ADD PRIMARY KEY (cart_id);
SELECT status, count(*) FROM carts GROUP BY status ORDER BY status;

SELECT 20000
ALTER TABLE
   status    | count
-------------+-------
 abandoned   |  9800
 checked_out |  9800
 open        |   400
(3 rows)

There are 400 open carts and 19,600 closed ones. The application only looks up the open cart of a customer.

Step 2: compare a full index with a partial one

The two indexes cover the same column. The second one adds a WHERE clause.

CREATE INDEX carts_customer_full ON carts (customer_id);
CREATE INDEX carts_customer_open ON carts (customer_id) WHERE status = 'open';
ANALYZE carts;
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('carts_customer_full', 'carts_customer_open')
ORDER BY relname;

CREATE INDEX
CREATE INDEX
ANALYZE
       relname       |  size
---------------------+--------
 carts_customer_full | 256 kB
 carts_customer_open | 32 kB
(2 rows)

The partial index is one eighth of the size here, because it holds 400 rows instead of 20,000. Every insert, update and delete of a closed cart now skips that index, and a smaller index stays in memory more easily.

Step 3: the query has to match the condition

Drop the full index so that the partial one is the only choice, and ask for the open cart of a customer.

DROP INDEX carts_customer_full;
SET max_parallel_workers_per_gather = 0;
EXPLAIN (COSTS OFF) SELECT * FROM carts WHERE customer_id = 4700 AND status = 'open';
EXPLAIN (COSTS OFF) SELECT * FROM carts WHERE customer_id = 4700;

DROP INDEX
SET
                  QUERY PLAN
-----------------------------------------------
 Index Scan using carts_customer_open on carts
   Index Cond: (customer_id = 4700)
(2 rows)

           QUERY PLAN
--------------------------------
 Seq Scan on carts
   Filter: (customer_id = 4700)
(2 rows)

The first query repeats the index condition, so PostgreSQL can prove that the index holds every row it needs and uses it. The second does not mention the status, so any cart of that customer could be the answer, and the partial index cannot cover it: a sequential scan. The rule is simple. The planner can use a partial index only when it can prove that the query's WHERE implies the index's WHERE.

Step 4: the trap with parameters

Applications send prepared statements, and the status often arrives as a parameter. For a generic plan, which is made without knowing the parameter value, PostgreSQL cannot prove that status = $2 implies status = 'open'. In the default mode it makes the first five plans with the real values and may switch to a generic plan after that. Force the generic plan to see it right away.

SET plan_cache_mode = force_generic_plan;
PREPARE open_cart(int, text) AS
  SELECT * FROM carts WHERE customer_id = $1 AND status = $2;
EXPLAIN (COSTS OFF) EXECUTE open_cart(4700, 'open');
PREPARE open_cart_fixed(int) AS
  SELECT * FROM carts WHERE customer_id = $1 AND status = 'open';
EXPLAIN (COSTS OFF) EXECUTE open_cart_fixed(4700);

SET
PREPARE
                    QUERY PLAN
--------------------------------------------------
 Seq Scan on carts
   Filter: ((customer_id = $1) AND (status = $2))
(2 rows)

PREPARE
                  QUERY PLAN
-----------------------------------------------
 Index Scan using carts_customer_open on carts
   Index Cond: (customer_id = $1)
(2 rows)

The first form falls back to a sequential scan. The second form writes 'open' as a literal in the query and keeps the index. When a partial index exists for one status, write that status in the query text, and keep the parameters for the values that change.

Step 5: a unique rule for the active rows only

The same condition works on a unique index. "A customer can have only one open cart, and any number of closed ones" cannot be a normal unique constraint on the customer, but it is a unique partial index.

\set VERBOSITY terse
CREATE UNIQUE INDEX one_open_cart ON carts (customer_id) WHERE status = 'open';
INSERT INTO carts VALUES (20001, 4700, 'open', DATE '2026-10-01');
INSERT INTO carts VALUES (20002, 4700, 'checked_out', DATE '2026-10-01');

CREATE INDEX
ERROR:  duplicate key value violates unique constraint "one_open_cart"
INSERT 0 1

The second open cart for customer 4700 is rejected, and the closed one is accepted. The database now enforces the rule for every client, which an application check cannot do when two requests arrive together.

Related course: SQL Advanced.