Why we picked Postgres over a NoSQL ledger
When we wrote the OneAce stock ledger, the obvious-sounding choice was an append-only document store. We picked Postgres instead. Here's the reasoning and the tradeoff.
An inventory ledger is an append-only stream of movements: receipts, sales, transfers, adjustments. Read the word 'stream' and the modern instinct is to reach for a document database — DynamoDB, Mongo, a CQRS read model layered on top. We tried that on paper, modeled the read patterns, and ended up writing the production system in Postgres instead. Two years in, the choice has paid back.
The read patterns that decided it
Inventory looks like a write-heavy stream, but the actual workload is dominated by reads with arbitrary filters: 'on-hand by (item, warehouse, bin)', 'movements in the last 24h for these 12 SKUs', 'cost basis weighted by receipt order for FIFO valuation'. Every one of those is a join, an aggregate, or a window function. In Postgres they're a single query plan. In a document store they're either a denormalized projection you have to maintain, or a Lambda fan-out that hopes nothing changes mid-aggregation.
Transactional integrity, for free
A stock transfer is two reciprocal movements that must succeed together. Postgres gives us that in eight characters — BEGIN; … COMMIT;. Doing it on a NoSQL ledger means either a saga, a compensating transaction, or eventual consistency that confuses operators when their count is briefly wrong. The 'cost' of Postgres here is one BEGIN. The cost of avoiding it is a 200-line saga module that needs its own test suite.
What we gave up
Horizontal write scale at extreme volume. If OneAce processed a million movements per second we'd be in a different conversation. At SMB warehouse volume — a busy 3PL clears maybe 50 events per second across all customers — a single well-tuned Postgres instance has three orders of magnitude of headroom. We'll revisit when the metrics force it. Building for that scale on day one would have shipped a worse product on day three.
Where Postgres earns its keep
- Row-level security: per-organization isolation enforced at the database, not the app.
- JSONB columns for the custom-fields feature — flexible schema where we need it, strict columns where we don't.
- Generated columns + partial indexes for the 'low-stock items' query, which runs on every dashboard load.
- pg_dump as a disaster-recovery tool that every senior engineer can already operate.
The lesson isn't 'always pick Postgres'. It's that the rhyming pattern between your workload and a tool's marketing pitch isn't the whole story. The boring answer wins more often than the architecture-diagram answer suggests.