Signs Your App's Database Is the Bottleneck
By CodexierPublished 5 min read
An app that was fast with a hundred customers and is sluggish with a thousand is often not badly built. It is usually running queries that were fine on small tables and are now scanning large ones. The good news is that database problems are among the most diagnosable in software: the database can tell you exactly which queries cost the most. This guide covers the symptoms, how to find the culprits, the fixes that solve most cases and the rarer situation where the data model itself must change.
Symptoms users notice
- List views and search get slower week by week as customers add data.
- The slowness is worst for your largest customers and fine for new ones.
- Dashboards and reports time out, especially at the start of the month.
- Everything slows at once during peaks, and the database server's CPU or connection count is at its limit.
- Errors about too many connections or lock timeouts appear in logs.
If the app is equally slow for a customer with ten records and one with ten thousand, look elsewhere first: large front-end bundles, slow third-party calls or an under-sized server.
Finding slow queries
Measure before changing anything. In PostgreSQL the pg_stat_statements extension records every query shape with its call count and total time; managed platforms such as Supabase, AWS RDS and similar expose the same data in a dashboard. Sort by total time, not by the slowest single call: a query that takes a modest time but runs thousands of times per minute often costs more than the one report that takes several seconds.
- Enable query statistics and a slow query log with a sensible threshold.
- List the top ten queries by total time and by call count.
- Run EXPLAIN ANALYZE on each against realistic data, not a small development database.
- Look for sequential scans on large tables, row estimates far from reality, and sorts spilling to disk.
- Note which endpoint or screen each query belongs to, so fixes can be tested from the user's side.
Indexes and query patterns
| Problem | What you see | Usual fix |
|---|---|---|
| Missing index | Sequential scan on a large table filtered by a column | Index on the filter column, or a composite index matching filter and sort |
| N+1 queries | One query for a list, then one per row | Fetch related rows in one query with a join or batched lookup |
| Over-fetching | Selecting all columns and all rows, filtering in code | Select needed columns, filter and paginate in the database |
| Offset pagination on deep pages | Page 500 is much slower than page 1 | Keyset pagination on an indexed column |
| Functions on indexed columns | Index exists but is not used | Index the expression or rewrite the condition |
| Row-level security policies with subqueries | Every query pays for a slow policy check | Index the columns policies use and simplify the policy |
Indexes are not free: each one slows writes slightly and uses storage. Add them for measured problems, and remove unused ones.
In multi-tenant apps where every table has a tenant or organisation column, almost every index should start with that column, because almost every query filters on it. Our guide to multi-tenant architecture explains why.
Caching and read replicas
Connection pooling
Serverless functions and many app instances can exhaust database connections. A pooler such as PgBouncer, or the one your platform provides, is often the first fix for peak-time failures.
Caching
Cache results that are read often and change rarely: reference data, public pages, aggregates for dashboards. The hard part is invalidation, so start with short lifetimes.
Read replicas
Move heavy reporting and analytics reads to a replica so they do not compete with user traffic. Replicas lag slightly, so never read from them right after a write the user expects to see.
When the data model is the problem
Sometimes the execution plan is fine and the query is still slow, because the data is shaped wrong for the question. Signs include storing lists in JSON columns that you then filter on, computing totals over millions of rows on every page load, or a single table serving several unrelated purposes. The fixes are targeted: summary tables updated on write, splitting a table, or moving a JSON field into proper columns. That is refactoring, not a rewrite, and our guide on rebuild or refactor covers how to decide.
When you do not need us: if your team can read an execution plan, the steps above will solve most cases in-house. Outside help pays off when nobody on the team has done database tuning, when the problem shows up only under production load, or when a rewrite is being proposed without measurements. Our scaling and optimisation service starts with measurement; see pricing or book a call.
Frequently asked questions
Should we switch to a different database?
Almost never as a first step. PostgreSQL and MySQL handle far more data than most SaaS products ever reach when queries and indexes are right. Switching databases is expensive and usually moves the same query problems to a new system.
Will a bigger database server fix it?
It buys time and can be the right short-term move during a crisis. But a missing index or an N+1 pattern grows with your data, so a bigger server only delays the same problem at a higher monthly cost.
How do we avoid this in the first place?
Test with realistic data volumes, review execution plans for new list and search queries, add query statistics from day one and look at the top queries once a month. It takes little time and catches problems while they are small.
Is an ORM the cause?
Not by itself, but ORMs make N+1 patterns and over-fetching easy to write by accident. Log the SQL the ORM generates for your heaviest screens and use its eager-loading and column-selection features.
Find out what is really slowing your app down
Tell us your stack and which screens have become slow. In fifteen minutes we can tell you where to look first and whether it is likely a quick fix.
Book a free 15-minute call