Indexes: B-tree, Unique & Partial
Indexes: B-tree, Unique & Partial
An index is a separate data structure that Postgres maintains alongside your table to speed up lookups. Without an index, every query that filters by a column must scan all rows — a sequential scan that becomes painfully slow as the table grows.
B-tree Indexes (Default)
The default index type is B-tree. It is efficient for equality (=), range (<, >, BETWEEN), and ORDER BY operations on most data types.
-- Basic index on a single column
CREATE INDEX idx_posts_author ON posts (author_id);
-- Composite index — column order matters!
-- Best for queries that filter on (status, created_at) together
-- or on (status) alone, but NOT on (created_at) alone.
CREATE INDEX idx_posts_status_created ON posts (status, created_at DESC);
-- Check which indexes exist on a table
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'posts';Unique Indexes
A UNIQUE constraint automatically creates a unique index. You can also create a unique index directly for more control (e.g. adding WHERE conditions).
-- Unique index (equivalent to adding UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- Case-insensitive unique email using a functional index
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));Partial Indexes
A partial index only indexes rows that satisfy a WHERE clause. This keeps the index small and speeds up the specific queries you care about.
-- Only index published posts — queries like
-- WHERE published = true AND slug = '...' will use this
CREATE INDEX idx_posts_published_slug
ON posts (slug)
WHERE published = true;
-- Only index active users
CREATE INDEX idx_users_active_email
ON users (email)
WHERE is_active = true;Other Index Types
- GIN — Generalised Inverted Index. Best for
jsonb, arrays, and full-text search. - GiST — Generalised Search Tree. Used for geometric types and range types.
- Hash — Only for equality checks. Rarely needed; B-tree is usually better.
- BRIN — Block Range INdex. Very small index for naturally ordered columns like timestamps in append-only tables.
Every index speeds up reads but slows down writes because Postgres must update the index on every INSERT, UPDATE, and DELETE. Only add indexes that your actual query patterns need — do not index every column.
-- Create an index without blocking reads/writes (for production tables)
CREATE INDEX CONCURRENTLY idx_posts_created ON posts (created_at);
-- Drop an index
DROP INDEX idx_posts_created;