Database Indexing & Query Optimization

Make the DB up to 100x faster with the right indexes, the right queries and the right connection handling.

Without an index the DB scans the whole table: finding one user among 10 crore rows takes seconds.
Slow queries eat up the DB's CPU and connections, and the whole site becomes slow.
Mistakes like the N+1 query run 100 queries on a page instead of 1.
Creating a new DB connection on every request is expensive; connection limits run out quickly.

B-tree index: a sorted tree, the default index for equality and range queries (=, <, >, BETWEEN, ORDER BY).
Hash index: for equality (=) only; GIN/GiST (PostgreSQL) for full-text, JSONB, geo.
Composite index (a, b, c): the leftmost prefix rule; it works on a, (a,b), (a,b,c), not on b alone.
Covering index: if all the query's columns are in the index, the table need not be read at all (index-only scan).
EXPLAIN / EXPLAIN ANALYZE: shows the query plan: Seq Scan vs Index Scan, rows, cost, actual time.
N+1 problem: 1 query for the list + 1 query for each item. Fix: JOIN, IN (...) or ORM eager loading.
Connection pooling: reuse connections that already exist; on the app side (HikariCP) or via a proxy (PgBouncer, RDS Proxy).
Trade-off: every index slows down writes and takes disk space; create only the necessary indexes.
Pagination: to avoid large OFFSETs use keyset/cursor pagination (WHERE id > last_id LIMIT n).

Without index vs with index: SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20

1.No index: 50 crore rows in orders, the Seq Scan reads every row from row 1 to 50 crore
2.EXPLAIN ANALYZE: Seq Scan, 8 sec
3.Index (user_id, created_at DESC): B-tree root -> branch -> leaf straight to user 42
4.User 42's orders are already sorted by created_at DESC, take the top 20
5.EXPLAIN ANALYZE: Index Scan, 3 ms

The N+1 problem and its fix

1.The app runs 1 query for the list + 20 separate queries for the restaurant names of 20 orders = 21 queries
2.Fix: JOIN (or WHERE id IN ..., or ORM eager loading)
3.Now only 1 query goes to the DB

Connection pooling at sale time

Result: p99 latency drops from seconds to 50 ms

Tool	Type	How it is used
EXPLAIN / EXPLAIN ANALYZE	Query planner tool	Shows the query plan and actual time in MySQL and PostgreSQL.
pg_stat_statements / MySQL slow query log	Query monitoring	To find the slowest and the most frequently run queries.
PgBouncer	Connection pooler (proxy)	Sits in front of PostgreSQL and multiplexes thousands of client connections onto a few hundred DB connections.
HikariCP	JDBC connection pool	The default fast connection pool of Java/Spring Boot, inside the app.
AWS RDS Proxy / ProxySQL	Managed / MySQL proxy	Connection pooling, failover and read/write split; useful for serverless such as Lambda.
AWS Performance Insights / Azure Query Store / Cloud SQL Insights	Cloud DB monitoring	Show top SQL, wait events and load visually.

Point	PgBouncer	HikariCP	AWS RDS Proxy
Where it runs	A separate proxy process between the app and the DB	Inside the app (JVM library)	AWS managed proxy
Database	PostgreSQL	Any JDBC DB	RDS/Aurora MySQL and PostgreSQL
Pooling scope	Shared across all app servers	Each app instance has its own pool	Shared across all clients
Modes / features	Session, transaction, statement pooling	Fast pool, leak detection, timeouts	Pooling, IAM auth, faster failover
Ops effort	Deploy and manage it yourself	Just configuration	Managed, extra cost
Best for	Many app pods or short-lived connections	Java/Spring apps	Lambda/serverless and RDS failover

Index the columns that appear often in WHERE, JOIN, ORDER BY and have high selectivity.
In a composite index put the equality columns first and the range column after.
HikariCP: in every Java app; also add PgBouncer when very many pods/servers connect to the DB.
RDS Proxy: for AWS Lambda or when you want a shorter failover time.
For every slow query run EXPLAIN ANALYZE first, then add an index or rewrite the query.

The "My Orders" page of a Swiggy/Zomato-like app (an illustrative scenario)

Query: SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20; the orders table has 50 crore rows.
EXPLAIN shows a Seq Scan, 8 seconds: there is no index.
Created CREATE INDEX idx_orders_user_time ON orders (user_id, created_at DESC);
Now it is an Index Scan, the query takes 3 ms: the sort also came from the index.
On the page each order's restaurant name came from a separate query (20 orders = 21 queries, N+1). Added a JOIN and made it 1 query.
At sale time 500 app pods x 20 connections = 10,000 connections were more than PostgreSQL could handle; added PgBouncer in transaction mode, so the DB sees only 300 connections.
Result: p99 latency went from seconds to 50 ms.

An index on every column is a mistake: writes get slow and disk use doubles; drop unused indexes.
Applying a function on an indexed column (WHERE LOWER(email) = ...) is a mistake: the index will not be used, create an expression index.
SELECT * and pagination like OFFSET 100000 are mistakes: use keyset/cursor pagination.
Interview tip: remember all three: N+1, the composite index leftmost rule and connection pooling. Numbers: 8 sec Seq Scan -> 3 ms Index Scan, 10,000 connections -> 300.