SQL or NoSQL: choosing a data layer you won't regret

"Should we use Postgres or Mongo?" is the wrong first question, and it gets asked at the start of nearly every project. The right one is what the application actually does with the data, in what shapes, at what frequency. Answer that and the database mostly picks itself. What follows is the framework we use on client work, including the parts where the answer is not Postgres.

Start from access patterns

Before anyone draws an ERD or a document schema, write down the ten queries the product runs most often. Real ones, in English, with the filters and the sort order:

• The 20 most recent orders for one customer, with line items and the product name on each line.
• Revenue summed by month across all tenants for the admin dashboard.
• One workspace's settings blob, fetched on every page load.
• All documents where tags contains "urgent" and status is open, for the current tenant.
• Append a telemetry event, roughly 400 per second at peak, never updated after write.

Now look at the list. Do things join? Do you aggregate across entities? Is one hot record read and rewritten as a whole unit? Those properties determine the fit. A team that skips this step picks the database it used last time and finds the mismatch six months in, when the reporting requirements land.

Why Postgres is our default

For the typical product we build, a multi-tenant SaaS or a marketplace, Postgres wins on breadth. Foreign keys and check constraints keep data out of shapes your code doesn't handle. Real transactions mean "create the order, decrement the stock, write the ledger entry" either happens completely or not at all, with no compensating-transaction machinery in application code.

Joins matter more than people expect. Every product grows a screen that combines three entities, and every analytics request is a join. Denormalizing to avoid joins is a fine optimization once you know the access pattern; doing it up front is a bet you usually lose.

The flexible-document argument is largely handled by jsonb: schemaless storage per row, indexed, queryable, in the same transaction as your relational data.

create table listings (
  id          bigint generated always as identity primary key,
  tenant_id   uuid not null references tenants(id),
  status      text not null check (status in ('draft','open','closed')),
  attrs       jsonb not null default '{}'::jsonb,
  -- pulled out of the blob so it indexes and sorts normally
  price_cents int generated always as ((attrs->>'price_cents')::int) stored,
  created_at  timestamptz not null default now()
);

-- jsonb_path_ops: smaller and faster than the default opclass
-- when you only ever use containment (@>)
create index listings_attrs_gin on listings using gin (attrs jsonb_path_ops);

-- partial index: 90% of queries only touch open listings
create index listings_open_recent on listings (tenant_id, created_at desc)
  where status = 'open';

select id, attrs->>'title' as title, price_cents
from listings
where tenant_id = $1
  and status = 'open'
  and attrs @> '{"features": ["parking"], "furnished": true}'
order by created_at desc
limit 20;

That containment query runs off the GIN index. On a 4M-row listings table we measured p95 at 34ms with the index and just over 2 seconds without it.

Postgres also ships full-text search with tsvector, native arrays, range types, and LISTEN/NOTIFY for lightweight pub/sub. None of these beat a dedicated tool. All of them are good enough to push the "we need a second database" conversation out by a year or two, which on a small team is worth a great deal.

When a document store is the right answer

We reach for Mongo (or a DynamoDB-shaped store) when the access patterns genuinely say so:

• Aggregates read and written whole. A CMS page, a form definition, a game save. You always want the entire tree, you never query inside it from three directions, and there is no cross-document integrity to enforce.
• High-volume self-contained writes. Telemetry, audit trails, webhook payloads. Append-only, no joins, retention by TTL index.
• Schema that genuinely varies per tenant. If tenant A's "patient record" has 40 fields and tenant B's has 300 under different names, the relational model becomes an EAV table, and EAV in Postgres is worse than documents in Mongo.
• Real horizontal scale. Sharding when a single writer is no longer plausible. Be honest here: most apps we see never pass 200GB or 5k writes/sec, and a well-tuned single Postgres instance handles that without complaint.

Consistency, and what you give up

Mongo has supported multi-document transactions since 4.0, and they work. They also cost: locks held across shards, a default 60-second limit, and throughput under contention noticeably below single-document writes. If you find yourself wrapping most writes in a transaction, the data was relational and you modeled it as documents.

Secondary reads are eventually consistent. Read your own write from a secondary and you may not see it. Fine for a dashboard, not fine for "did my payment go through". Set read concern deliberately rather than inheriting the default.

The phrase to distrust is "we'll handle consistency in application code". It means the invariant lives in whichever service happened to write last, enforced by whoever remembered. That is where orphaned rows and double-decremented inventory come from.

Running both without regret

Polyglot persistence works under one rule: one system of record per entity. Postgres holds the truth. Derived stores get fed from it, via an outbox table written in the same transaction as the business change and relayed by a worker (or by Debezium reading the WAL if you want proper CDC):

create table outbox (
  id           bigint generated always as identity primary key,
  aggregate    text        not null,   -- 'listing', 'order'
  aggregate_id text        not null,
  event_type   text        not null,
  payload      jsonb       not null,
  created_at   timestamptz not null default now(),
  published_at timestamptz
);

create index outbox_unpublished on outbox (id) where published_at is null;

The relay reads unpublished rows in id order and pushes to OpenSearch, a Mongo read model, or a Redis cache. Derived stores can be rebuilt from scratch, so a bug in the projection is an afternoon, not an incident.

The pattern that fails is two systems of record for the same entity: user profiles written to both Postgres and Mongo by different services. They diverge within weeks, and there is no principled way to decide which one is right.

The real cost of migrating later

The schema is the cheap part; a translation script for a mid-size app is a few days. The expensive parts:

• Every query and ORM call rewritten, with the behavior differences you only find in production.
• Reporting and BI, usually written straight against SQL by someone outside engineering.
• The analytics pipeline and anything downstream of it.
• The team's fluency. People debug well in the system they know.

For a mid-size app, roughly 60 tables and 250k lines, budget 8 to 14 engineer-weeks plus a period of dual writes. Not fatal, but it is a quarter spent shipping nothing new.

A checklist a stakeholder can run

1. Does the data have relationships you'll report across? Postgres.
2. Do money, inventory, or bookings have to be exactly right? Postgres.
3. Is the main object a self-contained document, read and written whole? A document store is reasonable.
4. Are writes above roughly 10k/sec sustained today, not in a slide? Then scale-out matters.
5. Does the team already run one of these well? Weight that heavily.
6. Still unsure? Postgres with jsonb, revisit at real load.

How we work

On new builds we default to Postgres and say so in the first architecture note, with the access-pattern list attached so the client sees the reasoning rather than the conclusion. Where a document store fits, usually telemetry, per-tenant custom forms, or a cached read model, we add it as a derived store fed by an outbox and keep Postgres as the source of truth. On inherited codebases we don't migrate for aesthetics. We measure the queries that hurt, fix those, and propose a data layer change only when the numbers justify the quarter it costs.