Sleep when idle

​

Goal

​

Prerequisites

• A PostgreSQL database that can sleep (see What can’t sleep)
• An app whose database driver can retry a connection

​

Workflow

​

When a database counts as idle

​

How waking works

• The first connection after a sleep is held while the database starts. Your client sees a slower connect, not an error.
• As a target, a Starter database wakes in about a second and a Dedicated database in a few seconds. These are targets, not promises.
• Connections that arrive together are all held, up to 200 per database. Beyond that, and if the database hasn’t woken in time (30 seconds, or 60 on Dedicated), a connection gets the error below.
• The address, password and firewall rules stay in place while the database sleeps. Nothing changes for your app.

​

Shared connections and direct connections

• Shared connections (pooled) on Starter survive sleep. Your app’s connections stay open, the first query waits for the database to wake and then runs, and nothing disconnects. If the wake fails, only that query gets the error and the connection stays open.
• Direct connections are closed when the database goes to sleep. On Dedicated databases, Shared connections close at sleep too.
• Branches use Shared connections by default. Keep migrations and admin tools on the direct address, and let them reconnect.

​

The “database is waking up” error

async function withWakeRetry<T>(run: () => Promise<T>, attempts = 4): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try {
      return await run();
    } catch (error) {
      if ((error as { code?: string }).code !== "57P03" || attempt >= attempts) throw error;
      await new Promise((resolve) => setTimeout(resolve, attempt * 2000));
    }
  }
}

​

Driver settings

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  idleTimeoutMillis: 120000,
  connectionTimeoutMillis: 30000,
});
pool.on("error", (error) => console.error(error));

DATABASE_URL=postgresql://USER:PASSWORD@HOST:PORT/DB?connect_timeout=30&pool_timeout=30&max_idle_connection_lifetime=120

engine = create_engine(
    url,
    pool_pre_ping=True,
    pool_recycle=120,
    connect_args={"connect_timeout": 30},
)

production:
  url: <%= ENV['DATABASE_URL'] %>
  idle_timeout: 120
  connect_timeout: 30
  checkout_timeout: 30

config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
config.MaxConnIdleTime = 2 * time.Minute
config.HealthCheckPeriod = 30 * time.Second
config.ConnConfig.ConnectTimeout = 30 * time.Second

'pgsql' => [
    'url' => env('DATABASE_URL'),
    'options' => [PDO::ATTR_TIMEOUT => 30],
],

$rows = retry(4, fn () => DB::select('select 1'), 2000);

​

What can’t sleep

• Databases with a standby or read copies, and databases that are themselves a standby or a copy. Turn the standby or copies off first.
• Databases in use for logical replication, as publisher or subscriber.
• Databases with active scheduled jobs (pg_cron). A sleeping database runs no scheduled jobs. You can allow sleep anyway by accepting that, with allow_scheduled_jobs.
• Redis, Valkey, MySQL, MariaDB and ClickHouse databases.
• If sleep can’t be turned on, the answer says why (409 sleep_unavailable).

​

Cost and capacity

• Starter: sleep is included in your plan. Branches count toward how many can be awake at once on your plan.
• Dedicated billed hourly: a sleeping database pays for storage only. With monthly or yearly billing sleep is allowed but saves nothing, and the dashboard says so.
• A production database keeps its place while it sleeps, so waking it can’t fail for lack of room. A branch gives its place up and asks for it again when it wakes.
• Backups skip a sleeping database because nothing changes. One that slept longer than its backup interval is backed up when it next wakes.
• Once a day, each sleeping production database is woken at its quietest hour for a quick check, then put back to sleep, so a slow wake is caught before you meet it. The check shows in the database’s events.

​

Turn sleep on or off, and wake it

• The database’s sleep shows enabled, after_minutes, allow_scheduled_jobs and a state of awake, sleeping, asleep or waking. The dashboard shows Available or Sleeping.
• Name a branch as shop-db/fix-checkout to change its sleep timer or wake it: stackshift db sleep shop-db/fix-checkout --after 30m.
• POST .../wake?wait=true answers 200 once the database is awake. Without wait it answers 202 straight away.
• In Terraform, add sleep_after_minutes to your existing stackshift_database resource, or to stackshift_database_instance for a Dedicated database.

stackshift db sleep shop-db --after 15m
stackshift db sleep shop-db --off
stackshift db wake shop-db --wait

curl -X PUT https://api.stackshift.cloud/api/v1/databases/$DATABASE_ID/sleep \
  -H "Authorization: Bearer $STACKSHIFT_API_KEY" \
  -H 'Content-Type: application/json' \
  --data '{"enabled": true, "after_minutes": 15, "allow_scheduled_jobs": false}'

curl -X POST "https://api.stackshift.cloud/api/v1/databases/$DATABASE_ID/wake?wait=true" \
  -H "Authorization: Bearer $STACKSHIFT_API_KEY"

resource "stackshift_database" "shop" {
  name                = "shop-db"
  sleep_after_minutes = 15
}

​

Availability

​

Expected result

​

Common failures

​

Related guides