Advanced PostgreSQL Indexes GIN GiST BRIN and Partial Indexes

If your PostgreSQL database slows down on full-text search, JSON data, geometries, or date ranges, the problem isn't the load — it's the wrong index. A B-tree works fine for equality and range on numbers and short text, but on arrays, JSON documents, geographic coordinates, or columns with thousands of duplicate values it chokes. At Meteora Web, we see it every day in the projects we optimize: a well-chosen index turns a 10-second query into 10 milliseconds. This guide shows you how and when to use GIN, GiST, BRIN and partial indexes — no fluff, with copy-paste examples.

Why are advanced PostgreSQL indexes (GIN, GiST, BRIN) better than B-trees?

The B-tree is a workhorse but has clear limits: it doesn't support searching inside arrays, JSONB, full-text, or spatial data. For example, on a JSONB column with a B-tree you can only search for an exact match of the whole document. With a GIN index you can query individual JSON fields using @> or ?|. The same applies to full-text: to_tsvector() produces a lexeme vector that a B-tree cannot index. GIN and GiST are designed for composite data and similarity searches; BRIN is perfect for ordered, physically correlated data (e.g., time logs). If you don't use them, you pay in performance and disk space.

When to use a GIN index for full-text and JSONB searches?

The GIN index (Generalized Inverted Index) inverts the mapping: instead of row→value, it maps value→rows. It's ideal for arrays, JSONB, full-text, and component searches. Here's a concrete example on a JSON product catalog:

-- Products table with JSON attributes
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

-- Sample data
INSERT INTO products (name, attributes) VALUES
('Running Shoes', '{"color": "red", "sizes": [39,40,41], "material": "mesh"}'),
('Trekking Backpack', '{"color": "green", "capacity": "40L", "waterproof": true}');

-- GIN index on attributes
CREATE INDEX idx_attributes_gin ON products USING GIN (attributes);

-- Fast query: find products with red color
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

-- Full-text: GIN index on tsvector
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT
);

ALTER TABLE articles ADD COLUMN text_tsvector TSVECTOR
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX idx_text_gin ON articles USING GIN (text_tsvector);

-- Full-text query
SELECT * FROM articles WHERE text_tsvector @@ to_tsquery('english', 'performance & database');

Caution: GIN indexes take more write time and disk space than a B-tree. Use them only on columns with high unique value cardinality and low write rates (or when reads are critical). For JSONB columns with few variants, a B-tree + an expression index might suffice.

GiST: how to handle geographic and proximity full-text searches?

The GiST index (Generalized Search Tree) supports proximity, intersection, and similarity operators. It's the king for geospatial data (PostGIS), full-text with ranking, and date range searches. Here's a geographic example:

-- PostGIS extension required
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(Point, 4326)
);

-- GiST index on geography column
CREATE INDEX idx_location_gist ON stores USING GIST (location);

-- Find stores within 10 km of a center
SELECT name FROM stores
WHERE ST_DWithin(location, ST_MakePoint(13.36, 38.12)::geography, 10000);

-- Full-text with ranking (using pg_trgm)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_name_trgm ON stores USING GIST (name gist_trgm_ops);
SELECT *, similarity(name, 'Shoes') AS sim
FROM stores
WHERE name % 'Shoes'
ORDER BY sim DESC;

When to prefer GiST over GIN? GiST supports ordering operators (e.g., <-> for distance), while GIN doesn't. For pure full-text, GIN is faster on equality; GiST is better if you also need ranking by similarity. For geographic data, GiST is the only sensible choice.

BRIN: the forgotten index that saves space and time?

BRIN (Block Range INdex) does not index every value, but groups of physical blocks. It only works on columns that are physically ordered within the database (e.g., creation timestamps, incrementing IDs). The advantage: it takes very little space (often 0.1% of the table) and updates almost for free. Ideal for log tables, transactions, time-series data.

-- Log table with timestamp
CREATE TABLE access_logs (
    id BIGSERIAL,
    user_id INT,
    action TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- BRIN index with 128 blocks per range
CREATE INDEX idx_log_brin ON access_logs USING BRIN (created_at) WITH (pages_per_range = 128);

-- Date range query (typical in logs)
SELECT * FROM access_logs WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31';

Caution: on columns that are not physically ordered, BRIN is useless. It works well on SERIAL or TIMESTAMP columns inserted in chronological order. Do not use it on columns with random values or frequent updates.

How to create a partial index for selective queries?

A partial index includes only rows that satisfy a condition. It reduces size and write overhead. Classic example: indexing only active products:

CREATE TABLE products (
    id SERIAL,
    name TEXT,
    price NUMERIC,
    status TEXT CHECK (status IN ('active', 'inactive', 'pending'))
);

-- Partial index: only active products
CREATE INDEX idx_products_active_price ON products (price)
WHERE status = 'active';

-- Query that uses the index
SELECT * FROM products WHERE status = 'active' AND price > 50;

Another common case: excluding soft-deleted rows (deleted_at IS NULL). With a partial index, deleted rows don't clutter the index. Result: smaller index, faster writes, identical reads.

What to do now

Analyze your slow queries: enable pg_stat_statements and identify those that do full scans on JSONB, full-text, or geographic columns.
Choose the right index: for arrays/JSONB → GIN; for geographic search or text similarity → GiST; for ordered time-series → BRIN; for fixed subset filters → partial.
Create a test index: use CREATE INDEX CONCURRENTLY to avoid blocking writes. Verify with EXPLAIN (ANALYZE, BUFFERS).
Monitor space: SELECT pg_size_pretty(pg_indexes_size('table_name'));.
Remove unused indexes: pg_stat_user_indexes with idx_scan = 0.

At Meteora Web, we do this every day: we optimize PostgreSQL databases for e-commerce and web applications. For more depth, read our pillar guide on Advanced PostgreSQL.