Find the slowest queries first, then ask the database how it runs them: EXPLAIN in PostgreSQL and MySQL, the execution plan in SQL Server. The usual culprits are a missing index, so the database reads a whole table to find a few rows, and code that runs one query per item in a list instead of one query for the list. Fix those before paying for a bigger database. Bigger hardware hides slow queries for a while, then they come back.
What's the problem
Pages that touch the database are slow, getting slower as data grows, or time out at busy times. The app may hit connection limits and start returning errors under load.
Why it happens
- Missing indexes. Without an index on the columns you search or join by, the database checks every row. Fine with a thousand rows, painful with a million.
- One query per row. Code that loads a list, then runs another query for each item in it. Showing 100 orders becomes 101 queries. This is common in code written quickly or generated by AI.
- Loading too much. Selecting every column, or every row, then filtering in the app.
- Running out of connections. Each copy of the app opens connections. Under load, the pool runs out and requests queue up or fail.
- Too many indexes, or the wrong ones. Indexes speed up reads but add work to every write, so they need choosing, not piling on.
How to fix it
- Find the slow queries. Turn on your database's slow-query log, or use your hosting provider's query insights.
- Run
EXPLAINon each one. Look for full table scans on large tables. - Add indexes for the columns used in
WHERE,JOINandORDER BYon those queries, then measure again. - Fix one-query-per-row code by loading related records in one go. Most frameworks have a feature for it (for example, eager loading).
- Select only what you need, and paginate long lists.
- Pool connections, and check the app isn't opening a new one per request.
- Then size the database to what the measurements show, on a managed service you can resize without rebuilding.
When to call Preventionlabs
If the slow-query log points at a few queries, the steps above will fix most of it. Call us when the database is one part of an app that's falling behind generally: outdated, fragile, or unable to take more users. Every resurrection delivers infrastructure that scales, where capacity can be increased through configuration without changing the code. Whether a specific performance problem is within scope is something the free assessment will tell you, in writing, before you commit to anything.
Submit your project for a free assessmentFree assessment. $10,000 AUD flat to get it live, only if we take it on and you go ahead.
Sources
- PostgreSQL: Using EXPLAINdatabase docs
You can use the EXPLAIN command to see what query plan the planner creates for any query.
- PostgreSQL: Indexesdatabase docs
But indexes also add overhead to the database system as a whole, so they should be used sensibly.
- Ruby on Rails Guides: Active Record Query Interface: N + 1 Queries Problemframework docs
Retrieving a list of records N (where N is a number greater that 1) in a single query can sometimes trigger N extra queries; one for each record.
- MDN Web Docs: 503 Service Unavailableweb reference
In overload cases, some server-side applications will reject requests with a 503 status when resource thresholds like memory, CPU, or connection pool limits are met.