Why we picked Postgres over Mongo

The Postgres vs Mongo argument is older than this blog. We're going to make it again because the answer for SwyDex was less obvious than we expected.

Mongo's tooling for our data shape is fine. Wallet documents, transaction events, user records — all of them are roughly tree-shaped JSON. Sharding on tenant_id is straightforward.

What we lost in Mongo: foreign keys, transactions across documents, and easy multi-row reporting queries. The first two are recoverable with discipline. The third is the killer.

Custodial crypto means a lot of accounting queries: “sum all confirmed deposits for tenant X this month, broken down by chain” or “reconcile our gas vault outflows against on-chain settlement.” In Postgres these are 3-line SQL. In Mongo they're aggregation pipelines or — more often — lots of application code.

The other reason: row-level security. Mongo doesn't have an equivalent. RLS is a defense-in-depth tool we don't want to give up.

We use Postgres 16, JSONB columns where we want flexibility, and Prisma as the client. The flexibility we'd have wanted from Mongo is satisfied by JSONB on a per-row basis without giving up the relational guarantees we depend on.