MySQL, PostgreSQL, MongoDB or Redis: Choosing a Data Layer

Frameworks get replaced; databases get migrated, and migrations are measured in months. It is worth spending an afternoon on this decision rather than inheriting whatever the first developer happened to know.

Start relational, and justify anything else

Business data is relational. An invoice belongs to an order, which belongs to a customer, who belongs to an organisation. Relational databases enforce those relationships for you: a foreign key means an orphaned invoice cannot exist, and a transaction means money never leaves one account without arriving in the other. Rebuilding those guarantees in application code is possible, and it is how data quietly goes wrong.

MySQL is the safe default. It is everywhere, every hosting provider supports it, replication is well understood and the operational knowledge is common. PostgreSQL is what we reach for when the data is more demanding: native JSONB when part of the record is genuinely unstructured, window functions for real reporting, proper full-text search, PostGIS for anything geographic, and stricter behaviour that catches bad data at write time instead of at month-end.

-- The single most common performance fix we apply.
-- Before: a full table scan on every dashboard load.
SELECT id, total, status
FROM orders
WHERE customer_id = 4821
  AND created_at >= NOW() - INTERVAL 30 DAY;

-- After: one composite index turns it into an index range scan.
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at);

MongoDB when the shape of a record is genuinely unknown

Document stores are a good answer to a narrow question: what if every record has a different shape and you cannot know the fields in advance? Event logs, audit trails, IoT telemetry, scraped data and per-tenant custom forms all fit. Writes are fast, and the schema can change without a migration.

The mistake we see most is choosing MongoDB for ordinary relational data because it felt faster to start. Six months later the application is doing joins by hand in a loop, and nothing guarantees that a customer still exists when an order references them. If you can draw the entity diagram, use a relational database.

Redis is not a database you store things in

Redis holds data in memory, which makes it superb at things that must be instant and are cheap to lose: sessions, rate limiting, queue backing, real-time counters, and caching the results of expensive queries. A settings table read on every request belongs in Redis. Your only copy of a customer record does not.

"Most performance problems blamed on the database are missing indexes and queries inside loops. Fix those before you change engines."

What we usually end up with

A typical system we ship runs PostgreSQL or MySQL as the source of truth, Redis in front of it for sessions, caching and queues, and Elasticsearch alongside if search needs to be genuinely good rather than merely present. Each store does the one thing it is best at, and the relational database remains the record that everything else can be rebuilt from.