Why does the app get slower as data grows? Database bottlenecks, indexes and query plans

If your application felt fast for the first few months and the same screen now takes seconds to load, the cause is almost never a weak server. It is queries whose cost grows along with the number of rows. On a development database with five hundred records everything looks instant; at five million rows the same query scans the table end to end. Four causes repeat: a missing or unusable index, N+1 queries generated by the ORM, page-number pagination built on OFFSET, and an exhausted connection pool. All four are found by measuring, not by guessing. Bigger hardware can hide them for a while, but it does not fix them and it makes the bill permanent.

The average response time hides the problem

The first mistake is looking at the wrong number. Average response time can sit at 180 milliseconds while five percent of users wait six seconds. The complaints come from that five percent, and the dashboard stays green. Track p95 and p99 per endpoint instead.

Keep a second number next to it: how many queries run per request. This is the cheapest diagnostic you will ever add. If an endpoint's query count grows in proportion to the number of rows it returns, you have already found the answer. Where to put that instrumentation and how to set thresholds is covered in our piece on observability, SLOs and error budgets.

Find out which query first

On PostgreSQL the right tool is pg_stat_statements. It tracks planning and execution statistics for every SQL statement the server runs, but because it needs additional shared memory it has to be added to shared_preload_libraries in postgresql.conf and the server restarted. By default it tracks 5000 distinct statements.

The column that matters is not the time of a single call, it is total time. A 30-millisecond query that runs 40,000 times a day costs far more than a two-second query that runs twice. Teams often spend a week optimising the two-second one because it is the one they noticed.

On MySQL the slow query log is disabled by default, and long_query_time defaults to 10 seconds. That threshold catches nothing useful, because the queries that are actually hurting you sit between 200 and 800 milliseconds. Lower it to something like 0.2. Turn on log_queries_not_using_indexes for a while as well; it flags statements that resolve rows without an index.

Learn to read EXPLAIN ANALYZE, and ignore the cost number

EXPLAIN shows the plan. EXPLAIN ANALYZE actually executes the statement and adds real timings. Do not skim past that difference: run it on an UPDATE or a DELETE and it really does modify your data. When you inspect a write, wrap it in BEGIN and finish with ROLLBACK.

What you are looking for in the plan is not the abstract cost estimate next to each node. It is the gap between estimated rows and actual rows. If the planner expected 50 rows and 400,000 came back, it picked the wrong join strategy and your real problem is stale or insufficient statistics. Since PostgreSQL 18, buffer information is included automatically when you use ANALYZE, and the block counts there tell you whether the query was served from cache or went to disk. That is usually the explanation for a query that is fast one minute and slow the next.

PostgreSQL does not index your foreign keys

This is the single most common concrete defect we find. The PostgreSQL documentation states it plainly: since deleting a row from the referenced table or updating a referenced column requires a scan of the referencing table for matching rows, it is often a good idea to index the referencing columns too, and because this is not always needed and there are many indexing choices available, declaring a foreign key constraint does not automatically create an index on those columns.

MySQL behaves the other way around. InnoDB requires indexes on foreign keys so that constraint checks do not need a table scan, and creates one on the referencing table automatically if it does not already exist. So a schema migrated from MySQL to PostgreSQL can see every join and every delete slow down without anyone having touched the code. Audit this once: a single query that walks your foreign key columns and reports which ones have no supporting index will usually find several.

An index that exists is not an index that gets used

Three situations quietly disable an index. First, column order in a composite index: an index on (customer_id, created_at) helps a query filtering by customer_id, but not one filtering only by created_at. Second, a function applied to the column: write WHERE lower(email) = ... and the plain index on email is out of play, because the stored value is not the transformed one. Fix the query or define an expression index. Third, leading wildcards: LIKE '%invoice%' cannot use a B-tree index at all, and you need full-text search or a trigram index instead.

It cuts the other way too. An unused index is not free. Every INSERT, UPDATE and DELETE has to maintain it, and it occupies disk. Review the ones whose scan counter in pg_stat_user_indexes is still zero. Five indexes added "just in case" on a write-heavy table add up to a measurable slowdown on exactly the path you care about.

