Why the database decision outlives the framework decision
Teams spend weeks debating a frontend framework and an afternoon picking a database. The proportions are backwards. Application code is rewritten every 18 to 36 months as product priorities shift, but the data layer tends to survive three or four generations of that churn. The schema you design, the replication topology you choose, the backup and restore procedure you rehearse, and the query patterns your developers internalize all become institutional knowledge. Replacing them later means rewriting migrations, retraining the team, rebuilding observability dashboards, and renegotiating every integration that touches the storage layer.
That asymmetry is the reason a database comparison should feel less like shopping and more like choosing a foundation. Open source engines make this easier in one important way and harder in another. Easier because there is no licensing negotiation standing between you and a prototype — you can install a candidate in a container and run a realistic workload the same afternoon. Harder because the absence of a contract removes the external pressure that normally forces a decision. Nothing stops you from running three engines in production for four years because nobody wants to own the consolidation project.
A useful way to think about the choice is to separate three questions that are often tangled together: what shape is your data, how will it grow, and who will operate it? The first is about modeling. The second is about scaling strategy. The third is about the humans on call at two in the morning. Almost every bad database decision traces back to answering one of those questions confidently while ignoring the other two.
The four families of open source databases
Before comparing specific products, it helps to know which family you are actually shopping in. Engines in the same family tend to share failure modes, tuning levers, and operational rituals. Engines in different families can look deceptively similar in a hello-world test and diverge completely under load.
Relational engines
Relational systems store rows in tables with a declared schema enforced by the engine. They offer ACID transactions, joins, constraints, and a mature SQL dialect that every analyst and ORM already understands. PostgreSQL, MySQL, and MariaDB are the dominant open source members. The strength is integrity: the database refuses to store data that violates the rules you declared, which eliminates an entire category of application bugs.
Document stores
The document model stores self-contained JSON-like objects. MongoDB is the best-known open source implementation. Documents are useful when an entity's shape varies between records, when you want to evolve fields quickly without a migration step, or when the natural unit of retrieval is a whole object rather than a set of joined rows. The trade-off is that cross-document consistency and multi-collection joins require more deliberate design.
Key-value and wide-column stores
These engines optimize for extremely high write throughput and horizontal scale at the cost of query flexibility. You generally access data by a known key or a narrow range, not by ad hoc predicates. They are excellent for session state, counters, caches, event streams, and time-series ingestion. They are a poor fit for the reporting workload that inevitably shows up later.
Distributed SQL
This family keeps the relational model and SQL interface but spreads data across many nodes with a consensus protocol underneath. CockroachDB, TiDB, YugabyteDB, and Vitess in front of MySQL all fit here. The pitch is horizontal scale without abandoning transactions or joins. The cost is latency: consensus adds coordination overhead to every write, and operational complexity is meaningfully higher than a single primary node.
PostgreSQL: the default that earns it
PostgreSQL has become the safe answer for a large share of new projects, and the reputation is deserved rather than fashionable. It offers a genuinely wide feature surface: rich indexing including GIN and GiST, native JSON and JSONB types, full-text search, window functions, materialized views, logical replication, partitioning, and a serious extension ecosystem for geospatial, time-series, and vector workloads. When a project's requirements are unclear — which is most of the time — PostgreSQL covers more of the plausible futures than any other single engine.
Where it wins
Choose PostgreSQL when correctness matters more than raw insert throughput, when queries are analytical as well as transactional, when you want one engine to serve OLTP and moderate reporting, or when you expect requirements to change. JSONB is particularly valuable as a pressure valve: it lets you store semi-structured attributes without abandoning relational integrity for the rest of the schema. The extension model also means you can add capabilities such as vector similarity search without introducing a second database.
Where it strains
PostgreSQL's default architecture is a single writable primary with read replicas. That is fine for the vast majority of applications but becomes a constraint at very high write volumes, where vertical scaling of the primary and careful partitioning are the main levers. Table bloat from aggressive updates, connection management under spiky load, and autovacuum tuning are the three operational topics that separate teams who run it well from teams who fight it monthly. None of these are reasons to avoid the engine, but all of them are reasons to have someone on the team who has operated it before.
MySQL and MariaDB: throughput and ecosystem reach
MySQL remains one of the most widely deployed databases on the internet, and MariaDB is a compatible fork with additional storage engines and a different governance model. The practical differences between them matter less than the family's shared characteristics: fast simple reads, straightforward replication, ubiquitous tooling, and a very large pool of engineers who already know how to run it.
Where they win
These engines are excellent for read-heavy web applications, content platforms, and workloads dominated by primary-key lookups and simple joins. Replication is well understood, managed cloud offerings are mature and comparatively cheap, and the operational learning curve for a competent team is short. If your application is a conventional CRUD service behind a cache, the marginal benefit of a more sophisticated engine is often smaller than the cost of the team learning it.
Where they strain
MySQL's historical approach to features such as full outer joins, advanced indexing, and JSON querying has improved substantially but still trails PostgreSQL in flexibility. Storage engine choice, character set configuration, and replication lag handling are perennial sources of incidents. MariaDB's divergence from MySQL is small but real, and a decision to adopt it should account for ecosystem compatibility in drivers, managed services, and monitoring tools rather than assuming perfect interchangeability.
MongoDB: documents for evolving product data
The document model shines when a single entity is naturally a graph of nested objects and you almost always read it whole. Product catalogs, user profiles with heterogeneous fields, content metadata, event payloads, and configuration trees all fit comfortably. Schema validation exists but is optional, which means the engine will happily accept malformed documents if the application lets it.
Where it wins
MongoDB is a strong fit when requirements change weekly, when documents are the natural unit of consistency, and when you want horizontal sharding without designing a manual partitioning scheme. Aggregation pipelines handle a surprising amount of reporting, and the developer experience of working with documents that mirror your application objects reduces translation overhead.
Where it strains
The failure mode is not the engine; it is unmanaged schema drift. Once five services write slightly different shapes into the same collection, every query becomes defensive and every migration becomes archaeology. Multi-document transactions exist but carry more overhead than their relational equivalents, and analytical queries over large collections often need a separate store. A document database rewards discipline about indexing and ownership far more than a relational one, because nothing enforces it for you.
Comparing engines on the axes that matter
Product marketing comparisons tend to fixate on synthetic throughput numbers that rarely predict production behavior. A more reliable comparison uses five axes that map directly to the decisions you will have to live with.
Query patterns and schema discipline
Write down your ten most frequent queries and your five most important reports. If the reports involve multi-table aggregation with grouping and windowing, relational engines will be simpler. If the queries are always key-based retrieval of whole objects, documents or key-value stores will be faster to build against. If both are true, plan for a primary transactional store plus a derived analytical store rather than forcing one engine to do both.
Consistency and transactions
Determine which operations must be atomic across multiple entities. Relational engines give you this by default. Document stores give it to you with explicit transactions and additional cost. Distributed SQL gives it to you across nodes at the price of write latency. Key-value stores generally do not, and using them for financial or inventory state requires application-level compensation logic that is easy to get subtly wrong.
Scaling model
Vertical scaling is boring, cheap, and effective until it is not. Horizontal sharding is powerful but shifts complexity into the application and the operations team. A useful rule: if you can predict your data volume and write rate for the next two years and they fit on a single well-provisioned primary, prefer the simpler architecture and revisit later. Premature sharding costs more than delayed sharding in almost every case.
Operational burden
Backups that you have never restored, replication you have never failed over, and monitoring you have never alerted on are liabilities, not features. When comparing engines, count the number of operational tasks the team will own: vacuuming, compaction, rebalancing, upgrade procedures, connection pooling, and disaster recovery drills. A slightly slower engine that your team can operate confidently beats a faster engine that scares them.
Total cost of ownership
Open source means no licence fee, not no cost. The bill arrives as compute for replication and failover capacity, engineering time for tuning and upgrades, storage for backups and point-in-time recovery, and the opportunity cost of the features you did not ship while stabilizing the data layer. Model these for a three-year horizon. The engine with the highest raw performance often loses on this axis once staffing assumptions are included.
Benchmarking without fooling yourself
If you benchmark, benchmark your workload. Load a production-shaped dataset, an order of magnitude larger than your current one, and replay realistic read/write ratios. Measure the ninety-ninth percentile, not the average, because users experience tails. Test failover and measure how long writes are unavailable. Test the restore procedure end to end. Test what happens when the connection pool saturates. Most benchmark surprises come from these operational scenarios rather than from query speed.
Governance, privacy, and compliance by design
Retrofitting governance onto a running system is expensive. Retrofitting it onto a schema that thousands of queries already depend on is worse. Treat these requirements as design inputs before the first table is created.
Encryption and access control
Encrypt data in transit and at rest, and be explicit about which columns need application-level encryption because the key holder should not be the database operator. Use role-based access with least privilege and separate roles for migrations, application traffic, and analytics. Avoid shared superuser credentials, which is the single most common governance gap in small teams.
Row-level security and multi-tenancy
If you serve multiple tenants from one schema, decide early between database-enforced isolation and application-enforced filtering. PostgreSQL's row-level security lets the database enforce tenancy, which reduces the risk of a missing WHERE clause leaking data. Application-level filtering is more portable but depends entirely on developer discipline and code review.
Retention, deletion, and auditability
Deletion requests need a defined path that covers backups, replicas, derived stores, and caches — not just the primary table. Audit logging should record who accessed sensitive records and when, and it should be tested, because an audit log nobody reads is not a control. Write the retention policy before you need it; deleting data from immutable backup chains is far harder than defining expiry up front.
Migration, integration, and the hybrid reality
Most real systems end up with more than one data store, and that is fine as long as the boundaries are intentional. The common pattern is a relational primary for transactional state, a search engine for text queries, a cache for hot reads, and an analytical warehouse fed by change data capture. The failure mode is not polyglot persistence; it is undocumented polyglot persistence, where nobody knows which store is authoritative for a given field.
A migration sequence that survives contact with reality
Start by freezing the schema and writing down the invariants the old system enforces implicitly. Build dual-write or change-data-capture replication into the new engine and verify that row counts, checksums, and sampled records match. Then run shadow reads: send production queries to both systems and compare results without serving the new responses. Only after the differences are explained should you cut over reads, then writes, with the old system kept warm for rollback. Budget more time for verification than for migration itself.
Integration pitfalls
Beware of ORMs that abstract away engine-specific behavior and then surprise you with N+1 queries in production. Beware of connection pooling defaults that assume a single small instance. Beware of treating the analytical store as a real-time source when its replication lag is measured in minutes. And beware of schema migrations that lock large tables during business hours; every engine has a different tolerance for this, and the safest migration is always the one that can be run incrementally.
Common mistakes that cost teams months
Choosing an engine before defining query patterns is the root error from which most others grow. A close second is optimizing for the workload you imagine rather than the one you have. Teams routinely adopt distributed systems to solve a scaling problem they will not face for years, then pay the complexity cost immediately.
Another frequent mistake is conflating storage with search. Relational engines can do full-text search, and document stores can do aggregations, but neither is a substitute for a purpose-built search index once relevance ranking and faceting enter the requirements. Similarly, treating a cache as a system of record works until the cache is evicted.
Finally, teams underinvest in observability. You need query latency percentiles, slow query logs, replication lag, connection counts, disk growth rate, and backup success metrics from day one. Without them, capacity planning becomes guesswork and incidents become mysteries. Database problems are almost always visible in trends weeks before they become outages; the only requirement is that someone is looking at the trends.
FAQ
Which open source database should a new project start with? For most applications with unclear future requirements, a relational engine — PostgreSQL in particular — is the safest starting point because it covers transactional, analytical, and semi-structured workloads reasonably well. Move to a specialised engine when a specific requirement makes it clearly better, not preemptively.
Is a document database faster than a relational one? Not inherently. It is often faster for retrieving a whole nested object by key, because no joins are needed. It is usually slower for ad hoc aggregation across many entities. Performance depends on access patterns, not on the storage model in the abstract.
When does distributed SQL make sense? When you genuinely need multi-region writes with strong consistency, or when a single primary can no longer absorb write volume even after partitioning. If neither condition holds, a well-tuned single-primary setup with read replicas is simpler and usually cheaper.
How do we decide between MySQL and PostgreSQL? Start with team familiarity and the managed services available to you. If both are equal, PostgreSQL offers a broader feature surface for complex queries and extensions, while MySQL often has a lower operational learning curve for conventional web workloads.
How much capacity headroom should we plan for? Aim to run at no more than sixty to seventy percent of provisioned capacity during normal peaks, so that failover, backups, and unexpected traffic spikes do not push you into saturation. Revisit the number quarterly using measured growth rather than projected growth.
What is the single highest-value investment in a database stack? Rehearsed restore procedures. Teams test backups; almost nobody tests recovery. A restore drill that reveals a nine-hour recovery window is worth more than any tuning exercise you will run this quarter.
Do we need more than one database? Only when the boundary is explicit. One engine that the team operates confidently beats three that nobody fully understands. Introduce a second store when a specific workload is measurably poorly served, and document which system is authoritative for every piece of data.
How do we avoid vendor lock-in with managed offerings? Keep the schema and queries portable, avoid engine-specific features in the critical path unless the benefit is large, and make sure your team could run the engine themselves if they had to. Portability is a property of your architecture, not of your contract.
