A command that forks a throwaway copy of your Postgres, runs a query against it, and deletes the copy the moment you close the session.

Most of the tools we use all day keep a scratchpad somewhere. Emacs opens on *scratch*, the JetBrains IDEs have scratch files, vim has a buffer you never bother to save, and the shell has /tmp. None of them are precious. You open one, try a thing, see what happens, and you never think about the mess you leave behind, because there isn't one: you close it and it's gone.

A copy of a real production database, with real data in it, has always been slow enough and expensive enough that you treat it as a serious object rather than a throwaway. So when you want to run one heavy query against production data, you usually end up doing something worse instead: running it straight against production and watching the latency graphs, pulling a few rows into a local Postgres that behaves nothing like the real one, or quietly talking yourself out of running it at all.

What changes that is the cost of a copy. Once a branch of your database takes about a second to create and gets deleted the moment you stop using it, it stops being a staging environment you have to plan around and starts behaving like that /tmp file. We've written before about how the branches get that cheap; this post is about the small command that sits on top of them.

xata scratch does one thing. It forks a branch off your database, hands it to psql or whatever tool you point it at, and deletes the branch the moment you close the session. The closest thing to it is mktemp, except that instead of an empty temp file you get a full Postgres with your data already in it.

TL;DR (on an initialized project):

# run a heavy analytical query on a copy of production,
# without fighting live traffic for resources
xata scratch -x "SELECT <heavy analytical query> FROM <high-traffic table>"

# see how a query you want to optimize behaves on realistic data
xata scratch -x "SELECT ..."

# create a branch and drop into psql, pgcli, or any libpq tool
xata scratch psql

# let a coding agent reach for it while investigating a bug,
# rehearsing a migration, or testing a query
xata scratch <point your agent at the scratch command>

<aside> 📹

Embed the xata_scratch.mp4 demo video here (carried over from the original draft).

</aside>

Under the hood

A run with an inline query looks like this:

$ xata scratch -x "SELECT count(*) FROM events WHERE created_at > now() - interval '7 days'"
# forks a branch (~0.9s), runs the query, deletes the branch on exit
 count
-------
 4823196

Underneath, the command is a create, a run, and a delete. Here's the core, lightly trimmed, with the full version in the open-source CLI:

const branchName = `scratch-${randomUUID()}`;

try {
  // 1. fork an ephemeral branch off the parent. scale-to-zero on,
  //    so it stops costing compute after 10 minutes idle.
  const scratch = await createChildBranch(
    /* ... */ branchName, /* scaleToZero */ true, /* inactivityMinutes */ 10
  );

  const connectionString = buildConnectionString(scratch.connectionString, {
    database,
    endpointType: 'rw'
  });

  // 2. run an inline query, or hand the branch to a binary like psql
  if (flags.execute) {
    printSQLResult(this, flags.json, await executeSQL(this, flags.execute, connectionString));
  } else {
    const subprocess = spawnBinary(resolvedBinary, binaryArgs, connectionString, database);
    binaryExitCode = await subprocess.exited;
  }
} finally {
  // 3. whatever happened above, delete the branch
  await cleanup();
}

The part I find more interesting is what makes xata scratch psql work with a tool we didn't write. It comes down to one small function that takes the branch's connection string and sets the standard libpq environment variables before spawning the child process:

function buildPostgresEnvironment(connectionString: string, database: string) {
  const parsed = parse(connectionString);
  return {
    DATABASE_URL: connectionString,
    XATA_DATABASE_URL: connectionString,
    PGHOST: parsed.host ?? undefined,
    PGPORT: parsed.port ?? '5432',
    PGUSER: parsed.user,
    PGPASSWORD: parsed.password,
    PGDATABASE: database,
    PGSSLMODE: 'require'
  };
}

function spawnBinary(binary, args, connectionString, database) {
  return Bun.spawn([binary, ...args], {
    env: { ...Bun.env, ...buildPostgresEnvironment(connectionString, database) },
    stdin: 'inherit',
    stdout: 'inherit',
    stderr: 'inherit'
  });
}

Any tool built on libpq, which is to say psql, pgcli, or a script you wrote yourself, reads PGHOST, PGUSER and the rest and connects to the scratch branch without ever knowing it's a scratch branch. Because stdin, stdout and stderr are inherited, psql stays fully interactive. As far as it can tell it's talking to an ordinary Postgres, which it is.

If that were the whole thing the command would be about twenty lines. Most of the actual code exists to guarantee one property: the branch always gets deleted, including in the cases where you don't exit cleanly. The delete runs in a finally, so it fires whether the query returns, throws, or you end the psql session yourself. The signals you'd actually hit, SIGINT, SIGTERM and SIGHUP, are handled too, so a Ctrl-C halfway through a long query tears the branch down instead of leaking it, and the cleanup has its own ten-second timeout so that a delete which hangs can't take your shell down with it. The case that took the most thought is a create that succeeds on the server while the response never makes it back to the client: when that happens we look the branch up by the name we generated for it (scratch-<uuid>) and delete it anyway, so a dropped packet doesn't quietly leave an orphan running.

None of this is clever, which is sort of the point. It's the unglamorous bookkeeping you write once and then stop thinking about. What makes it worth writing at all is the layer underneath it: branches cheap and fast enough to create and throw away thousands of times a day, which is the only reason treating a Postgres copy as disposable feels ordinary rather than reckless.

Where this leads

Cheap, disposable branches keep turning up workflows that didn't quite make sense before. A scratchpad is the obvious one. Preview environments seeded with real data instead of an empty schema are another. The one we didn't plan for is coding agents reaching for a branch per task, spinning one up, running against it, and deleting it when they're done, which I find faintly strange to watch scroll past in the logs.