Optimizing Pytest Postgres Suites on CircleCI

Introduction

When a pytest job is slow on CircleCI, the instinct is often to use a larger resource class or raise parallelism. For many Django and Postgres suites that use pytest-xdist, that can make the problem worse: the expensive work is frequently building the same test database N times per node, not the assertions themselves.

This is because pytest-django’s default with xdist is to give each worker its own database (for example test_foo_gw0, test_foo_gw1), so isolation stays correct for transactional tests. Raising -n (or packing more concurrent workers onto a larger machine) therefore multiplies migrate/seed work against the same Postgres instance unless that setup is shared via cloning or caching. See also Running tests in parallel with pytest-xdist.

Two complementary patterns remove that waste:

Postgres TEMPLATE cloning: migrate and seed once per CircleCI node; each xdist worker clones with CREATE DATABASE … TEMPLATE (seconds instead of minutes).
CircleCI dump cache: pg_dump that template and restore_cache / pg_restore on later pipelines when migrations and seed inputs are unchanged.

In a production-scale suite after a comparable change (same resource class and parallelism throughout):

Metric (success runs, day-over-day)	Before	After	Change
Median job duration	~18.5 min	~12.5 min	−33%
Mean compute credits / run	~1,370	~1,040	−24%
Median CPU util	75%	61%	−14 pp
Median RAM util	77%	50%	−27 pp
Resource class / parallelism	`arm.large` × 3	same	unchanged

Max CPU stayed near ~98% on short bursts. The job got faster and cooler on medians, not closer to the resource ceiling. This provided evidence that the setup work was removed, not that the machine was undersized.

Who is this for?

Teams running large pytest suites (especially Django + pytest-xdist) against Postgres on CircleCI
Platform / CI engineers who see long gaps after collection before the first assertion
Anyone scaling resource class or -n without shrinking that early setup window

Prerequisites

A CircleCI job with a Postgres sidecar (or equivalent reachable Postgres)
Familiarity with pytest and (optionally) pytest-xdist
Ability to fingerprint migrations / seed inputs for cache keys

Recognize the anti-pattern

Typical layout on each CircleCI node:

CircleCI parallelism (split files across nodes)
  └─ Docker job + Postgres sidecar
       └─ pytest -n N   (xdist workers gw0…gwN-1)
            └─ on first DB use: EACH worker CREATE DATABASE + migrate + seed

Isolation is good. Paying full migrate+seed N times against one Postgres is not.

Symptoms

Long gap after collection before the first assertion
CircleCI CPU/RAM charts show a sustained climb for several minutes, then a flatter test phase
Scaling resource class or -n does not shrink that early window (and often lengthens contention)
Logs show multiple workers applying the same migrations in parallel

What will not help much

Bigger machines alone (multiplies concurrent migrate/seed)
More xdist workers alone (same)
pytest-django --reuse-db on CI (helps local persistent DBs; it does not replace cloning or dump cache across ephemeral containers)

Measure before changing anything

Phase timing in the suite

Emit structured markers so job logs line up with CircleCI CPU/RAM graphs. A typical line:

CI_TIMING phase=db_setup_finish elapsed_ms=412000 pid=123 worker=gw0 wall=2026-07-22T17:01:02+00:00 migrate_ms=398000

Useful phase order (per xdist worker):

collection_* → first_test_setup → db_setup_start / db_setup_finish → first_test_call → suite end

Phase	Where to emit
`collection_start` / `collection_finish`	`pytest_collection` hook
`first_test_setup`	first `pytest_runtest_setup` on that worker
`db_setup_start` / `db_setup_finish`	around migrate+seed (or TEMPLATE clone) on first DB use
`first_test_call`	first `pytest_runtest_call` on that worker

Where to configure: add a small helper module and wire it from the root conftest.py (or a dedicated pytest plugin registered via pytest_plugins). Call db_setup_* in the same place the suite already creates the worker database — often the first pytest_runtest_setup, or a custom override of pytest-django’s django_db_setup — not in pytest_sessionstart if migrate is lazy on first test use. That lazy path is what makes the gap after collection look like unexplained resource burn.

Illustrative helper (including hook sketch in comments):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99


"""Illustrative CI_TIMING helpers for correlating pytest phases with CircleCI graphs.

Emit one line per phase to job stdout (or a shared log file). Overlay ``elapsed_ms``
on the job's CPU/RAM chart: a long climb between ``db_setup_start`` and
``db_setup_finish`` means migrate/seed dominates wall time.

Wire emits from a pytest plugin or root ``conftest.py`` (see comments below).
"""

