Databases 8 min read

Combine Partial BTREE and BRIN Indexes in PostgreSQL for Space Savings and Speed

This article demonstrates how PostgreSQL's Partial Index feature can be used together with BTREE and BRIN indexes to dramatically reduce storage while maintaining high query performance on both low‑frequency and high‑frequency data in a heavily skewed dataset.

ITPUB

Jul 17, 2016

Combine Partial BTREE and BRIN Indexes in PostgreSQL for Space Savings and Speed

Background

The article is the second in a series comparing BRIN and BTREE indexes in PostgreSQL. The first part showed that BTREE generally offers better query performance but consumes more space, whereas BRIN is compact but slower for small‑result queries.

Partial Indexes in PostgreSQL

PostgreSQL supports Partial Indexes , which index only a subset of rows defined by a predicate. This is useful when a column exhibits data skew: high‑frequency values dominate the table and indexing them provides little benefit while wasting space.

Test Scenario

A table with 10,000,000 rows is created. Column id1 has 100 distinct values; values 1‑99 appear 10 times each, while id1 = 100 appears 9,999,010 times (≈99.99% of rows). Indexing the high‑frequency value with a regular BTREE would be unnecessary.

Indexes Created

Regular BTREE index idx_t2_id1_bt_in – 214 MB

Partial BTREE index idx_t2_id1_bt (excluding id1 = 100) – 40 KB

Regular BRIN index idx_t2_id1_br – 24 KB

Partial BRIN index idx_t2_id1_br_ex (only id1 = 100) – 24 KB

Performance Results

Querying a low‑frequency value ( id1 = 99) uses the partial BTREE index, touching only 11 buffers and taking 0.246 ms.

Querying the high‑frequency value ( id1 = 100) uses the partial BRIN index, scanning all heap pages (lossy = 44,248) with a total of 44,256 buffers, which is expected given the data distribution.

Using a regular BRIN index for the same high‑frequency query also scans all heap pages, but the BRIN buffer count is lower (2 buffers) because the index entries are sparse.

Conclusion

By leveraging Partial BTREE indexes to exclude dominant values and combining them with BRIN indexes for large‑scale scans, PostgreSQL can achieve both significant storage savings and high query performance. In the test, total index size dropped from 560 MB (regular BTREE + BRIN) to 346 MB (partial BTREE + BRIN).

The example uses an artificial data skew to illustrate the concept; real‑world workloads may differ, but the principle shows PostgreSQL’s flexibility compared to many other database systems.