The database is the foundation of nearly every application. Choosing the wrong database can limit your application's scalability, complicate your architecture, or lock you into costly infrastructure. The open source database ecosystem offers excellent options for every use case, but understanding the differences between them is critical for making the right choice.
This guide compares four widely used open source databases: PostgreSQL, MySQL, SQLite, and CockroachDB. Each serves a different niche, and the best choice depends on your specific requirements for consistency, scalability, operational complexity, and feature richness.
PostgreSQL: The Feature-Rich Workhorse
PostgreSQL is the most advanced open source relational database, known for its standards compliance, extensibility, and rich feature set. Originally developed at UC Berkeley in the 1980s, PostgreSQL has evolved into a database that handles everything from small web applications to massive analytical workloads.
Key Strengths
- Advanced data types: Native support for JSON/JSONB, arrays, hstore (key-value), ranges, geometric types, and full-text search. You can use PostgreSQL as both a relational database and a document store.
- Extensibility: Extensions like PostGIS (geospatial), TimescaleDB (time-series), pgvector (vector search for AI), and Citus (distributed tables) transform PostgreSQL into a specialized database for virtually any workload.
- ACID compliance: Full support for transactions, foreign keys, triggers, views, and stored procedures with strict ACID guarantees using Multi-Version Concurrency Control (MVCC).
- Standards compliance: PostgreSQL adheres closely to the SQL standard, making it easier to write portable SQL and reducing surprises when queries behave differently than expected.
- Write-Ahead Logging (WAL): Supports streaming replication, point-in-time recovery, and logical replication for flexible high-availability configurations.
Limitations
PostgreSQL's single-node architecture means horizontal scaling requires external tools like Citus or application-level sharding. Write-heavy workloads on a single node eventually hit the limits of vertical scaling. The MVCC implementation requires regular VACUUM operations to reclaim space from deleted or updated rows, which can cause performance issues if not properly configured.
Ideal Use Cases
PostgreSQL excels as the primary database for web applications, analytical workloads, geospatial applications, and any scenario that benefits from its rich type system and extensibility. It is the default recommendation for most new projects.
MySQL: The Battle-Tested Web Database
MySQL is the most widely deployed open source database in the world, powering WordPress, Facebook, Uber, and millions of web applications. Now owned by Oracle, MySQL continues to be developed alongside the community fork MariaDB.
Key Strengths
- Read performance: MySQL's InnoDB storage engine is highly optimized for read-heavy workloads. With proper indexing and query optimization, MySQL can handle millions of reads per second.
- Replication: MySQL's replication system is mature and well-understood. Read replicas, group replication, and InnoDB Cluster provide multiple approaches to high availability and read scaling.
- Ecosystem: Decades of widespread use mean there are tools, tutorials, hosting providers, and managed services for every conceivable MySQL deployment scenario.
- Simplicity: MySQL is generally easier to set up and administer than PostgreSQL for straightforward use cases.
Limitations
MySQL historically had weaker standards compliance than PostgreSQL, though recent versions have improved significantly. Its JSON support, while functional, is less mature than PostgreSQL's JSONB. Window functions, CTEs, and other advanced SQL features were added much later than in PostgreSQL. MySQL also lacks PostgreSQL's extensibility model.
Ideal Use Cases
MySQL is an excellent choice for read-heavy web applications, content management systems, and applications where the existing MySQL ecosystem (hosting, tools, expertise) provides practical advantages.
SQLite: The Embedded Database
SQLite is fundamentally different from PostgreSQL and MySQL. It is not a client-server database. It is an embedded database engine that reads and writes directly to a single file on disk. SQLite is the most widely deployed database in the world, present in every smartphone, web browser, and most desktop applications.
Key Strengths
- Zero configuration: No server process, no setup, no administration. Your application opens a file, and you have a fully functional SQL database.
- Reliability: SQLite is one of the most thoroughly tested software projects in existence. Its test suite contains over 100 million lines of test code, achieving 100% branch coverage.
- Portability: A SQLite database is a single cross-platform file. You can copy it between operating systems, email it, or embed it in a mobile app.
- Performance: For read-heavy workloads and small to medium datasets, SQLite is often faster than client-server databases because it eliminates network round-trip overhead.
- WAL mode: Write-Ahead Logging mode allows concurrent readers and a single writer, significantly improving performance for applications with mixed read-write workloads.
Limitations
SQLite uses file-level locking, which limits write concurrency. Only one process can write to the database at a time. It does not support network access natively (though projects like LiteFS and Litestream add replication capabilities). SQLite is not suitable for applications with many concurrent writers or large-scale analytical workloads.
Ideal Use Cases
SQLite excels in mobile applications, desktop applications, embedded systems, testing and prototyping, edge computing, and any scenario where simplicity and zero administration are more important than concurrent write performance.
CockroachDB: Distributed SQL
CockroachDB represents a newer category: distributed SQL databases that provide the familiar SQL interface and ACID guarantees of traditional databases while scaling horizontally across multiple nodes and data centers.
Key Strengths
- Horizontal scalability: Add nodes to increase capacity. CockroachDB automatically rebalances data across nodes without application changes.
- Serializable isolation: The strongest isolation level in SQL, preventing all concurrency anomalies. Most other databases default to weaker isolation levels.
- Geo-partitioning: Pin data to specific geographic regions for compliance (e.g., keeping EU customer data in EU data centers) while maintaining a single logical database.
- Automatic failover: If a node fails, the cluster automatically continues operating using replicas on other nodes. No manual intervention required.
- PostgreSQL wire compatibility: CockroachDB speaks the PostgreSQL wire protocol, so existing PostgreSQL drivers, ORMs, and tools work with minimal or no modification.
Limitations
CockroachDB introduces latency compared to single-node databases because distributed transactions require coordination across nodes. It does not support all PostgreSQL features (some extensions, stored procedures, and data types are missing or limited). Operational complexity is higher than single-node databases, even with automation. The core product changed from Apache 2.0 to the Business Source License (BSL) in 2019, which restricts offering CockroachDB as a service.
Ideal Use Cases
CockroachDB excels for applications that require global distribution, high availability without manual failover, and horizontal write scaling. It is particularly well-suited for multi-region applications, financial services requiring strict consistency, and SaaS platforms that need to scale without rearchitecting their database layer.
Making the Right Choice
For most applications, PostgreSQL is the safest default choice. Its combination of features, performance, and ecosystem support makes it suitable for the widest range of use cases. Choose MySQL if your team has deep MySQL expertise, if you are building on platforms that assume MySQL (like WordPress), or if read-heavy performance is your primary concern. Choose SQLite for embedded, mobile, or edge applications where simplicity and zero administration are paramount. Choose CockroachDB when you need horizontal scalability, multi-region deployment, or automatic failover that a single-node database cannot provide.
The best database is the one that fits your actual requirements, not the one with the most impressive feature list. Start with the simplest option that meets your needs, and migrate to a more complex solution only when you have concrete evidence that you need it.
