query_timeout doesn't cancel anything

Where it came from

An hourly inventory sync was stuck for 17 days. The BullMQ job sat in active forever, with no timeout anywhere, and it looked alive because every deploy ran the sync once on boot. We deploy often, so it took a while until somebody looked at the job itself and not at the screen.

There wasn't one timeout in the whole path, so the first fix added them in four layers (HTTP, the Postgres pool, Redis, the job). On the pool I set two:

const pool = new Pool({
  connectionString,
  connectionTimeoutMillis: 15_000,
  statement_timeout: 120_000,
  query_timeout: 130_000, // client-side net, in case the server never answers
});

It looked like a safety net in the client, a bit longer than the server limit. In the review of the same day I took query_timeout out, and the reason is the rest of this post.

What it really does

In node-postgres, query_timeout is a timer inside your process. Only that. When it fires the promise rejects with Query read timeout, and that's all: nothing goes to Postgres. The backend keeps running the query, with its locks and its CPU, until it ends by itself.

I didn't want to write this from memory, so I tested on a local Postgres 16 with pg_sleep(8) and a 1 second limit:

The third column is what I call a poisoned slot. Look at the last one too. If you connect() and release() the client in a finally, the pool takes back a connection that still has a query in flight, and the next caller who gets it waits behind a result nobody wants. With pool.query() the client gets thrown away instead, and the backend keeps working on the server anyway for the full 8 seconds.

The fix

import { Pool } from "pg";

const pool = new Pool({
  connectionString,
  max: 20,                             // pg default is 10; explicit, so the ceiling is visible
  connectionTimeoutMillis: 15_000,     // to open a connection or to get one from the pool
  statement_timeout: 120_000,          // the server cancels the query
  keepAlive: true,                     // detect sockets dropped by NAT or idle
  keepAliveInitialDelayMillis: 30_000,
});

Different mechanism. statement_timeout is sent to Postgres as a session setting when the connection opens. At the limit the server cancels the statement and answers 57014 canceling statement due to statement timeout; the connection is clean after that and goes back to the pool ready.

The case query_timeout was meant to cover, a server that never answers, in our case was a dead socket (a NAT or a firewall that dropped the connection without telling anybody). TCP keepalive finds that at the socket level, it's what keepAlive above is for.

To see it yourself, point this at any test database:

const { Pool } = require("pg");
const cs = process.env.DATABASE_URL;
const mon = new Pool({ connectionString: cs, max: 1 });
const running = async (q) => (await mon.query(
  "select count(*)::int n from pg_stat_activity where query = $1 and state = 'active'", [q]
)).rows[0].n;

(async () => {
  const a = new Pool({ connectionString: cs, max: 1, query_timeout: 1000 });
  await a.query("select pg_sleep(8.1)").catch((e) => console.log("A:", e.message));
  console.log("A still on server:", await running("select pg_sleep(8.1)")); // 1

  const b = new Pool({ connectionString: cs, max: 1, statement_timeout: 1000 });
  await b.query("select pg_sleep(8.2)").catch((e) => console.log("B:", e.code));
  console.log("B still on server:", await running("select pg_sleep(8.2)")); // 0

  await Promise.all([a.end(), b.end(), mon.end()]);
})();

If you use another driver, it's worth to check what its timeout does before trusting the name, the question I ask now is who cancels the query, the client or the server.

Filed under the problem it solves

• postgres
• node-postgres
• connection pool
• timeouts
• reliability