五個 Agent 共用一個開發資料庫：如何用 Postgres 18 為每個 Worktree 克隆資料庫

• Book: Database Playbook
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

One agent is in ../app-auth, renaming users.email to users.login_email. Another is in ../app-invoices, about to run its test suite. Each worktree has its own branch, its own files, its own node_modules. Both .env files point at the same app_dev database. The migration lands, the invoices tests go red on column users.email does not exist, and the second agent starts fixing code that was never broken.

Worktrees gave each agent a private copy of the code. They did nothing for the database, and on a dev machine that's usually one shared Postgres. The old answer is one database per branch, which used to mean a full copy of your seeded data every time. Postgres 18 makes that copy close to free on the right filesystem. Below is a short script that gives each worktree its own cloned database and cleans it up when the branch is done.

Worktrees isolate files, not databases

git worktree lets one repository "check out more than one branch at a time", each in its own directory. That's why tools that run several coding agents side by side build on it: no two agents edit the same checkout. Everything outside the repo stays shared, and the database is the shared thing agents write to.

A migration is a schema change on a shared resource. A seed script that truncates tables wipes another agent's fixtures, and a test that counts rows flakes because someone else inserted some. What you see is a red test in the wrong worktree, and an agent that will happily try to fix it.

You could run a separate Postgres server per worktree on its own port. That works, and it costs a server process and a full data copy per agent. One server with a database per worktree is lighter, as long as making the database is cheap.

Separate checkouts, one shared database: the migration from one worktree breaks the tests in another.

What Postgres 18 changed

CREATE DATABASE new TEMPLATE old has always copied a database. Since Postgres 15 the default STRATEGY is WAL_LOG, which copies the template block by block and writes each block to the write-ahead log. The CREATE DATABASE docs call it "the most efficient strategy in cases where the template database is small". The older FILE_COPY strategy copies at the filesystem level and writes only a small WAL record per tablespace, at the price of a checkpoint before and after the copy.

Postgres 18, released 2025-09-25, added a server variable called file_copy_method (contributed by Nazir Bilal Yavuz). The default is copy. Set it to clone and, per the configuration docs, FILE_COPY uses copy_file_range() on Linux and FreeBSD or copyfile on macOS, "giving the kernel the opportunity to share disk blocks". On a copy-on-write filesystem the new database points at the template's blocks, and the two only diverge where one of them writes.

You need all three pieces in the same session:

SET file_copy_method = clone;
CREATE DATABASE wt_agent_auth
  TEMPLATE app_template
  STRATEGY = FILE_COPY;

Leave out STRATEGY = FILE_COPY and you get WAL_LOG, which ignores file_copy_method completely. The setting has user context and needs no restart (postgresqlco.nf), so a plain SET is enough. You don't have to touch postgresql.conf.

The published numbers are the reason to bother. Radim Marek cloned a 6,289 MB database in 67,000 ms with WAL_LOG and in 212 ms with FILE_COPY plus clone. Ruohang Feng reports about 200 ms for a 120 GB database on NVMe and about 569 ms for 797 GB. Those are their machines. The script section shows how to get your own numbers.

A clone shares the template's blocks until someone writes; only the changed blocks get their own copy.

Check the filesystem before you trust it

Clone only shares blocks on a copy-on-write filesystem: APFS on macOS, XFS with reflinks, Btrfs, or ZFS (OpenZFS 2.2+ with block cloning enabled, per Feng). Christophe Pettus puts the ext4 case plainly: "On ext4 there is nothing to reflink."

What happens on ext4 is where sources disagree. Pettus writes that clone "fails rather than silently falling back to a copy". Feng's table says ext4 "falls back to traditional copy". The Postgres code has no fallback of its own: clone_file() raises an error when the system call fails, and a comment in its Linux loop allows for the kernel falling "back to a slow copy". On macOS it passes COPYFILE_CLONE_FORCE, which Apple's copyfile man page says returns an error "if cloning fails". Whichever way your setup goes, a filesystem without reflinks saves you nothing. Check before you build on it.

