Does a B-tree index work on jsonb, array or tsvector columns?

B-tree index on a jsonb, array, or tsvector column

Note: this works and blocks nothing, but a performance regression is likely.

What happens

A B-tree on a jsonb, array, or tsvector column only serves equality and ordering on the whole value, never the containment (@>), key-existence (?), or text-search (@@) operators those types exist for. The queries that motivated the index still sequential-scan, and the index (often huge, since it stores whole documents) taxes every write.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

CREATE INDEX idx_events_payload ON events (payload);

The safe pattern

Use GIN: it indexes the elements inside the value, so containment, key-existence, and full-text operators can use it. For jsonb, jsonb_path_ops is smaller and faster when you only need @>. An expression index on a specific key (payload->>'kind') is the right choice when queries target one field. An opclass or a UNIQUE index keeps this rule silent, those are deliberate.

CREATE INDEX CONCURRENTLY idx_events_payload ON events USING gin (payload jsonb_path_ops);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (3)

btree on array

SET lock_timeout = '5s';
ALTER TABLE posts ADD COLUMN tags text[] NOT NULL DEFAULT '{}';
CREATE INDEX CONCURRENTLY idx_posts_tags ON posts (tags);

btree on jsonb

SET lock_timeout = '5s';
CREATE TABLE events (id bigint PRIMARY KEY, payload jsonb NOT NULL);
CREATE INDEX idx_events_payload ON events (payload);

btree on live tsvector

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_docs_search ON docs (search_vector);

Stays silent (4)

expression on key

SET lock_timeout = '5s';
CREATE TABLE events (id bigint PRIMARY KEY, payload jsonb NOT NULL);
CREATE INDEX idx_events_kind ON events ((payload->>'kind'));

gin

SET lock_timeout = '5s';
CREATE TABLE events (id bigint PRIMARY KEY, payload jsonb NOT NULL);
CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);

scalar column

SET lock_timeout = '5s';
CREATE TABLE events (id bigint PRIMARY KEY, payload jsonb NOT NULL, kind text);
CREATE INDEX idx_events_kind ON events (kind);

unique jsonb

SET lock_timeout = '5s';
CREATE TABLE events (id bigint PRIMARY KEY, payload jsonb NOT NULL);
CREATE UNIQUE INDEX idx_events_payload_unique ON events (payload);

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BP002 BP004 →