Design concept by Krishna Bhupathi. Backfill is not a real product.
Read the case study
Backfill reads the migration in every pull request, works out which Postgres locks it takes and for how long on your real table sizes, and comments with a safe rewrite.
For Postgres 12 and newer. GitHub at launch.
Three steps on every pull request that touches a migration.
Step 1
Read the migration
Finds new migration files in the pull request and turns them into the exact SQL your framework will run.
Step 2
Measure it against your tables
Looks up the lock each statement takes and estimates how long it holds it, using row counts and table sizes from your database statistics.
Step 3
Comment with a safer way
Posts one comment on the pull request: what blocks, for how long, and a rewrite that doesn’t.
Backfill commented
Blocks reads and writes
Line 2 adds a column with a volatile default, so Postgres rewrites all 120M rows of orders while holding an ACCESS EXCLUSIVE lock. Estimated 32 to 44 minutes.
Split it into three migrations instead. None of them holds a lock for more than a moment.
-- 1. Metadata only: nullable column, default for new rows ALTER TABLE orders ADD COLUMN public_id uuid; ALTER TABLE orders ALTER COLUMN public_id SET DEFAULT gen_random_uuid(); -- 2. Backfill in batches of 10,000 (row locks only) UPDATE orders SET public_id = gen_random_uuid() WHERE id IN (SELECT id FROM orders WHERE public_id IS NULL LIMIT 10000); -- 3. Validate without blocking writes, then enforce ALTER TABLE orders ADD CONSTRAINT public_id_nn CHECK (public_id IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT public_id_nn; ALTER TABLE orders ALTER COLUMN public_id SET NOT NULL;
The migration was one line. It passed review, passed CI and ran in two seconds on staging, which had 4,000 orders.
The story that inspired Backfill. It’s a composite of incidents engineering teams have written about, not one company’s outage.
16:52
Deploy starts.
The migration adds a column with a generated default to orders.
16:52
The table is locked.
Postgres starts rewriting 120 million rows and holds an ACCESS EXCLUSIVE lock until it finishes.
16:54
Checkout stops.
Every request that reads or writes an order waits. Connection pools fill up.
17:05
Someone suggests cancelling.
Nobody is sure what cancelling a half-finished rewrite will do, so the team waits.
17:32
The rewrite finishes.
The lock is released and checkout recovers.
Checkout down
40 min
The schema changes that look harmless in review and take locks in production.
Add a column with a volatile default, like gen_random_uuid()
ACCESS EXCLUSIVE
plus a full table rewrite
All reads and writes
Add it nullable, set the default for new rows, backfill in batches.
Change a column type, like integer to bigint
ACCESS EXCLUSIVE
plus a rewrite for most type changes
All reads and writes
A new column kept in sync by a trigger, a batched backfill, then a swap.
SET NOT NULL on an existing column
ACCESS EXCLUSIVE
while it scans every row
All reads and writes
A CHECK … NOT VALID constraint, then VALIDATE, then SET NOT NULL, which skips the scan.
CREATE INDEX
SHARE
for the whole build
Inserts, updates and deletes
CREATE INDEX CONCURRENTLY, in its own migration outside a transaction.
Add a foreign key
SHARE ROW EXCLUSIVE
on both tables while it validates
Writes on both tables
Add it NOT VALID, then VALIDATE CONSTRAINT separately.
Any lock without a timeout
Waits behind long-running queries
Everything queued behind it, even for a fast change
SET lock_timeout = '3s' and retry, so a stuck migration fails fast instead.
Lock types are exact, taken from the Postgres documentation. Durations are estimates and shown as a range.
Backfill reads the SQL your framework generates, so the check matches what will actually run.
Rails
At launch
Django
At launch
Alembic
At launch
Plain SQL
At launch
Prisma
Planned
Flyway
Planned
Priced per repository, not per engineer.
Open source
$0
public repositories
Lock checks on every pull request
Suggested rewrites
Join the waitlist
Team
$39
a month, up to 10 private repositories
Duration estimates on your real table sizes
Required status check
Slack alert for blocking changes
Join the waitlist
Does Backfill connect to my production database?
+
Only if you want duration estimates, and then only to a read replica through a role that can read table statistics: row estimates and table sizes. It never reads your rows. You can also export the statistics from CI instead.
How accurate are the time estimates?
+
The lock type is exact. The duration is an estimate from table size and how fast your past migrations ran, so it’s shown as a range. It’s meant to tell two seconds apart from forty minutes.
Will it block my merges?
+
Only if you make the Backfill check required in your branch rules. Otherwise it comments and you decide.
Which Postgres versions?
+
12 and newer. Some advice changes between versions. For example, adding a column with a constant default stopped rewriting the table in Postgres 11, and Backfill knows which version you run.
Join the waitlist. We’re inviting teams that run Postgres in production first.
Project
Backfill is a design concept by Krishna Bhupathi. It is not a real product or company.
Drawn by
Krishna Bhupathi
Sheet
1 of 1