# Make the test PostgreSQL/MySQL server skip durability work

> Speed up database-heavy tests by running the test PostgreSQL or MySQL server with fsync=off, synchronous_commit=off and a tmpfs data directory, in Docker or GitHub Actions.

A production database promises that a committed transaction survives a crash or a power cut. To keep that promise, PostgreSQL and MySQL write every commit to a log and wait for the disk to confirm it with `fsync()` before they answer. That wait is often the slowest part of a small write.

A test database doesn't need any of that. It's thrown away at the end of the run (or rebuilt from migrations), and nobody cares what's left of it after a crash. Tell the server so, and creating the test database, running migrations, and every `INSERT` and `COMMIT` in your tests stop waiting on the disk.

This only changes how the server writes to disk. Queries, transactions, constraints and locking behave exactly the same.

## What the server is waiting for

- **fsync on commit.** Every commit flushes the write-ahead log (PostgreSQL) or redo log (InnoDB) to disk and waits for it. Tests commit a lot: migrations, `TransactionTestCase` flushes, fixtures that commit, and every write made in autocommit mode.
- **Checkpoints and full-page writes.** PostgreSQL periodically flushes data files and, after each checkpoint, writes whole pages to the WAL to guard against torn pages.
- **The binary log.** MySQL 8 writes every change to the binary log for replication by default.
- **Disk I/O at all.** On a CI runner or a laptop the data directory sits on a real disk, often a slow virtual one.

## PostgreSQL settings

The [PostgreSQL docs on non-durable settings](https://www.postgresql.org/docs/current/non-durability.html) list what's safe to turn off when you don't need durability. For a test server:

| Setting | What it skips |
|---|---|
| `fsync=off` | flushing data and WAL to disk |
| `synchronous_commit=off` | waiting for the WAL write on every commit |
| `full_page_writes=off` | writing whole pages to the WAL after checkpoints |
| data directory on tmpfs | disk I/O altogether: the data lives in RAM |

`fsync` and `full_page_writes` can only be set in the server config or on its command line, so pass them when you start the server. The official `postgres` image passes extra arguments straight to the server.

### docker run

```console
$ docker run -d --name test-postgres \
    -e POSTGRES_PASSWORD=postgres -p 5432:5432 \
    --tmpfs /var/lib/postgresql \
    postgres:18 -c fsync=off -c synchronous_commit=off -c full_page_writes=off
```

Since PostgreSQL 18 the image keeps its data under `/var/lib/postgresql` (in `/var/lib/postgresql/18/docker`). For PostgreSQL 17 and older, mount the tmpfs at `/var/lib/postgresql/data` instead.

### docker compose

```yaml
services:
  postgres:
    image: postgres:18
    command: ["-c", "fsync=off", "-c", "synchronous_commit=off", "-c", "full_page_writes=off"]
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    tmpfs:
      - /var/lib/postgresql
```

Check that it took effect:

```console
$ psql -h localhost -U postgres -c "SHOW fsync" -c "SHOW synchronous_commit"
```

### GitHub Actions service container

Service containers accept a `command`, passed to the container like the arguments after the image name in `docker run`, and `options` for extra `docker create` flags such as `--tmpfs`:

```yaml
jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:18
        command: -c fsync=off -c synchronous_commit=off -c full_page_writes=off
        env:
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --tmpfs /var/lib/postgresql
          --health-cmd pg_isready
          --health-interval 5s
          --health-timeout 5s
          --health-retries 10
    steps:
      - uses: actions/checkout@v5
      # set up Python and install your dependencies here
      - run: pytest
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost:5432/postgres
```

`command` on service containers is a fairly recent addition. If your runner rejects it (an older GitHub Enterprise Server, or another CI system whose services can't take arguments), start the database in a step with the `docker run` command above instead, and wait for `pg_isready` before running the tests.

### Only synchronous_commit on a shared server

If you can't restart the server (a shared dev database), `synchronous_commit` can still be turned off per connection, for the test connections only. With Django:

```python
# settings/test.py
DATABASES["default"].setdefault("OPTIONS", {})["options"] = "-c synchronous_commit=off"
```

That risks losing the last few commits if the server crashes, never corruption, and leaves other clients alone.

## MySQL settings

| Option | What it skips |
|---|---|
| `--innodb-flush-log-at-trx-commit=0` | writing and flushing the redo log on every commit (it's flushed about once a second instead) |
| `--skip-log-bin` | the binary log (on by default in MySQL 8) |
| data directory on tmpfs | disk I/O altogether |

```yaml
services:
  mysql:
    image: mysql:8.4
    command: --innodb-flush-log-at-trx-commit=0 --skip-log-bin
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test_myapp
    ports:
      - "3306:3306"
    tmpfs:
      - /var/lib/mysql
```

The same `command` works in a GitHub Actions service container, with `--tmpfs /var/lib/mysql` in `options`.

## Keep the tests correct

- **Never on a server with data you need.** With `fsync=off` an operating system crash or power loss can corrupt the whole cluster, and a tmpfs data directory is gone when the container stops. Run a separate, throwaway server for tests.
- It only affects crash safety of the throwaway test server; query behaviour, isolation and constraints are identical, so tests stay as meaningful as before.
- If you [reuse the test database](https://flakytest.dev/guides/reuse-test-database/) (`--reuse-db` / `--keepdb`) with a tmpfs data directory, the kept database survives only as long as the container. That's fine in CI; locally, use a disk-backed volume if you want the database to survive restarts.
- Keep the same database engine and major version as production. A fast server that differs from production hides real bugs; SQLite instead of PostgreSQL is a different database, not a faster setting.

## How flakytest finds it

flakytest adds up the time tests spend in the database, schema setup and migrations. When your Django settings use PostgreSQL or MySQL and that time is at least 5 seconds and 20% of test time, it suggests **Make the test database server skip durability work**. It estimates the saving conservatively, as a fraction of the database time, because how much a server waits on the disk depends on the machine. Measure it yourself: profile a run, restart the server with these settings, profile again and run `flakytest compare`.

## Further reading

- [PostgreSQL: non-durable settings](https://www.postgresql.org/docs/current/non-durability.html)
- [PostgreSQL: WAL configuration (`fsync`, `synchronous_commit`, `full_page_writes`)](https://www.postgresql.org/docs/current/runtime-config-wal.html)
- [MySQL: `innodb_flush_log_at_trx_commit`](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit)
- [Docker Hub: the official postgres image](https://hub.docker.com/_/postgres)
- [GitHub Actions: `services.<service_id>.command`](https://docs.github.com/en/actions/reference/workflows-and-actions/workflow-syntax#jobsjob_idservicesservice_idcommand)