The filesystem that counts is the one under the Postgres data directory, not the one under your repo. If Postgres runs in a container, run the check inside the container.

dir="$(psql -d postgres -Atc 'SHOW data_directory')"

# Linux: xfs, btrfs or zfs are good; ext4 is not
findmnt -no FSTYPE -T "$dir"
# XFS only: reflink=1 means clones can share blocks
xfs_info "$(findmnt -no TARGET -T "$dir")" | grep -o 'reflink=.'

# macOS: you want "APFS"
diskutil info "$(df "$dir" | awk 'END {print $NF}')" \
  | grep Personality

SHOW data_directory needs a superuser or a role with pg_read_all_settings. If Postgres rejects clone as a value for file_copy_method, your build has no clone support: the commit that added it only compiles the option in when the platform has copy_file_range or a copyfile with clone support.

The script: one database per worktree

wt-db has three commands. template builds app_template from your migrations and seed, then locks it. clone <worktree> creates a database named after that worktree's branch and writes its URL into the worktree's .env. drop <worktree> removes it.

#!/usr/bin/env bash
# wt-db: one cloned Postgres 18 database per git worktree.
# Uses PGHOST/PGUSER/PGPORT like psql does. MIGRATE is the
# command that migrates and seeds the DB in $DATABASE_URL.
set -euo pipefail

TEMPLATE="${TEMPLATE:-app_template}"
PGPORT="${PGPORT:-5432}"
: "${PGHOST:?set PGHOST}" "${PGUSER:?set PGUSER}"

sql() { psql -X -d postgres -v ON_ERROR_STOP=1 "$@"; }
url() { echo "postgres://$PGUSER@$PGHOST:$PGPORT/$1"; }

db_name() {
  local branch
  branch="$(git -C "$1" branch --show-current)"
  if [ -z "$branch" ]; then
    echo "no branch checked out in $1" >&2
    exit 1
  fi
  branch="$(tr 'A-Z' 'a-z' <<<"$branch" | tr -c 'a-z0-9\n' '_')"
  echo "wt_${branch:0:60}"
}

set_env() {
  local file="$1" value="$2" tmp
  tmp="$(mktemp)"
  if [ -f "$file" ]; then
    grep -v '^DATABASE_URL=' "$file" > "$tmp" || true
  fi
  echo "DATABASE_URL=$value" >> "$tmp"
  mv "$tmp" "$file"
}

build_template() {
  : "${MIGRATE:?set MIGRATE}"
  sql <<SQL
SELECT format('ALTER DATABASE %I IS_TEMPLATE false', datname)
FROM pg_database WHERE datname = '$TEMPLATE' \gexec
DROP DATABASE IF EXISTS "$TEMPLATE" WITH (FORCE);
CREATE DATABASE "$TEMPLATE";
SQL
  DATABASE_URL="$(url "$TEMPLATE")" bash -c "$MIGRATE"
  sql <<SQL
ALTER DATABASE "$TEMPLATE"
  IS_TEMPLATE true ALLOW_CONNECTIONS false;
SQL
}

clone() {
  local wt="$1" db
  db="$(db_name "$wt")"
  sql <<SQL
SELECT count(pg_terminate_backend(pid)) AS kicked
FROM pg_stat_activity WHERE datname = '$TEMPLATE';
SET file_copy_method = clone;
\timing on
CREATE DATABASE "$db" TEMPLATE "$TEMPLATE" STRATEGY = FILE_COPY;
SQL
  set_env "$wt/.env" "$(url "$db")"
  echo "$wt -> $db"
}

drop() {
  local db
  db="$(db_name "$1")"
  sql -c "DROP DATABASE IF EXISTS \"$db\" WITH (FORCE)"
}

case "${1:-}" in
  template) build_template ;;
  clone) clone "${2:?worktree path}" ;;
  drop) drop "${2:?worktree path}" ;;
  *) echo "usage: wt-db template | clone <wt> | drop <wt>" >&2
     exit 2 ;;
esac

A few of those choices are deliberate.