N+1: the work your ORM does quietly

You build a page listing a hundred orders, each showing the customer name. The ORM runs one query for the orders, then one more query per order inside the loop to fetch its customer. That is 101 queries. Nobody notices in development because there are ten records; in production, a hundred extra round trips at a millisecond each turn into half a second.

The reason is that most ORMs lazy-load relations by default. The fix is to ask for the relation up front: with() in Laravel, select_related() and prefetch_related() in Django, includes in Rails, join fetch or an entity graph in Hibernate. In GraphQL APIs the same job is done by a batching layer such as DataLoader. What all of them share is that a developer has to write them deliberately. None of it happens on its own.

So pair the fix with a guard. Write a test that asserts the query count for your critical endpoints: "this endpoint must run at most 5 queries" catches the N+1 that a future refactor reintroduces, before it ships. Where that kind of check belongs in the wider suite is covered in our piece on test automation and the test pyramid.

Pagination: OFFSET gets more expensive the deeper you go

The classic LIMIT 20 OFFSET 10000 has two separate problems. In the words of the PostgreSQL documentation, the rows skipped by an OFFSET clause still have to be computed inside the server, so a large OFFSET might be inefficient. Asking for page 500 means the database produces the preceding 10,000 rows and throws them away. Every page is slower than the one before it.

The second problem is consistency. If a new record lands in the list while the user moves from page 1 to page 2, the window shifts: some records appear twice, others are never shown. The answer is keyset pagination, also called the seek method. Instead of "skip 10,000 rows," you say "give me the rows after the last one I saw": WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20. With the matching index, the query takes the same time on page 500 as on page 1. The trade-off is that you cannot jump to an arbitrary page number, which suits infinite scroll and "load more" flows and does not suit a traditional page bar. One more thing worth checking: the COUNT(*) you run to render the total page count is often more expensive than the query itself. Are you actually showing that number, or computing it out of habit?

Connection pools: PostgreSQL forks a process per connection

By design, PostgreSQL forks a new backend process for each client connection. A connection is not a cheap object; it is an operating system process with its own memory footprint. Scale your application horizontally to 15 instances with a pool of 20 each and you are asking for 300 connections, and when the database refuses them the error message tells you nothing about queries.

The answer is not a bigger pool, it is a pooler in between. A layer like PgBouncer does transaction-level pooling, collapsing hundreds of application connections onto far fewer real server connections. There is a related misconfiguration in the same area: work_mem defaults to 4MB, and that budget applies per operation, not per connection. As the documentation warns, a complex query may run several sort and hash operations at once, several sessions may be doing the same concurrently, and total memory used can end up many times work_mem. Bumping it to 256MB because "we have RAM" is a well-known way to run the server out of memory at peak hour.

The door you open when you drop to raw SQL

At some point in a tuning effort you leave the ORM and write SQL by hand, and there is nothing wrong with that. What is wrong is building dynamic filters and sort fields through string concatenation. The thing protecting you inside the ORM was parameterised queries; the moment you write raw SQL, that protection becomes your responsibility. Pass values as bound parameters, always. Column and table names cannot be bound as parameters, so for something like a user-selected ORDER BY field the only safe approach is to map the incoming value against a fixed allow-list. We covered this failure mode with examples in our OWASP Top 10 write-up.

Five things you can do this week

One: enable pg_stat_statements, or turn on the MySQL slow query log with a 0.2 second threshold, collect a week of data and sort by total time. Two: run EXPLAIN ANALYZE on the five most expensive queries and compare estimated rows against actual rows. Three: list the foreign key columns that have no index and fill the gaps. Four: count queries per request on your three busiest endpoints, and add eager loading wherever that count scales with the result set. Five: convert deep-pagination screens to keyset pagination.

Those five usually account for most of the slowness. When they do not, the cause is not individual queries but the data model or the access pattern itself: the wrong normalisation, an event history piled into one table, reporting queries running synchronously on every write. At that point the decision stops being an optimisation and becomes a design call. How to prioritise it is in our piece on technical debt, and the choice between incremental modernisation and a rewrite is in rewrite or renovate.