系统设计中的PostgreSQL（详细指南）

这篇文章的目的是：

• 简要介绍一下PostgreSQL的关系型数据库管理系统
• 提供一些与其他替代品（如MySQL）的简要比较
• 说明PostgreSQL的有用功能
• 讨论PostgreSQL在某些系统中被证明是更有用的。

那么，什么是PostgreSQL？

要谈这个，我们首先要谈SQL和关系型数据库管理系统。

SQL和关系型数据库管理系统

什么是SQL？

SQL是结构化查询语言的缩写。它是一种通过数据存储、检索和修改与数据库进行交互的语言标准。SQL语言有一套标准函数，如SELECT、INSERT和DELETE。

那么，我们如何使用SQL与数据进行交互？或者在数据库中创建表格？

我们使用关系型数据库管理系统。

关系数据库管理系统（RDBMS）是一个管理和修改数据库中表的关系的软件。换句话说，它是应用程序和数据库之间的中介软件，用于写入、读取和修改组织在表中的数据。

PostgreSQL

PostgreSQL是RDBMS的一种类型。它的文档宣称有许多功能，如：

• 支持通常的数据类型（即TEXT、INTEGER等），以及更高级的数据类型，如数组和自定义数据类型。
• 对象关系原则，包括从表继承特性的能力。
• 除了关系型查询外，还支持NoSQL查询，即JSON查询。
• 支持多版本并发控制（MVCC），保证读取数据与写入数据不冲突，甚至在并发操作之间。
• 可扩展性，包括定义你自己的函数的能力。

PostgreSQL与其他RDBMS的比较

如果你正在阅读这篇文章，你可能已经熟悉了其他关系型数据库管理系统，如MySQL、SQL Server等。那么，PostgreSQL与其他数据库管理系统的不同之处是什么？

首先，PostgreSQL是免费和开源的。例如，微软的SQL Server和Oracle DB是闭源的，需要商业许可。PostgreSQL还提供对高级数据结构的支持，如数组。此外，PostgreSQL似乎是少数提供非关系型查询支持的关系型数据库管理系统之一。

此外，由于其对象-关系原则的性质，PostgreSQL还包含许多有用的面向对象的功能，如一个表能够继承另一个表。这使得任何程序员都可以很容易地，例如，从父表创建一个子表，而不需要费力地创建近似的表。例如，这个功能在MySQL中是不可用的。

PostgreSQL的功能实例

那么，程序员可以通过什么方式来利用其中的一些功能呢？

在这里，我们将看一下两个特别强大的特性：继承和用户定义类型。

• 继承

在Java和Python这样的编程语言中，程序员可能会发现让一些类继承其他类的属性是很直观的。例如，子类 "Laptop "可以从超类 "Computer "继承方法，或者类似的东西。

PostgreSQL的继承性采用了这种直觉，并将其应用于数据库编程。例如，假设你想存储一个计算机产品的目录，以及它们的细节。我们知道，笔记本电脑会有与其他电脑类似的字段，如价格和操作系统。然而，笔记本电脑还有一些必要的字段需要考虑，比如它的电池寿命。

如果没有继承，你会有下面的情况：

-- create
CREATE TABLE computers (
  id INTEGER PRIMARY KEY, -- product ID
  name TEXT NOT NULL, -- name of product
  price DOUBLE PRECISION NOT NULL, -- price of product
  os TEXT NOT NULL -- operating system
);

CREATE TABLE laptops (
  id INTEGER PRIMARY KEY, -- product ID
  name TEXT NOT NULL, -- name of product
  price DOUBLE PRECISION NOT NULL, -- price of product
  os TEXT NOT NULL, -- operating system
  life INTEGER NOT NULL -- battery life (hours)
);

-- insert
-- Dell Optiplex, $459.99, Windows OS
INSERT INTO computers VALUES(1, 'Dell Optiplex', 459.99, 'Windows');
-- Lenovo Ideapad, $359.99, Windows OS, 9 hour battery
INSERT INTO laptops VALUES(2, 'Lenovo Ideapad', 359.99, 'Windows', 9);
-- Macbook Air, $999.99, Apple OS, 14 hour battery
INSERT INTO laptops VALUES(3, 'Macbook Air', 999.99, 'Apple OS', 14);
-- iMac, $1,399.99, Apple OS
INSERT INTO computers VALUES(4, 'iMac', 1399.99, 'Apple OS');

-- retrieve
SELECT id, name, price FROM computers WHERE price < 500
  UNION
