PostgreSQL B-tree vs GIN: Which Index for Which Query
postgresql btree vs gin explained — when to use a B-tree, when GIN wins (JSONB, full-text), plus partial, expression, and concurrent index recipes.
Pick the wrong index and your query does a sequential scan; pick the right one and it answers in milliseconds. The postgresql btree vs gin question comes up constantly because these are the two index types you'll reach for most: B-tree for equality and range lookups, GIN for "does this value appear inside this container" queries on JSONB, arrays, and full-text search. This guide shows you exactly when to use each, with runnable examples and the production gotchas (write costs, concurrent builds) nobody mentions. What you'll learn B-tree vs GIN: which query patterns each index type accelerates GIN indexes on JSONB: jsonb_ops vs jsonb_path_ops Partial indexes, expression (functional) indexes, and composite column order CREATE INDEX CONCURRENTLY for zero-downtime index builds The GIN write tax: pending lists and fastupdate How to check whether Postgres is actually using your index Step 1 — Set up a table with searchable content We'll use an articles table with a JSONB tags column — it gives us both classic B-tree territory (dates, IDs) and GIN territory (JSONB membership): CREATE TABLE articles ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, tags JSONB NOT NULL DEFAULT '[]', created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); INSERT INTO articles (title, tags, created_at) VALUES ('Intro to Postgres', '["postgres", "sql", "beginner"]', '2026-01-05'), ('JSONB Deep Dive', '["postgres", "jsonb"]', '2026-02-10'), ('Indexing Strategies', '["postgres", "performance"]', '2026-03-15'), ('Python for Data', '["python", "data"]', '2026-04-01'); Step 2 — B-tree: the default for equality and ranges When you write CREATE INDEX without a USING clause,…