|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/optimization

$cat docs/query-optimization.md

updated Recently·50 min read·published

Query Optimization

◆Optimization◆Performance◆Databases◆Intermediate to Advanced🎯Free Tools

Introduction

Database performance is often the bottleneck in web applications. A single slow query can bring your entire application to its knees under load. Understanding how databases execute queries, how indexes work, and how to read execution plans is essential for building performant systems.

Optimization is not about making every query fast — it is about making the right queries fast. Profile your application, identify the slowest queries, and optimize those first. Premature optimization of fast queries wastes time and adds complexity.

This guide covers execution plan analysis, indexing strategies, the N+1 problem, connection pooling, caching patterns, and database-specific tuning.

ℹ

info

The cardinal rule of database optimization: measure first, optimize second. Use EXPLAIN ANALYZE (PostgreSQL) orEXPLAIN (MySQL) to understand what the database is actually doing before changing anything.

Reading Execution Plans

An execution plan shows how the database will execute a query. It reveals which indexes are used, how tables are joined, and where bottlenecks exist. Learning to read execution plans is the single most valuable database skill.

execution-plans.sql

SQL

1	-- PostgreSQL: Detailed execution plan
2	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
3	SELECT u.name, COUNT(o.id) AS order_count
4	FROM users u
5	JOIN orders o ON o.user_id = u.id
6	WHERE o.created_at >= '2025-01-01'
7	GROUP BY u.id, u.name
8	ORDER BY order_count DESC
9	LIMIT 10;
10
11	-- What to look for:
12	-- 1. Seq Scan → Missing index (bad for large tables)
13	-- Fix: Add an index on the filtered/joined columns
14
15	-- 2. Nested Loop vs Hash Join vs Merge Join
16	-- Nested Loop: good for small result sets
17	-- Hash Join: good for large, unsorted joins
18	-- Merge Join: good for pre-sorted data
19
20	-- 3. Sort → Check if sort_key matches an index
21	-- If "Sort Method: external merge", you need more work_mem
22
23	-- 4. Rows Estimated vs Actual (rows)
24	-- Large discrepancy → stale statistics → run ANALYZE
25
26	-- 5. Buffers: shared hit vs shared read
27	-- High "read" means hitting disk (slow)
28	-- High "hit" means hitting cache (fast)
29
30	-- MySQL equivalent
31	EXPLAIN FORMAT=VERBOSE
32	SELECT u.name, COUNT(o.id)
33	FROM users u
34	JOIN orders o ON o.user_id = u.id
35	WHERE o.created_at >= '2025-01-01'
36	GROUP BY u.id, u.name;
37
38	-- Check actual execution time
39	EXPLAIN ANALYZE
40	SELECT * FROM orders WHERE user_id = 'u1' AND status = 'completed';

explain-output.txt

Bash

1	# Key EXPLAIN output explained:
2
3	# PostgreSQL example output:
4	# Sort (cost=1000.00..1000.03 rows=10)
5	# Sort Key: (count(o.id)) DESC
6	# -> Limit (cost=999.50..999.52 rows=10)
7	# -> HashAggregate (cost=999.50..999.52 rows=10)
8	# Group Key: u.id
9	# -> Hash Join (cost=10.00..999.00 rows=1000)
10	# Hash Cond: (o.user_id = u.id)
11	# -> Index Scan using idx_orders_created on orders o
12	# Filter: (created_at >= '2025-01-01')
13	# Rows Removed by Filter: 5000
14	# -> Hash (cost=8.00..8.00 rows=100)
15	# -> Seq Scan on users u
16
17	# Good signs:
18	# ✓ Index Scan (using an index)
19	# ✓ Hash Join (efficient for large joins)
20	# ✓ Rows Estimated ≈ Rows Actual
21
22	# Bad signs:
23	# ✗ Seq Scan on large table (missing index)
24	# ✗ Sort Method: external merge (insufficient memory)
25	# ✗ Rows Estimated >> Rows Actual (stale stats)
26	# ✗ Nested Loop with high loop count
27	# ✗ High "shared read" in BUFFERS output

🔥

pro tip

Run ANALYZE on your tables after bulk data changes. PostgreSQL stores table statistics that the query planner uses to estimate costs. Stale statistics lead to poor plan choices — for example, choosing a sequential scan when an index scan would be 100x faster.

Indexing Strategies

