Database Query Optimization for Web Apps: A 2026 Field Guide
Most web app slowness is database slowness. The good news is that 80% of query problems come from a small set of patterns, and they all have fixes that don't require rewriting your app. Here's the field guide.
When a web application gets slow, the cause is almost always the database. Not always the database itself, but the queries hitting it, the patterns of access, the missing indexes, the N+1s, the unbounded result sets, and the rare query that locks a table for everyone else. The reassuring part is that the patterns that cause these problems are well-known and the fixes are usually straightforward. You don't need to rewrite your application to make it fast, you need to know what to look for and what to change. This field guide covers the query patterns we see most often in production Postgres and MySQL applications, the tools that surface them, and the fixes that hold up under real traffic.
The Single Most Common Cause: N+1 Queries
The N+1 query pattern is the runaway leader in performance bug counts. You fetch a list of N items, then for each item you fetch related data, producing N+1 total queries when one or two would have sufficed.
The classic shape:
// Fetches all posts (1 query)
const posts = await db.post.findMany();
// Fetches the author for each post (N queries)
const postsWithAuthors = await Promise.all(
posts.map(async post => ({
...post,
author: await db.user.findUnique({ where: { id: post.authorId } }),
}))
);
A page that loads 50 posts now runs 51 queries. Each one is fast on its own, but the latency compounds: 50 round trips of 5ms each is 250ms before the page can render. On a slow database connection it can be a full second.
The fix is to fetch the related data in the same query:
const posts = await db.post.findMany({
include: { author: true },
});
Most ORMs (Prisma, Drizzle, TypeORM, Sequelize) have a syntax for this. Use it. For raw SQL, this means a JOIN:
SELECT
p.id, p.title, p.content,
u.id AS author_id, u.name AS author_name, u.avatar_url
FROM posts p
JOIN users u ON p.author_id = u.id
ORDER BY p.created_at DESC
LIMIT 50;
A single query, a single round trip, dramatically faster. Most ORMs default to N+1 if you don't explicitly ask for the join. That default is the source of most production database load.
Catching N+1s Before They Ship
N+1 bugs are easy to miss in development because the dataset is small. The page loads fine with 5 records. Production has 5,000.
Two patterns catch them early:
Query logging in development. Log every query your ORM makes, watch the console as you click around. If a single page interaction produces 30 log lines, you have an N+1.
Test fixtures with realistic sizes. Seed your test database with enough data that performance problems are visible. 100 to 1,000 records per major table is usually enough.
Database query monitoring in production. Tools like Datadog APM, New Relic, or open-source pganalyze surface N+1 patterns automatically. They group similar queries and show the total count and time per endpoint. The endpoint making 200 queries in 800ms jumps out immediately.
Missing Indexes: The Other Big Win
After N+1s, missing indexes account for most of the remaining "why is this query slow" cases. The pattern is consistent: a query filters or sorts by a column that has no index. The database scans every row to find matches.
Postgres makes this easy to diagnose. Prefix any slow query with EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = '...' ORDER BY created_at DESC LIMIT 20;
The output shows the query plan. The phrase to look for is "Seq Scan" (sequential scan), meaning the database read every row. For a table with 10 million orders, this is a half-second query that should be 2 milliseconds.
The fix is an index on the columns being filtered or sorted:
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
A few rules for indexing:
Index columns used in WHERE, JOIN, and ORDER BY. Not every column, just the ones queries actually use.
Composite indexes follow the order of selectivity. Put the most selective column first. An index on (customer_id, created_at) works for filters on customer_id and filters on customer_id + ordering by created_at. It does not work for filters on created_at alone.
Indexes have a cost. Every write to the table also writes to every index. Tables with very high write rates (logs, events) should have minimal indexes. Read-heavy tables (products, users) can support many.
Don't index everything. Bulk-indexing every column "just in case" adds storage cost, slows down writes, and complicates query plans. Add indexes when a query needs them.
Unbounded Queries: The Time Bomb
A query that runs fast on a 1,000-row table can take down production when the table grows to 1 million rows. The pattern is fetching without a limit:
// Works fine when the user has 50 orders, breaks when they have 5,000
const orders = await db.order.findMany({
where: { userId },
include: { items: true },
});
Every list query should have a limit, ideally with pagination. Pagination by offset works for small datasets:
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET 100;
For larger datasets, cursor-based pagination is significantly faster:
SELECT * FROM orders
WHERE user_id = ? AND created_at < ?
ORDER BY created_at DESC
LIMIT 20;
Cursor pagination passes the timestamp of the last item in the previous page. The query uses the index efficiently and doesn't slow down as you paginate deeper. Offset pagination at page 1,000 is dramatically slower than at page 1.
The Hidden Cost of SELECT *
Selecting every column when you only need a few has real costs:
- More data to transfer over the network
- More memory used in the application
- Larger row sizes that don't fit in CPU cache as well
- Inability for Postgres to use index-only scans
When you only need three columns, ask for three columns:
const posts = await db.post.findMany({
select: { id: true, title: true, publishedAt: true },
});
For pages displaying summaries (lists, dashboards, search results), this is usually a 5 to 10x reduction in data transferred. On large tables with many columns, it can be more.
Counting Rows the Slow Way
The COUNT(*) query is innocent-looking and surprisingly slow on large tables. Counting 10 million rows takes seconds, every time, because Postgres has to actually count them.
Three patterns handle counts differently:
Estimated counts. For statistics dashboards or pagination controls where exact precision doesn't matter, Postgres's pg_class.reltuples gives you an approximate count instantly:
SELECT reltuples::bigint AS approximate_count
FROM pg_class
WHERE relname = 'orders';
Cached counts. For UI displays like "1,234 orders" where the number changes slowly, cache the count in Redis with a short TTL and a background refresh.
Counter columns. For frequently-displayed counts (followers, comment counts, likes), maintain a counter column on the parent record. Update it via trigger or application logic. This makes display instant and moves the cost to write time.
The wrong answer is running SELECT COUNT(*) FROM orders on every page load.
Connection Pooling and the Per-Request Limit
Web apps that connect directly to Postgres run into a hard limit: Postgres allows around 100 connections by default. A serverless function model that opens a new connection per request will exhaust this limit under any meaningful traffic.
The standard fix is connection pooling. PgBouncer sits between your application and Postgres, multiplexing many application connections onto a smaller pool of database connections. For Postgres in production, this is non-negotiable above any real traffic.
For serverless Postgres specifically (Neon, Supabase, AWS Aurora Serverless), use the platform's pooler endpoint. Connecting to the standard endpoint from a serverless function is the most common cause of "too many connections" errors.
Query Caching with Stale-While-Revalidate
For queries whose results don't need to be perfectly fresh, caching is the highest-leverage optimization. The pattern that works in production:
import { cache } from "@/lib/redis";
async function getPopularProducts() {
const cached = await cache.get("popular-products");
if (cached) return cached;
const products = await db.product.findMany({
orderBy: { sales: "desc" },
take: 10,
});
// Cache for 5 minutes
await cache.set("popular-products", products, 300);
return products;
}
For higher-traffic endpoints, stale-while-revalidate avoids the cache-stampede problem where many requests hit the database simultaneously when the cache expires:
async function getPopularProductsSWR() {
const cached = await cache.get("popular-products");
if (cached && cached.expiresAt > Date.now()) {
return cached.data; // fresh cache
}
if (cached) {
// Return stale data immediately, refresh in background
refreshInBackground("popular-products");
return cached.data;
}
// No cache, fetch synchronously
return await fetchAndCache();
}
Cache invalidation is the hard part. The pattern that works is invalidating on write (when a product is updated, invalidate the lists it might appear in) plus a short TTL as a safety net.
The Tools That Surface Real Problems
Three tools to have in production:
Slow query log. Postgres can log every query that exceeds a threshold (start with 500ms). The log shows the actual query, the duration, and the parameters. Review it weekly.
APM tools. Datadog APM, New Relic, Honeycomb. These show you query patterns aggregated by endpoint, surface N+1s and slow queries, and link slow user experiences back to the database queries causing them.
Postgres extensions. pg_stat_statements aggregates statistics about all queries that have run. Sorting by total time shows you which queries are eating the most database capacity, which is often different from the queries you'd expect.
Frequently Asked Questions
Should I use an ORM or write raw SQL?
Use an ORM for routine queries (CRUD, joins, filters). Drop to raw SQL for queries with complex JOINs, window functions, CTEs, or anything where you need fine control over the plan. The mistake is forcing every query through one approach.
When should I add a read replica?
When the database CPU is consistently above 60% and most of the load is reads. Read replicas distribute read traffic but add complexity (replication lag, separate connection logic). Start with optimization and caching, add replicas when scaling becomes the real bottleneck.
How big can a table get before performance suffers?
In well-indexed Postgres, tables of 100 million rows can perform fine for simple queries. The bottleneck is usually a query that does sequential scans or large in-memory sorts, not the row count itself. Partition tables when you have time-series data with a clear retention boundary (logs, events).
Is Postgres faster than MySQL in 2026?
For most web app workloads, they're comparable. Postgres has a more sophisticated query planner, richer extension ecosystem, and better support for advanced features (JSON, full-text search, geo). MySQL has slightly faster simple queries and a larger hosted ecosystem. Pick based on team expertise and ecosystem fit, not raw performance.
How do I optimize for serverless?
Use a connection pooler (Neon, Supabase, AWS RDS Proxy). Keep queries fast and small, serverless functions have short timeouts and pay per invocation. Avoid long-running transactions. Cache aggressively at the application layer.
If your application is hitting database scaling issues or you're not sure where the bottleneck is, our team handles performance audits and optimization for production stacks.
Tags
