postgres / dev test clusters

updated August 21, 2026

I run separate Postgres clusters for development and test environments. Both are sized for the machine they run on. The test cluster also has durability settings disabled for fast automated test execution.

Setup

I automate cluster setup in cmd / laptop:

PG_VERSION="${PG_VERSION:-17}"

brew install postgresql@$PG_VERSION

export PATH="$BREW/opt/postgresql@$PG_VERSION/bin:$PATH"
if ! command -v initdb >/dev/null || ! command -v pg_ctl >/dev/null; then
  echo "initdb and/or pg_ctl not found in PATH"
  exit 1
fi

postgres_settings_differ() {
  local data_dir="$1"
  local running token wanted wanted_count=0 running_count
  local -a tokens

  running="$(pg_ctl -D "$data_dir" status 2>/dev/null | tr -s '[:space:]' ' ')"
  if [ -z "$running" ]; then
    return 0
  fi

  wanted="$(tr -s '[:space:]' ' ' <<<"$2")"
  read -ra tokens <<<"$wanted"

  for token in "${tokens[@]}"; do
    case "$token" in
    -c | -p | [0-9]*) continue ;;
    esac

    wanted_count=$((wanted_count + 1))

    if [[ "$running" != *"\"$token\""* ]]; then
      return 0
    fi
  done

  running_count="$(awk -F'"-c"' '{ print NF - 1 }' <<<"$running")"
  [ "$wanted_count" -ne "$running_count" ]
}

start_postgres_cluster() {
  local port="$1"
  local data_dir="$2"
  local log_file="$3"
  local opts="$4"

  mkdir -p "$(dirname "$data_dir")"
  mkdir -p "$(dirname "$log_file")"

  if [ ! -f "$data_dir/PG_VERSION" ]; then
    initdb -D "$data_dir" -U postgres
  fi

  if pg_ctl -D "$data_dir" status >/dev/null 2>&1; then
    if ! postgres_settings_differ "$data_dir" "-p $port $opts"; then
      echo "Postgres is already running with these settings for $data_dir"
      return
    fi

    echo "Restarting Postgres with changed settings for $data_dir"
    pg_ctl -D "$data_dir" stop
  elif lsof -i "tcp:$port" >/dev/null 2>&1; then
    echo "Port $port is already in use"
    return
  fi

  pg_ctl -D "$data_dir" -l "$log_file" -o "-p $port $opts" start
}

pg_tuning="-c shared_buffers=8GB \
  -c effective_cache_size=96GB \
  -c work_mem=64MB \
  -c maintenance_work_mem=2GB \
  -c random_page_cost=1.1 \
  -c max_wal_size=16GB"

pg_durability="-c fsync=off -c synchronous_commit=off -c full_page_writes=off"

# dev databases
start_postgres_cluster 5432 \
  "$HOME/.local/share/postgres/data_dev" \
  "$HOME/.local/share/postgres/log_dev.log" \
  "$pg_tuning"

# test databases
start_postgres_cluster 5433 \
  "$HOME/.local/share/postgres/data_test" \
  "$HOME/.local/share/postgres/log_test.log" \
  "$pg_tuning $pg_durability"

The clusters start automatically when I run the laptop script. I default to PostgreSQL 17 and can override it by exporting PG_VERSION first, for example PG_VERSION=18.

Settings for the machine

initdb writes a configuration for hardware it knows nothing about. On a 128GB laptop whose main job is one large database, two of its defaults describe a different machine:

effective_cache_size=4GB tells the planner it will find almost none of a table in memory, when nearly all of it is there.
random_page_cost=4 prices a random read like a disk seek, which overcharges every index scan on an SSD.

Both are planner inputs rather than allocations, so what they change is which plan gets chosen.

How much that is worth, measured: six read-path queries against a 5GB database, two replicates of each setting profile. Plans changed on four of the six, so the settings do reach plan choice. Four of the six timings landed inside the 2-3ms of run-to-run noise.

A changed plan can still be the slower one. A page of companies filtered on status moved from a sequential scan to an index scan and took the same time either way, because 30,000 of 38,000 rows match the filter and sorting them is the real work. At that selectivity the index scan is the more questionable plan.

Full-text search is where it paid. Under the defaults it scanned the table sequentially. With these settings it used the GIN index on search_vector, and ran 3.7ms against 6.4ms across both replicates.

On a database this size, resident in RAM, that is the honest size of the effect: one query in six, and a plan the defaults get wrong in a way that grows as the data outgrows the cache.

shared_buffers=8GB is sized to hold a whole database. Buffers past the size of the largest one cache nothing, so the usual quarter-of-RAM figure would reserve 32GB to no purpose.

I pass these to pg_ctl at start instead of editing postgresql.conf, so a data directory created before I picked the values still gets them, and the laptop script stays the only place they are defined.

There is no effective_io_concurrency here. macOS has no posix_fadvise, and the server refuses to start with any value but 0.

Changing a setting

Passing settings at start has one consequence worth handling. A cluster that is already running keeps what it was given, so editing the script changes nothing by itself. The earlier version returned early on a running cluster and said it was already running, which reads as success. A cluster could sit on 128MB of shared buffers while the script said 8GB.

Restarting on every run would fix that at too high a price. The laptop script also upgrades Homebrew and syncs editor plugins, and a restart drops every open connection, so a routine run could not happen with a dev server or test suite up.