Indexes are the most impactful performance tool in your database arsenal. But not all indexes are equal — choosing the right type and columns requires understanding your query patterns.

indexing-strategies.sql

SQL

1	-- Strategy 1: Cover the WHERE clause
2	-- Query: SELECT * FROM orders WHERE user_id = ? AND status = ?
3	CREATE INDEX idx_orders_user_status ON orders (user_id, status);
4
5	-- Strategy 2: Cover the ORDER BY clause
6	-- Query: SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC
7	CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
8
9	-- Strategy 3: Covering index (INCLUDE avoids table lookup)
10	-- Query: SELECT user_id, status, total FROM orders WHERE user_id = ?
11	CREATE INDEX idx_orders_covering ON orders (user_id, status)
12	INCLUDE (total, created_at);
13
14	-- Strategy 4: Partial index (index only what you query)
15	-- Query: SELECT * FROM orders WHERE status = 'pending'
16	CREATE INDEX idx_orders_pending ON orders (created_at, user_id)
17	WHERE status = 'pending';
18
19	-- Strategy 5: Expression index (index computed values)
20	-- Query: SELECT * FROM users WHERE LOWER(email) = ?
21	CREATE INDEX idx_users_lower_email ON users (LOWER(email));
22
23	-- Strategy 6: Composite index following ESR rule
24	-- E: Equality (status = 'completed')
25	-- S: Sort (created_at DESC)
26	-- R: Range (total > 100)
27	CREATE INDEX idx_orders_esr ON orders (status, created_at DESC, total);
28
29	-- Monitor index usage
30	SELECT
31	schemaname,
32	tablename,
33	indexrelname AS index_name,
34	idx_scan AS times_used,
35	idx_tup_read AS rows_read,
36	pg_size_pretty(pg_relation_size(indexrelid)) AS size
37	FROM pg_stat_user_indexes
38	WHERE schemaname = 'public'
39	ORDER BY idx_scan ASC; -- Least used first (candidates for removal)
40
41	-- Find missing indexes (queries doing sequential scans)
42	SELECT
43	relname AS table_name,
44	seq_scan,
45	seq_tup_read,
46	idx_scan,
47	pg_size_pretty(pg_relation_size(relid)) AS table_size
48	FROM pg_stat_user_tables
49	WHERE seq_scan > 100
50	ORDER BY seq_tup_read DESC;

⚠

warning

Indexes are not free. Each index adds overhead to write operations and consumes storage. A table with 10 indexes will have 10x slower INSERT operations. Monitorpg_stat_user_indexes and remove unused indexes.

The N+1 Problem

The N+1 problem occurs when your code issues one query to fetch a list of items, then N additional queries to fetch related data for each item. It is the most common performance anti-pattern in ORM-based applications.

n-plus-one.ts

TypeScript

1	// THE N+1 PROBLEM (Prisma example)
2	// 1 query to fetch 100 users + 100 queries for their orders = 101 queries!
3	const users = await prisma.user.findMany({ take: 100 });
4	for (const user of users) {
5	const orders = await prisma.order.findMany({
6	where: { userId: user.id }
7	});
8	}
9
10	// FIX 1: Eager loading (include)
11	const usersWithOrders = await prisma.user.findMany({
12	take: 100,
13	include: {
14	orders: {
15	orderBy: { createdAt: "desc" },
16	take: 5
17	}
18	}
19	});
20	// Single query with LEFT JOIN
21
22	// FIX 2: Raw SQL JOIN
23	const result = await db.execute(sql`
24	SELECT u.id, u.name, o.id AS order_id, o.total, o.created_at
25	FROM users u
26	LEFT JOIN orders o ON o.user_id = u.id
27	ORDER BY u.name, o.created_at DESC
28	`);
29
30	// FIX 3: DataLoader pattern (batch queries)
31	import DataLoader from "dataloader";
32
33	const orderLoader = new DataLoader(async (userIds: string[]) => {
34	const orders = await prisma.order.findMany({
35	where: { userId: { in: userIds } }
36	});
37
38	// Group orders by userId
39	const ordersByUser = new Map();
40	for (const userId of userIds) {
41	ordersByUser.set(userId, orders.filter(o => o.userId === userId));
42	}
43
44	return userIds.map(id => ordersByUser.get(id) \|\| []);
45	});
46
47	// Usage: automatically batches and caches within a single request
48	const orders = await orderLoader.load(userId);
49
50	// Detect N+1: Count queries per request
51	const prisma = new PrismaClient({
52	log: [{ level: "query", emit: "event" }]
53	});
54
55	let queryCount = 0;
56	prisma.$on("query", () => queryCount++);
57
58	// After request completes:
59	console.log(`Total queries: ${queryCount}`); // Should be < 10 for most requests

