Why does my web app get slower as data grows

The pattern is familiar enough to be boring. An app ships, it is quick, everyone is pleased. A year later the same list page takes ten seconds, nobody has deployed anything unusual, and the only visible difference is that the table now has two million rows instead of two thousand.

The explanation people reach for is that there is more data, so of course it is slower. That is true and it is not a diagnosis. Plenty of systems hold hundreds of millions of rows and answer in milliseconds. Growth is the trigger, not the cause, and the difference matters because you cannot fix a trigger.

What actually happens is one of a small number of specific things, each with its own measurement and its own fix. Here they are in the order you should check them, with the mechanism stated rather than implied.

More rows and more users are different problems

Separate these before anything else, because they have almost nothing in common. More users at once is a concurrency problem: connections, locks, queueing, capacity. More rows per query is a data problem: how much work one request does. We treated the first case separately in how to scale a web app for more users.

The test is quick. Load the slow page at your quietest hour with nobody else using the system. If it is still slow, you have a data problem and the rest of this note applies. If it is fast when nobody else is on, you have a concurrency problem and adding indexes will not help you.

The mechanism is selectivity, not size

This is the part that is usually described wrongly, so it is worth taking from the source. PostgreSQL's documentation on examining index usage explains how the planner decides, and the explanation is about proportions rather than totals.

The docs put it in terms of a trap that catches people testing on small data: selecting 1,000 out of 100,000 rows could be a candidate for an index, while selecting 1 out of 100 rows will hardly be, because those 100 rows probably fit in a single disk page and no plan beats fetching one page. The deciding factor is the fraction of rows a query returns, not how many rows exist.

That is why growth changes behaviour without changing code. A filter that matched a small share of a small table can come to match a large share of a large one, or the reverse, and the plan that suited the first shape is wrong for the second. The documentation does not claim that growth on its own flips a plan, and neither should you. It is the changing proportion that does it.

The same page gives the honest warning about why none of this showed up in development: very small test data sets are especially misleading, and values that are very similar, completely random, or inserted in sorted order skew the statistics away from the distribution real data would have. Seeded test data is the single most common reason a query that was fine in staging is not fine in production.

Statistics go stale and the planner believes them anyway

The planner does not look at your data when choosing a plan. It looks at a statistical summary of it, and that summary has to be refreshed.

The documentation is unusually blunt here. Always run ANALYZE first, it says, because that command collects the statistics needed to estimate how many rows a query will return, and without real statistics some default values are assumed which are almost certain to be inaccurate. It then states the conclusion in one line: examining an application's index usage without having run ANALYZE is a lost cause.

It also names the failure precisely. The total cost of a plan is computed from per-row costs multiplied by the selectivity estimate, and an inaccurate selectivity estimate is due to insufficient statistics. So a query that is suddenly slow after a bulk import is often not a missing index. It is a planner working from a picture of the table as it was before the import.

There is a specific practice worth knowing for diagnosis. The documentation notes that you can turn off plan types to force a different plan for testing, such as disabling sequential scans, and that if the system still chooses a sequential scan there is probably a more fundamental reason, for example that the query condition does not match the index. Then time the query both ways, because forcing an index proves only that an index can be used and not that it should be.

Dead rows, and the space nobody reclaims

The second mechanism is invisible in application code and accounts for a lot of gradual decay. In PostgreSQL's multiversion model an UPDATE or DELETE does not remove the old row immediately, because that version must stay while any transaction could still see it. The documentation on routine vacuuming explains that the space it occupies must then be reclaimed for reuse, to avoid unbounded growth of disk space requirements.

Two consequences follow for a system that has been running a while. A standard VACUUM marks space reusable but generally does not hand it back to the operating system, so the goal is steady-state disk usage rather than shrinkage, and the docs note that a traffic spike can bloat a table to the point where VACUUM FULL is really necessary. And vacuum maintains the visibility map that index-only scans depend on, which is the difference between reading an index and reading the index plus the table row for every match.

The docs also connect this back to the previous section, since stale statistics can cause poor choices of plans which might degrade database performance. Autovacuum is enabled in the default configuration and requires track_counts to be on, and it handles both the vacuuming and the analyzing. It is also easy to starve: the documentation warns that regularly running commands which take conflicting locks can effectively prevent autovacuums from ever completing.

So the slow degradation that nobody can attribute to a deploy is often a table that is being updated heavily, never getting vacuumed properly, and accumulating both dead rows and a statistical picture of itself from months ago.

The deadline that is not about speed at all

There is one more consequence of not vacuuming, and it belongs in a note about growth because it is driven by transaction volume rather than row count. It is also the only item here that ends in data loss rather than slowness.

Transaction IDs are 32-bit, and the documentation states that to avoid wraparound it is necessary to vacuum every table in every database at least once every two billion transactions, with the consequence of failing to do so described as catastrophic data loss. The escalation is documented precisely: at 40 million transactions from wraparound the server begins emitting warnings, and with fewer than three million left it refuses to assign new transaction IDs, at which point only read-only transactions can start and writes fail.

This is why anti-wraparound vacuums run even on tables where autovacuum is disabled, and why autovacuum_freeze_max_age defaults to 200 million transactions, far below the hard limit. The documentation also puts a number on the storage that implies: roughly 50MB of pg_xact and about 2GB of pg_commit_ts at that default.

A busy write-heavy application can burn through transactions much faster than it accumulates interesting rows. If nobody owns database maintenance, this is the thing that eventually stops being a performance conversation.

A diagnostic order that does not waste a week

• Reproduce it at the quietest hour. This separates a data problem from a concurrency problem in five minutes and saves you from optimising the wrong layer.
• Find the one query. Almost always it is one, and almost always it is a list, search or report page. Measure before theorising.
• Run ANALYZE on the tables involved, then measure again. This is free, it is the documented first step, and it resolves a surprising share of sudden slowdowns after imports.
• Read the plan with EXPLAIN ANALYZE and compare estimated rows against actual rows. A large gap is a statistics problem, not an index problem, and the fix is different.
• Check whether the query returns a small or large fraction of the table. Selectivity decides whether an index can help at all.
• Check vacuum and autovacuum on the heavily updated tables, including whether anything is blocking autovacuum from finishing.
• Only then consider adding an index, and time the query with and without it rather than assuming.

Two structural causes sit outside that list and are worth naming because no amount of tuning fixes them. The first is a query issued once per row of a result instead of once for the whole result, which is invisible at twenty rows and fatal at twenty thousand. The second is a page that offers to show everything, with no limit and no bound on the date range, which was a reasonable offer when everything was two thousand rows. Both are application design, and the groundwork is in database design for beginners.

What this does not cover

Everything above is PostgreSQL, quoted from its own documentation. The selectivity and statistics reasoning is general and applies to any cost-based planner, but the specific parameter names, defaults and the vacuum model are not. If you are on MySQL or SQL Server, the shape of the diagnosis holds and the commands do not.

And the slowness your users complain about may not be in the database at all. If the page is slow on a phone and fine on a laptop, you are looking at the wrong layer entirely, and why is my website slow on mobile walks the front-end sequence instead. Pages that are slow because they were never a database in the first place are a different story again, covered in migrating from spreadsheets to a database.

The honest summary: an app that gets slower as data grows is usually not suffering from the amount of data. It is running a query whose shape stopped matching its data, against statistics that stopped describing it, on tables nobody is maintaining. All three are measurable in an afternoon, which is the good news buried in a bad week.