Add database indexes for columns used in WHERE, JOIN, and ORDER BY clauses. Without indexes, the database scans every row in the table for every query — a table with 1M rows takes seconds instead of milliseconds.
Without indexes, the database performs a sequential scan — reading every row in the table to find matches. On a table with 1 million rows, a query that should take 1ms takes 500ms+. As the table grows, performance degrades linearly. Every unindexed WHERE clause, JOIN condition, and ORDER BY is a ticking time bomb that will cause performance issues as data grows.
BeforeMerge scans your pull requests against this rule and 4+ others. Get actionable feedback before code ships.
A database index is like a book's index — instead of reading every page to find a topic, you look it up in the index and jump directly to the right page. Without an index, the database must perform a sequential scan, reading every row to find matches.
The impact scales with data:
| Rows | Without index | With index |
|---|---|---|
| 1,000 | 1ms | <1ms |
| 100,000 | 50ms | <1ms |
| 1,000,000 | 500ms | 1ms |
| 10,000,000 | 5,000ms | 1ms |
A query that works fine in development with 100 rows becomes unusable in production with 1 million rows. This is the #1 cause of "it was fast, then it got slow" database performance issues.
Add indexes for:
-- Table with no indexes beyond the primary key
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
customer_id uuid NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
total_amount numeric NOT NULL
);
-- This query scans every row in the orders table
SELECT * FROM orders
WHERE customer_id = 'abc-123'
AND status = 'pending'
ORDER BY created_at DESC;CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
customer_id uuid NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
total_amount numeric NOT NULL
);
-- Index for the most common query pattern
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);
-- The same query now uses the index — milliseconds instead of seconds
SELECT * FROM orders
WHERE customer_id = 'abc-123'
AND status = 'pending'
ORDER BY created_at DESC;Use EXPLAIN ANALYZE to check if queries use indexes:
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 'abc-123';If the output shows Seq Scan instead of Index Scan, the query is not using an index.
Also check for missing indexes on foreign keys:
SELECT
tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
SELECT 1 FROM pg_indexes
WHERE tablename = tc.table_name
AND indexdef LIKE '%' || kcu.column_name || '%'
);pg_stat_statements or application loggingEXPLAIN ANALYZE on each slow querypg_stat_user_indexes — drop unused indexes to save write performance