Partial, Expression, GIN, GiST, and BRIN Indexes

Specialized PostgreSQL indexes exist because one B-tree over one stored column is not enough for many real workloads. A partial index indexes only rows that satisfy a predicate. An expression index stores the result of an expression rather than a base column. GIN, GiST, and BRIN are access methods with different internal shapes for composite values, geometric or extensible searches, and very large naturally ordered tables. The outcome of this lesson is practical: choose the index form that matches the predicate, operator, and data distribution, then prove the planner can use it.

How These Indexes Fit The Planner

Every PostgreSQL index is a separate relation maintained alongside the table. When a row is inserted, updated, or deleted, PostgreSQL updates the table heap and any affected index entries. The planner later estimates whether an index path is cheaper than a sequential scan using table statistics, index statistics, predicates, sort requirements, and the operators in the query. The index is not a hint. It is a structure the planner may choose when it can prove the query condition is compatible with the index definition and the estimated cost is favorable.

A partial index adds a Boolean predicate to the index definition. Only rows for which the predicate is true have entries. The planner can use the index only when the query’s WHERE clause implies that predicate. For example, an index with WHERE billed IS NOT TRUE is useful for a query that says billed IS NOT TRUE, but a parameterized query such as billed = $1 may not prove the predicate at plan time. Partial indexes reduce size and write cost when the indexed subset is small and queried often.

An expression index stores computed keys. PostgreSQL evaluates the expression during writes and stores the value in the index. A query can use it when the same expression appears in a compatible form, such as lower(email). Expression indexes are common for case-insensitive lookup, date bucketing, JSON extraction, and normalized search keys. The trade-off is that writes pay the expression cost, and changes to functions used by the expression can require rebuilding the index.

GIN, short for Generalized Inverted Index, maps elements inside a composite value to posting lists of row locations. It is strong for arrays, JSONB containment, full-text search vectors, and other cases where a single row contains many searchable keys. GiST, short for Generalized Search Tree, is a balanced tree framework where operator classes define how to compress values, split pages, and test bounding relationships. It powers geometric, range, nearest-neighbor, and exclusion-constraint use cases. BRIN, short for Block Range Index, stores summaries for ranges of heap pages, such as minimum and maximum values. It is tiny and fast to maintain, but only selective when table order correlates with the searched value.

Syntax Anatomy

The shared shape is CREATE INDEX name ON table USING method (key_definition) WHERE predicate. The method defaults to B-tree. A key definition can be a column, an expression in parentheses, an operator class, sort direction, or storage parameters. Partial predicates appear after WHERE. GIN, GiST, and BRIN are selected with USING gin, USING gist, and USING brin. CREATE INDEX CONCURRENTLY avoids blocking ordinary writes during the build, but it cannot run inside a transaction block and it takes longer.

CREATE INDEX idx_orders_unbilled_customer
ON orders (customer_id, order_date DESC)
WHERE billed IS NOT TRUE;

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

CREATE INDEX idx_articles_search
ON articles USING gin (search_vector);

CREATE INDEX idx_events_created_brin
ON events USING brin (created_at) WITH (pages_per_range = 64);

Read each definition from right to left. The predicate or access method decides which rows and operators are represented. The key list decides which comparisons, ordering, or lookups are efficient. The name should describe the access pattern so later maintainers can identify whether the index still earns its write cost.

Example 1: Partial Index For A Hot Subset

Assume an order system where most historical orders are billed, but operators frequently inspect open unbilled orders for a customer. A full index on every order may be much larger than necessary. A partial index keeps only the open subset.

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  order_date timestamptz NOT NULL,
  billed boolean,
  total numeric(12,2) NOT NULL
);

CREATE INDEX idx_orders_unbilled_customer
ON orders (customer_id, order_date DESC)
WHERE billed IS NOT TRUE;

EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42
  AND billed IS NOT TRUE
ORDER BY order_date DESC
LIMIT 10;

The expected plan shape, with enough representative rows, is an index scan or bitmap index scan using idx_orders_unbilled_customer. The index can satisfy the customer filter and the date ordering for the unbilled subset. If the query is changed to billed = false, rows where billed is null no longer match the same semantics, so the planner’s ability to use the index depends on whether the predicate is still implied. Make the application predicate exactly match the index predicate unless you intentionally need different null behavior.

Example 2: Expression Index For Case-Insensitive Login

Email addresses are often looked up without case sensitivity, but a normal index on email does not help lower(email) = lower($1). An expression index stores the normalized value. When uniqueness is required, make the expression index unique so PostgreSQL enforces the invariant instead of relying on application checks.

