How to Create and Use Indexes in PostgreSQL

Indexes dramatically speed up queries by letting PostgreSQL locate rows without scanning the entire table. However, they consume disk space and slow down writes, so you should create them strategically.

Creating a Basic B-tree Index

-- Single column index
CREATE INDEX idx_customers_email ON customers (email);

-- Composite index (multi-column)
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users (username);

B-tree is the default index type and works well for equality and range queries. Composite indexes are useful when you frequently filter on multiple columns together. The column order matters -- put the most selective column first.

Partial Indexes

-- Only index active users (smaller, faster)
CREATE INDEX idx_users_active_email
ON users (email)
WHERE is_active = true;

-- Only index pending orders
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

Partial indexes cover only a subset of rows. If your queries consistently filter on a condition, a partial index is smaller and faster than a full index.

GIN Indexes for JSONB and Arrays

-- Index JSONB columns for containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Now this query uses the index
SELECT * FROM products
WHERE metadata @> '{"color": "red"}';

-- Index array columns
CREATE INDEX idx_products_tags ON products USING GIN (tags);

Checking Index Usage

-- See if your query uses an index
EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'alice@example.com';

-- List all indexes on a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'customers';

When to Create Indexes

• Columns used frequently in WHERE, JOIN, or ORDER BY clauses
• Foreign key columns (PostgreSQL does not index them automatically)
• Columns with high cardinality (many distinct values)
• Avoid over-indexing write-heavy tables where insert speed matters more