A common story goes like this. A startup launches, gets its first few thousand users, and everything feels fast. Six months later dashboards take eight seconds to load, the admin panel times out, and the team debates whether they need a bigger server. Very often the real culprit is a handful of slow database queries that were fine on small tables and are painful on large ones.
This guide is a practical walkthrough for founders and developers who want faster apps without a rewrite. Examples use PostgreSQL terminology, but the ideas apply to most relational databases. Any timings below are illustrative, not measured benchmarks.
A database can answer a question in two basic ways. It can scan every row and check each one, or it can jump straight to the matching rows using an index. On a table with a few thousand rows, a scan takes milliseconds. On a table with tens of millions, the same scan can take seconds, and if many users trigger it at once the whole database slows down.
Growth also reveals other habits that were harmless early on: loading entire tables into memory, running a query inside a loop, sorting large result sets without limits, and returning far more columns than a screen needs.
The first rule of performance work is to find the real bottleneck. Adding indexes at random wastes time and can make writes slower.
Turn on slow query logging with a sensible threshold, such as anything over 200 milliseconds, and enable a statistics view such as pg_stat_statements. Sort by total time consumed, which is calls multiplied by average duration. A query that takes 30 milliseconds but runs 50,000 times an hour often hurts more than one that takes two seconds but runs twice.
Run EXPLAIN ANALYZE on the slow query. Look for sequential scans on large tables, big gaps between estimated and actual row counts, expensive sorts, and nested loops with huge iteration counts. The plan tells you what the database actually did, which is often different from what you assumed. Pair this with the tracing habits we describe in our guide to observability and tracing for AI apps, since the same idea of tracing one request end to end applies to ordinary web endpoints.
Consider an illustrative D2C store whose admin dashboard lists recent orders for one customer. The query filters by customer ID and sorts by created date. With 20,000 orders it feels instant. At 5 million orders, it takes several seconds, because the database scans the entire table and sorts the matches.
The fix is a composite index on customer ID and created date, in that order. The database can now jump to the customer's rows already sorted, and stop after the first page. In a scenario like this, a query that took seconds could drop to a few milliseconds. No new servers, no rewrite. If your business is in this space, our notes on multi-channel inventory sync automation show why order and stock tables grow so quickly.
Foreign key columns are frequently left without indexes, so joins and cascading deletes crawl. Composite indexes built in the wrong column order may exist but never get used.
Object mappers make it easy to load a list of orders and then, inside a loop, load each order's customer with a separate query. One page view triggers hundreds of queries. Use eager loading, joins or batched lookups to collapse them into one or two queries. A query counter in your development environment makes these problems obvious.
Skipping 100,000 rows to show page 5,000 forces the database to read and discard all of them. Use keyset pagination instead: ask for rows after the last seen ID or timestamp, which uses an index and stays fast on any page.
Wrapping a column in a function, such as lowercasing an email in the query, prevents the plain index from being used. Either store normalised values or create an expression index that matches the query.
Selecting large text or JSON columns for a list view moves far more data than needed. Fetch only what the screen displays.
Analytics style queries running against your production database can starve customer traffic. Move them to a read replica or a warehouse, as discussed in our guide to the modern data stack for startups without a data team.
Indexes are not free. Every insert, update or delete must maintain each index, so an over-indexed write heavy table slows down. Indexes also consume disk and memory, which matters when your working set no longer fits in RAM. Review index usage statistics periodically and drop indexes that are never used or that duplicate another. The goal is a small set of indexes that match your real query patterns.
Once the queries are sensible, other levers come into play:
Tune queries first, because it is usually the cheapest and most durable improvement. Our SaaS development services include performance reviews like this for products approaching their first scaling wall.
Some performance problems are born in the schema. Use the smallest sensible data types, since narrower rows mean more rows per page and better cache use. Store timestamps as proper timestamp types, not strings, so range queries can use indexes. Keep frequently updated counters out of large, frequently read rows, and consider a separate table for them. Where you are tempted to store everything in one flexible JSON column, index the specific keys you query, or promote them to real columns once they stabilise.
Multi-tenant products deserve special care. If every query filters by tenant, put the tenant identifier at the start of your composite indexes so each customer's data is contiguous and fast to reach. This also keeps one large customer from slowing everyone else, a pattern we cover in depth in our guide to multi-tenant SaaS database design.
Most performance problems are not server problems. They are questions the database was never given a fast way to answer.
Set aside an hour each month to review the top ten queries by total time, check for new sequential scans on large tables, and look at slow endpoints reported by your monitoring. Small, regular attention prevents the sudden slowdowns that appear when a table crosses a size threshold. Add a note to your release checklist: any new feature that queries a large table should include its expected query plan.
Also load test important flows before big campaigns. A promotion that triples traffic will expose slow queries at the worst possible time, and a simple test with realistic data volumes can reveal them days earlier while there is still time to fix them calmly.
Slow apps are rarely mysterious. Measure to find the queries that consume the most time, read their plans, add the right indexes, remove N+1 patterns, paginate with keys, and only then reach for caching or bigger machines. A few hours of focused tuning can often buy months of headroom, and it keeps your infrastructure bill honest while your user base grows.