Moving a 300-field document to PostgreSQL, live

Context

There was one collection that everything about a clip lived in. It was the output of ingesting a stream, so it held the clips, and then it held everything that had ever been attached to a clip. Its name referenced a company that had nothing to do with what it stored by the time I arrived. That tells you how old it was: seven or eight years, most of that spent growing.

It grew because nothing stopped it. Mongo doesn’t make you write a migration to add a field, so when product wanted a new one, adding it to the document was the shortest path, and the shortest path got taken every time for years. By the time I looked at it, the document had a few hundred fields.

Two things followed from that.

Queries got slow. The interesting reads needed aggregation pipelines, which is Mongo’s way of doing what a join does, and they were doing it over a document carrying far more than the query needed.

The second one mattered more. Anything new we wanted to attach to a clip had two options: add it to the document and make the problem worse, or build it somewhere completely disconnected. Both choices made the eventual cleanup harder than it had been the day before. My résumé compresses this to “it blocked shipping new clip-variant types”, which is true but flattens it. It wasn’t that we couldn’t ship. It was that every shipment raised the cost of ever fixing this.

Constraints

No downtime, and no cutover date. This document was the live product. Anything with a flag day was disqualified.

The codebase was the real obstacle, not the data. Modeling clip variants in PostgreSQL is not hard. Untangling seven years of code that reads that document is. That work was still going when I left.

Clips stay in Mongo. They live in a different service with a different database. Whatever I built had to reference them across that boundary rather than pull them along.

Design

Inside the document was a nested structure holding edited videos. A collection inside a collection, effectively, expressed as JSON because Mongo let it be. That nested piece is what came out.

In PostgreSQL it became normalized tables keyed by a clip ID, which is a reference across the service boundary rather than a foreign key. Each row carries a content type and, depending on the variant, its own typed fields: auto-flip (the same video re-cut to a different aspect ratio, 9:16 and friends), subtitles, dubbing.

Replication rides the event stream that already existed.

flowchart TD
  W["Write to clip document"] --> M[("MongoDB<br>legacy collection")]
  M -->|"change event"| SNS{{"SNS"}}
  SNS --> C["Replication consumer"]
  C -->|"create / update / soft delete"| PG[("PostgreSQL<br>normalized variants")]
  BF["Backfill job<br>(Go, EC2)"] --> PG
  BF --> AUD[("Per-clip audit")]
  PG --> NEW["Migrated endpoints"]
  M --> OLD["Endpoints not moved yet"]

Mongo stays the writer. Postgres follows it. Nothing had to be switched over on a date.

Mongo keeps doing exactly what it did. We were already publishing events to SNS on those writes, so I subscribed to that stream and let a consumer create, update, or soft-delete the corresponding Postgres rows. Nothing is ever hard-deleted, which meant a bad replication decision was recoverable rather than final.

Lag sat under 100ms at p95. I measured it from the logs we were already shipping, which I pulled into a notebook and charted rather than eyeballing.

Existing clips came over through a separate backfill: a Go job running on EC2, parallelized, writing a per-clip audit record so I could prove every clip had actually made it rather than assume it.

Tradeoffs

Replicate off events over a big-bang migration. A flag day on the live product was never a real option, and a migration you can run for months is one you can also stop.

Mongo first, Postgres following, over writing to Postgres first. Writing Postgres-first is the cleaner end state, and it was the wrong first step. It would have required refactoring the endpoints before any data moved, and those endpoints are the seven years of code that made this hard in the first place. Shadowing the data into Postgres first meant the cleanup could happen gradually, endpoint by endpoint, with the new store already correct and waiting.

Soft delete over hard delete. During a migration, the ability to be wrong and recover is worth more than the storage.

What I got wrong. Two things.

The backfill job was my first real go at concurrency in Go, and parallelizing it to finish in reasonable time cost me several rounds of bugs before it was right.

The bigger one: I built against the schema I had watched for two years and assumed that was the schema. It wasn’t. Data older than that carried shapes nobody supported anymore, written by code that no longer existed, and the backfill started failing on them. That turned into a product decision rather than an engineering one. Almost nobody reaches for two-year-old clips, so we migrated what we could of the old data and accepted that the long tail would be imperfect rather than delaying everything to chase it.

Outcome

No outage. The replication path has been running in production since November 2025. On-call knew the work was happening and came to me directly the few times something looked off, which is the version of this that goes well.

It is also not finished, and I’d rather say that than imply otherwise. When I left, some endpoints read from PostgreSQL and some still read from Mongo. The data path was done. The codebase catching up to it is the long tail, and it was always going to be.