Pages that used to be instant
When the app launched, everything felt quick. Now the orders page takes a while to load, the monthly report sometimes times out, and staff have learned to open a search and go and make tea. Customers notice too, especially on account pages that list their history.
Someone suggested upgrading the database server. Maybe you already did, and it helped for a while.
Why it slows down over time
Most slow apps are not slow because of the database product. They are slow because of how the app asks it for data, and that only shows once there is enough data.
| Cause | What it looks like |
|---|---|
| Missing indexes | The database reads every row to find a few, fine at a thousand rows, slow at millions |
| N+1 queries | A page runs one query per item in a list, so a list of hundreds means hundreds of queries |
| Fetching too much | Queries select every column and every row, then the code throws most away |
| Reports on live tables | Heavy reporting queries compete with customers for the same database |
| Locking | Long operations hold locks that make other requests wait |
| Growing tables nobody trims | Logs, sessions and audit records kept forever in the main database |
The N+1 pattern deserves attention because frameworks make it easy to write without noticing. Code that looks tidy can quietly fire off a query for every row it displays.
What the slowness costs
Staff time adds up, one slow page at a time. Customers give up on slow account pages and phone instead. Reports that time out get run less often, so decisions rest on older numbers. And upgrading the server treats the symptom: the bill goes up, and the underlying queries keep getting slower as data grows until the bigger server is not big enough either.
There is a reliability cost too. A database that is running close to its limits is the first thing to fall over when traffic spikes, taking the whole app with it.
How we find and fix slow queries
- Measure before changing anything. We turn on query logging or use tools such as the database's slow query log, pg_stat_statements for PostgreSQL, Query Store for SQL Server, or the performance insights in AWS RDS and Azure SQL, to see which queries take the most total time.
- Trace slow pages to their queries using application monitoring, so we fix what users feel rather than what looks worst on paper.
- Read the query plans. The database explains how it runs each query, which shows missing indexes, full scans and bad joins.
- Add or adjust indexes carefully, checking the effect on writes as well as reads, and applying them in a way that does not lock busy tables.
- Fix the code. Replace N+1 patterns with a single query, fetch only what is needed, and paginate long lists.
- Move heavy reporting off the live path, onto a read replica or a separate reporting store, so reports do not slow down customers.
- Add caching where the data allows it, with clear rules for when it refreshes.
- Archive or trim data that does not need to live in the main tables, such as old logs and sessions.
Each change is tested on a staging copy with realistic data volumes first, because a fix that works on a small test database can behave differently on the real one.
After tuning
The pages staff use most respond quickly again. Reports run without timing out, and without dragging everyone else down. You have monitoring that shows which queries are getting slower as data grows, so the next problem is spotted early. And any decision to upgrade the database server is based on measured need rather than hope.
Does your app show these signs?
- Pages that were fast at launch are now noticeably slow.
- Reports or exports time out or take a long time.
- Upgrading the database server helped only briefly.
- The app slows down at particular times, such as when reports run.
- Nobody has looked at which queries take the most time.