When your managed database goes to sleep: designing for auto-pause
Auto-pause saves money until the first request after a quiet weekend. What a paused database looks like to your app, and a design that survives it.
A database that pauses itself looks, from the application, exactly like a database that is down. The first connection after a quiet period hangs or fails, and a few seconds later the same query works. Nothing crashed. A feature the provider documents behaved as documented, and the application's assumptions about connection timeouts, health checks and scheduled jobs were written for a database that is always awake.
This article is a reference for that situation. It starts with what the major providers actually do, then puts the symptoms next to their causes, then covers the three pieces of design that make a pausing database tolerable: a connect deadline that covers the resume, a readiness check that does not trigger restarts, and scheduled work that lives outside the database.
What each provider does when the database sleeps#
The behaviours differ by orders of magnitude, so the first design input is which one you have. Amazon Aurora Serverless v2 is the most fully documented, so it is the reference through the rest of the article.
| Aurora Serverless v2 | Azure SQL Database serverless | Neon | |
|---|---|---|---|
| How it is switched on | Minimum capacity of 0 ACUs | Auto-pause delay set (General Purpose; Hyperscale is in preview) | Scale to zero on the compute |
| Idle interval | 300 s default and minimum, 86,400 s maximum | 60 min default, 15 min minimum in General Purpose, 10,080 min maximum, -1 disables | 5 minutes; fixed on the Free plan, can be disabled on paid plans |
| Resume time | About 15 s; 30 s or longer after more than 24 hours paused | Generally under a minute | Within a few hundred milliseconds |
| What the client sees first | A slow connect, and failed sessions if many arrive before resume completes | Error 40613, database unavailable, on the first attempt | Nothing unusual, if the client timeout is sane |
| Billed while paused | Storage and cluster-level costs only | Storage only | Compute billing is on Neon's pricing page |
Sources: the Aurora auto-pause documentation, the Azure SQL serverless auto-pause and auto-resume page and Neon's scale-to-zero page. A design tuned for a 15-second resume is far more cautious than one for a sub-second resume, and the reverse is dangerous, so treat resume characteristics as a requirement that changes when you change provider.
Aurora also needs a minimum engine version for auto-pause (16.3, 15.7, 14.12 or 13.15 for PostgreSQL at the time of writing), and enabling it is an ordinary scaling-configuration change:
aws rds modify-db-cluster --db-cluster-identifier serverless-v2-cluster \
--serverless-v2-scaling-configuration MinCapacity=0,MaxCapacity=8,SecondsUntilAutoPause=3600Giving the cluster a nonzero floor turns it off again, and the interval property disappears from the configuration:
aws rds modify-db-cluster --db-cluster-identifier serverless-v2-cluster \
--serverless-v2-scaling-configuration MinCapacity=2,MaxCapacity=8The capacity numbers there are placeholders; size them from your own load.
Symptom, cause, fix#
This is the table to keep open when something fails on the first request after a quiet period. Each row is a documented behaviour or a direct consequence of one.
| Symptom | Cause | Fix |
|---|---|---|
| First request after idle times out, later ones succeed | Client connect timeout shorter than the resume time | A connect deadline longer than the slowest documented resume, with retries |
| A burst of connection errors when several instances start together | Many simultaneous connection attempts reach a database that is still resuming; Aurora says some sessions might be unable to connect | Retry with jittered backoff and small pools; Aurora suggests retrying a failed connection three times |
| Instances restart in a loop while the database wakes | Liveness probe runs a database query, fails, and the platform replaces the instance, which opens more connections | Liveness never touches the database; readiness may |
| Pages are slow for minutes after the database is back | Aurora resumes at a relatively small capacity and scales up from there | Pre-warm before known busy periods, or keep a nonzero minimum |
| A scheduled job inside the database did not run and left no error | pg_cron and the MySQL event scheduler do not wake a paused instance, and Aurora does not queue the missed run | Schedule from outside the database |
| Autovacuum or a long job stops partway | Internal tasks underway when the pause begins can be cancelled | Lengthen the idle interval, or keep the database awake during maintenance windows |
| Database never pauses although nobody uses it | Open user connections, RDS Proxy, replication or other features keep it active | See the next section |
| A health probe with wrong credentials wakes the database every few minutes | Aurora resumes the instance even for a connection attempt with invalid credentials | Point probes at the application, not at the database port |
| Authentication errors during resume get retried forever | The retry loop does not distinguish transient errors from credentials errors | Retry connection errors, stop on SQLSTATE class 28 |
Telling a paused database from a broken one#
The platform will often report the database as healthy while it is asleep, so the signals have to come from somewhere else.
I lost most of an incident to this. On the staging environment of a large-scale social platform I'm building, everything that touched the graph database started failing at once one morning: likes would not register, and the Pub/Sub worker errored on every delivery while its retries piled up. The log said ENOTFOUND, a DNS failure on the database hostname, so I worked through application theories in order. I diffed recent commits for database config changes, restarted services in case a connection pool was poisoned, and read the driver's retry settings. All wrong. The instance was a low-tier Neo4j Aura one that had auto-paused after a quiet stretch, and a paused instance does not refuse connections or say it is paused: its endpoint stops resolving. Resuming it from the console took under a minute, after staging had been down for a whole night.
On Aurora, the instance status stays available while it is paused and while it resumes. While paused, ServerlessDatabaseCapacity reports zero, along with CPUUtilization and ACUUtilization, and the instance stops writing to its logs. The documentation also lists the events for each transition, RDS-EVENT-0370 through RDS-EVENT-0374: pausing begins, pausing finishes, resuming begins, resuming finishes, and failures. It suggests watching how often ServerlessDatabaseCapacity moves from nonzero to zero and how long it stays there: if the instance pauses more often than you expected, users pay in latency, and if less often, you are not saving what you planned.
On Azure SQL, the first connection to a paused database returns error 40613 and starts the resume, and the Azure Monitor activity log records a Resume Databases operation whose Caller identifies what woke it.
I alert on one derived number: time from the first connection attempt to the first successful query after a pause. If it approaches the connect deadline you set in the next section, the next long weekend will fail.
The rule I wrote down after the Aura incident is that when a dependency starts failing wholesale, every operation and every service at once, I check the provider's console before reading any application code. Bugs are usually selective, and infrastructure is indiscriminate. The runbook is short, almost embarrassingly so: graph-related ENOTFOUND across services, check the instance state, resume if paused, then check the retry backlog and the dead-letter queues. The backlog was the one part that went well. Pub/Sub held the undeliverable work and retried it, nothing was dropped, and replaying the burst afterwards was safe only because the handlers were idempotent. What the incident did show me is how slowly a human finds out that a backlog is forming, which became follow-up work.
What keeps a database awake#
An unexpected cost is a database that should be paused and is not. The Aurora documentation lists the causes, and the Azure one is similar in spirit:
- Any open user-initiated connection stops the pause, whether or not it is running a query. Forgotten psql sessions, BI tools and developer tooling count.
- An attached RDS Proxy keeps a connection open to each instance, so a cluster with a proxy does not pause.
- Logical replication on Aurora PostgreSQL, a global database primary, a Redshift zero-ETL integration, and provisioned instances in the same cluster all prevent the writer from pausing.
- On Azure SQL, geo-replication, long-term backup retention and a DNS alias on the logical server prevent auto-pause, and open sessions are the most common reason it does not pause when it should.
Keepalive pings belong in this list too. A ping that holds a connection stops the pause, so it is a hand-rolled minimum capacity with extra ways to fail. If you want the database always awake, say so in the configuration.
Pausing removes instance charges while storage and other cluster-level costs continue, as the Aurora documentation states. The saving is real for something used a few hours a day and small for a database touched every few minutes, because the idle interval never elapses. Anything in the previous section, such as a proxy or a long-lived connection, turns the saving into zero without any error, so check the monthly pause time rather than assuming it. Rates are on the Aurora pricing page and the Neon pricing page; compare the saving with what a slow first request costs the people who wait for it.
A connect deadline that covers the resume#
The connection code is where a paused database is either tolerated or turned into an error page. The deadline has to cover the slowest resume the provider documents, which for Aurora means 30 seconds or more if the database may be paused for over a day, and the retry loop has to stop on authentication errors.
Pool sizes, idle timeouts and the retry for too many clients belong to a different problem, covered in connection pooling when every service is tiny. What is specific to auto-pause is a single bounded wait at startup and in background work, with the deadline stated in code:
import pg from "pg";
// SQLSTATE class 28: invalid authorization. Retrying cannot fix these.
const AUTH_FAILURES = new Set(["28000", "28P01"]);
const ATTEMPT_TIMEOUT_MS = 8_000;
export async function waitForDatabase(
connectionString: string,
deadlineMs = 45_000,
): Promise<void> {
const started = Date.now();
for (let attempt = 0; ; attempt++) {
const client = new pg.Client({
connectionString,
connectionTimeoutMillis: ATTEMPT_TIMEOUT_MS,
});
try {
await client.connect();
await client.query("SELECT 1");
return;
} catch (err) {
const code = (err as { code?: string }).code;
const outOfTime = Date.now() - started + ATTEMPT_TIMEOUT_MS > deadlineMs;
if ((code && AUTH_FAILURES.has(code)) || outOfTime) throw err;
const base = Math.min(1_000 * 2 ** attempt, 8_000);
await new Promise((r) => setTimeout(r, base / 2 + Math.random() * (base / 2)));
} finally {
await client.end().catch(() => {});
}
}
}The 45-second deadline covers the deep-sleep resume with room to spare, the per-attempt timeout is 8 seconds, and the waits are jittered so several instances waking together do not retry in lockstep. A user-facing request should never block for 45 seconds. That deadline belongs to startup and background work. For requests that arrive while the database is resuming, return a 503 with a Retry-After header and let the front end show a waking-up state, which turns a 500 into an explanation and keeps handlers from holding threads for 30 seconds.
Readiness is not liveness#
A liveness check answers whether the process is healthy enough to keep. It must not touch the database. A readiness check answers whether the instance can serve now, and it may. A slow dependency should make an instance not ready, never dead.
import type { FastifyInstance } from "fastify";
import type { Pool } from "pg";
export function registerHealth(app: FastifyInstance, pool: Pool) {
app.get("/livez", async () => ({ ok: true }));
app.get("/readyz", async (_req, reply) => {
try {
await Promise.race([
pool.query("SELECT 1"),
new Promise((_, reject) => setTimeout(() => reject(new Error("probe timeout")), 3_000)),
]);
return { ok: true };
} catch {
return reply.code(503).send({ ok: false, reason: "database_unavailable" });
}
});
}/readyz gives up after three seconds on purpose, because the point of a probe is to say not yet quickly. The actual warm-up happens once at startup with waitForDatabase, so the instance starts taking traffic as soon as the database answers. Platforms differ in how they expose the two probes, so check yours, but the principle carries over.
Scheduled work cannot live inside a database that sleeps#
If a job is scheduled inside the database, it cannot start a database that is asleep. Aurora states it plainly: it does not resume a paused instance for pg_cron or MySQL event-scheduler jobs, does not queue them, and skips any whose scheduled time falls during the pause. A job that never ran produces no errors, which is why this failure is found weeks late, by someone noticing that old data is still there.
Move the schedule out. A scheduler outside the database opens a connection, which wakes it, and then runs the job with a deadline longer than the worst-case resume:
import pg from "pg";
import { waitForDatabase } from "../src/db/wake.ts";
const url = process.env.DATABASE_URL!;
await waitForDatabase(url, 90_000);
const client = new pg.Client({ connectionString: url });
await client.connect();
try {
const res = await client.query(
"DELETE FROM sessions WHERE expires_at < now() - interval '7 days'",
);
console.log(JSON.stringify({ event: "cleanup", deleted: res.rowCount }));
} finally {
await client.end();
}Run it on whatever scheduler you already use; Cloud Scheduler jobs that don't fail silently covers making that side observable. Alert on the absence of the success line rather than on errors. For a known busy period, the same trick works as a pre-warm: a scheduled connection a few minutes before working hours means the first human does not pay for the resume, and the instance has time to scale up from its small starting capacity.
Which databases may sleep#
My rule has three parts, applied in this order.
- Customer-facing production paths do not pause. Set a nonzero minimum capacity and accept the floor. The cost of one cold first request for a real user is higher than the saving.
- Development, preview and internal tools may pause, provided the connect deadline, the readiness split and the external scheduling above are in place.
- Anything scheduled inside the database moves out of it before the database is allowed to pause.
My staging instance follows the second rule, and I would still choose that low tier today: the auto-pause is why it is affordable. The mistake was not the tier but letting its behaviour live in the provider's documentation instead of in my own operational notes.
Two smaller points. Do not assume session state survives a pause: set what you need per transaction and recreate it at connect time. And if the workload is a nightly batch, a scheduled start and stop, or an environment created and destroyed per run, may fit better than automatic pausing, because the wake-up is then deliberate rather than a surprise.
Related articles
All articles →A jobs table, one claiming query and a lease column give a small team a reliable work queue on the Postgres it already runs. What to build, and where it stops.
11 min
Which Postgres schema changes lock a table, which are safe, and the three-release pattern I use to change a live column without an outage or a long window.
12 min
Twelve small Cloud Run services, each with a sensible pool, can still exhaust one Postgres. How to do the connection math and pick a pooler.
14 min