articles>get-to-know-postgresql>
认识一下PostgreSQL

认识一下PostgreSQL

PostgreSQL 是一个强大的、商业级别的开源关系型数据库。它允许使用关系型 SQL 和非关系型 JSON 数据和查询。PostgreSQL 背后有一个强大的社区。PostgreSQL 是一个非常可靠的数据库管理系统，具有优秀的支持水平、安全性和准确性。一些手机和网络应用程序使用 PostgreSQL 作为他们的默认数据库。许多地理空间和分析解决方案也使用了 PostgreSQL。

PostgreSQLMySQLDatabase
2475字

最近在接触 Next.js 的时候，跟着它的新手教程一步步走。它提供了一个比较容易上手的 dashboard 实例，能够让开发者快速上手。

作为一款全栈框架，这个 demo 内部集成了 Prisma 以及传统关系型数据库 PostgreSQL。

对于这一款数据库，更多只是听闻其相较于 MySQL 更为强大，却不知强大在何处，因此进行了系统性的调研，并准备下一步对 littleSharing 重构时采用其作为 ORM 框架的映射数据库。

参考文献：

PostgreSQL 是什么

PostgreSQL 是一个强大的、商业级别的开源 关系型数据库。

它允许使用关系型 SQL 和非关系型 JSON 数据和查询。PostgreSQL 背后有一个强大的社区。PostgreSQL 是一个非常可靠的数据库管理系统，具有优秀的支持水平、安全性和准确性。一些手机和网络应用程序使用 PostgreSQL 作为他们的默认数据库。许多地理空间和分析解决方案也使用了 PostgreSQL。

PostgreSQL 支持复杂的数据类型。事实上，该数据库在创建时考虑到了大量的数据类型。它的数据库性能与它的竞争对手，如 Oracle 和 SQL Server 相似。AWS ，它的亚马逊关系数据库服务为 PostgreSQL 提供了一个完全维护的数据库服务。PostgreSQL 也被用于构建 Amazon Aurora 。

主要特点

标准符合性

  • 遵循 SQL 标准：PostgreSQL 是最符合 SQL 标准的开源数据库之一，支持 ANSI SQL:2016 大部分特性。
  • 扩展支持：支持多种数据类型、索引和高级查询功能，使其在复杂查询和数据分析中表现优异。

高级数据类型

  • JSON 和 JSONB：支持 JSON 和 JSONB 数据类型，可以高效地存储和查询 JSON 数据。
  • 数组：支持数组类型，允许在单个字段中存储多个值。
  • 地理空间数据：通过 PostGIS 扩展，支持地理空间数据类型和操作。

高级查询功能

  • 窗口函数：支持复杂的窗口函数，用于排名、移动平均等分析计算。
  • 公用表表达式（CTE）：支持递归和非递归的公用表表达式，便于编写复杂查询。
  • 全文搜索：内置全文搜索功能，支持多种语言的全文检索。
  • 部分聚合和分组：支持 ROLLUP、CUBE 和 GROUPING SETS 等高级聚合操作。

数据完整性和并发控制

  • ACID 支持：确保事务的原子性、一致性、隔离性和持久性。
  • 多版本并发控制（MVCC）：支持高并发环境下的高效数据访问，无需锁定读操作。
  • 外键约束：支持外键、唯一约束和检查约束，确保数据完整性。

扩展性和灵活性

  • 扩展模块：可以通过扩展模块增加功能，如 PostGIS、PL/pgSQL、PL/Python 等。
  • 自定义类型和函数：允许用户定义自己的数据类型、函数、操作符等，满足特定业务需求。
  • 分区表：支持表分区，提高大表的查询和维护性能。

性能和优化

  • 高级索引：支持 B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN 等多种索引类型，适用于不同的查询场景。
  • 查询优化器：强大的查询优化器，自动选择最优的查询执行计划。
  • 并行查询：支持并行查询，提高大数据量下的查询性能。
  • 自动化维护：支持自动化的表清理（VACUUM）、统计信息收集和分析。

安全性

  • 身份验证：支持多种身份验证方法，包括密码、LDAP、Kerberos 和 GSSAPI 等。
  • 访问控制：基于角色和权限的细粒度访问控制。
  • 数据加密：支持传输层加密（SSL/TLS）和数据加密扩展。

高可用性和灾备

  • 复制和高可用性：支持多种复制方式，包括流复制、逻辑复制和同步复制，便于实现高可用性和灾难恢复。
  • 备份和恢复：提供丰富的备份和恢复工具，如 pg_basebackup 和 WAL 归档。

社区和支持

  • 开源社区：活跃的开源社区，定期发布新版本和安全更新。
  • 文档和资源：详细的官方文档和丰富的社区资源，包括教程、论坛和扩展库。

跨平台支持

  • 多平台支持：支持多种操作系统，包括 Linux、Windows 和 macOS。

Postgre vs. MySQL

MySQL 是一个纯粹的关系型数据库，但 PostgreSQL 是一个对象关系型数据库或ORDBMS，具有表继承和函数重载等功能。SQL，即结构化查询语言，是 PostgreSQL 和 MySQL 中与关系型数据库管理系统交互的通用语言。