danger

The N+1 problem gets worse under load. If 100 concurrent users each trigger an N+1 query with 100 items, your database receives 1,000,100 queries instead of 100. Always profile with production-like data volumes.

Connection Pooling

Creating a new database connection is expensive — it involves TCP handshakes, authentication, and memory allocation on the database server. Connection pooling maintains a cache of reusable connections.

connection-pooling.ts

TypeScript

1	// PostgreSQL connection pooling options:
2
3	// Option 1: Application-level pool (node-postgres)
4	import { Pool } from "pg";
5
6	const pool = new Pool({
7	connectionString: process.env.DATABASE_URL,
8	max: 20, // max connections in pool
9	min: 5, // min idle connections
10	idleTimeoutMillis: 30000, // close idle connections after 30s
11	connectionTimeoutMillis: 5000, // fail after 5s if no connection
12	statement_timeout: 30000, // kill queries after 30s
13	query_timeout: 30000
14	});
15
16	// Monitor pool usage
17	pool.on("connect", () => console.log("New connection created"));
18	pool.on("acquire", () => console.log("Connection acquired from pool"));
19	pool.on("release", () => console.log("Connection released back to pool"));
20	pool.on("remove", () => console.log("Connection removed from pool"));
21
22	// Check pool status
23	const stats = pool.totalCount; // total connections
24	const idle = pool.idleCount; // idle connections
25	const waiting = pool.waitingCount; // waiting for connection
26
27	// Option 2: PgBouncer (external connection pooler)
28	// Install: sudo apt install pgbouncer
29	// Config: /etc/pgbouncer/pgbouncer.ini
30	// [databases]
31	// myapp = host=localhost port=5432 dbname=myapp
32	//
33	// [pgbouncer]
34	// pool_mode = transaction
35	// max_client_conn = 1000
36	// default_pool_size = 20
37
38	// Option 3: Prisma Accelerate (serverless connection pooling)
39	// datasource db {
40	// provider = "postgresql"
41	// url = env("DATABASE_URL") // direct connection
42	// directUrl = env("DIRECT_DATABASE_URL") // for migrations
43	// }
44
45	// Option 4: Neon serverless driver (WebSocket-based)
46	import { neon } from "@neondatabase/serverless";
47	const sql = neon(process.env.DATABASE_URL!);
48	const result = sql`SELECT * FROM users WHERE id = ${userId}`;

ℹ

info

In serverless environments (Vercel, AWS Lambda), each function invocation creates a new connection. Use PgBouncer in transaction pooling mode, Prisma Accelerate, or a serverless-compatible driver like Neon's WebSocket driver to avoid exhausting your database connection limit.

Caching Layers

Caching reduces database load by storing frequently accessed data in faster storage (typically Redis or in-memory). The key decisions are what to cache, when to invalidate, and how to handle cache misses.

caching-layers.ts

TypeScript

