SQL Academy · Lesson

B-tree vs Hash vs GiST vs GIN Indexes

Compare the main index types in PostgreSQL and pick the right one for equality, range, geometry, JSON, and full-text queries.

B-tree vs Hash vs GiST vs GIN Indexes is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Index Types Overview

PostgreSQL has several index types, each optimised for different access patterns:

B-tree — equality and range (default)
Hash — equality only
GiST — geometric, full-text, custom
GIN — composite (arrays, JSONB, full-text)
BRIN — block range — huge, sorted tables
SP-GiST — space-partitioned trees

B-tree: The Default

Used 95% of the time. Supports =, <, <=, >, >=, BETWEEN, ORDER BY:

CREATE INDEX users_email_idx ON users(email);
CREATE INDEX orders_created_at_idx ON orders(created_at DESC);

Hash Index

Only equality lookups. Crash-safe since PG 10. Smaller and slightly faster than B-tree for pure equality, but very narrow use case:

CREATE INDEX sessions_token_hash ON sessions USING HASH (token);
-- Useful for very high-cardinality equality lookups; usually B-tree is fine.

GiST Index

Generalised Search Tree — pluggable, supports range types, geometric types, IP addresses, full-text:

CREATE INDEX events_during_idx ON events USING GIST (during);
-- 'during' is a tstzrange — finds overlapping ranges efficiently.

CREATE INDEX places_location_idx ON places USING GIST (location);
-- PostGIS geometry — nearest neighbour, intersects.

GIN Index

Generalised Inverted Index — best for composite values where each item maps to many rows:

CREATE INDEX articles_tags_gin ON articles USING GIN (tags);
-- tags is TEXT[]; query with @> or && operators

CREATE INDEX articles_doc_gin ON articles USING GIN (search_doc);
-- For tsvector full-text search

CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);
-- For JSONB containment queries

BRIN Index

Block Range INdexes summarise ranges of values per N pages. Tiny (kilobytes for terabyte tables) but only effective when data is physically sorted by the indexed column:

CREATE INDEX events_ts_brin ON events USING BRIN (ts);
-- Excellent for append-only time-series tables.

Comparing Sizes

For a billion-row table:

B-tree on a BIGINT: ~30 GB
BRIN on a TIMESTAMPTZ: ~1 MB

BRIN is dramatically smaller but only beats B-tree for sequential / sorted queries.

Choosing an Index Type

Decision flow:

Equality + range on scalar → B-tree
Equality on huge scalar set → B-tree (Hash only if you've measured)
Arrays / JSONB / full-text → GIN
Range types, geometry, fuzzy text → GiST
Massive sorted table, append-only → BRIN

GIN Tradeoffs

GIN is the fastest for "find all rows containing X" queries, but slower to INSERT/UPDATE than B-tree. For very write-heavy tables, consider fastupdate=off to control GIN's pending list.

Operator Classes

Each index type works with specific operators. JSONB uses jsonb_path_ops for smaller, faster containment-only indexes:

CREATE INDEX e_data_gin ON events USING GIN (data jsonb_path_ops);
-- Half the size of default jsonb_ops, supports @> only.

Composite Indexes Per Type

B-tree composites use leftmost-prefix matching. GIN composites work but are larger; you usually create separate single-column GIN indexes.

Recap

Pick the index type to fit the query.

B-tree: default
GIN: arrays/JSONB/full-text
GiST: ranges/geometry/fuzzy
BRIN: sequential/append-only

Quick Check

You're indexing a TEXT[] column for "contains" queries. Which index type fits?

Frequently asked questions

Is the “B-tree vs Hash vs GiST vs GIN Indexes” lesson free?

Yes — the full text of “B-tree vs Hash vs GiST vs GIN Indexes” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “B-tree vs Hash vs GiST vs GIN Indexes”?

Compare the main index types in PostgreSQL and pick the right one for equality, range, geometry, JSON, and full-text queries. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “B-tree vs Hash vs GiST vs GIN Indexes” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy