Postgres after Firebase and Mongo
Six years on Firestore, then two on MongoDB. Eight years of document stores across two companies before Giant Context, and the decision to run this one on Postgres was made before the repository existed.
Both of those stores get plenty right, and the internet is oversupplied with people pretending otherwise. Firestore is cheap at small scale and it scales without ceremony. It is reactive out of the box: a client subscribes to a query, any write anywhere shows up in that subscription, and entire categories of UI plumbing simply do not exist in your codebase. Mongo gives you a real query language and an operational story that holds up. Nobody picks either one because they love JSON trees. They pick them because the fast path is genuinely fast.
Neither is why I left. Both times it was the schema.
The migration directory
A schema change in a document store is a running mechanism. It is not written down anywhere, it cannot be traced after the fact, and it cannot be reversed. You deploy code that writes the new structure, and from that moment the change is simply happening, document by document, for as long as users touch documents. There is no record that it started, no way to ask how far it has gotten, and no way to back out.
By year six, the Firestore collections held every era of that product at once. Every read defended itself against three generations of shape, because nothing anywhere could say when generation two had stopped being written. Then two years on MongoDB, better tooling, same property. That is when I stopped treating it as a Firestore complaint. It is what happens when the schema is never declared, on any engine.
A relational database makes you say what your data is, and makes you say it again, in writing, every time the answer changes. On Giant Context every schema change is a migration script, the scripts run in order, and the migration table records what ran and when. The directory, a few weeks into the project, is already a build log:
Migration scripts, in execution order
packages/api/migrations/ 1734800000000_initial-schema.sql 1734800000001_seed-demo-data.sql 1734886800000_rbac-tables.sql 1734900000000_add-firebase-uid.sql 1734950000000_files-table.sql 1735000000000_multi-tenant-schema.sql 1735010000000_drop-users-role.sql 1735020000000_remove-wildcard-permission.sql ... 77 scripts and countingEvery structural decision the system has made, in execution order, reviewable in any diff, reversible from any point. The fourth file is the old world getting a column in the new one, because identity stayed on Firebase. Here it is, whole:
1734900000000_add-firebase-uid.sql
-- Add firebase_uid column to users table-- Links Firebase Authentication to internal user records
-- Up MigrationALTER TABLE users ADD COLUMN firebase_uid VARCHAR(128) UNIQUE;CREATE INDEX idx_users_firebase_uid ON users(firebase_uid) WHERE firebase_uid IS NOT NULL;
---- Down MigrationDROP INDEX IF EXISTS idx_users_firebase_uid;ALTER TABLE users DROP COLUMN IF EXISTS firebase_uid;The bottom half is the part the document store never offered. Every one of the 77 scripts carries its own down migration. The Firestore version of this change was a deploy you could not take back. This version is eight lines, and the last two are how you back out.
A document store lets you skip declaring your schema's history, and then your schema has a history whether you declared it or not. It is just stored as guesswork instead of scripts, in the defensive branches of every read, and you find out how much of it there is when someone new asks what a field means.
Rebuilding the reactive layer
Leaving Firebase costs you the reactive layer, and the reactive layer was the point. Postgres will not push a changed row into your UI. If you migrate and skip this part, every screen needs a refresh button, and the product feels ten years older.
So the reactive layer got rebuilt on Postgres, and the recipe is small enough to describe completely. Every mutation in the API declares a target, the name of the data domain it touched. Committing a write emits an event over a WebSocket to connected clients, and the event is an instruction rather than an announcement. It tells the frontend what to do, which in most cases is rehydrate a surface:
A realtime notification
[api] Received realtime notification {"event":{"action":"refetch","target":"users", "payload":{"id":"…","operation":"INSERT"}}}On the client, the query cache is organized by those same target names, so handling an event is one call. Invalidate the keys for that target, and the affected queries refetch. Screens showing that data update. Screens that are not showing it do nothing. The surface is connected to the query, the query to the cache, the cache keys to the API schema. One tight chain.
Mutation
A write commits
Event
action, target, payload
WebSocket
Pushed to clients
Invalidate
Cache keys for the target
Refetch
Stale queries re-render
Mutation
A write commits
Event
action, target, payload
WebSocket
Pushed to clients
Invalidate
Cache keys for the target
Refetch
Stale queries re-render
Neither end of the loop is hand-maintained. The client's query hooks, their cache keys, and their invalidation groups are generated from the API's schemas, the same generation step that produces the API client and the form validation. A mutation cannot drift out of agreement with the cache keys it invalidates, because both sides are materialized from one declaration.
The second time I built this
I had already built this once, on MongoDB Atlas.
On Firestore the loop comes free, which is the whole appeal. Atlas does not give it to you, so the answer there was Atlas Triggers. A write fires a trigger, the trigger notifies the backend, the backend pushes over a WebSocket, and the client rehydrates. Postgres is the same shape again with the trigger moved into the write path:
One pattern, three engines
Firestore write → onSnapshot → rehydrate (free)MongoDB Atlas write → Atlas Trigger → backend → WebSocket → rehydratePostgres write → table event → WebSocket → handler → rehydrateThree engines, two companies, one pattern. The database supplies the change signal and everything downstream of the signal is yours, which means it ports. The reactive subscription was never a technology moat. It is a small pattern that one vendor happens to package better than the others, and packaging is worth paying for right up until the point where the schema history starts costing more.
The receipts on how long it took sit in the commit log. The migration tooling landed on December 21st (bb861a3ce). Before that date there was no database to be reactive about. Four days later, on Christmas, the realtime work was already down to edge cases, and the commit that proves it is a fix whose note says exactly where the system stood:
2025-12-25 · 6e07b76c0
fix(portal): reconnect websocket when auth state changes
WebSocket connections were connecting anonymously before Firebase auth initialized, causing user-targeted messages (like locale sync) to fail. Now the WebSocket reconnects when user auth state changes, ensuring authenticated connections receive targeted broadcasts.From no database to a reactive one took days, not the quarter you might expect. Keeping it correct costs nothing after that, because the generation step keeps both ends of the loop in agreement.
What comes back in exchange is everything the document store withheld. Joins, transactions, constraints, a query planner, and the migration directory.
A database a test can build from scratch
There is a second use for that directory that has nothing to do with production, and it is the one I expect to get the most out of.
If the scripts are the complete and ordered definition of the schema, then a database is something a machine can construct from scratch. Integration tests do not need a shared instance sitting somewhere with fixtures everyone is careful not to disturb. A test run can create an empty database, apply every migration in order, load its own data, run against it, and throw the whole thing away.
That buys three things. Tests get a database in a known state every time, so a failure is a real failure rather than residue from whatever ran before. Nothing in the test path touches production, which removes an entire category of accident. And the migrations themselves get exercised on every run, which means the down migrations are tested rather than merely written.
None of that is possible when the schema is implicit. You cannot build a document store from its history, because there is no history to build it from, so the test database ends up being a copy of a real one with all the drift that implies. This part is not wired up yet. But it is available because the migration directory exists, and that is the sort of thing worth knowing when you are choosing a database rather than after.
One database, including the vectors
One more decision rode along from day one. Embeddings live in the same database, via pgvector, as columns on the rows they describe. An AI-heavy product accumulates embeddings the way any product accumulates rows, and the default industry answer is a second, dedicated vector store. The cost of that answer is a synchronization problem you own forever. Every embedded row now exists in two systems with two lifecycles and two failure modes. In one database, semantic search is a WHERE clause away from the relational facts, in the same transaction, the same backup, the same access control. At the scale of a young product there is no performance argument that pays for the second system.
The parts that stayed
Leaving Firestore did not mean leaving Google. Giant Context runs on Google Cloud end to end. The Postgres in this article is Cloud SQL, the file storage is Cloud Storage, and the models are Gemini. What I left was the document store.
Choosing Google again was its own decision with its own reasoning, and it deserves its own article. The short version is that two years running a full production stack on Azure taught me what depth on one cloud is worth, I wanted that depth on Google's side this time, and keeping every service under one roof shortens every path, from billing to IAM to the model APIs the product is built around.
And one piece of Firebase survives the move. Identity Platform will handle auth. That is what add-firebase-uid.sql is doing in the listing above: the new schema keeps a column pointing at the old identity provider, on purpose. Auth was never the problem. It is the part of Firebase with boring, well-defined edges, and ripping it out would have been ideology.
The decision rule
Choose a document store when the product is a guess, when the schema is young, iteration speed is everything, reactivity is load-bearing, and nobody will care about the data's history because most of it will not survive contact with users. A prototype earns Firestore, and it earns Mongo, and I would make that choice again.
Choose relational the moment the schema's history becomes an asset, which is roughly the moment the product is real. The running, untraceable, irreversible schema change is the real bill for the free reactivity, and it comes due years later, for whoever is still there. I paid it twice before I stopped arguing with it.
And the reason it is specifically Postgres, rather than any relational engine, is that it is the one built for the person doing the work. Migrations are a first-class idea rather than a library you bolt on. Deployment is a solved, boring problem with a dozen good answers. The extensions are there when you need them, pgvector included. Nothing about it is trying to be a platform, so nothing about it charges you for one.
And price the reactivity gap at what the commit log says it costs. Days of plumbing, built once, if your cache keys and your API schemas come from the same source of truth.
Building something like this
I'm Jesse. I build platforms end to end, and I'm open to work. If this is the kind of engineering you need, get in touch.