Choosing a Database: Postgres, MySQL, Mongo, and When Each Fits

Of all the technical choices made at the start of a project, the database is the one people most often get wrong for the least interesting reasons. Someone picks what they used last time, or what a blog post praised, or whatever the loudest engineer prefers. Then the decision hardens. Six months in, the database is woven through everything, and changing it means rewriting a large part of the application.

Because the choice is expensive to reverse, it deserves more thought than it usually gets — but not the kind it usually attracts. The debates online are tribal and abstract. The useful question is narrower and more honest: given the actual shape of your data and how you will actually query it, which tool fits? Here is how I work through that with clients.

Start with the shape of your data, not the brand

"SQL versus NoSQL" is not a contest between competitors. They are answers to different questions, and reaching for brand names first is backwards. Reaching for the shape of your data first is how you get to the right answer. So ask plain questions about the information the application handles.

• Is it highly structured and related? Customers who have orders, orders that have line items, line items that reference products. This is relational data, and relational databases exist precisely for it.
• Do different records need wildly different fields? A catalogue where a book, a sofa, and a subscription share almost no attributes may resist a rigid table.
• How much does consistency matter? If two people act on the same record at the same instant, does a brief disagreement cost you money or trust, or is it harmless?
• What are the read and write patterns? Mostly key lookups, or complex queries that slice the data many ways?

Most business applications — anything with customers, invoices, bookings, inventory, or accounts — are structured, related data where consistency genuinely matters. That fact points hard in one direction before you have named a single product.

Consider what "related" really means in practice. In a relational store, the connection between an order and its customer is a first-class thing the database enforces and can query efficiently.

-- The relationship is declared once and the engine guarantees it.
create table customer (
  id    bigint generated always as identity primary key,
  email text not null unique
);

create table "order" (
  id          bigint generated always as identity primary key,
  customer_id bigint not null references customer(id),
  total_cents integer not null check (total_cents >= 0),
  placed_at   timestamptz not null default now()
);

-- "Every customer with their lifetime spend" is one query, computed server-side.
select c.email, count(o.id) as orders, coalesce(sum(o.total_cents), 0) as spend
from customer c
left join "order" o on o.customer_id = c.id
group by c.id;

That join and sum run next to the data, over indexes, returning only the answer. Hold the same relationship in a document store and you are often fetching both collections and stitching them together in application code — reinventing, more slowly, what the database would have done for free.

PostgreSQL: the default that's hard to regret

For most projects my honest default is PostgreSQL, and it is worth being clear why. Postgres is a relational database that has quietly absorbed the good ideas from everywhere else. It enforces structure and relationships, handles transactions properly so your data stays consistent, and is dependable under load.

What makes it more than "just relational" is that it did not stay in the box. When part of your data is genuinely unstructured, jsonb lets you store and index a document inside a row — without leaving the database that handles the structured 90%.

create table product (
  id         bigint generated always as identity primary key,
  sku        text not null unique,
  price_cents integer not null,
  attributes jsonb not null default '{}'   -- the shape that varies per product type
);

-- Index into the document so these queries use an index, not a scan.
create index on product using gin (attributes);

-- Structured columns and flexible attributes queried together, one engine.
select sku, price_cents
from product
where price_cents < 5000
  and attributes @> '{"colour": "blue", "in_stock": true}';

It also has capable full-text search — enough that many applications never need a separate search engine — and a deep ecosystem of extensions for when a plain relational store is not enough. The practical upshot: Postgres lets you start with structure, which is what most applications actually want, while keeping an escape hatch for the parts that are genuinely flexible. You rarely regret it, which is a rare and underrated property in a technical decision.

MySQL: a perfectly good relational choice

MySQL sits in the same relational family, close to Postgres, and I want to be fair rather than tribal. It is mature, extremely widely deployed, and for a large class of straightforward applications it does the job without drama. If your team already knows it well, or your hosting and tooling are built around it, that familiarity is a real and legitimate advantage that outweighs abstract feature comparisons.

I lean towards Postgres when an application is likely to grow into more demanding territory — complex queries, heavier concurrency, the flexible-data features above, or stricter correctness guarantees around unusual data. Postgres has historically been the more rigorous of the two there. But for a conventional web application with conventional needs, MySQL is not a mistake, and anyone who calls it an embarrassing choice is arguing about identity, not engineering.

MongoDB: right for the right shape

MongoDB, and document databases generally, attract a lot of undeserved scorn — usually from people who watched someone use one for data that was obviously relational. Used for the wrong shape it causes real pain. Used for the right shape it is a clean fit.

Document databases store records as flexible documents rather than rows in a fixed table. That is a genuine advantage when your data is naturally document-shaped: records that are largely self-contained, vary a lot in their fields, and are fetched and updated as a whole rather than joined across many tables. Content items, event payloads, catalogues with wildly varying attributes, and certain telemetry sit comfortably in a document model.

// A self-contained document: everything about this article travels together.
await articles.insertOne({
  _id: "a-fast-marketing-site",
  title: "Making a Marketing Site Genuinely Fast",
  author: { name: "Pratima", role: "Founder" },   // embedded, not joined
  tags: ["performance", "nextjs"],
  blocks: [                                        // shape varies per article
    { type: "paragraph", text: "..." },
    { type: "code", lang: "ts", source: "..." },
  ],
  publishedAt: new Date(),
});

// Fetched whole, in one round-trip, with no joins to assemble.
const article = await articles.findOne({ _id: "a-fast-marketing-site" });

The trouble arrives when people use a document store for data that is actually relational — customers and orders and invoices with many relationships — and then find themselves manually stitching those relationships together in application code, reinventing, badly, the joins a relational database gives for free. That failure is not MongoDB's; it is a mismatch between the tool and the shape of the data.

A decision, not a philosophy

When we help a client choose, we are really answering a handful of concrete questions rather than winning a philosophical argument:

1. 01Is the data mostly structured and related, or mostly self-contained documents?
2. 02How much does strict consistency matter for this application?
3. 03What do the real query patterns look like — key lookups, or complex multi-way queries?
4. 04What does the team already know, and what can they realistically operate well?
5. 05Where is this likely to be in two years, and does the choice leave room to grow?

Don't over-engineer the decision

There is a failure mode at the ambitious end too: reaching for several specialised databases before there is any need. A relational store for the core, a document store for one thing, a separate search engine, a cache, a queue — each justified in isolation, together a sprawling system a small team cannot realistically operate.

Most applications are well served for a long time by a single well-chosen database. Postgres in particular can carry a surprising load and cover search, flexible data, and queuing needs that people assume require separate tools. Add specialised stores when you have hit a real limit and can name it — not in anticipation of scale you do not yet have. Every additional database is another thing to run, secure, back up, and understand, and that ongoing cost is easy to ignore at the start and painful to carry later.

The honest bottom line

If you want a default that is very hard to regret, choose PostgreSQL and only move off it when you have a concrete reason. If your team lives in MySQL and your needs are conventional, stay there with a clear conscience. If your data is genuinely document-shaped, a document database is the right tool and not a compromise. And whatever you choose, prefer the option your team can actually operate well over the one that wins arguments on the internet.

The database is one of the few decisions that is genuinely hard to undo — which is exactly why it rewards a few hours of honest thought about your real data, and punishes an afternoon of picking by fashion.