CREATE TABLE app_users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  display_name text NOT NULL
);

CREATE UNIQUE INDEX idx_app_users_lower_email
ON app_users ((lower(email)));

INSERT INTO app_users (email, display_name)
VALUES ('Admin@Example.com', 'Admin');

-- This second insert fails with a duplicate-key error.
INSERT INTO app_users (email, display_name)
VALUES ('admin@example.com', 'Duplicate Admin');

The deterministic behavior is the second insert being rejected because both rows produce the same indexed key, admin@example.com. A lookup such as WHERE lower(email) = lower('ADMIN@example.com') can use the index. Do not hide the indexed expression behind a volatile function; index expressions must be stable enough for stored index entries to remain meaningful.

Example 3: GIN For JSONB Containment

GIN is the usual choice when a row contains many searchable tokens or document keys. For JSONB containment, the index breaks the document into searchable items and maps those items back to candidate rows. PostgreSQL then rechecks candidates against the original JSONB value because an inverted index can produce lossy or partial matches depending on the operator class.

CREATE TABLE tickets (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

CREATE INDEX idx_tickets_payload_gin
ON tickets USING gin (payload jsonb_path_ops);

SELECT id
FROM tickets
WHERE payload @> '{"priority":"high","status":"open"}'::jsonb;

With data where a small fraction of tickets are high-priority and open, the expected behavior is a bitmap index scan using the GIN index followed by a heap recheck. The jsonb_path_ops operator class is compact and effective for containment with @>, but it does not support every JSONB operator. If the workload also needs key-existence operators, the default JSONB GIN operator class may be the better design despite a larger index.

Example 4: GiST And BRIN For Shape And Scale

GiST and BRIN solve different problems. GiST helps when comparisons are not simple equality or ordering. The following range example supports overlap queries and can also back exclusion constraints. BRIN helps when the table is huge and values are physically clustered, such as append-only events ordered by creation time.

CREATE TABLE room_bookings (
  room_id integer NOT NULL,
  during tstzrange NOT NULL
);

CREATE INDEX idx_room_bookings_during_gist
ON room_bookings USING gist (during);

SELECT room_id, during
FROM room_bookings
WHERE during && tstzrange('2026-09-06 10:00+00', '2026-09-06 11:00+00');

CREATE TABLE audit_events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL,
  actor_id bigint NOT NULL
);

CREATE INDEX idx_audit_events_created_brin
ON audit_events USING brin (created_at) WITH (pages_per_range = 64);

The GiST query returns bookings whose time range overlaps the requested hour. The BRIN index stores summaries per block range; if the heap is loaded roughly in created_at order, PostgreSQL can skip block ranges whose min-max summary cannot contain the searched time. If old rows are frequently updated into new time ranges, or data is imported out of order, BRIN becomes less selective until the table is reordered or the design changes.

Design Choices And Trade-Offs

Choose a partial index when the workload repeatedly queries a stable minority of rows. The predicate should be simple, immutable with respect to the row, and visible in the query. Avoid a collection of many nearly identical partial indexes for many values; that usually indicates partitioning, a broader composite index, or better statistics should be considered.

Choose an expression index when the query’s natural key is derived. Keep the expression inexpensive and deterministic. If the expression implements a business rule, such as normalized email uniqueness, prefer a unique expression index because it protects the rule under concurrent writes.

Choose GIN for membership, containment, and token search across composite values. Expect larger indexes and sometimes slower writes because a single row can produce many index entries. Choose GiST when the operator class models spatial, range, similarity, or nearest-neighbor logic. Choose BRIN when the table is very large, the indexed value correlates with physical order, and approximate block skipping is enough. BRIN is often measured in kilobytes or megabytes where a B-tree would be much larger, but it is not a replacement for precise point lookup on randomly distributed values.

Failure Modes And Troubleshooting

Symptom: EXPLAIN shows a sequential scan after a partial index is created. Cause: the query predicate does not imply the partial index predicate, the table is too small for an index to be cheaper, or statistics are stale. Diagnose: compare the query WHERE clause to the index definition from pg_indexes, run ANALYZE, and test with representative row counts. Correct: align the query predicate, rebuild the index with the predicate actually used, or remove the index if the planner is correctly choosing a scan.

Symptom: a GIN index exists but JSONB queries remain slow. Cause: the query uses an operator unsupported by the chosen operator class, or the condition matches too many rows. Diagnose: inspect the operator, check the index definition, and run EXPLAIN (ANALYZE, BUFFERS) to see rows removed by recheck. Correct: switch operator class, add a narrower partial GIN index, or pair the GIN condition with a selective B-tree filter.