由于 SQL 具有直接的结构和较少的源代码行，大多数非技术人员可以快速掌握它。SQL 消除了对分析人员的要求，即了解订单表在磁盘上的位置，如何进行搜索以找到一个特定的订单，或如何连接订单或客户表。查询是由数据库建立的，它也决定了适当的数据点。

这两种数据库都支持JSON--JavaScript 对象符号。PostgreSQL 数据库还提供 JSON 的二进制形式，删除重复的键和过多的空白--JSONB。除了更常规的援助渠道外，这两个数据库也提供了强大的社区支持。

GNU 通用公共许可证已被用于使 MySQL 数据库的源代码对所有人开放。同时，PostgreSQL 是在 PostgreSQL 许可证下提供的。PostgreSQL 是完全 ACID 兼容的，但 MySQL 只有在与 InnoDB 和 NDB 集群存储处理器结合时才是 ACID 兼容的。

MySQL 数据库与 SQL 只有弱的一致性。例如，不支持检查约束。另一方面，PostgreSQL 主要符合 SQL 标准。许多需要数据库进行简单数据传输的网络应用程序利用 MySQL。在复杂的网络中，读写数据库的性能是至关重要的，Postgres 经常被采用。

MySQL 是用 C/C++编写的，而 Postgres 是用 C 编写的。 MySQL 不支持级联，而 Postgres 支持。MySQL 使用 Workbench GUI 作为其用户界面，而 Postgres 使用 PgAdmin。MySQL 支持 SQL 语法和存储过程，而 PostgreSQL 支持高级过程和存储过程。

MySQL 允许的索引类型是二进制搜索树，而 Postgres 支持许多这种索引类型，包括 GIN 和 Hash。MySQL 使用传输层安全（TLS）协议来保证安全，而 Postgres 使用 SSL 协议。MySQL 数据库不支持高级数据类型，但 Postgres 允许高级数据类型，包括用户定义的数据类型。

尽管 PostgreSQL 和 MySQL 在概念上相似，但在实施它们之前需要考虑许多差异。

ACID 合规性

原子性、一致性、隔离和持久性（ACID）是数据库属性，可确保数据库即使在出现意外错误后，仍能保持有效状态。例如，如果您更新了大量行，但系统中途故障，则不应修改任何行。

只有将 MySQL 与 InnoDB 和 NDB 集群存储引擎或软件模块一起使用时，它才符合 ACID 标准。PostgreSQL 在所有配置中都完全兼容 ACID。

并发控制

多版本并发控制（MVCC）是一项高级数据库功能，可创建记录的重复副本，来安全地并行读取和更新相同的数据。使用 MVCC 时，多个用户可以同时读取和修改相同的数据，而不会影响数据完整性。

MySQL 数据库不提供 MVCC，但是 PostgreSQL 支持此功能。

索引

数据库使用索引来更快地检索数据。通过将数据库管理系统配置为以不同于其他数据的方式对经常访问的数据进行排序和存储，您可以为该数据建立索引。

MySQL 支持存储分层索引数据的 B 树和 R 树索引。PostgreSQL 索引类型包括树、表达式索引、部分索引和哈希索引。随着规模的扩展，还有更多选项可以微调数据库的性能要求。

数据类型

MySQL 是一个纯粹的关系数据库。而另一方面，PostgreSQL 是一个对象关系数据库。这意味着在 PostgreSQL 中，您可以将数据存储为具有属性的对象。对象是许多编程语言（如 Java 和 .NET）中的常见数据类型。对象支持诸如父-子关系和继承之类的范例。

对于数据库开发人员来说，使用 PostgreSQL 更加直观。PostgreSQL 还支持其他数据类型，例如数组和 XML。

视图

视图是数据库系统通过从多个表中拉取相关数据来创建的数据子集。

虽然 MySQL 支持视图，但 PostgreSQL 提供了高级视图选项。例如，您可以预先计算一些值（例如给定时段内所有订单的总价值），来创建实体化视图。实体化视图提高了复杂查询的数据库性能。

存储过程

存储过程是您可以预先编写和保存的结构化查询语言（SQL）查询或代码语句。您可以重复使用相同的代码，从而提高数据库管理任务的效率。

虽然 MySQL 和 PostgreSQL 都支持存储过程，但 PostgreSQL 允许您调用以 SQL 之外的语言所编写的存储过程。

触发器

触发器是数据库管理系统中发生相关事件时，自动运行的存储过程。

在 MySQL 数据库中，您只能为 SQL INSERT、UPDATE 和 DELETE 语句使用 AFTER 和 BEFORE 触发器。这意味着该过程将在用户修改数据之前或之后自动运行。相比之下，PostgreSQL 支持 INSTEAD OF 触发器，因此您可以使用函数运行复杂的 SQL 语句。

总结一下

PostgreSQL 和 MySQL 都是关系型数据库（RDBMS），但是 PostgreSQL 对大型企业级项目的支持比 MySQL 要更好，提供了很多在大型应用中的优化功能。MySQL 简单、纯粹，对 SQL 的处理最简单直接，不提供花里胡哨的额外功能。

并且，和 Prisma 这款 TypeScript ORM 框架配合的极好。Prisma 2 也是基于 PostgreSQL 的，因此在使用 Prisma 2 时，PostgreSQL 是一个更好的选择。

顺带一提，PostgreSQL 的市占率渐渐地超过 MySQL 成为最受欢迎的 RDBMS 了。

评论0