Which Database Reigns Supreme? MySQL vs PostgreSQL Deep Dive

Background

According to the 2023 StackOverflow Developer Survey, PostgreSQL has overtaken MySQL to become the most popular database among professional developers (50% choose PostgreSQL) while hobbyist programmers still prefer MySQL (54%).

Definition of Success

Success can be measured by popularity, technical features, application domains, etc. The DB‑Engines ranking is considered authoritative because it aggregates multiple signals.

DB‑Engines Ranking Methodology

Number of mentions on search engines (Google, Bing) for the database name together with the word “database”.

General interest measured by Google Trends.

Technical discussion frequency on Stack Overflow and DBA Stack Exchange.

Number of job postings mentioning the system on Indeed and Simply Hired.

Number of profiles on LinkedIn that mention the system.

Number of related tweets.

The scores are normalized and averaged, producing a relative popularity index that can be compared across systems.

Are Distributed Databases a False Need?

Some argue that distributed databases trade functionality, performance, complexity, and reliability for higher capacity and throughput. However, advances in NVMe SSDs and hardware have dramatically increased the performance‑price ratio of centralized databases, making distributed solutions unnecessary for most workloads. Real‑world cases where distributed databases are required are rare, typically limited to petabyte‑scale data with strict retention requirements.

For example, WeChat’s commercial refund service must retain five years of orders, amounting to petabytes of data, which is impractical for a single‑node database and motivates the use of a distributed system such as TDSQL.

Why TPC‑C Benchmarks Still Matter

TPC‑C is a rigorous benchmark that requires error‑free operation for eight hours and a volatility of less than 2 %. It also evaluates cost and stability, not just raw performance.

OceanBase achieved 7.07 billion tpmC in 2020, and Tencent Cloud’s recent run in early 2023 claimed the world‑first double‑lead in both throughput (tpmC) and price‑per‑tpmC.

Comprehensive MySQL vs PostgreSQL Comparison

Transaction Rollback Behavior

<code>BEGIN;
INSERT INTO t VALUES (1,...);
INSERT INTO t VALUES (1,...); -- primary‑key conflict, error
COMMIT;
SELECT * FROM t; -- returns the first row
</code>

MySQL commits the successful statements by default, while PostgreSQL can be configured to roll back the whole transaction on error.

Licensing

PostgreSQL uses a permissive PostgreSQL License (similar to MIT), allowing commercial modification without source disclosure. MySQL is licensed under GPL v2, which requires derivative works to be open‑sourced.

MVCC Implementation

PostgreSQL stores both old and new tuple versions in the heap and relies on a vacuum process to clean up dead rows. MySQL (and Oracle) use undo segments to record previous versions, enabling in‑place updates.

Process vs Thread Model

PostgreSQL adopts a multi‑process architecture, offering stability and memory isolation at the cost of higher resource usage. MySQL uses a multi‑threaded model, providing better resource efficiency and concurrency but with potential stability risks.

Storage Engine Differences

PostgreSQL uses heap tables with CTID pointers, offering simple inserts and flexibility but slower queries without indexes. MySQL’s InnoDB uses a clustered index (primary‑key order), giving fast primary‑key lookups and better space efficiency, though inserts and updates can be more expensive.

Conclusion

PostgreSQL provides richer features and excels at complex queries, but MySQL has captured the internet boom, proving consistency, performance, reliability, and operability at massive OLTP scales. In China, major financial services such as WeChat Pay (TXSQL) and Alipay (OceanBase) rely on MySQL‑compatible engines, underscoring MySQL’s success.