Symptom: a BRIN index barely reduces I/O. Cause: the heap is not correlated with the indexed column or the block range is too large. Diagnose: compare insertion order with the searched column, inspect pg_stats.correlation, and test smaller pages_per_range. Correct: load or cluster data in time order, recreate the BRIN index with smaller ranges, or use B-tree partitioning for selective lookups.

Operational Implications

Indexes improve reads by adding write work, storage, vacuum overhead, and migration complexity. Build large indexes with CREATE INDEX CONCURRENTLY when write availability matters, then verify the index is valid. Remember that concurrent builds can fail and leave an invalid index that should be dropped. Security is indirect but real: expression and partial unique indexes can enforce identity rules that application code might race; GIN indexes over JSONB can make ad hoc document search attractive, so pair them with column privileges and avoid storing sensitive fields merely because they are searchable.

Hands-On Lab

Prerequisites: a PostgreSQL database where you can create and drop tables, plus a SQL client such as psql. Use a scratch database, not a shared production schema.

1. Create the lab table and load enough ordered data to make planner choices visible.
2. Create a partial index for unbilled rows and an expression index for normalized customer reference.
3. Run ANALYZE, then compare EXPLAIN (ANALYZE, BUFFERS) before and after the indexes.
4. Add a GIN index if your build includes JSONB payload queries, and test a containment predicate.
5. Verify that each plan names the intended index or explain why the sequential scan is cheaper.
6. Clean up by dropping the lab tables and indexes.

CREATE TABLE index_lab_orders AS
SELECT gs AS id,
       (gs % 1000) AS customer_id,
       now() - (gs || ' minutes')::interval AS order_date,
       CASE WHEN gs % 20 = 0 THEN false ELSE true END AS billed,
       lower('customer-' || (gs % 1000)) AS customer_ref,
       jsonb_build_object('priority', CASE WHEN gs % 50 = 0 THEN 'high' ELSE 'normal' END,
                          'status', CASE WHEN gs % 20 = 0 THEN 'open' ELSE 'closed' END) AS payload
FROM generate_series(1, 100000) AS gs;

ANALYZE index_lab_orders;

CREATE INDEX idx_lab_unbilled_customer
ON index_lab_orders (customer_id, order_date DESC)
WHERE billed IS NOT TRUE;

CREATE INDEX idx_lab_customer_ref_expr
ON index_lab_orders ((upper(customer_ref)));

CREATE INDEX idx_lab_payload_gin
ON index_lab_orders USING gin (payload jsonb_path_ops);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM index_lab_orders
WHERE customer_id = 40 AND billed IS NOT TRUE
ORDER BY order_date DESC
LIMIT 5;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM index_lab_orders
WHERE upper(customer_ref) = 'CUSTOMER-40';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM index_lab_orders
WHERE payload @> '{"priority":"high","status":"open"}'::jsonb;

DROP TABLE index_lab_orders;

Verification: the first plan should mention idx_lab_unbilled_customer, the second should mention idx_lab_customer_ref_expr when the expression is selective enough, and the third should mention idx_lab_payload_gin. If a sequential scan appears, check row estimates, selectivity, and whether the query expression exactly matches the indexable expression. Cleanup is the final DROP TABLE, which removes the table and its indexes.

Assessment Exercises

1. You have 80 million audit rows inserted in timestamp order and queries usually ask for one day. Which index type would you test first, and what evidence would make you reject it?
2. A partial index is defined with WHERE deleted_at IS NULL, but an ORM emits WHERE COALESCE(deleted_at, now()) = now(). Explain why the index may not be used and how you would correct the query.
3. Design a unique expression index that prevents two active users from sharing the same case-insensitive username while allowing inactive historical rows.
4. A JSONB GIN index speeds up one containment query but slows inserts noticeably. Name two design changes you would evaluate before dropping the feature.
5. Why can BRIN be excellent for range scans but poor for random customer-id lookup, even when both columns have indexes?

Summary

Partial indexes shrink the indexed universe to rows a query actually needs. Expression indexes store derived keys so the planner and uniqueness checks can use normalized values. GIN inverts composite values into searchable elements, GiST delegates tree behavior to operator classes for ranges and spatial relationships, and BRIN stores page-range summaries for massive ordered tables. In this indexes and planning section, the recurring discipline is the same: define the access pattern, choose the index whose internal structure matches it, and verify the actual plan with representative data.