from __future__ import annotations

import os
import time
from datetime import datetime, timezone

_SESSION_START = time.monotonic()
_FIRST_TEST_SETUP_EMITTED = False
_FIRST_TEST_CALL_EMITTED = False


def worker_label() -> str:
    return os.environ.get("PYTEST_XDIST_WORKER") or "controller"


def emit(phase: str, **fields: object) -> None:
    """Print a single structured line (also append to CI_TIMING_LOG if set)."""
    elapsed_ms = int((time.monotonic() - _SESSION_START) * 1000)
    wall = datetime.now(timezone.utc).isoformat()
    parts = [
        "CI_TIMING",
        f"phase={phase}",
        f"elapsed_ms={elapsed_ms}",
        f"pid={os.getpid()}",
        f"worker={worker_label()}",
        f"wall={wall}",
    ]
    for key, value in fields.items():
        parts.append(f"{key}={value}")
    line = " ".join(parts) + "\n"

    # Optional shared file: under pytest-xdist + --capture=fd, worker stdout is
    # often not forwarded; append + tail -F in the job step so phases appear in
    # CircleCI logs. Example: export CI_TIMING_LOG=/tmp/ci-timing.log
    path = os.environ.get("CI_TIMING_LOG")
    if path:
        with open(path, "a", encoding="utf-8") as fh:
            fh.write(line)
            fh.flush()
    else:
        print(line, end="", flush=True)


# --- Where to call emit (pytest hooks / DB setup) ----------------------------
#
# Put this module on PYTHONPATH and register hooks in conftest.py or a plugin:
#
#   # conftest.py
#   import pytest
#   from ci_timing import emit, emit_first_test_setup_once, emit_first_test_call_once
#
#   @pytest.hookimpl(wrapper=True)
#   def pytest_collection(session):
#       emit("collection_start")
#       result = yield
#       emit("collection_finish", num_items=len(session.items))
#       return result
#
#   @pytest.hookimpl(tryfirst=True)
#   def pytest_runtest_setup(item):
#       emit_first_test_setup_once()
#       # Call migrate/seed (or TEMPLATE clone) once per worker here, wrapping:
#       #   emit("db_setup_start")
#       #   ... build DB ...
#       #   emit("db_setup_finish", migrate_ms=...)
#
#   @pytest.hookimpl(hookwrapper=True)
#   def pytest_runtest_call(item):
#       emit_first_test_call_once()
#       yield
#
# Do not migrate in pytest_sessionstart if the suite lazily builds the DB on
# first test use — that is what makes the gap after collection look like
# unexplained CPU/RAM burn on CircleCI.


def emit_first_test_setup_once() -> None:
    global _FIRST_TEST_SETUP_EMITTED
    if _FIRST_TEST_SETUP_EMITTED:
        return
    _FIRST_TEST_SETUP_EMITTED = True
    emit("first_test_setup")


def emit_first_test_call_once() -> None:
    global _FIRST_TEST_CALL_EMITTED
    if _FIRST_TEST_CALL_EMITTED:
        return
    _FIRST_TEST_CALL_EMITTED = True
    emit("first_test_call")

Reading the result on CircleCI: open the job → Resources (CPU/RAM) and search step logs for CI_TIMING. Overlay elapsed_ms on the graph: a multi-minute climb between db_setup_start and db_setup_finish (after collection_* / first_test_setup) means setup dominates. With pytest-xdist and --capture=fd, worker stdout may not reach the controller — set CI_TIMING_LOG and tail -F that file in the test step so worker phases appear in the job log.

Rule of thumb: if migrate+seed on the critical path is ≥ ~2–3 minutes, TEMPLATE is worth trying. If that cost repeats every pipeline with the same schema/fixtures, add dump caching.

CircleCI Insights and Usage

Use Insights (or the CLI) for median job duration over a date range. A step-change with unchanged resource class / parallelism is a strong signal that setup behavior changed.

Usage export fields such as MEDIAN_CPU_UTILIZATION_PCT and MEDIAN_RAM_UTILIZATION_PCT show whether the job is compute-bound or waiting. After a successful DB-setup optimization, expect lower median duration and lower median CPU/RAM, with max CPU still high on short peaks. Do not treat lower median CPU as under-utilization that requires a smaller class until the test phase is healthy and queues are acceptable.

