PostgreSQL vs MySQL

前段时间，9万名开发人员参与了Stackoverflow年度调查，调查内容主要涉及开发者如何学习和升级，他们正在使用哪些工具，以及他们想要哪些工具。PostgreSQL超越MySQL成为最受欢迎的数据库。在所有的有效问卷中（76,634位受访者），有45.55%的开发者正在使用PostgreSQL，首次超越MySQL，这使得PostgreSQL登上数据库流行度的榜首。MySQL退居第二，有41.09%的开发者正在使用MySQL。其次是SQLite（占比30.9%）。Oracle位列第九，占比9.8%。

为什么PostgreSQL可以超过MySQL，今天就来比较下这两个数据库。

PostgreSQL vs.MySQL 是在选择开源关系型数据库管理系统时的重要决策。PostgreSQL和 MySQL 都是经过时间验证的解决方案，可以与企业级解决方案（如 Oracle 和 SQL Server） 竞争。MySQL 因其易用性和速度而闻名，而 PostgreSQL 则拥有更多高级功能，这也是 PostgreQL 经常被描述为开源版 racle 的原
下表对比了 PostgreSQL和MySQL的特性

特性对比

PostgreSQL和 MySQL 都作为关系型数据库管理系统提供了广泛的功能，但两者之间存在一些关键差异:

数据类型： PostgreSQL 支持更广泛的高级数据类型，包括数组、hstore（键值存储）和JSONB（二进制JSON）。另一方面MySQL 的数据类型集合较为有限，更适用于简单的 Web 应用程序。MySQL 的JSON 二进制存储格式可以与 PostgreSQL的JSONB 进行比较。实际上，MySQL的JSON 存储非常适合简单的JSON 结构和直接的 Web 应用程序。

地理空间支持： PostgreSQL 强力支持地理空间数据，包括用于处理地理数据的丰富的数据类型、函数和操作符。MySQL 在地理空间支持方面虽有一些功能，但在这个领域可能还需要更多的改进。

索引： 在 MySQL中，默认的索引类型是 B-tree，非常适合大多数使用情况。PostareSOL比 MySQL 拥有更为精细的索引系统包括对 B-tree、GiST（广义搜索树）和 GIN （广义反向索引）索引的支持。这些提供了更多优化查询性能和数据检索的选项。

复制： PostgreSQL和 MySQL 都支持主从数据库复制，但复制的方法和选项有所不同PostgreSQL 支持使用第三方扩展实现多主复制。MySQL 最近引入了一种名为 Group Replication 的新的复制模型，但它仍然是一个相对较新的功能，有一些限制。

事务： PostgreSQL和 MysQL lnnoDB 都使用 MVCC（多版本并发控制）来处理并发数据访问然而，PostgreSQL 提供了更先进的事务管理功能，如事务隔离级别、原子事务和保存点。相比之下，MySQL 的事务管理选项较为有限。对于需要高并发或复杂事务逻辑的应用程序，PostgreSQL可能更加合适。

存储过程： PostgreSQL和 MySQL 都支持存储过程，但存储过程的语言和功能有所不同。PostgreSQL 支持使用多种语言编写存储过程，包括 PL/pgSQL、PL/Tcl、PL/Perl 等。而MySQL 主要支持使用SQL编写存储过程。

扩展： PostgresQL 拥有一个强大的扩展框架，允许开发人员添加自定义功能并扩展数据库的核心特性。虽然 MySQL 对扩展有定支持，但与 PostgreSQL 相比，其扩展性有所不同。

放一个官网的表格

	PostgreSQL	MySQL
Known as	The world’s most advanced open source database.	The world’s most popular open source database.
Development	PostgreSQL is an open source project.	MySQL is an open-source product.
Pronunciation	post gress queue ell	my ess queue ell
Licensing	MIT-style license	GNU General Public License
Implementation programming language	C	C/C++
GUI tool	PgAdmin	MySQL Workbench
ACID	Yes	Yes
Storage engine	Single storage engine	Multiple storage engines e.g., InnoDB and MyISAM
Full-text search	Yes	Yes (Limited)
Drop a temporary table	No TEMP or TEMPORARY keyword in DROP TABLE statement	Support the TEMP or TEMPORARY keyword in the DROP TABLE statement that allows you to remove the temporary table only.
DROP TABLE	Support CASCADE option to drop table’s dependent objects e.g., tables and views.	Does not support CASCADE option.
TRUNCATE TABLE	PostgreSQL TRUNCATE TABLE supports more features like CASCADE, RESTART IDENTITY, CONTINUE IDENTITY, transaction-safe, etc.	MySQL TRUNCATE TABLE does not support CASCADE and transaction safe i.e,. once data is deleted, it cannot be rolled back.
Auto increment Column	SERIAL	AUTO_INCREMENT
Identity Column	Yes	No
Analytic functions	Yes	No
Data types	Support many advanced types such as array, hstore, and user-defined type.	SQL-standard types
Unsigned integer	No	Yes
Boolean type	Yes	Use TINYINT(1) internally for Boolean
IP address data type	Yes	No
Set default value for a column	Support both constant and function call	Must be a constant or CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns
CTE	Yes	Yes (Supported CTE since MySQL 8.0)
EXPLAIN output	More detailed	Less detailed
Materialized views	Yes	No
CHECK constraint	Yes	Yes (Supported since MySQL 8.0.16, Before that MySQL just ignored the CHECK constraint)
Table inheritance	Yes	No
Programming languages for stored procedures	Ruby, Perl, Python, TCL, PL/pgSQL, SQL, JavaScript, etc.	SQL:2003 syntax for stored procedures
FULL OUTER JOIN	Yes	No
INTERSECT	Yes	No
EXCEPT	Yes	No
Partial indexes	Yes	No
Bitmap indexes	Yes	No
Expression indexes	Yes	No
Covering indexes	Yes (since version 9.2)	Yes. MySQL supports covering indexes that allow data to be retrieved by scanning the index alone without touching the table data. This is advantageous in case of large tables with millions of rows.
Triggers	Support triggers that can fire on most types of command, except for ones affecting the database globally e.g., roles and tablespaces.	Limited to some commands
Partitioning	RANGE, LIST	RANGE, LIST, HASH, KEY, and composite partitioning using a combination of RANGE or LIST with HASH or KEY subpartitions
Task Schedule	pgAgent	Scheduled event
Connection Scalability	Each new connection is an OS process	Each new connection is an OS thread

