Scale the database
Read which limit the Database hits, then take load off it with a larger box, a Pooler, a Cache, Read Replicas, or a dedicated store for each kind of query.
Use this guide when the Database is the bottleneck, or when it shows a red or amber callout in the Inspector.
App Servers are easy to copy. The Database is not, because it holds the state. So you scale it in two ways: give it a larger box, or send it less work. See The data tier for the reason.
Find the limit#
Select the Database. The Inspector opens.
Read the callouts at the top of the panel. Each one names a different limit:
"Refusing … queries/s: too many connections." The connection limit is full.
"The working set does not fit in RAM: reads hit disk." Reads are slow because the hot data is larger than memory.
"Out of CPU credits: throttled to 20% CPU." A Burstable box spent its credits.
"A new query has no index." A Slow query incident runs. Go to Fix slow queries.
Open Resources and find the fullest gauge: CPU, RAM, or Connections.
Select the Users Node and open Requests. Note which request kinds you serve. Each kind sends different queries to the Database.
Use the section below that matches the limit.
Connections are full#
Postgres opens one process per connection, up to 100. Every App Server copy holds its own connections, so more App Servers can take the Database down.
Research Connection pooling.
Place a Connection Pooler between the App Servers and the Database. Link each App Server to the Pooler, and the Pooler to the Database.
Keep Pooling mode on Transaction. In this mode, many app connections share 20 connections to the Database. Session mode needs one per client and barely helps.
If requests fail as
crashafter the change, queue the Drop session state task in the Team panel (T). Transaction pooling breaks code that keeps session state until it ships.
Check that the Connections gauge falls and the "Refusing" callout disappears.
CPU is full#
Select Larger in the Instance section. Each size doubles vCPU and RAM.
If the Database is still the bottleneck, take reads off it:
Research Caching and place a Cache between the App Servers and the Database. It answers reads from memory while the hot data fits. Writes pass through.
Research Read replicas and place a Read Replica. Link the App Servers to it. It answers read queries only.
If you run Laravel, Rails, or Django, research Eager loading and queue its task. These ORM frameworks send several read queries where one would do. See N+1 query.
Check that the Database Load row falls under 70% and that the BOTTLENECK tag moves away.
Reads hit disk#
Open the Instance section and pick the Memory family. It has twice the RAM of General per vCPU.
Or select Larger for more RAM.
Check that the "reads hit disk" callout disappears.
The working set grows with users, so this callout comes back as you grow.
Move work to the right store#
The Database can answer almost every query, but badly at volume. When a new request kind arrives in an Era, give its queries their own Component:
| Kind of work | Component | Research |
|---|---|---|
| Search queries | Search Engine | Full-text search |
| Uploaded files | Object Storage | Object storage |
| Slow work, export | Queue and Worker | Message queues |
| Reports | Data Warehouse | Data warehousing |
Reports on the primary also evict its buffer cache, so every other kind slows down. A Data Warehouse stops the Report kills the database incident.
When one Database is not enough#
From the Series C Era, the leaderless and consensus stores take writes on more than one node:
Wide-column Store replicates each key to N nodes with tunable quorums. Research Leaderless replication.
Distributed SQL keeps transactions on a majority of replicas. Research Consensus databases.
Both cost far more Burn than a Database. Move to them only when the largest Database with a Cache and replicas still cannot keep up, or when you need to survive a region outage. See Go global.
Check the result#
Wait a few seconds of game time after each change.
Check that the Database Load row is plain, under 70%.
Check that Apdex on the HUD rises toward its target.
With Structured logs, select the Users Node and read Errors by cause.
refused,stale, andcrashmust stay near zero.
Practice#
Connection Limit: a deploy doubles every pool.
Read Mostly: reads dominate, under a Burn limit.
Stampede: the Cache restarts empty.
Analytics on Primary: a report scans every table.
Related#
Connection pooling and Read replica in the glossary.