When to scale
Symptom: queries slow down as data or traffic grows, and you're wondering whether the database needs a bigger server, a replica, or just tuning.
Scale last. Most slowdowns are fixed by an index or a setting, and a bigger server only hides them for a while. Work through it in this order.
1. Tune first
Before paying for more hardware, rule these out:
- The top statements in Queries account for most of the time, and one index often removes half of it.
- Unused and duplicate indexes add write cost for nothing.
- Connections are pooled, so hundreds of idle sessions aren't holding memory.
- Vacuum keeps up, so tables aren't bloated to twice their real size.
work_mem,shared_buffersandeffective_cache_sizesuit the server (the Configuration area lists them).
2. Scale up when one server is the limit
Signs the database needs more memory, CPU or faster storage:
| Signal | What it means | Confirm |
|---|---|---|
| Data is much larger than memory and the cache hit rate is under 97% | The working set doesn't fit in RAM, so reads go to disk | select round(100.0*sum(blks_hit)/nullif(sum(blks_hit+blks_read),0),2) from pg_stat_database; and compare pg_database_size(current_database()) with shared_buffers |
Many active sessions waiting on IO |
Storage speed is the limit | select wait_event_type, wait_event, count(*) from pg_stat_activity where state='active' group by 1,2 order by 3 desc; |
| Many sessions running with no wait event at once, and host CPU near 100% | CPU is the limit | Your provider's CPU graph (SQL can't see host CPU) |
| Steady temp-file writes | Sorts and joins spill because work_mem or memory is too small |
select temp_files, pg_size_pretty(temp_bytes) from pg_stat_database where datname=current_database(); |
On managed providers this is a setting: Supabase's compute size, Neon's autoscaling limits, or an RDS or Cloud SQL instance class. Expect a restart or brief failover, so do it in a quiet window.
3. Scale out when reads are the problem
- Read replica: when reads far outnumber writes and the primary is under pressure. Send reports, dashboards and anything that tolerates a second of lag to the replica.
- Connection pooler: when connection count, not query load, is the limit.
- Partition big tables: when one table is over roughly 100 GB, or most of the database. Partition by time, then drop old partitions instead of deleting rows.
Writes don't scale out with a replica. If writes are the limit, scale up, batch them, or split data across databases.
Supabase publishes a useful framework for the read-replica question: replicas pay off when reads are around 80% or more of your traffic, and they can't help writes. See read replicas vs bigger compute.
What pg-checkup can and can't see
The Capacity & scaling area infers pressure from what Postgres reports: cache hit rate, wait events, temp files, autovacuum activity and table sizes. It can't see the host's CPU, memory or disk, which come from your provider's metrics.
The numbers cover the time since statistics were last reset, so a quiet week can hide a busy day. Run a checkup during your peak hour for the most honest picture, and repeat it after major changes.
pg_cron
If you use pg_cron, two checks apply. cron.job_run_details grows forever unless you trim it, and a failing job silently stops work such as cleanups and refreshes.
pg_cron shows each role only its own jobs, so the failed-job check needs the scanning role to own the jobs or be a superuser. The history-size check works with any monitoring role.