1	import { createClient } from "redis";
2	import { Pool } from "pg";
3
4	const redis = createClient({ url: process.env.REDIS_URL });
5	const db = new Pool({ connectionString: process.env.DATABASE_URL });
6
7	// Multi-level cache strategy
8	async function getCachedProduct(productId: string) {
9	// L1: Redis cache (sub-ms)
10	const cached = await redis.get(`product:${productId}`);
11	if (cached) return JSON.parse(cached);
12
13	// L2: Database (ms)
14	const result = await db.query(
15	"SELECT * FROM products WHERE id = $1", [productId]
16	);
17	const product = result.rows[0];
18	if (!product) return null;
19
20	// Populate cache
21	await redis.setEx(`product:${productId}`, 300, JSON.stringify(product));
22	return product;
23	}
24
25	// Cache warming: pre-populate cache for hot data
26	async function warmProductCache() {
27	const hotProducts = await db.query(
28	"SELECT id FROM products WHERE view_count > 1000 ORDER BY view_count DESC LIMIT 100"
29	);
30
31	const pipeline = redis.multi();
32	for (const row of hotProducts.rows) {
33	const product = await db.query("SELECT * FROM products WHERE id = $1", [row.id]);
34	pipeline.setEx(`product:${row.id}`, 3600, JSON.stringify(product.rows[0]));
35	}
36	await pipeline.exec();
37	console.log(`Warmed cache for ${hotProducts.rows.length} products`);
38	}
39
40	// Cache-aside with stampede prevention
41	async function getProductWithStampedeProtection(productId: string) {
42	const key = `product:${productId}`;
43	const cached = await redis.get(key);
44	if (cached) return JSON.parse(cached);
45
46	// Prevent cache stampede: only one process fetches from DB
47	const lockKey = `${key}:lock`;
48	const locked = await redis.set(lockKey, "1", { NX: true, PX: 5000 });
49
50	if (!locked) {
51	// Another process is fetching — wait and retry
52	await new Promise(r => setTimeout(r, 100));
53	return getProductWithStampedeProtection(productId);
54	}
55
56	try {
57	const result = await db.query("SELECT * FROM products WHERE id = $1", [productId]);
58	const product = result.rows[0];
59	await redis.setEx(key, 300, JSON.stringify(product));
60	return product;
61	} finally {
62	await redis.del(lockKey);
63	}
64	}
65
66	// Cache invalidation on write
67	async function updateProduct(productId: string, data: Partial<Product>) {
68	const result = await db.query(
69	"UPDATE products SET name = $1, price = $2 WHERE id = $3 RETURNING *",
70	[data.name, data.price, productId]
71	);
72
73	// Invalidate cache
74	await redis.del(`product:${productId}`);
75
76	return result.rows[0];
77	}

Bulk Operations

Bulk operations process many rows in a single query, drastically reducing round-trips and improving throughput. They are essential for data imports, batch updates, and ETL pipelines.

bulk-operations.sql

SQL

1	-- Bulk insert with ON CONFLICT (upsert)
2	INSERT INTO products (name, price, category, stock)
3	VALUES
4	('Widget A', 9.99, 'widgets', 100),
5	('Widget B', 19.99, 'widgets', 50),
6	('Gadget X', 29.99, 'gadgets', 25),
7	('Gadget Y', 39.99, 'gadgets', 0)
8	ON CONFLICT (name) DO UPDATE
9	SET
10	price = EXCLUDED.price,
11	stock = EXCLUDED.stock,
12	updated_at = NOW();
13
14	-- Bulk update with VALUES list
15	UPDATE products AS p
16	SET
17	price = v.new_price,
18	stock = v.new_stock
19	FROM (VALUES
20	('product-1', 12.99, 200),
21	('product-2', 24.99, 150),
22	('product-3', 34.99, 75)
23	) AS v(id, new_price, new_stock)
24	WHERE p.id = v.id::uuid;
25
26	-- Batch processing (for millions of rows)
27	-- Process in chunks to avoid memory issues
28	DO $$
29	DECLARE
30	batch_size INT := 10000;
31	total_processed INT := 0;
32	affected INT;
33	BEGIN
34	LOOP
35	UPDATE orders
36	SET status = 'archived'
37	WHERE id IN (
38	SELECT id FROM orders
39	WHERE status = 'completed'
40	AND created_at < NOW() - INTERVAL '1 year'
41	LIMIT batch_size
42	FOR UPDATE SKIP LOCKED -- skip rows locked by other transactions
43	);
44
45	GET DIAGNOSTICS affected = ROW_COUNT;
46	total_processed := total_processed + affected;
47
48	EXIT WHEN affected = 0;
49
50	RAISE NOTICE 'Archived % orders (total: %)', affected, total_processed;
51
52	-- Small delay to avoid overwhelming the database
53	PERFORM pg_sleep(0.1);
54	END LOOP;
55	END $$;
56
57	-- COPY for massive imports (PostgreSQL fastest method)
58	COPY products (name, price, category, stock)
59	FROM STDIN WITH (FORMAT csv, HEADER true);
60	-- Can import millions of rows per second

ℹ

info

Use FOR UPDATE SKIP LOCKED when processing queues or batch jobs. It prevents multiple workers from processing the same rows, enabling efficient parallel processing without explicit locking.

Anti-Patterns and Fixes

Certain SQL patterns consistently cause performance problems. Recognizing and fixing these anti-patterns will prevent most performance issues.