SELECT id, name, price FROM laptops WHERE price < 500;

即使只是看一下表的创建，语句也会变得很乱，因为有重复的列名。此外，假设你想找到所有价格低于500美元的电脑，不管是不是笔记本电脑。标记为 "检索 "的行也开始变得复杂，其特点是UNION子句。输出结果是我们所期望的：

 id |      name      | price  
----+----------------+--------
  2 | Lenovo Ideapad | 359.99
  1 | Dell Optiplex  | 459.99

问题是，如果有更多的表可以直观地继承另一个表的属性呢？比如说，如果我们假设的计算机商店扩展到一般的电子产品呢？那么我们就会有诸如 "电视"、"笔记本电脑"、"台式机"、"电话"、"平板电脑 "等子表，如此等等，这就需要使用更多的UNION语句!

继承使这个过程更简单。由于我们直觉上说笔记本电脑继承于计算机，我们可以按如下方式构建我们的SQL:

-- create
CREATE TABLE computers (
  id INTEGER PRIMARY KEY, -- product ID
  name TEXT NOT NULL, -- name of product
  price DOUBLE PRECISION NOT NULL, -- price of product
  os TEXT NOT NULL -- operating system
);

CREATE TABLE laptops (
  life INTEGER NOT NULL -- battery life (hours)
) INHERITS (computers);

-- insert
-- Dell Optiplex, $459.99, Windows OS
INSERT INTO computers VALUES(1, 'Dell Optiplex', 459.99, 'Windows');
-- Lenovo Ideapad, $359.99, Windows OS, 9 hour battery
INSERT INTO laptops VALUES(2, 'Lenovo Ideapad', 359.99, 'Windows', 9);
-- Macbook Air, $999.99, Apple OS, 14 hour battery
INSERT INTO laptops VALUES(3, 'Macbook Air', 999.99, 'Apple OS', 14);
-- iMac, $1,399.99, Apple OS
INSERT INTO computers VALUES(4, 'iMac', 1399.99, 'Apple OS');

-- retrieve
SELECT id, name, price FROM computers WHERE price < 500;
SELECT id, name, price FROM ONLY computers WHERE price < 500;

不仅创建表的代码看起来更简洁，而且我们可以在一个SQL语句中检索到500美元以下的笔记本电脑和计算机型号:

 id |      name      | price  
----+----------------+--------
  1 | Dell Optiplex  | 459.99
  2 | Lenovo Ideapad | 359.99
(2 rows)

此外，我们现在可以用 "ONLY "子句来配对我们的语句，这样我们就可以只在父表中查找而不考虑笔记本电脑。例如，如果我们只想要价格低于500美元的非笔记本电脑，我们可以运行上面代码中的最后一条语句，结果如下:

 id |     name      | price  
----+---------------+--------
  1 | Dell Optiplex | 459.99
(1 row)

• 灵活的数据选项

此外，PostgreSQL定义数据类型的能力使该系统对需要存储和查询新种类数据的程序员非常有用。PostgreSQL的用户定义类型的文档页面提供了复数作为用户定义类型的例子:

-- complex number --> a + (b)i
CREATE TYPE complex AS (
  a DOUBLE PRECISION,
  b DOUBLE PRECISION
);

-- results from some experiment
CREATE TABLE results (
  trial INTEGER,
  result COMPLEX
);

INSERT INTO results VALUES(1, (4, -9));
INSERT INTO results VALUES(2, (3, 24));
INSERT INTO results VALUES(3, (6, -12));

SELECT * FROM results;

有输出:

 trial | result  
-------+---------
     1 | (4,-9)
     2 | (3,24)
     3 | (6,-12)
(3 rows)

因此，人们可以看到PostgreSQL在需要对复杂数据类型进行数据处理的科学或统计研究中的作用。

然而，不是所有的程序员都可能发现PostgreSQL适合在他们的应用中使用。由于PostgreSQL的功能较多，它的学习曲线可能比其他一些关系型数据库管理系统（如MySQL）更陡峭。再加上其繁琐的安装过程，使学习曲线更加陡峭。

系统使用案例

那么，PostgreSQL最适合什么样的系统？

我们可以通过看一下Stackshare来得到提示，这个网站展示了一些大公司使用的技术。一些被引用的已经使用（或正在使用）PostgreSQL的公司包括大公司:

• Instagram
• Spotify
• Instacart
• Twitch
• Uber

