Specialised index extensions: btree_gin, bloom and intarray

conceptThe concept

btree_gin adds GIN operator classes for scalar types (integers, text, timestamps, and more). Its main use is a single GIN index on (customer_id, payload jsonb_path_ops) so one index serves "this customer's events with this JSON property". (btree_gist, covered earlier, does the same for GiST and exclusion constraints.)

bloom is an index access method that stores a small signature per row built from several columns. A query with equality filters on any subset of those columns scans the whole (small) index and then rechecks candidate rows. It replaces many separate B-tree indexes for wide tables filtered by changing combinations of columns. It supports only equality, only int4 and text out of the box, and is lossy.

intarray adds functions and operators for int[] without NULLs: & (intersection), | (union), sort, uniq, icount, and the query_int type for boolean queries like tag_ids @@ '3 & (7 | 9) & !11', indexed by gin__int_ops or gist__int_ops.

whyWhy it matters

Without these, teams either create a pile of overlapping indexes (slow writes, bloat) or accept sequential scans. One well-chosen specialised index can replace several general ones.

Use caseReal-world scenario

An events table is queried by customer plus JSON attributes, so a btree_gin index combines both. A product search page filters on any 3 of 10 numeric attributes; one bloom index replaces ten B-trees. A content site stores tag ids as int[] and answers "has tag A and (B or C) but not D" from a GIN intarray index.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE EXTENSION btree_gin;
CREATE INDEX events_cust_payload ON events USING gin (customer_id, payload jsonb_path_ops);
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE customer_id = 42 AND payload @> '{"device": "mobile"}';
ROLLBACK;

BEGIN;
CREATE EXTENSION bloom;
CREATE TABLE listing AS
SELECT g AS id, (g * 7) % 1000 AS a, (g * 13) % 1000 AS b, (g * 17) % 1000 AS c, (g * 19) % 1000 AS d
FROM generate_series(1, 20000) g;
CREATE INDEX listing_bloom ON listing USING bloom (a, b, c, d)
  WITH (length = 80, col1 = 2, col2 = 2, col3 = 2, col4 = 2);
ANALYZE listing;
EXPLAIN (COSTS OFF) SELECT * FROM listing WHERE c = 17 AND d = 323;
EXPLAIN (COSTS OFF) SELECT * FROM listing WHERE a = 7 AND b = 13;
SELECT pg_size_pretty(pg_relation_size('listing_bloom')) AS bloom_size;
ROLLBACK;

BEGIN;
CREATE EXTENSION intarray;
SELECT '{1,2,3,4}'::int[] & '{3,4,5}'::int[] AS intersection,
       uniq(sort('{3,1,1,2}'::int[])) AS sorted_unique,
       icount('{1,2,3}'::int[]) AS n;
CREATE TABLE post (id int PRIMARY KEY, tag_ids int[] NOT NULL);
INSERT INTO post SELECT g, ARRAY[g % 50, (g * 7) % 50, (g * 11) % 50] FROM generate_series(1, 5000) g;
CREATE INDEX post_tags ON post USING gin (tag_ids gin__int_ops);
ANALYZE post;
EXPLAIN (COSTS OFF) SELECT count(*) FROM post WHERE tag_ids @@ '(3 | 7) & !21';
SELECT count(*) FROM post WHERE tag_ids @@ '(3 | 7) & !21';
ROLLBACK;

internalsUnder the hood

GIN indexes store each key once with a posting list of row ids, so multicolumn GIN with btree_gin keeps separate key spaces per column and intersects posting lists at query time. A bloom signature sets a few bits per column value in a bit array of length bits; a query computes the same bits and scans every signature, so false positives are rechecked against the heap (Recheck Cond). intarray operators shadow the built-in &&, @> and <@ for int[] once the extension is installed, which can change which index a query uses.

Avoid / preferDo this, not that

CREATE INDEX ON listing (a);
CREATE INDEX ON listing (b);
CREATE INDEX ON listing (c);
CREATE INDEX ON listing (d);   -- and every pair, "just in case"

CREATE INDEX ON listing USING bloom (a, b, c, d);

CREATE EXTENSION intarray;   -- on a database where int[] queries use a GIN array_ops index

-- check plans for @> and && on int[] before and after installing intarray,
-- and build gin__int_ops indexes if you adopt it

PitfallsCommon mistakes

• Expecting a bloom index to serve range conditions, ORDER BY or uniqueness; it only answers equality.
• Using intarray on arrays that contain NULLs; its functions reject them.
• Putting a low-selectivity scalar first in a B-tree when a btree_gin multicolumn GIN would serve the combined filter better.
• Forgetting that bloom indexes support only int4 and text without extra operator classes.

LimitsWhen not to use it

For a few predictable filter combinations, ordinary composite B-tree indexes are faster and support ranges and sorting. For tagging in a normalised schema, a join table with a B-tree index is often simpler than int[] plus intarray.

SecuritySecurity notes

These are indexes, so the risk is operational: every extra index slows writes and grows backups, and GIN indexes can delay writes through their pending list. Review index additions like schema changes and drop unused ones.

ChallengeTry it yourself

BEGIN;
CREATE EXTENSION intarray;
CREATE TABLE post (id int PRIMARY KEY, tag_ids int[] NOT NULL);
INSERT INTO post SELECT g, ARRAY[g % 50, (g * 7) % 50, (g * 11) % 50] FROM generate_series(1, 5000) g;
CREATE INDEX post_tags ON post USING gin (tag_ids gin__int_ops);
ANALYZE post;
EXPLAIN (COSTS OFF) SELECT count(*) FROM post WHERE tag_ids @@ '5 & 15 & !25';
SELECT count(*) FROM post WHERE tag_ids @@ '5 & 15 & !25';
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Index types
• BRIN indexes
• GIN indexes