anti-patterns.sql

SQL

1	-- ANTI-PATTERN 1: SELECT * (fetches unnecessary data)
2	-- BAD
3	SELECT * FROM orders WHERE user_id = $1;
4	-- GOOD: Only select what you need
5	SELECT id, status, total, created_at FROM orders WHERE user_id = $1;
6
7	-- ANTI-PATTERN 2: LIKE '%prefix%' (prevents index usage)
8	-- BAD
9	SELECT * FROM users WHERE name LIKE '%alice%';
10	-- GOOD: Use full-text search or prefix match
11	SELECT * FROM users WHERE name ILIKE 'alice%';
12	-- Or use a GIN index for substring search
13
14	-- ANTI-PATTERN 3: NOT IN with NULLs (returns no rows)
15	-- BAD
16	SELECT * FROM products WHERE category_id NOT IN (SELECT id FROM categories WHERE archived = true);
17	-- GOOD: Use NOT EXISTS or LEFT JOIN
18	SELECT * FROM products p
19	WHERE NOT EXISTS (SELECT 1 FROM categories c WHERE c.id = p.category_id AND c.archived = true);
20
21	-- ANTI-PATTERN 4: Implicit type conversion (prevents index usage)
22	-- BAD: Comparing varchar column to integer
23	SELECT * FROM orders WHERE user_id = 123; -- if user_id is varchar
24	-- GOOD: Use correct type
25	SELECT * FROM orders WHERE user_id = '123';
26
27	-- ANTI-PATTERN 5: Functions on indexed columns
28	-- BAD: Prevents index usage on created_at
29	SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';
30	-- GOOD: Use range query
31	SELECT * FROM orders
32	WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16';
33
34	-- ANTI-PATTERN 6: OR preventing index use
35	-- BAD
36	SELECT * FROM orders WHERE user_id = 'u1' OR status = 'completed';
37	-- GOOD: Use UNION ALL (each part can use its own index)
38	SELECT * FROM orders WHERE user_id = 'u1'
39	UNION ALL
40	SELECT * FROM orders WHERE status = 'completed' AND user_id != 'u1';
41
42	-- ANTI-PATTERN 7: Unnecessary subqueries
43	-- BAD
44	SELECT * FROM (SELECT * FROM orders WHERE status = 'completed') sub WHERE sub.total > 100;
45	-- GOOD: Direct filter
46	SELECT * FROM orders WHERE status = 'completed' AND total > 100;

PostgreSQL Tuning

PostgreSQL has numerous configuration parameters that affect performance. The right settings depend on your hardware, workload, and available memory.

postgresql-tuning.conf

Bash

1	# postgresql.conf key settings:
2
3	# Memory (most important)
4	shared_buffers = "4GB" # 25% of total RAM
5	effective_cache_size = "12GB" # 75% of total RAM
6	work_mem = "256MB" # per-operation memory for sorts/hashes
7	maintenance_work_mem = "1GB" # for VACUUM, CREATE INDEX
8
9	# Write-Ahead Log (WAL)
10	wal_buffers = "64MB"
11	checkpoint_completion_target = 0.9
12	max_wal_size = "4GB"
13	min_wal_size = "1GB"
14
15	# Query Planner
16	random_page_cost = 1.1 # SSD: 1.1, HDD: 4.0
17	effective_io_concurrency = 200 # SSD: 200, HDD: 2
18	default_statistics_target = 100 # higher = more accurate but slower ANALYZE
19
20	# Parallelism
21	max_worker_processes = 8
22	max_parallel_workers_per_gather = 4
23	max_parallel_workers = 8
24	max_parallel_maintenance_workers = 4
25
26	# Connections
27	max_connections = 100 # use PgBouncer for more
28
29	# Logging (for performance analysis)
30	log_min_duration_statement = 1000 # log queries > 1 second
31	log_statement = "none" # disable in production
32	shared_preload_libraries = "pg_stat_statements"
33
34	# Check current settings
35	SHOW shared_buffers;
36	SHOW work_mem;
37	SHOW effective_cache_size;
38
39	# Check if settings are applied
40	SELECT name, setting, unit
41	FROM pg_settings
42	WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

ℹ

info

The most impactful PostgreSQL tuning parameters areshared_buffers,effective_cache_size, andwork_mem. Start with these three and adjust based on your workload. Usepg_stat_statements to identify the slowest queries.