变更数据捕捉

变更数据捕获(Change Data Capture, CDC) 方面，MySQL 二进制志和 PostgreSQL 预写日志(Write-Ahead Logs,WAL)都可以捕获对数据库所做的更改。然而，CDC的具体特性和用法可能会有所不同。DBConvert Streams 是一款软件，可以读取 MySQL和 PostgreSQL事务日志，并将记录转换为另一种方言，从而适用于实时异构数据库复制。

性能

MySQL 以其高性能和处理大量数据的能力而闻名。它经过优化，适用于读密集型工作负载，并具有快速的索引系统，有助于提高查询性能。然而，当与写操作结合时，可能会出现并发问题，如锁争用，导致性能下降。lnnoDB 存储引擎用于解决表级别锁问题。它是 MySQL 生态系统中最受欢迎和广泛使用的存储引整之一。lnnoDB 支持行级别锁定，提高了混合工作负载的并发性。此外，近期开发的高性能存储引擎 MyRocks 进一步提高了MySQL处理写入密集型工作负载的能力。

PostgreSQL 的设计更加多样化，可以处理读密集型和写密集型的工作负载，但相对于针对读密集型工作负载进行优化的 MySQL性能略低。然而，PostgreSQL 在最近的版本中改进了性能，特别是在处理复杂查询和数据处理方面。此外，与 MySQL 相比，PostgreSQL 具有更先进的索引系统，可以提高复杂查询的性能。PostgreSQL 还支持高级数据类型，如数组和JSONB，可以实现更高效的数据存储和检索。

可扩展性

MySQL和 PostgreSQL 都可以进行扩展，但在可扩展性方面它们具有不同的优势和劣势。MySQL 常常被人们喜欢是因为其水平可扩展性，也就是说可以通过向数据库集群添加更多节点来进行横向扩展。它非常适合需要处理大量并发连接的 Web 应用程序。

另一方面，PostgreSQL 以其垂直可扩展性而闻名，也就是说可以通过向单个节点添加更多资源(如内存和CPU)来处理大量数据和处理能力。它还通过分片等技术支持水平扩展，允许将大型数据集分割到多个节点上。PostgreSQL 更适合需要复杂查询和事务以及数据仓库和商业智能工作负载的应用程序。
在考虑可扩展性时，要考虑您的应用程序的具体要求。如果您需要处理大量并发连接并需要水平可扩展性，那么 MySQL 可能是个更好的选择。然而，如果您需要复杂的事务和查询，那么 PostgreSQL 可能更合适。

成本

在2023年，PostgreSQL仍然是完全开源和由社区驱动的，而MySQL在许可方面有更复杂的历史。MySQL最初是由MySQL AB开发的商业产品，提供免费和付费版本。2010年，Oracle收购了MySQL AB，这引发了开发人员对其开源状态的未来的一些担忧。然而，一些原始MySQL的开源分支，包括MariaDB和Percona，已经帮助缓解了这些担忧。

PostgreSQL的问题

尽管PostgreSQL具有先进的特性和功能，但它尚未达到MySQL的普及程度和广泛使用。这导致了第三方工具数量较少以及PostgreSQL生态系统中经验丰富的开发人员或数据库管理员数量较少。

由于其先进的特性，相对于MySQL，设置和管理PostgreSQL可能更复杂，更适合有经验的数据库管理员和开发人员。

由于其更复杂的架构和特性，PostgreSQL在某些使用情况下可能比MySQL更慢。

PostgreSQL可能需要比MySQL更多的资源，特别是在内存和CPU使用方面。

尽管PostgreSQL是开源的，但由于其先进的特性和增加的资源需求，实施和维护的成本仍然可能很高。

对于每个新的客户端连接，PostgreSQL都会分叉一个新的进程，这可能会分配大量的内存，通常每个连接约为10 MB。然而，这种架构的设计旨在提供更好的性能、可靠性和可扩展性之间的安全性和隔离性权衡。

PostgreSQL的设计注重可扩展性、标准符合性、可扩展性和数据完整性。有时，这些特性与MySQL相比可能会降低性能，特另是在简单的读密集型工作负载中。