So the script compares before deciding. pg_ctl status prints the postmaster command line with each token quoted on its own:

/opt/homebrew/Cellar/postgresql@17/17.10/bin/postgres "-D" "..." "-p" "5432" "-c" "shared_buffers=8GB" ...

Each wanted name=value is a substring search against that line. The -c counts have to agree as well, since a setting deleted from the script is a difference that searching for the remaining ones cannot see. A restart then happens on a run that changes something, which is close to never.

Test speed optimizations

The test cluster disables durability:

fsync=off: no forced disk synchronization
synchronous_commit=off: transactions commit before the write lands
full_page_writes=off: less write volume

A test database is built by whatever run needs it, so a crash costs the time to build it again and nothing else.

The development cluster does not get these, even though I restore it from a production backup most days and could afford to lose one. I measured four restores of a 5GB database, two with these settings and two without. They averaged 65s and 66s, and the spread inside either pair was wider than the gap between them. Durability costs the most on many small transactions, which is what a test suite runs and what a bulk restore does not.

fsync=off also gives up less than it sounds like on macOS. The default wal_sync_method is open_datasync, which does not flush the drive cache, so a cluster with fsync=on is already open to power loss.

A database per worktree

I work in several git worktrees at once, which means several checkouts of one repo. Sharing one development database across them means no two of them can hold a schema change, and a schema change is most of what a change is.

So app_development on the dev cluster is golden: it is what the prod restore fills, and nothing connects to it. Every checkout serves a copy, named for its directory: app_dev_main for the main clone, app_dev_app_42 for the APP-42 worktree.

go run ./cmd/db newdb

That clones golden, writes the two files the checkout needs of its own, applies pending migrations, and builds the test database. It is idempotent, so a checkout that has some of that already gets the rest.

The clone is CREATE DATABASE ... STRATEGY = FILE_COPY, which takes about five seconds on a 5GB database against half a minute for the default WAL_LOG. That strategy is also why golden is served to nobody: FILE_COPY refuses to run while any session is connected to the template, and a dev server holds connections open as long as it runs. So newdb reads pg_stat_activity first and names what is connected, rather than letting Postgres say only that the template is in use.

The directory rather than the branch, even though a worktree spells the two the same. A git switch in place would rename the database a checkout wants while the file naming it still said the old one.

The pending migration list measures golden's age

newdb prints a line per migration it applies to the new copy, and that list gets longer with every worktree I cut. That surprised me until I looked at where golden comes from.

The restore drops golden and rebuilds it from the production dump, so its schema is whatever production had when the dump was taken. Nothing migrates it after that, because nothing connects to it. So every copy has to apply everything merged since that dump, and the list only grows until the next restore.

Which makes the length of the list a reading of how stale golden is. Running the download and the restore again shortens it. It does not reach zero: it settles at whatever has merged but not deployed, since the dump comes from production.

Existing copies are unaffected. They are separate databases that already applied those migrations, so only copies made after the restore get the shorter list.

The highest applied version does not say which ones are missing. A migration with an older timestamp than golden's latest can still be pending, because a branch cut before another one can merge after it. The pending set is a set difference, not a cutoff.

Migrating golden after the restore would shorten the list to nothing. I don't, for two reasons. Golden would then hold a schema no production dump ever had, which is a second thing to reason about when a restore looks wrong. And a migration holds a connection, which is exactly what a FILE_COPY clone refuses to run alongside, so the two would race whenever I cut a worktree.

The two generated files

.env.local holds what belongs to this checkout and nothing else:

DATABASE_URL="postgres://postgres@localhost:5432/app_dev_app_42"
PORT=3001

It is read before the shared .env, so these two win and everything else still comes from there. The port is in it because each checkout runs its own web server; the main clone keeps 3000 and worktrees take the numbers above it, lowest one no sibling has claimed and nothing is listening on.

.db holds the database name alone, which is what runs a SQL file from Vim. Both files are untracked, and both are thrown away with the worktree.

Test databases are named for the schema

A test database on the 5433 cluster is named for a hash of the schema file: app_test_<md5(db/schema.sql)>. So two worktrees on the same schema share one, and switching between them rebuilds nothing. CI names it the same way, from the same file, which is half of what makes a cached test result valid across boxes.

Pruning

A copy of a 5GB database per worktree adds up, so one command drops the ones nothing asks for:

go run ./cmd/db prune

It works forward from the checkouts on disk to the databases they claim, and drops the rest, reading the DATABASE_URL each .env.local names rather than recomputing names from directories. A renamed directory or a hand-edited file therefore cannot drop a database something is serving.

Working forward is also what lets one command serve both ends of a worktree's life. deletetree runs it after removing the worktree: the checkout that is gone claims nothing, so its copy is dropped without anything having to name it, and the disk comes back then rather than whenever I next start work. newdb runs it first as well, which covers a worktree removed by hand and keeps the copy about to be made from competing for disk with ones nobody wants.

Golden is never a candidate. That is worth stating twice in the code, because LIKE 'app_dev_%' reads _ as a single-character wildcard, so it matches app_development, and the first version of prune dropped golden.

Usage

Set test database URLs as needed in test suites:

postgres://postgres@localhost:5433/app_test

Used by go / postgres test helpers and setup scripts, and ruby / test framework transaction isolation.

← All articles