Performance Monitoring

Continuous monitoring catches performance regressions before they impact users. Track query performance, connection counts, cache hit rates, and replication lag.

monitoring.sql

SQL

1	-- pg_stat_statements: Top queries by total time
2	SELECT
3	query,
4	calls,
5	ROUND(total_exec_time::numeric, 2) AS total_ms,
6	ROUND(mean_exec_time::numeric, 2) AS avg_ms,
7	ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
8	rows,
9	ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct
10	FROM pg_stat_statements
11	ORDER BY total_exec_time DESC
12	LIMIT 20;
13
14	-- Cache hit ratio (should be > 99%)
15	SELECT
16	sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
17	FROM pg_statio_user_tables;
18
19	-- Table bloat (wasted space)
20	SELECT
21	schemaname,
22	tablename,
23	pg_size_pretty(pg_total_relation_size(schemaname \|\| '.' \|\| tablename)) AS total_size,
24	pg_size_pretty(pg_relation_size(schemaname \|\| '.' \|\| tablename)) AS table_size,
25	pg_size_pretty(pg_total_relation_size(schemaname \|\| '.' \|\| tablename) - pg_relation_size(schemaname \|\| '.' \|\| tablename)) AS index_size,
26	n_live_tup AS row_count,
27	n_dead_tup AS dead_rows,
28	ROUND(n_dead_tup::float / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
29	FROM pg_stat_user_tables
30	ORDER BY n_dead_tup DESC
31	LIMIT 10;
32
33	-- Active connections
34	SELECT
35	pid,
36	usename,
37	application_name,
38	client_addr,
39	state,
40	NOW() - query_start AS query_duration,
41	LEFT(query, 100) AS query_preview
42	FROM pg_stat_activity
43	WHERE state != 'idle'
44	ORDER BY query_duration DESC;
45
46	-- Index usage statistics
47	SELECT
48	indexrelname AS index_name,
49	idx_scan AS scans,
50	idx_tup_read AS tuples_read,
51	idx_tup_fetch AS tuples_fetched,
52	pg_size_pretty(pg_relation_size(indexrelid)) AS size
53	FROM pg_stat_user_indexes
54	ORDER BY idx_scan ASC; -- Least used indexes first

✓

best practice

Set up alerts for: cache hit ratio below 99%, active connections above 80% of max, query duration above 5 seconds, and replication lag above 10 seconds. These early warnings prevent outages.

Optimization Checklist

Use this checklist when investigating performance issues. Work through the items in order — most issues are caught in the first few steps.

optimization-checklist.sh

Bash

1	# Database Performance Optimization Checklist
2
3	# 1. MEASURE
4	# □ Enable pg_stat_statements
5	# □ Identify top 10 slowest queries
6	# □ Check cache hit ratio (> 99%)
7	# □ Check connection count vs max_connections
8	# □ Check for table bloat (dead tuples)
9
10	# 2. INDEX
11	# □ Add indexes for WHERE clauses
12	# □ Add composite indexes for JOINs
13	# □ Add covering indexes to avoid table lookups
14	# □ Remove unused indexes
15	# □ Check EXPLAIN plans use indexes
16
17	# 3. QUERY
18	# □ Fix N+1 queries (use JOINs or DataLoader)
19	# □ Replace SELECT * with specific columns
20	# □ Use EXISTS instead of COUNT(*) for existence checks
21	# □ Avoid LIKE '%prefix%' (use full-text search)
22	# □ Use UNION ALL instead of OR across indexed columns
23
24	# 4. ARCHITECTURE
25	# □ Add Redis caching for hot data
26	# □ Use connection pooling (PgBouncer or app-level)
27	# □ Consider read replicas for read-heavy workloads
28	# □ Batch bulk operations
29	# □ Move large analytics to materialized views
30
31	# 5. CONFIGURE
32	# □ Tune shared_buffers (25% of RAM)
33	# □ Tune work_mem (256MB for complex queries)
34	# □ Tune effective_cache_size (75% of RAM)
35	# □ Run ANALYZE after bulk data changes
36	# □ Monitor and adjust continuously

🔥

pro tip

Remember: premature optimization is the root of all evil. Profile first, identify the actual bottlenecks, and optimize those. Most applications have 3-5 queries that account for 80% of database load — focus on those.

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor