A client came to us convinced they needed to move off PostgreSQL. The application had slowed to the point where the main listing page took nine seconds, and the prevailing theory, arrived at as these theories usually are without measurement, was that the database had been outgrown.
They had 40,000 rows in the largest table. Forty thousand. Postgres will hold that in memory and answer from it without noticing.
The listing page was issuing 1,300 queries per request.
The N+1 problem is still, by a wide margin, number one
Every ORM makes it easy to write. Fetch a collection, loop over it, touch a relationship inside the loop, and you have silently issued one query per row. It is invisible in the code, where the line that costs you 1,200 queries looks exactly like a property access, and it is invisible in development, where the table has thirty rows and the database is on localhost.
It becomes visible in production, at 400 rows, over a network, all at once.
The fix is eager loading and it is one line. The real work is noticing, which means instrumentation rather than vigilance. Install a query counter in local development that shouts when a single request crosses a threshold. Log the count per request in production. Make a query-count assertion part of the test for any endpoint that matters. Humans do not reliably catch this by reading code; tooling catches it every time.
Select the columns you need
The default of fetching every column is fine until a table acquires a large text field, a JSON blob, or a base64 image someone stored in a hurry. Then a listing page that displays a name and a date is pulling several megabytes across the wire, hydrating it into objects, and discarding almost all of it.
We have made list endpoints four times faster by naming six columns.
An index on the column you actually filter by
Two failure modes, and the second is sneakier.
The first is no index at all on a frequently filtered column: easy to find, easy to fix.
The second is an index that exists but cannot be used, because the query wraps the column in a function, or the types do not match, or it is a composite index and the query filters on the second column rather than the first. A composite index on (status, created_at) will not help a query that filters only on created_at. The index is there, everyone assumes it is working, and the planner is quietly ignoring it.
EXPLAIN ANALYZE is not an advanced technique. It is the first thing to run on any query that is slow, and it will tell you plainly whether the index was used. Most teams we work with have simply never looked.
Counting is not free
Pagination that reports "showing 1-20 of 84,312" has to compute that total, and on a large filtered set that count can cost more than fetching the page of results. On an unbounded table it gets slower every month while the page of results stays constant, so the symptom is a page that mysteriously degrades over a year with no code change.
Ask whether anyone uses the number. Very often nobody does, and cursor pagination is both faster and more correct under concurrent writes. When it genuinely is needed, an approximate count from table statistics is usually fine for anything above a few thousand.
Caching last, not first
Caching is the most common first response to a slow page and it should be close to the last. A cache in front of a bad query gives you the same bad query plus a new class of bug: stale reads, invalidation logic that is wrong in a way nobody notices, and a cold-start cliff that turns a bad day into an outage. It also hides the underlying problem well enough that it will not be fixed until it is much more expensive to fix.
Fix the query. Then measure again. Then, if it is still slow, cache. By then you are caching something that was already fast, which is a much better position to be in.
What happened to the client
Eager loading on three relationships. Two composite indexes. A select naming eleven columns instead of everything. Cursor pagination on the one endpoint that needed it.
Nine seconds to under two hundred milliseconds, on the same instance they already had, in an afternoon. They had been quoted a substantial migration.
This is not a clever story and that is rather the point. The overwhelming majority of "we have outgrown our database" turns out to be four or five queries, and you will not find them by reasoning about your architecture. You find them by looking at what is actually being sent.