Advanced Postgres Indexing: Mastering BRIN, GIN, and Partial Indexes for Large Tables

Almost every database developer understands the basics of B-Tree indexes. They are the default index type in PostgreSQL, structured as balanced trees that make searching, updating, and deleting individual rows incredibly efficient ($O(\log N)$ complexity).

But B-Tree indexes have a major problem: size. On a table with a billion rows, a B-Tree index on a single timestamp or UUID column can easily consume hundreds of gigabytes of RAM and disk space, choking your cache hit rates.

To handle scale efficiently, PostgreSQL offers specialized indexing strategies that are highly optimized for specific query patterns and data structures.

In this deep dive, we will explore three advanced PostgreSQL index types—BRIN, GIN, and Partial Indexes—explaining how they work under the hood, how they save space, and how to analyze their query plans for maximum performance.

1. BRIN: Block Range Indexes (The Space Saver)

A BRIN (Block Range Index) is designed for extremely large tables where the data is physically ordered on disk according to the index column (e.g., auto-incrementing IDs, creation timestamps, transaction dates).

How BRIN Works Under the Hood

Unlike a B-Tree index, which maps an index key to a specific row pointer, BRIN operates on ranges of disk pages (typically 128 contiguous pages, or 1MB of table storage by default).

For each page range, BRIN stores only two values:

1. The minimum value found in that range.
2. The maximum value found in that range.

PostgreSQL Disk Blocks:
[Page 1 ... Page 128]  ──────►  BRIN stores: [Min: 2026-04-01, Max: 2026-04-03]
[Page 129 ... Page 256] ──────►  BRIN stores: [Min: 2026-04-04, Max: 2026-04-06]

When you run a query:

SELECT * FROM orders WHERE created_at = '2026-04-05';

PostgreSQL scans the BRIN index. It checks each 128-page block’s min/max range.

• Does '2026-04-05' fall between 2026-04-01 and 2026-04-03? No (Skip scanning these 128 pages entirely).
• Does '2026-04-05' fall between 2026-04-04 and 2026-04-06? Yes (Scan the 128 pages sequentially to find the matching rows).

This process is called Block Exclusion.

The Massive Space Advantage

Because a BRIN index only stores two values per 128 pages, its size is minuscule compared to a B-Tree.

• B-Tree Index Size: ~20 GB (for a 500-million row timestamp column).
• BRIN Index Size: ~15 MB ($99.9\%$ space savings!).

When to Use BRIN

• Use it when: Tables are massive (100M+ rows), data is strictly appended in chronological order, and queries usually filter by large date ranges or sequential IDs.
• Avoid it when: Data is heavily out-of-order, or queries require highly selective, sub-millisecond single-row lookups (where B-Tree is still king).

2. GIN: Generalized Inverted Indexes (For Arrays and JSONB)

A GIN (Generalized Inverted Index) is an “inverted index” designed for data types where a single row can contain multiple values, such as arrays, full-text documents, and JSONB documents.

How GIN Works Under the Hood

Think of a GIN index like the index at the back of a textbook. Instead of mapping a row to a list of its columns, it maps individual component values (words, array elements, or JSON keys) to the list of row IDs (TIDs) containing them.

A GIN index consists of:

1. Entry Tree (B-Tree): A standard B-Tree containing all unique elements/keys.
2. Posting List/Tree: For each key in the Entry Tree, GIN stores a list of all row pointers (TIDs) containing that element. If this list is large, it is structured as a separate B-Tree (Posting Tree) to maintain performance.

JSONB Row 1: {"tags": ["go", "docker"]}
JSONB Row 2: {"tags": ["go", "kubernetes"]}

GIN Entry Tree:
"docker"     ──► Posting List: [Row 1]
"go"         ──► Posting List: [Row 1, Row 2]
"kubernetes" ──► Posting List: [Row 2]

When you query for a record:

SELECT * FROM articles WHERE tags @> '["kubernetes"]';

PostgreSQL traverses the GIN Entry Tree to find "kubernetes", immediately grabs its Posting List ([Row 2]), and retrieves the corresponding page. It does not scan the rest of the database.

GIN Fastupdate Overhead

Updating a GIN index is expensive. Adding an array with 10 elements requires updating 10 separate entries in the GIN tree.

To mitigate this, PostgreSQL uses the fastupdate feature:

• Write changes are appended to a flat Pending List in memory/disk.
• Read queries check both the main GIN tree and the Pending List.
• A background process (autovacuum or explicit flush) merges the Pending List into the main GIN index in batches.

3. Partial Indexes (The Laser-Focused Index)

A Partial Index is a standard B-Tree index built over a subset of a table, defined by a conditional WHERE clause.

CREATE INDEX idx_orders_unprocessed 
ON orders (created_at) 
WHERE status = 'unprocessed';

Why Use Partial Indexes?

In many application architectures, we deal with “skewed state distributions.” For example, an orders table might have 99% of its rows in the 'completed' status, while only 1% are in the 'unprocessed' status.

If our background processing workers frequently query the table to find jobs that need to be processed:

SELECT * FROM orders WHERE status = 'unprocessed' ORDER BY created_at;

A standard index on status or created_at would index the entire 100-million row table, consuming massive amounts of storage and memory cache.

By using a Partial Index:

1. Size Reduction: The index only contains row pointers where status = 'unprocessed'. The index size drops from 10GB to 50MB.
2. Write Performance: If an order’s status transitions from 'unprocessed' to 'completed', the row is deleted from the partial index. Subsequent updates to completed orders never need to update this index, saving valuable CPU cycles during writes.
3. Cache Efficiency: The index fits entirely inside the CPU/RAM cache, ensuring instant lookup speeds.

Analyzing Query Execution Plans

Let’s look at how PostgreSQL behaves when executing queries using these indexes. We use EXPLAIN ANALYZE to inspect execution paths.

Case A: BRIN Scan

EXPLAIN ANALYZE SELECT * FROM logs WHERE logged_at >= '2026-04-01' AND logged_at <= '2026-04-02';

->  Bitmap Heap Scan on logs  (cost=12.10..452.10 rows=450000 width=128)
      Recheck Cond: ((logged_at >= '2026-04-01'::timestamp) AND (logged_at <= '2026-04-02'::timestamp))
      Rows Removed by Index Recheck: 1240
      ->  Bitmap Index Scan on idx_logs_brin  (cost=0.00..8.10 rows=450000 width=0)
            Index Cond: ((logged_at >= '2026-04-01'::timestamp) AND (logged_at <= '2026-04-02'::timestamp))

Note the Bitmap Heap Scan and Bitmap Index Scan. Because BRIN works on page ranges, it performs a bitmap index scan to select matching physical pages, then does a sequential scan over those selected pages to verify individual rows (Recheck Cond), dropping rows that do not match (Rows Removed by Index Recheck).

Case B: Partial Index Scan

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'unprocessed' ORDER BY created_at;

->  Index Scan using idx_orders_unprocessed on orders  (cost=0.15..12.30 rows=50 width=256)
      Index Cond: (status = 'unprocessed'::text)

This is an Index Scan (direct lookup) because the partial index meets the query conditions perfectly. No sequential search is required.

Indexing Selection Matrix

Conclusion

B-Tree indexes are a powerful tool, but they are not the only indexing tool available in PostgreSQL. As data volumes scale, thinking strategically about indexing can save gigabytes of RAM, keep your database operating entirely in-memory, and maintain fast response times under heavy production write loads.