Fix 1 — Migrate once, clone with Postgres TEMPLATE

Postgres can create a database by copying an existing one (template databases):

CREATE DATABASE suite_gw0 TEMPLATE suite_template;

Build the template once per node (migrate + seed), then clone per xdist worker. Illustrative helpers:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95


"""Illustrative Postgres TEMPLATE helpers for pytest-xdist on CircleCI.

Build the template once per CircleCI node (migrate + seed), then each xdist
worker clones with CREATE DATABASE ... TEMPLATE so workers stay isolated
without repeating full populate.
"""

from __future__ import annotations

import os

import psycopg2
from django.conf import settings
from django.core.management import call_command
from django.db import connection
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT


def _admin_connect_kwargs() -> dict:
    return {
        "dbname": os.environ.get("ADMIN_DB", "postgres"),
        "user": settings.DATABASES["default"]["USER"],
        "password": settings.DATABASES["default"]["PASSWORD"],
        "host": settings.DATABASES["default"]["HOST"],
        "port": settings.DATABASES["default"]["PORT"],
    }


def _worker_label() -> str:
    return os.environ.get("PYTEST_XDIST_WORKER") or "controller"


def _drop_if_exists(cur, db_name: str) -> None:
    cur.execute("SELECT 1 FROM pg_database WHERE datname = %s", (db_name,))
    if not cur.fetchone():
        return
    cur.execute(
        """
        SELECT pg_terminate_backend(pid)
        FROM pg_stat_activity
        WHERE datname = %s AND pid <> pg_backend_pid()
        """,
        (db_name,),
    )
    cur.execute(f'DROP DATABASE "{db_name}"')


def build_template_database(template_name: str | None = None) -> str:
    """Migrate + seed once into a TEMPLATE source database."""
    name = template_name or os.environ.get("DB_TEMPLATE", "suite_template")
    conn = psycopg2.connect(**_admin_connect_kwargs())
    conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    cur = conn.cursor()
    _drop_if_exists(cur, name)
    cur.execute(f'CREATE DATABASE "{name}"')
    cur.close()
    conn.close()

    settings.DATABASES["default"]["NAME"] = name
    connection.close()
    connection.settings_dict["NAME"] = name

    call_command("migrate", verbosity=0, interactive=False)
    # call_command("loaddata", ...)  # or project-specific seed / factories
    connection.close()
    return name


def ensure_worker_database(*, template: str | None = None) -> str:
    """Create a per-worker DB (empty, or cloned from ``template``)."""
    db_name = f"suite_{_worker_label().replace('/', '_')}"
    conn = psycopg2.connect(**_admin_connect_kwargs())
    conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    cur = conn.cursor()
    _drop_if_exists(cur, db_name)
    if template:
        # TEMPLATE clone requires no other sessions on the template DB.
        cur.execute(
            """
            SELECT pg_terminate_backend(pid)
            FROM pg_stat_activity
            WHERE datname = %s AND pid <> pg_backend_pid()
            """,
            (template,),
        )
        cur.execute(f'CREATE DATABASE "{db_name}" TEMPLATE "{template}"')
    else:
        cur.execute(f'CREATE DATABASE "{db_name}"')
    cur.close()
    conn.close()

    settings.DATABASES["default"]["NAME"] = db_name
    connection.close()
    connection.settings_dict["NAME"] = db_name
    return db_name

Wire the mode with an env var (for example DB_SETUP_MODE=full|template) in the pytest / django_db_setup hook so baseline and optimized paths can A/B in the same workflow.

Workers still get isolated databases; only construction changes.

Fix 2 — Cache the template dump across pipelines

TEMPLATE alone only helps inside one job. The template dies with the container. If migrations and fixtures are stable, cache a pg_dump and restore it on the next pipeline.

Illustrative cache helpers (fingerprint → restore or dump):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72


#!/usr/bin/env bash
# Illustrative helpers for caching a Postgres template dump on CircleCI.
#
# Usage:
#   bash scripts/db_cache.sh fingerprint
#   bash scripts/db_cache.sh restore
#   bash scripts/db_cache.sh dump
#
# Fingerprint every input that changes DB contents (migrations, seed knobs,
# Postgres major). Prefer a pg_dump/pg_restore major that matches the sidecar.

set -euo pipefail

