Databases3 min read
SQL vs NoSQL: a decision guide that does not start with 'it depends'
The honest default for most products is PostgreSQL. Here is the specific set of conditions under which it is not.
Model MongoDB documents around how your application reads data. Embed related data that is always read together, is bounded in size, and changes with its parent. Reference data that is large, unbounded, shared across documents, or updated independently. The 16MB document limit and unbounded array growth are the two constraints that force most design decisions.
| Signal | Embed | Reference |
|---|---|---|
| Read together always | Yes | — |
| Bounded size (tens, not thousands) | Yes | — |
| Updated independently | — | Yes |
| Shared by many parents | — | Yes |
| Grows over time indefinitely | — | Yes |
| Needs its own queries and indexes | — | Yes |
// Embed: an order's line items are read with the order and never alone
{ _id: "ord_1", total: 4900, items: [ { sku: "A1", qty: 2, price: 1200 } ] }
// Reference: a product is shared across thousands of orders and updated separately
{ _id: "ord_1", items: [ { productId: "prd_9", qty: 2, priceAtPurchase: 1200 } ] }An array that grows with user activity will eventually make the document huge, slow to update, and finally impossible to save.
Comments on a post, events on a session, messages in a conversation — these look natural to embed and are the most common cause of MongoDB rewrites. Every update rewrites the whole document, indexes on the array grow with it, and at 16MB writes simply fail.
The ESR rule governs compound indexes: Equality fields first, then Sort fields, then Range fields. Getting this order wrong means MongoDB performs an in-memory sort, which fails outright past its memory limit for sorts without an index.
// Query: find open orders for a customer, newest first
db.orders.find({ customerId: 42, status: "open" }).sort({ createdAt: -1 })
// ESR: equality (customerId, status), then sort (createdAt)
db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 })MongoDB supports multi-document ACID transactions on replica sets, but they are more expensive than single-document writes and have time limits. The idiomatic design keeps anything that must change atomically inside one document, where atomicity is free.
The database does not require a schema, but your application has one implicitly. Use JSON Schema validation on collections so the shape is enforced where the data lives rather than in every code path.
When documents genuinely vary in shape, when your access pattern is overwhelmingly by primary key or a single index, or when horizontal sharding is a near-term requirement. For relational data with reporting needs, PostgreSQL is usually the better fit — and it handles JSONB well.
Store an array of references on the side with the smaller, bounded set, or use a dedicated join collection when both sides are large. Duplicating the relationship on both sides invites divergence.
Grouping many small time-series records into one document per time window. It reduces document count and index size dramatically for metrics and event data.
Harshal Patel
Founder & Lead Engineer, ROVQIX
Harshal leads engineering at ROVQIX, where he has shipped production Next.js, Node.js and PostgreSQL systems for startups, SaaS teams and ecommerce brands. He writes about the trade-offs behind architecture decisions rather than the framework of the week.
ROVQIXdesigns and builds production web platforms — Next.js front ends, Node.js APIs and the infrastructure behind them. Tell us what you're building and we'll scope it with you.
The honest default for most products is PostgreSQL. Here is the specific set of conditions under which it is not.
Adding an index is easy. Knowing which one, in which column order, and which existing indexes to delete is the part that changes query times.
You do not have backups. You have restores — and you only know whether you have those if you have done one this quarter.
No spam. Just the occasional case study and craft breakdown.