Sefism is free for learners. We are here to help you grow, one step at a time.Join for free
Sefism.
Cloud & DevOps

Zero-Downtime PostgreSQL Migration Checker with Lock Testing in CI

A CI tool that catches database migrations likely to cause downtime before they reach production: it lints PostgreSQL migration SQL, then replays it against a shadow database under simulated traffic and reports the locks taken, how long they were held and which queries were blocked. An advanced cloud and DevOps final year project.

Topics
Difficulty

Advanced

Needs a team, real planning, and a supervisor who knows the area.
Effort

2 semesters · 2 to 3 students

Deliverables

6 to ship

3 optional extras

Suggested stack

Go or RustPostgreSQLpg_query parser bindingsDockerGitHub Actionspgbench or k6 for load
A suggestion, not a requirement. Swap anything for what you already know.

What you should ship

  • A static checker that parses migrations with the real PostgreSQL parser and flags risky operations (non-concurrent index builds, table rewrites, unvalidated constraints, missing lock_timeout) using version-aware rules.
  • A shadow runner that starts the target PostgreSQL version in Docker, loads a schema plus synthetic or sampled data at a chosen size, and applies the migration while a traffic generator runs typical queries.
  • Lock telemetry sampled from pg_locks and pg_stat_activity during the run, turned into a timeline of lock modes held, wait queues and blocked queries.
  • A pull request report combining static findings and measured impact, with a pass, warn or fail verdict and a suggested safer rewrite for each finding.
  • Adapters for two migration frameworks (for example plain SQL files and one ORM such as Prisma, Django or Rails) that extract the SQL they will run.
  • An evaluation on a corpus of real migrations from open source projects, reporting precision of warnings and the accuracy of measured lock times against production-like runs.

If you have time left

  • Automatic rewrite suggestions for common patterns, such as splitting an ADD COLUMN NOT NULL into add nullable, backfill in batches, then validate.
  • Table size awareness: read row counts from a production statistics snapshot so the checker scales its warnings to real table sizes.
  • A MySQL mode using the same report format, compared against the approach of online schema change tools.

The problem: the migration that took the site down

Schema changes are one of the most common causes of self-inflicted outages. A developer adds an index on a busy table without CONCURRENTLY and writes block until it finishes. Someone changes a column type and PostgreSQL rewrites the whole table under an exclusive lock. Even a fast ALTER TABLE can cause an outage: it waits for its lock behind a long-running report query, and every other query on that table queues behind it. On a developer laptop with ten rows, all of these run in milliseconds, so code review rarely catches them.

Stripe has written about running online migrations at scale with careful multi-step processes, and GitHub built gh-ost for online MySQL schema changes. Small teams have the same risk with none of that tooling, and usually no database specialist to review migrations.

What already exists and the gap

  • Squawk is a fast open source linter for PostgreSQL migrations with rules for common lock hazards; it is static and does not know table sizes or measure real impact.
  • strong_migrations catches unsafe migrations in Rails, and only in Rails.
  • pgroll from Xata performs reversible, zero-downtime migrations by serving old and new schema versions at once, which changes how you migrate rather than checking what you wrote.
  • Atlas offers migration linting and schema management, with some analysis features in its paid tiers.

The gap is measurement: a tool that combines static rules with an actual rehearsal on a realistic database under load, and reports the locks and blocked queries a migration really causes, in the pull request, for any framework.

What you build

  • Parser and rules: use the PostgreSQL parser itself (through pg_query bindings) so every statement is understood exactly. Rules are version-aware, because behaviour changed across releases; for example, adding a column with a constant default no longer rewrites the table in modern versions.
  • Shadow runner: a Docker PostgreSQL matching production's major version, a data generator that fills tables to a configured size, and a traffic generator running a representative query mix.
  • Lock observer: samples lock and activity views at short intervals during the migration and reconstructs who held what, who waited and for how long.
  • Report: a PR comment with findings, a lock timeline, blocked query counts and a suggested safer version.
  • Framework adapters: get the exact SQL from plain files and from one ORM.

The technically interesting part

The hard part is making the rehearsal meaningful. Lock duration depends on table size, index count and concurrent traffic, so you must generate data and load that resemble production closely enough to rank migrations correctly, without copying production data. You also need to observe locks accurately: sampling too slowly misses short waits, sampling too fast perturbs the database. Finally, you must connect measurements back to statements and to advice. Show evaluators a migration that the linter alone would pass but the rehearsal catches, and the reverse, a scary-looking one that turns out harmless.

How to evaluate it

  • Corpus study: collect at least 100 real migrations from open source projects. Hand-label a subset as safe or risky and report precision and recall for static rules alone and with rehearsal.
  • Measurement accuracy: for ten migrations, compare lock times measured in the shadow runner with runs on a larger, production-like database; report the ranking agreement.
  • Cost of the check: CI time added per migration at different data sizes.
  • Developer usefulness: show reports to five backend developers and ask them to fix the flagged migrations; record whether the suggestions helped.

Suggested plan

  1. Semester 1, weeks 1 to 4: study PostgreSQL lock modes and migration hazards; build the parser layer and first ten rules.
  2. Weeks 5 to 9: shadow runner with data and traffic generation.
  3. Weeks 10 to 14: lock observer and timeline reconstruction.
  4. Semester 2, weeks 1 to 5: PR reports, suggestions and framework adapters.
  5. Weeks 6 to 10: corpus collection, labelling and experiments.
  6. Weeks 11 to 14: developer study and write-up.

Path to a product

The first customer is a product company with a PostgreSQL database large enough that migrations have bitten them at least once, and no dedicated DBA. Release the linter as open source and sell the rehearsal as a hosted CI service priced per repository, since it needs compute. Consultancies that manage databases for clients are a second market.

Scope warning

Do not build a migration framework, an automatic migration executor or a schema diff tool. Support PostgreSQL only in the core project. Do not try to reproduce production traffic exactly; a representative, documented query mix is enough.

Learn what you need on Sefism

Get help with this idea

Designing the labelled migration corpus is the step most teams underestimate. A mentor can help you set the scope, pick a stack you can finish with, and design an evaluation your examiners will respect. The build is yours; the guidance is on scope, stack and evaluation. New here? Create a free account first.

Ideas and guidance, not finished projects

These are project ideas and scoping guidance, published free for students to use as a starting point. I do not build, write, or sell final-year projects, and I do not complete coursework for anyone. Take an idea, make it yours, and build it.

Not sure this one fits you?

I help students pick an idea that matches their skills and their deadline, then scope it down until it is finishable. Guidance only, never done for you.

Discuss this idea
Work with me

Stuck on something specific?

Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.

Book a free sessionSee all services

Follow along

New writing, resources and project ideas land here first.