Introduction to Open Source Databases

In a previous article, [A Close Look at the Evolution of Databases](https://xx/A Close Look at the Evolution of Databases), we examined the historical development of databases. Within this evolution, open-source databases have played a pivotal role, driving one of the most significant technological revolutions. They broke the monopoly once held by commercial giants such as Oracle, IBM, and Microsoft, and with the rise of the internet and big data computing, the open-source movement has steadily reshaped the landscape.

This article focuses on two representative open-source databases—MySQL and PostgreSQL—analyzing them from multiple perspectives including architecture, performance, and usage.

Why Choose Open Source Databases?

Before open-source alternatives emerged, enterprises had to pay hefty licensing fees to use databases. For startups and small-to-medium businesses, these costs created significant financial pressure and often became a barrier for technical entrepreneurs. This pressing need paved the way for open-source databases.

The advantages of open-source databases include:

1. Cost Efficiency: No expensive licensing fees; startups and SMBs can adopt quickly and at no cost.
2. Community-Driven: Rapid iteration with global developer support for troubleshooting.
3. Flexibility and Control: Open source code allows customization and optimization to fit business needs.
4. Cloud-Native Compatibility: Modern open-source databases integrate seamlessly with architectures like Kubernetes and Serverless.
5. Thriving Ecosystem: A rich ecosystem of tools and plugins lowers the barrier to database development and operations.

Thanks to these advantages, open-source databases have grown from experimental alternatives to mainstream solutions.

MySQL Database

MySQL was created as an extension of the SQL language (based on Edgar F. Codd’s relational model). It was developed and released in 1995 by the Swedish company MySQL AB, founded by David Axmark, Allan Larsson, and Michael Widenius. The name MySQL combines “My” (the name of Widenius’s daughter) and “SQL” (Structured Query Language). Later, it was acquired by Sun Microsystems and eventually became part of Oracle’s portfolio.

Today, MySQL is one of the most widely used open-source relational databases and served as a core foundation of the Web 2.0 era.

Architecture

MySQL’s logical architecture consists of three layers: connection layer, service layer, and storage engine layer:

• Connection Layer
  Handles client connections, assigns a thread per session, and performs authentication.
• Service Layer
  Core component providing query parsing, SQL execution, transaction management, and lock management.
• Storage Engine Layer
  Manages data storage and retrieval, interacting with the service layer via a standard API.

A unique feature of MySQL is its pluggable storage engine architecture, allowing different engines for different scenarios:

1. InnoDB: Default engine, supports transactions, row-level locking, and foreign keys; ideal for high-concurrency OLTP workloads.
2. MyISAM: Optimized for read-heavy workloads and full-text indexing; lacks transactions and row-level locks; suitable for OLAP use cases.
3. Memory: Stores data in memory with hash and B-tree indexing; delivers high write performance but volatile in case of crashes.
4. Others: Includes engines like Archive and NDB Cluster for archiving and distributed systems.

PostgreSQL Database

PostgreSQL originated from the POSTGRES project at the University of California, Berkeley. Over more than two decades, it has grown into one of the most advanced open-source databases available today.

Like MySQL, PostgreSQL’s architecture consists of connection layer, service layer, and storage engine layer. However, unlike MySQL, PostgreSQL does not rely on multiple pluggable engines—its robustness comes from a single powerful engine.

Key Features

• Strict ACID compliance with MVCC (Multi-Version Concurrency Control), ensuring non-blocking reads and writes.
• Rich functionality: Supports complex SQL, stored procedures, triggers, and user-defined functions.
• Extensibility: Custom data types, operators, and index methods.
• Native JSON and GIS support, blending the strengths of both RDBMS and NoSQL.

MySQL vs PostgreSQL

Although both are leading open-source relational databases, MySQL and PostgreSQL differ significantly in design philosophy, functionality, and use cases:

1. Transactions and Concurrency
   • MySQL (InnoDB): Provides transactions, but relies heavily on locks, which may cause contention under high concurrency.
   • PostgreSQL: MVCC-based, ensuring non-blocking reads/writes, making it more suitable for high-concurrency environments.
2. Support for Unstructured Data
   • MySQL: JSON support is basic, with limited indexing and query capabilities.
   • PostgreSQL: Full JSON support, with indexing and advanced query operations.
3. Complex Queries and Optimizer
   • MySQL: Simple optimizer tied closely to the storage engine; best for OLTP workloads.
   • PostgreSQL: Advanced optimizer, excels in handling complex SQL and multi-table joins; suitable for some OLAP workloads.
4. Horizontal Scalability
   • MySQL: Relies on middleware solutions for sharding and distributed scaling.
   • PostgreSQL: Supports distributed scaling via extensions and plugins.

Conclusion

MySQL and PostgreSQL are the two flagships of open-source databases, each playing a vital role across industries. MySQL, with its lightweight and efficient design, dominates internet applications, while PostgreSQL, with its transactional integrity, extensibility, and complex query support, is favored in enterprise and industrial scenarios.

Looking forward, as industries continue to evolve, both will keep advancing—sometimes competing, sometimes complementing—while together driving the prosperity of the open-source database ecosystem.