intermediatePerformancePostgreSQL Specific100% Free

Indexing in PostgreSQL

Definition

PostgreSQL supports multiple index types including B-tree, Hash, GiST, GIN, BRIN, and SP-GiST, each optimized for different data types and query patterns.

Introduction to Indexing in PostgreSQL

PostgreSQL offers the most diverse index type selection of any open-source database. Beyond the standard B-tree index, PostgreSQL's GIN indexes power full-text search and JSONB queries, GiST handles geometric and range data, and BRIN provides efficient coverage for naturally ordered large tables.

Syntax

SQL

-- Standard B-tree
CREATE INDEX idx_col ON table(col);

-- GIN for full-text / JSONB
CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('english', body));

-- Partial index (only indexes rows matching condition)
CREATE INDEX idx_active ON orders(customer_id) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

Examples

Partial Index for Active Records

-- Only index active orders (not archived)
CREATE INDEX idx_active_orders_customer
ON orders(customer_id, created_at)
WHERE status = 'active';

-- This query uses the partial index (much smaller than full index)
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'active'
ORDER BY created_at DESC;

Partial indexes are smaller, faster, and cheaper to maintain than full indexes. If 90% of orders are archived, the partial index covers only 10% of rows.

Try in Playground

GIN Index for JSONB

CREATE INDEX idx_user_prefs ON users USING GIN(preferences);

-- Now these JSONB queries use the index:
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';
SELECT * FROM users WHERE preferences ? 'newsletter';

GIN (Generalized Inverted Index) indexes all keys and values in a JSONB column, enabling fast @> (contains) and ? (key exists) operators.

Try in Playground

Common Mistakes

Using a regular B-tree index on a JSONB column — use GIN instead

Creating indexes without CONCURRENTLY on production tables — locks the table during build

Forgetting to run VACUUM ANALYZE after bulk data changes

Not using expression indexes for case-insensitive lookups (use LOWER(email))

Frequently Asked Questions

What index types does PostgreSQL support?

PostgreSQL supports: B-tree (default, works for <, <=, =, >=, >, BETWEEN, LIKE 'prefix%'), Hash (only =, not always faster than B-tree), GiST (geometric, range, full-text), GIN (JSONB, arrays, full-text — great for containment queries), BRIN (block range index — efficient for large sorted data like timestamps), SP-GiST (space partitioned, specialized).

What is a partial index in PostgreSQL?

A partial index is built on a subset of rows, defined by a WHERE clause: CREATE INDEX idx ON orders(customer_id) WHERE status = 'pending'. The index only includes rows where status is 'pending', making it smaller and faster. Ideal when queries almost always filter on a specific value.

How do you create indexes without locking the table in PostgreSQL?

Use CREATE INDEX CONCURRENTLY: CREATE INDEX CONCURRENTLY idx_name ON table(col). This builds the index without holding a lock that blocks reads and writes, but takes longer and cannot be done inside a transaction block. Always use CONCURRENTLY for production databases.

Practice This in the SQL Playground

Write real queries, see live results, and master Indexing in PostgreSQL hands-on. 100% free.

Open SQL Playground Browse All Lessons