例如，Instagram多年前曾写过一篇文章，介绍PostgreSQL如何用于分片数据库（即在多个服务器和数据库中分割数据）。

我们的分片系统由几千个 "逻辑 "分片组成，这些分片在代码中被映射到少得多的物理分片。使用这种方法，我们可以从几个数据库服务器开始，并最终转移到更多的数据库，只需将一组逻辑分片从一个数据库转移到另一个，而无需重新装入任何数据。我们使用了Postgres的模式功能，使之易于编写和管理。模式（不要与单个表的SQL模式相混淆）是Postgres的一个逻辑分组功能。每个Postgres数据库可以有几个模式，每个模式可以包含一个或多个表。表的名字必须是每个模式唯一的，而不是每个DB唯一的，默认情况下，Postgres把所有的东西都放在一个名为 "公共 "的模式中。

可以看出，Instagram将PostgreSQL作为其横向扩展数据库的解决方案的一个组成部分。

同样，Spotify在横向扩展他们的数据库时也使用了PostgreSQL和Cassandra（一种NoSQL数据库管理软件）。它指出:

如果功能的[即播放列表的创建、追随者列表等]数据需要分区，那么小队必须在他们的服务中自己实现分片，然而许多服务依靠Cassandra在站点之间做数据的完全复制。建立一个完整的存储集群，并在站点之间进行复制和故障转移是很复杂的，所以我们正在建立基础设施，以设置和维护多站点的Cassandra或postgreSQL集群作为一个单元。

从中可以得到什么启示？PostgreSQL对于任何想要将数据分布在多个服务器上以提高交易性能的服务来说都是有用的，比如依赖数据库分片的系统。当然，这包括Instagram等社交媒体应用或Spotify等娱乐应用。

值得注意的是，在Stackshare列出的使用过PostgreSQL的公司中，Uber从PostgreSQL转向了MySQL。他们这样做的最大原因之一是，PostgreSQL的架构在处理数据库复制方面效率不高。相比之下，他们指出，MySQL更适合这项任务。

在MySQL中，只有主索引有一个指向行的磁盘偏移量的指针。当涉及到复制时，这有一个重要的后果。MySQL的复制流只需要包含关于行的逻辑更新的信息。复制更新的种类是 "将X行的时间戳从T_1改为T_2"。复制会自动推断出由于这些语句的结果而需要进行的任何索引更改。

相比之下，Postgres复制流包含物理变化，例如 "在磁盘偏移量8,382,491，写入字节XYZ"。在Postgres中，对磁盘的每一个物理变化都需要包括在WAL流中。小的逻辑变化（比如更新一个时间戳）需要在磁盘上做许多改变。Postgres必须插入新的元组并更新所有的索引以指向该元组。因此，许多变化将被放到WAL流中。这种设计差异意味着MySQL复制的二进制日志明显比PostgreSQL的WAL流更紧凑。

然而，Uber并不是唯一一家承认在数据库复制中使用PostgreSQL的弊端的公司。

有趣的是，Twitch，一家使用PostgreSQL作为其后台架构的一部分的公司，也写到了PostgreSQL在数据库复制方面的低效方法。Twitch指出，虽然PostgreSQL确实提供了数据库复制的能力，但其复制的方法却带来了一些问题。

PostgreSQL的多版本并发控制（MVCC）的故事有点弱。由于存储的工作方式，也许还有其他原因，复制必须与磁盘上的主站完全一致。我们曾经看到很多错误的消息，canceling statement due to conflict with recovery ，而不是得到一个结果集。自从在配置中积极限制每个角色的statement_timeout 和setting hot_standby_feedback = on ，这已经不是什么问题了。

可以看出，依赖数据库复制的应用（即维护和制作关键数据的副本，如金融交易）可能会发现不同的数据库管理系统，如MySQL，更有用。这种应用可能包括金融交易系统（即银行系统）或其他基于金融的服务（即Uber）。

结论

因此，我们已经讨论了以下内容:

• PostgreSQL是众多关系型数据库管理系统中的一种。
• PostgresQL具有许多方便的功能，如表的继承、用户定义的类型和多版本并发控制。
• PostgreSQL的许多功能可以用来优化许多大型和复杂的操作，但可能对数据库程序员构成一个陡峭的学习曲线。
• 对于那些依赖在服务器上分布数据的系统（即数据库分片），PostgreSQL通常是首选的技术。
• 对于依赖在服务器间复制和存储数据的系统（即数据库复制），PostgreSQL可能不是最好的技术。