Speed up queries with indexes — the everyday B-tree, unique and multicolumn indexes, partial and expression indexes, plus GIN for JSON and full-text search and BRIN for huge time-ordered tables.
Why: without an index, finding matching rows means scanning the whole table top to bottom. An index is a sorted lookup structure (like the index at the back of a book) that lets PostgreSQL jump straight to the rows it needs. The default type is a B-tree, perfect for equality and range filters.
CREATE INDEX idx_orders_user_id ON orders (user_id);-- now this query can jump straight to the user's orders
SELECT * FROM orders WHERE user_id = 2;Why: a UNIQUE index also enforces that no two rows share the value, so it doubles as a constraint. A multicolumn index covers filters that use several columns together — order matters, so put the column you filter on most first.
CREATE UNIQUE INDEX idx_users_email ON users (email);-- good for: WHERE user_id = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);Why: a partial index only covers rows matching a condition, so it stays small and fast when you almost always query one subset (like active users). An expression index indexes the result of a function, so a search on that transformed value can use the index.
-- only index the rows you actually query
CREATE INDEX idx_active_users ON users (email) WHERE is_active = true;-- index lower(email) so case-insensitive lookups are fast
CREATE INDEX idx_users_lower_email ON users (lower(email));SELECT * FROM users WHERE lower(email) = 'ada@example.com';Why: a B-tree cannot look "inside" a jsonb document or a block of text. A GIN index does, making it the right choice for searching keys/values in jsonb columns and for full-text search.
CREATE INDEX idx_products_metadata ON products USING gin (metadata);-- fast: does the JSON contain this key/value?
SELECT * FROM products WHERE metadata @> '{"color": "red"}';Why: a BRIN index is tiny and ideal for very large tables whose rows are naturally ordered, like an append-only log sorted by time. Note: indexes are not free — each one slows down INSERT/UPDATE/DELETE and uses disk, so only add indexes that real queries need.
-- tiny index, ideal for a big, time-ordered table like orders
CREATE INDEX idx_orders_created ON orders USING brin (created_at);