CACHE_DIR="${DB_CACHE_DIR:-/tmp/db-cache}"
DUMP="${CACHE_DIR}/template.dump"
TEMPLATE="${DB_TEMPLATE:-suite_template}"
mkdir -p "$CACHE_DIR"

# Prefer major-matched client tools when installed beside an older default.
if [[ -d /usr/lib/postgresql/15/bin ]]; then
  export PATH="/usr/lib/postgresql/15/bin:${PATH}"
fi

export PGHOST="${PGHOST:-localhost}"
export PGPORT="${PGPORT:-5432}"
export PGUSER="${PGUSER:-postgres}"
export PGPASSWORD="${PGPASSWORD:-}"

fingerprint() {
  {
    echo "seed_profile=${SEED_PROFILE:-default}"
    find path/to/migrations -type f | sort | while read -r f; do
      sha256sum "$f"
    done
  } > "${CACHE_DIR}/fingerprint"
  cp "${CACHE_DIR}/fingerprint" .db_cache_fingerprint
  echo "Wrote fingerprint:"
  cat "${CACHE_DIR}/fingerprint"
}

dump() {
  echo "pg_dump=$(command -v pg_dump); $(pg_dump --version)"
  pg_dump --format=custom --file="$DUMP" "$TEMPLATE"
  echo "CACHE_SAVED path=$DUMP"
}

restore() {
  if [[ ! -f "$DUMP" ]]; then
    echo "CACHE_MISS reason=no_dump"
    return 0
  fi
  echo "pg_restore=$(command -v pg_restore); $(pg_restore --version)"
  psql -d postgres -v ON_ERROR_STOP=1 <<SQL
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '${TEMPLATE}' AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS "${TEMPLATE}";
CREATE DATABASE "${TEMPLATE}";
SQL
  pg_restore --dbname="$TEMPLATE" --no-owner --no-acl "$DUMP"
  echo "CACHE_HIT path=$DUMP"
}

case "${1:-}" in
  fingerprint) fingerprint ;;
  dump) dump ;;
  restore) restore ;;
  *)
    echo "usage: $0 {fingerprint|dump|restore}" >&2
    exit 2
    ;;
esac

Critical: matching `pg_dump` to the server major version

pg_dump refuses to dump a server newer than its client. If the sidecar is Postgres 15 and the primary image ships an older client, install a matching client (for example postgresql-client-15) and put it first on PATH.

End-to-end CircleCI job

Adapt paths, package installs, and fingerprint inputs to the project:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92


version: 2.1

# Example: pytest + Postgres with TEMPLATE cloning and optional dump cache.
# Adapt paths, package installs, and seed/fingerprint inputs to the project.

jobs:
  pytest-postgres:
    docker:
      - image: cimg/python:3.10
      - image: cimg/postgres:15.5
        environment:
          POSTGRES_USER: postgres
          POSTGRES_DB: postgres
          POSTGRES_HOST_AUTH_METHOD: trust
    resource_class: arm.large
    parallelism: 3
    environment:
      DB_SETUP_MODE: template
      DB_TEMPLATE: suite_template
      DB_CACHE_DIR: /tmp/db-cache
      PGHOST: localhost
      PGPORT: "5432"
      PGUSER: postgres
    steps:
      - checkout
      - run:
          name: Install Python deps
          command: pip install -r requirements.txt
      - run:
          name: Wait for Postgres
          command: dockerize -wait tcp://localhost:5432 -timeout 1m
      - run:
          name: Install postgresql-client-15 (match sidecar major version)
          command: |
            # pg_dump refuses to dump a server newer than its client major.
            sudo apt-get update -qq
            sudo apt-get install -y -qq postgresql-client-15
            export PATH="/usr/lib/postgresql/15/bin:$PATH"
            pg_dump --version
            echo 'export PATH="/usr/lib/postgresql/15/bin:$PATH"' >> "$BASH_ENV"
      - run:
          name: Write DB cache fingerprint
          command: |
            # Fingerprint migrations + anything that changes seed contents.
            {
              echo "seed_profile=${SEED_PROFILE:-default}"
              find path/to/migrations -type f | sort | while read -r f; do
                sha256sum "$f"
              done
            } > .db_cache_fingerprint
      - restore_cache:
          keys:
            - pgdump-v1-{{ checksum ".db_cache_fingerprint" }}
            - pgdump-v1-
      - run:
          name: Restore template dump or build template + dump
          command: |
            mkdir -p "$DB_CACHE_DIR"
            DUMP="$DB_CACHE_DIR/template.dump"
            if [ -f "$DUMP" ]; then
              psql -d postgres -v ON_ERROR_STOP=1 \<<SQL
            SELECT pg_terminate_backend(pid)
            FROM pg_stat_activity
            WHERE datname = '${DB_TEMPLATE}' AND pid <> pg_backend_pid();
            DROP DATABASE IF EXISTS "${DB_TEMPLATE}";
            CREATE DATABASE "${DB_TEMPLATE}";
            SQL
              pg_restore --dbname="$DB_TEMPLATE" --no-owner --no-acl "$DUMP"
              echo "CACHE_HIT path=$DUMP"
            else
              python -c "from db_template import build_template_database; build_template_database()"
              pg_dump --format=custom --file="$DUMP" "$DB_TEMPLATE"
              echo "CACHE_SAVED path=$DUMP"
            fi
      - run:
          name: Run pytest (workers clone template)
          command: |
            # Split files across CircleCI nodes; xdist clones DBs inside each node.
            circleci tests glob 'tests/**/test_*.py' \
              | circleci tests split --split-by=timings \
              | xargs pytest -n 3 --dist=load
      - save_cache:
          key: pgdump-v1-{{ checksum ".db_cache_fingerprint" }}
          paths:
            - /tmp/db-cache
      - store_test_results:
          path: test-results

