When your database needs a restart, your app now waits instead of failing

What a restart used to look like

A hosted database is restarted for reasons that are ours, not yours: a new Postgres image with a security fix, a service you switched on that needs the database made again. Until this week, that restart stopped Postgres at once. Statements that were running were cancelled, every open connection was cut, and for the seconds it took the database to come back, the front door still called it ready and sent new connections to a database that was not there. They were refused.

An app with good retry logic rode through that. Most apps are not written for it, and they should not have to be for something that is our maintenance, not their bug.

What it looks like now

• The door holds new connections. The first thing a planned restart does is tell the front door the database is starting rather than ready. A connection that arrives now waits at the door, exactly as it does when it wakes a paused project, and goes through once the database answers. The door keeps its answer for five seconds, so the restart waits six before it touches anything.
• A checkpoint first. Stopping Postgres writes every changed page to disk, and on a busy database that is most of the stop. Taking that checkpoint while the database is still serving means the stop itself is short.
• Work in flight finishes. A statement that is running, or a transaction that is open, gets up to ten seconds to finish. Each session is closed the moment it goes idle, so it never starts another statement on a database about to stop. A pool notices the closed connection and opens a new one, which waits at the door like any other.
• Then it is made again. Stopped, started, checked ready, and the door lets the held connections in.

None of this is something you switch on. It is how every planned restart of a SnoutData Cloud database runs.

What we measured

We tested it the way an app would meet it: a throwaway project on the live platform, reached through the public front door, with two clients writing to it every 100 milliseconds. One opened a new connection for every write; the other used a pool of two connections, the way most servers do. Twenty seconds in, we restarted the database under them, then kept writing for thirty more, and counted every write each client was told had succeeded against the rows actually in the table. We ran it three times.

Each restart took 16 seconds from start to ready. Of 2,592 writes, none was lost and none on a new connection failed: the worst any of them saw was a ten-second wait. A write through the pool waited at most five, because the pool's connections were closed early in the drain and reopened at once.

The one failure, and why it is still there

In the second run, one write through the pool failed with error 57P01, "terminating connection due to administrator command". It was sent on an open connection in the same instant that connection was being closed.

That one cannot be removed from our side. A connection your pool holds open belongs to the Postgres process that is about to stop, so it has to be closed at some point, and a client can always send a statement at that moment. Closing it while idle makes the window very small; it does not make it zero. At most one statement per open connection can meet it, once per restart.

57P01 is the standard code for a connection the server ended, and the answer is to send the statement again. The pool has already dropped the dead connection, so the retry gets a fresh one through the door. In that run the refused write was not in the table: 921 writes acknowledged, 921 rows.

async function query(pool, text, values) {
  try {
    return await pool.query(text, values)
  } catch (error) {
    if (error.code !== '57P01') throw error
    // The server ended that connection; the pool has dropped it.
    return await pool.query(text, values)
  }
}

With that, a restart is a short pause your app does not otherwise notice.

And a plan change no longer needs one

Your plan sets three limits on your database: how much disk it may use, how much write-ahead log it keeps between checkpoints, and how much one query may spill to temporary files before it is stopped. They used to be applied when the database was made, so a plan change reached it only at the next restart.

Now they are applied to the running database within a minute of the change. The disk limit is a quota on its volume, and the other two are settings Postgres reloads without a restart. So an upgrade gives you the larger limits right away, and nothing restarts for it.

Where it is today

Live on SnoutData Cloud for every project on every plan, since October 22. Restarts we plan (an image update, a service switched on or off) drain this way. A restart nobody planned (a database that crashed, a machine that failed) cannot drain first; your data is safe there for other reasons, and a new connection still waits at the door while the database comes back.

A statement still running after ten seconds is ended by the stop, as before. If you run long reports or migrations, run them where a retry is cheap.

The changelog lists both changes.