SQL or NoSQL: choosing a database — a step-by-step guide

SQL or NoSQL: choosing a database

I've watched teams pick a database because a conference talk made it sound magical, then spend months fighting a poor fit. The real decision isn't trendy—it's boring, practical, and rooted in how your application actually reads and writes data. Let me walk you through the framework I use after 5+ years building systems that had to handle scale and change.

Start with access patterns, not the name

The moment someone asks "should we use SQL or NoSQL," I stop them. That's the wrong question. The right one is: "what does our code need to ask the database?"

I think about access patterns in two dimensions: structure and consistency. Structure tells you how your data relates. Consistency tells you when readers see what writers just wrote.

Here's the practical breakdown:

Notice I didn't say "use NoSQL if you have big data." Data size alone is meaningless. I've run 200 million rows in PostgreSQL faster than a poorly indexed MongoDB instance with 1 million. The access pattern decides the tool.

PostgreSQL covers far more than you think

I used to reach for three databases per project out of habit. Then I spent a week really learning PostgreSQL's depth, and honestly, I use it as my primary database for 80% of new systems now. That's not dogmatism—it's economics. Fewer moving parts means fewer failure modes.

Here's what PostgreSQL can do that surprises people:

JSONB for flexible schemas. Your product catalog has variants with different attributes? Store them as JSONB. You get the flexibility of a document database without surrendering relational guarantees.

-- Product with flexible variant data
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  variants JSONB NOT NULL,
  created_at TIMESTAMP DEFAULT now()
);

-- Query it like a document store
SELECT name, variants->>'color' as color
FROM products
WHERE variants @> '{"size": "M"}';

Full-text search built in. No need for Elasticsearch just to search product names and descriptions. PostgreSQL's tsvector and tsquery handle it, and it integrates with your transactions.

-- Full-text index on product descriptions
CREATE INDEX products_search ON products 
USING GIN (to_tsvector('english', description));

-- Query
SELECT * FROM products 
WHERE to_tsvector('english', description) @@ 
      plainto_tsquery('english', 'wireless headphones');

Transactions across multiple tables. Update inventory, insert order, log the event—all atomic. One write fails, all roll back. MongoDB and DynamoDB have transactions now, but they're still not the default mental model. In PostgreSQL, ACID is the assumption.

-- Start a transaction
BEGIN;

UPDATE inventory SET quantity = quantity - 1 
WHERE product_id = 42 AND quantity > 0;

INSERT INTO orders (user_id, product_id, quantity) 
VALUES (100, 42, 1);

COMMIT;
-- If either fails, both roll back

The reason I reach for PostgreSQL first isn't that it's magic. It's that operations are simpler. One database to version, backup, monitor, and tune. One connection pool. One set of permissions to manage. That simplicity compounds over years.

Polyglot persistence: add a second database when you have a real reason

That said, I've definitely had moments where a second database was worth it. The key word is real.

Real reasons I've had:

• Redis for sessions and caching. User sessions need microsecond lookups and TTL expiry. PostgreSQL can do it, but Redis is built for it. After the 100th millisecond saved per request, it compounds.
• DynamoDB for high-volume event streams. In a ride-hailing system I worked on, driver location updates came in millions per minute. Inserting each one into PostgreSQL would choke. We streamed to DynamoDB, aggregated every 5 seconds, then moved the summary to Postgres.
• Elasticsearch for user-facing search. Not because PostgreSQL full-text is bad, but because product teams wanted typo-tolerance, facets, and suggestions. Elasticsearch's ecosystem won.

Fake reasons I've seen (and avoided):

• "NoSQL scales better." Scaling is about queries, not the database type. A bad query in MongoDB is still a bad query.
• "We might need to change schema." That's premature optimization. Change schema in PostgreSQL when you know what you're changing to. It's not that painful.
• "It's more trendy." Seriously, I've heard this. Trends end. Your data doesn't.

My rule: introduce a second database only when a specific access pattern is slow and you've measured it. Not when you're starting. Not when "we might need to." When it actually hurts.

Migrations cost more than the initial choice

Here's the uncomfortable truth that changes how I think about this decision: moving a system from one database to another is exponentially harder than choosing the right one upfront.

I've been part of one migration from MongoDB to PostgreSQL. It took weeks. Not just the data move—that's the easy part. It was rewriting queries, handling the schema design, coordinating deployments across services, validating consistency at scale, then running both systems in parallel to catch edge cases.

The lesson: spend a day thinking about your access patterns now. Spend three months unsticking a bad database choice later.

This is why I ask these questions before I touch the schema:

• Do we have transactions that span multiple entities? (SQL wins)
• Are we doing complex aggregations or JOINs? (SQL)
• Do we need sub-millisecond reads on simple keys? (Redis)
• Does our schema change frequently across different records? (NoSQL documents)
• Are we building a search feature users interact with? (Elasticsearch)
• Do we need time-series analytics? (ClickHouse, TimescaleDB)

Answer those honestly, and the database choice becomes obvious. It's not exciting, but it's right.

Common mistakes

• Picking based on a tutorial. You built a cool app in MongoDB. That doesn't mean your production system needs it. Your tutorial had 100 documents. Your system has 10 million.
• Assuming one database can't handle your scale. PostgreSQL handles Wikipedia, Stripe, and Instagram's metadata. If you're at scale where it actually breaks, you've earned the complexity of sharding or polyglot persistence.
• Ignoring operational cost. Running three databases means three monitoring setups, three backup strategies, three on-call pages. I've seen teams pick NoSQL and then spend more time operating it than they saved on queries.
• Treating schema design as optional. A bad schema in PostgreSQL will kill you just as fast as a bad schema in MongoDB. The database is a tool. Thoughtfulness is required regardless.
• Not measuring access patterns before deciding. Build a prototype. Profile it. Then choose. You'll be shocked how often "the obvious choice" wasn't.

Key takeaways

• Access patterns (how you read and write) decide the database, not data size or hype.
• PostgreSQL's JSONB, full-text search, and ACID transactions cover most product needs—start there before adding complexity.
• Use a second database (Redis, Elasticsearch, DynamoDB) only when a specific access pattern is slow and you've measured it.
• Migrations are expensive. Spend time on the initial choice to avoid costly rewrites later.
• The "best" database is the simplest one that solves your problem without becoming an operational burden.