The template is its own database, not the app_dev you work in every day. CREATE DATABASE "will fail if any other connection exists when it starts" (docs), and something is always connected to the database you use. ALLOW_CONNECTIONS false keeps new sessions out, but setting the flag to false doesn't end existing sessions (template docs), so clone terminates any leftovers first. IS_TEMPLATE true lets any role with CREATEDB clone it, not only its owner (ALTER DATABASE).

Names come from the branch: lowercased, every character that isn't a letter or digit turned into _, and cut so the full name stays inside Postgres's 63-byte identifier limit. Longer names get truncated with nothing more than a NOTICE, which is how two long branch names end up pointing at one database, and dropping one worktree's database drops the other's. The cleanup also makes it safe to interpolate the name into SQL. Two branches that differ only in punctuation, fix/login and fix-login, map to the same name, and the second clone fails on CREATE DATABASE rather than reusing the first one's data.

DROP DATABASE ... WITH (FORCE) terminates sessions a worktree left open, such as a dev server still running, and fails only if something like a prepared transaction would remain (DROP DATABASE). If you need a password in the URL, add it in url().

Wiring it up for five agents:

export MIGRATE='npm run db:migrate && npm run db:seed'
./wt-db template

for name in auth invoices search billing docs; do
  git worktree add -b "agent/$name" "../app-$name"
  cp .env "../app-$name/.env"
  ./wt-db clone "../app-$name"
done

The cp is there because .env is untracked, so git worktree add doesn't bring it along, and clone only rewrites the DATABASE_URL line. When a branch is finished, drop its database before removing the worktree, because drop reads the branch name from the worktree:

./wt-db drop ../app-auth
git worktree remove ../app-auth

git worktree remove refuses a worktree with untracked or modified files unless you pass --force (docs), so look at what's left before you force it.

To get numbers for your own machine, clone the same template three ways in psql and compare the Time: lines that \timing prints:

\timing on
CREATE DATABASE t_wal TEMPLATE app_template;

SET file_copy_method = copy;
CREATE DATABASE t_copy TEMPLATE app_template
  STRATEGY = FILE_COPY;

SET file_copy_method = clone;
CREATE DATABASE t_clone TEMPLATE app_template
  STRATEGY = FILE_COPY;

Run df -h on the data volume before and after t_clone too. pg_database_size('t_clone') will still report the template's full size, because it counts logical size and not shared blocks, which Marek shows in his write-up. If your seed is only a few megabytes, don't be surprised when WAL_LOG keeps up: that's the small-template case the docs say it was built for. Clone pulls ahead as the template grows. Drop the three t_ databases when you're done.

What goes wrong later

Most of these show up a week in, once the agents have been writing for a while.

• Clones diverge as agents write. Blocks stay shared only until one side changes them. Marek watched an UPDATE of 20 rows split off the changed extent while the rest of the table stayed shared, and notes that UPDATEs and VACUUM keep unsharing pages. A migration that rewrites a large table gives that one worktree a full copy of it. Size the disk for the tables your agents rewrite; the reads cost nothing.
• Every FILE_COPY forces two checkpoints. One before the copy, one after. On a laptop Postgres that's usually cheap. On a shared server with other busy databases, the docs warn it "may have a noticeable negative impact on overall system performance".
• This is a local trick. On a managed Postgres service you don't choose the filesystem, so don't plan your hosted environments around it. It's for dev machines and self-hosted servers you control.

Clones are also snapshots. After a migration merges to main, run wt-db template again so new worktrees start from the new schema; existing worktrees keep the schema they cloned until you drop and re-clone them. And if your test runner connects to a separate database (many frameworks append _test), give each worktree a second clone and point that variable at it, or the agents will share the test database instead.

If this was useful

A database per worktree is a small version of a bigger design question: which state is shared, who is allowed to change its shape, and what a migration costs when several writers depend on one schema. The Database Playbook is about choosing and changing data stores (Postgres, SQLite, Redis, DynamoDB) and the schema and migration decisions that come with each one. I also build Hermes IDE, so weigh this with that in mind: it gives each task a hermes/<name> branch, a worktree and its own ports, it's free and source-available, and the script above covers the database those worktrees would otherwise share.