workflows:
  test:
    jobs:
      - pytest-postgres

Bump the cache key prefix when dump format or client assumptions change. Only cache synthetic / scrubbed test data — never production dumps with secrets or PII.

Validate with an A/B workflow

Run two jobs in the same workflow, identical except for DB setup:

Job	Mode
Baseline	Each worker: `CREATE DATABASE` → migrate → seed
Optimized	Once per node: populate template (or restore dump); workers: `CREATE DATABASE … TEMPLATE`

Hold fixed: resource class, CircleCI parallelism, xdist width, test matrix, and per-test work.

Check	Baseline	Optimized
Per-worker `db_setup`	minutes	~tens–hundreds of ms (clone)
Collected / passed / skipped	—	must match
Job wall	higher	lower by ~setup critical path
`restore_cache` (warm)	n/a	`Found a cache from job …`
Status line	n/a	`CACHE_HIT` / `CACHE_SAVED`

Store a one-line artifact (CACHE_HIT / CACHE_SAVED / CACHE_MISS) so reviews do not require digging through step logs.

What good looks like after rollout

Median duration drops with no resource-class / parallelism change.
Distribution may be briefly bimodal: most runs in the fast cohort, a small slow tail still paying the old cost (cold cache / non-cached path). That is expected during rollout; watch the slow-tail share fall as cache hits dominate.
Median CPU/RAM fall; max CPU stays saturated on peaks. That is consistent with removing long setup, not with needing a larger machine.

Compare concrete job URLs from before and after the flip (same branch, same job name) when explaining the change to stakeholders — duration and resource charts tell the story faster than config diffs alone.

Decision tree

Is migrate+seed ≥ ~2–3 min on the critical path?
  ├─ no  → profile tests, deps, image pulls instead
  └─ yes
       Do N xdist workers each migrate against one Postgres?
         ├─ yes → implement TEMPLATE (Fix 1)
         └─ also same schema/fixtures every pipeline?
              └─ yes → add dump cache (Fix 2)

Still slow after that?
  → optimize the test phase itself (collection size, skip rate,
    fixture scope, split-by-timings, flaky retries, etc.)

Safety checklist

Fingerprint all inputs that change DB contents (migrations, fixture versions, seed knobs, Postgres major)
Match pg_dump / pg_restore major version to the sidecar
Never cache dumps with production secrets or PII
Keep seed deterministic so A/B comparisons are fair
Confirm identical collected / passed / skipped counts before declaring victory
Watch both median and P90 / slow-tail share after rollout

Official references

Topic	Link
Postgres template databases	https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
`CREATE DATABASE … TEMPLATE`	https://www.postgresql.org/docs/current/sql-createdatabase.html
`pg_dump` / `pg_restore`	https://www.postgresql.org/docs/current/app-pgdump.html
CircleCI caching	https://circleci.com/docs/guides/optimize/caching/
CircleCI resource classes	https://circleci.com/docs/guides/optimize/resource-class/
pytest-django database	https://pytest-django.readthedocs.io/en/stable/database.html