September 22, 20265 min read

Making Order Currency Required While Checkout Keeps Writing

Use Ptah to make order currency required while old and new checkout writers run, with a measured PostgreSQL workload and bounded lock waits.

Simon HartSolutions Engineer

A checkout migration must keep the old writer working while the new version rolls out.

The checkout in this example starts with a nullable currency column. Existing orders were priced in US dollars, and the old writer omitted the column. A new writer supplies EUR explicitly. Before another service can require a currency on every order, we need to fill the historical values and enforce NOT NULL without stopping either writer.

This example runs both checkout transactions throughout the change: each creates an order and reads it back. Ptah runs the staged migrations with bounded lock waits, then checks the final schema. PostgreSQL’s pgbench supplies the traffic.

Make the old writer compatible first

A default handles future inserts that omit the column. It does not fill existing nulls. We therefore need a default, a backfill, and a constraint, in that order.

DefaultNew legacy orders receive USD. Explicit EUR values remain EUR.
BackfillExisting nulls become USD. Each range commits separately.
ConstrainValidate the data, then require a currency on every row.

Both checkout writers run through these phases. The old reader already treats a missing currency as USD.

The run uses Ptah 0.7.0, PostgreSQL 18.6, one million existing orders, and eight concurrent clients. DB_URL points to a disposable checkout database. The example files include both transaction bodies, the seed data, every migration pair, and the recorded results.

Give the migration a lock budget

The first change is small:

ALTER TABLE public.orders ALTER COLUMN currency SET DEFAULT 'USD';

It still needs a table lock. To test contention, we held a read transaction open and attempted only this migration:

ptah migrations up \
  --db-url "$DB_URL" --migrations-dir migrations \
  --to-version 1790078460 --verify-sum \
  --lock-timeout 200ms --statement-timeout 30s --json

PostgreSQL rejected the lock wait with SQLSTATE 55P03. Ptah exited 2 and recorded a failed revision with zero of one statements applied. Checkout traffic continued, and the default had not changed. Once the reader finished, --allow-dirty let Ptah verify and retry the unchanged migration.

The lock timeout bounds a database lock wait. It does not establish an end-to-end request latency guarantee.

Backfill before enforcing the constraint

The backfill changes only nulls, so it preserves the new writer’s EUR orders. The first range is:

UPDATE public.orders SET currency = 'USD'
WHERE currency IS NULL AND id > 0 AND id <= 250000;

The complete example uses four ranges in separate migration files. The final range also catches legacy orders created before the default took effect. These batch sizes belong to this dataset; choose yours from a rehearsal’s transaction time and replication impact.

Ptah’s default file transaction mode commits each file separately. Keep that boundary for the constraint too:

ALTER TABLE public.orders
    ADD CONSTRAINT orders_currency_present CHECK (currency IS NOT NULL) NOT VALID;

ALTER TABLE public.orders VALIDATE CONSTRAINT orders_currency_present;

NOT VALID postpones checking existing rows; it still checks new writes. Validation runs in the next transaction under a weaker lock. Combining both statements into one transaction would retain the stronger lock during the scan.

Apply through validation, including the verified retry:

ptah migrations up \
  --db-url "$DB_URL" --migrations-dir migrations \
  --to-version 1790078820 --verify-sum --allow-dirty \
  --lock-timeout 200ms --statement-timeout 30s

Finish with the validated proof in place

PostgreSQL can use the validated check to establish that no currency is null, avoiding another table scan when setting NOT NULL:

ALTER TABLE public.orders ALTER COLUMN currency SET NOT NULL;
ALTER TABLE public.orders DROP CONSTRAINT orders_currency_present;

Review the pending change with Ptah:

ptah migrations lint --dir migrations --dialect postgres --latest 1

The linter flags the not-null change and removal of a constraint. It does not prove that an earlier migration has validated the replacement invariant. Here NOT NULL takes over before the temporary check is removed. After reviewing that specific removal, apply the one remaining migration:

ptah migrations up \
  --db-url "$DB_URL" --migrations-dir migrations --verify-sum \
  --lock-timeout 200ms --statement-timeout 30s --allow-destructive

Do not combine the statements into one ALTER TABLE: PostgreSQL needs the check to remain present while establishing NOT NULL.

What the running checkout observed

With Ptah running beside the database, the 90-second run targeted 300 transactions per second and completed 26,855 checkout transactions:

Signal	Observed result
Failed or skipped transactions	0
Transactions over the 2-second budget	0
Mean latency, including scheduling delay	7.031 ms
Old-writer orders / new-writer orders	13,538 / 13,317

An earlier run used the laptop’s CLI over an SSH tunnel while other images were compiling on the database host. It completed 35,655 transactions but skipped 191 scheduled arrivals and exceeded the budget on 19 completed transactions. That run failed the latency target. The recorded results preserve both runs; they do not isolate the cause of the difference.

The data checks found all one million original orders, no missing or incorrect currencies, and unchanged order totals. An explicit-null insert now fails. Both application versions still create and read orders successfully.

Check the declared final schema and migration history independently:

ptah schema compare --db-url "$DB_URL" --schema-file schema.sql --exit-code
ptah migrations status --db-url "$DB_URL" --migrations-dir migrations --verify-sum --exit-code

Both exited 0. The tested SQL path needs no application pause, but it does not cover every production query, replica, or traffic spike. Keep the compatible writer behavior in place until the rollout is complete, and use the same request budget in the rehearsal that production must meet.

Example files

← All articles