Index Types (B-tree, GIN, GiST)

Indexes are lookup structures that help PostgreSQL find rows without scanning the entire table. Choosing the right index type is crucial for performance — the wrong type either won't be used or won't fit in memory efficiently.

Why Indexes Exist

Without an index, PostgreSQL reads every row (Sequential Scan). With an index, it can jump directly to matching rows:

-- Table with 10 million rows
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Without index: Seq Scan on orders (cost=0.00..250000.00 rows=150 ...)
-- With index:    Index Scan using orders_customer_id_idx (cost=0.43..12.50 rows=150 ...)

The index cost is ~20,000x lower for this query.

B-tree Index (Default)

B-tree (Balanced Tree) is the default index type — use it for almost everything.

Supports: =, <, >, <=, >=, BETWEEN, IS NULL, IN, ORDER BY, LIKE 'prefix%'

-- Basic B-tree index (implicit)
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

-- Explicit B-tree
CREATE INDEX orders_date_idx ON orders USING BTREE (order_date);

-- Multi-column (composite) B-tree index
CREATE INDEX orders_status_date_idx ON orders (status, order_date);
-- Useful for: WHERE status = 'pending' ORDER BY order_date
-- Note: left-prefix rule applies — this helps (status=...) but not (order_date=...) alone

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX users_email_idx ON users (email);

-- Descending index (optimizes ORDER BY col DESC queries)
CREATE INDEX orders_date_desc_idx ON orders (order_date DESC);

Prefix LIKE

B-tree can accelerate LIKE 'prefix%' but NOT LIKE '%suffix':

-- This CAN use a B-tree index (prefix match)
SELECT * FROM products WHERE name LIKE 'Widget%';

-- This CANNOT use a B-tree index (leading wildcard)
SELECT * FROM products WHERE name LIKE '%Widget';
-- Use GIN with pg_trgm for suffix/contains searches

Hash Index

Hash indexes store a 32-bit hash of the column value. Fast for equality (=) only, but smaller than B-tree for very long columns.

Supports: Only =

CREATE INDEX sessions_token_idx ON sessions USING HASH (session_token);
-- Only useful if token is very long (e.g., 256-char strings) and only used with =

-- In practice, B-tree is usually preferred even for equality
-- Hash indexes were unreliable before PostgreSQL 10; they're fine now

GIN Index — Generalized Inverted Index

GIN indexes are for columns that contain multiple values — JSONB, arrays, and full-text search (tsvector).

Supports: @>, <@, ?, ?|, ?& (JSONB), @@ (full-text), @> (arrays), LIKE '%substring%' (with pg_trgm)

GIN for JSONB

CREATE TABLE events (
    id      SERIAL PRIMARY KEY,
    data    JSONB
);

-- GIN index on the whole JSONB column
CREATE INDEX events_data_gin ON events USING GIN (data);

-- Now these queries use the index:
SELECT * FROM events WHERE data @> '{"type": "click"}';
SELECT * FROM events WHERE data ? 'user_id';      -- key exists
SELECT * FROM events WHERE data @> '{"status": "active", "region": "US"}';

GIN for Arrays

CREATE TABLE posts (
    id   SERIAL PRIMARY KEY,
    tags TEXT[]
);

CREATE INDEX posts_tags_gin ON posts USING GIN (tags);

-- Find posts containing all specified tags
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'database'];

-- Find posts containing any of the tags
SELECT * FROM posts WHERE tags && ARRAY['postgresql', 'sql'];

GIN for Full-Text Search

CREATE TABLE articles (
    id      SERIAL PRIMARY KEY,
    title   TEXT,
    body    TEXT,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        TO_TSVECTOR('english', COALESCE(title, '') || ' ' || COALESCE(body, ''))
    ) STORED
);

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

-- Full-text search using the index
SELECT title FROM articles
WHERE search_vector @@ TO_TSQUERY('english', 'postgresql & index')
ORDER BY TS_RANK(search_vector, TO_TSQUERY('english', 'postgresql & index')) DESC;

GIN with pg_trgm — Substring Search

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Now LIKE '%substring%' can use the index!
SELECT * FROM products WHERE name ILIKE '%widget%';
SELECT * FROM products WHERE name % 'widgit';  -- similarity search

GiST Index — Generalized Search Tree

GiST is an extensible framework for spatial and range data.

Supports: Geometric types, range types (@>, &&, <<, etc.), full-text (@@), nearest-neighbor searches

GiST for Ranges

CREATE TABLE reservations (
    id          SERIAL PRIMARY KEY,
    room_id     INTEGER,
    stay        DATERANGE
);

CREATE INDEX reservations_stay_gist ON reservations USING GIST (stay);

-- Find reservations overlapping a date range
SELECT * FROM reservations
WHERE stay && '[2024-03-10, 2024-03-15)';

-- Find reservations contained within a date range
SELECT * FROM reservations
WHERE stay <@ '[2024-03-01, 2024-03-31)';

GiST for Geometric Types (with PostGIS)

-- PostGIS uses GiST for geospatial indexes
CREATE INDEX locations_geom_idx ON locations USING GIST (geom);

-- Find locations within 5km of a point
SELECT name FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(-122.4, 37.7)::geography, 5000);

SP-GiST Index

Space-Partitioned GiST. Good for non-balanced data structures like quad-trees and tries.

-- Better than B-tree for IP address lookups on INET columns
CREATE INDEX servers_ip_idx ON servers USING SPGIST (ip_address);

-- Good for text prefix lookups with many common prefixes
CREATE INDEX products_code_spgist ON products USING SPGIST (product_code);

BRIN Index — Block Range Index

BRIN (Block Range INdex) is tiny and fast to build — ideal for huge tables where data is naturally ordered (time-series, log tables).

How it works: Stores only the min/max value for each "block range" of physical storage. Trades precision for small size.

-- IoT sensor data — billions of rows, naturally time-ordered
CREATE TABLE sensor_readings (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sensor_id   INTEGER,
    recorded_at TIMESTAMPTZ DEFAULT NOW(),
    temperature DOUBLE PRECISION
);

-- BRIN index on timestamp — very small, works because data is inserted in time order
CREATE INDEX sensor_recorded_brin ON sensor_readings USING BRIN (recorded_at);

-- BRIN works well for: WHERE recorded_at > NOW() - INTERVAL '1 hour'
-- BRIN does NOT work for: WHERE sensor_id = 42 (not physically ordered by sensor)

Partial Indexes

Index only a subset of rows — dramatically reduces index size for filtered queries:

-- Only index pending orders (the active ones we query most)
CREATE INDEX orders_pending_idx ON orders (created_at)
WHERE status = 'pending';

-- The index is only 0.1% the size if 99.9% of orders are 'completed'
-- Query: WHERE status = 'pending' ORDER BY created_at  -- uses the index!
-- Query: WHERE status = 'completed'  -- ignores the index (correct behavior)

-- Only index non-deleted rows (soft delete pattern)
CREATE INDEX users_active_email_idx ON users (email)
WHERE deleted_at IS NULL;

Expression Indexes

Index the result of a function or expression:

-- Case-insensitive email lookup
CREATE INDEX users_email_lower_idx ON users (LOWER(email));

-- Now this query uses the index:
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.COM');

-- Index for fast date extraction
CREATE INDEX orders_year_month_idx ON orders (DATE_TRUNC('month', order_date));

-- JSON field index
CREATE INDEX events_type_idx ON events ((data->>'type'));
-- Note the extra parentheses — required for expression indexes

Covering Indexes (INCLUDE)

A covering index includes extra columns so that PostgreSQL can answer a query from the index alone without touching the heap (table data):

-- Query: SELECT email, name FROM users WHERE user_id = 42
-- Without covering index: Index Scan on user_id, then fetch name from heap
-- With covering index:

CREATE INDEX users_id_covering ON users (user_id) INCLUDE (email, name);
-- Now PostgreSQL uses Index Only Scan — never reads the table!

-- Best for: frequently executed queries on large tables
-- Cost: larger index size

Indexing Decision Guide