PostgreSQL 的一些你可能不知道但应该尝试的功能

PostgreSQL包含许多重要的功能。他们中的许多人都非常知名。其他人可以是非常有用的，但没有广泛赞赏。以下是我们首选的PostgreSQL功能，您可能没有仔细看过，但实际上应该这样做，因为它们可以帮助您更快地将代码投入生产，使操作更轻松，并且通常可以使用更少的代码和出汗来完成任务。

发布/订阅通知

PostgreSQL带有一个简单的非持久基于主题的发布 - 订阅通知系统。它不是Kafka，但功能确实支持常见用例。

关于特定主题的消息可以广播给正在监听该主题的所有连接的订阅者。这些消息被 Postgres服务器推送给侦听客户端。轮询不是必需的，但您的数据库驱动程序应支持异步向应用程序传递通知。

通知由主题名称和有效负载组成（最多约8000个字符）。有效载荷通常是一个JSON字符串，但它当然可以是任何东西。您可以使用 NOTIFY 命令发送通知：

NOTIFY'foo_events'，'{“userid”：42，“action”：“grok”}'

或者 pg_notify（）函数：

SELECT pg_notify（'foo_events'，'{“userid”：42，“action”：“grok”}'）;

订阅发生在 LISTEN 命令中，但通常您必须使用驱动程序特定的API。这里的围棋版本的例子。

假如有一张叫 “invoices（发票）” 的表。你现在想支持 “government invoices（政府发票）”，这种发票在原来的发票之上添加了一些字段。该如何建模？是在 invoices 表中添加若干可空字段，还是增加一个可空的 JSON 字段？不妨试试继承功能：

CREATE TABLE invoices (
    invoice_number   int  NOT NULL PRIMARY KEY,
    issued_on        date NOT NULL DEFAULT now()
);

CREATE TABLE government_invoices (
    department_id    text NOT NULL
) INHERITS (invoices);

上述模型反映出了政府发票就是发票，但比发票多一些属性的情况。上面的 “government_invoices” 表总共有 3 列：

test=# \d invoices
                  Table "public.invoices"
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 invoice_number | integer |           | not null |
 issued_on      | date    |           | not null | now()
Indexes:
    "invoices_pkey" PRIMARY KEY, btree (invoice_number)
Number of child tables: 1 (Use \d+ to list them.)

test=# \d government_invoices
            Table "public.government_invoices"
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 invoice_number | integer |           | not null |
 issued_on      | date    |           | not null | now()
 department_id  | text    |           | not null |
Inherits: invoices

为它添加数据行就跟独立表一样：

INSERT INTO invoices (invoice_number) VALUES (100);

INSERT INTO government_invoices
    (invoice_number, department_id) VALUES (101, 'DOD');

不过观察一下 SELECT 时的情况：

test=# SELECT * FROM government_invoices;
 invoice_number | issued_on  | department_id
----------------+------------+---------------
            101 | 2018-06-19 | DOD
(1 row)

test=# SELECT * FROM invoices;
 invoice_number | issued_on
----------------+------------
            100 | 2018-06-19
            101 | 2018-06-19
(2 rows)

子表添加的编号为 101 的发票，也父表中也列出来了。这样做的好处是在父表中进行的各种算法In完全以忽略子表的存在。

从这个文档可以了解到更多关于 PostgreSQL 继承方面的内容。

外部数据包装器

你知道你可以有一张虚表用来指向另一个PostgreSQL实例吗？或者另一个SQLite、MongoDB、Redis甚至其它的数据库？这个功能叫做外部数据包装器(FDW)，它提供一个标准化的方法来存取和操作连接到Postgres服务器的外部数据源。有各种各样的FDW实现让你可以连接到不同的数据源，它们通常被打包为扩展插件。

标准Postgres分发包中有一个postgres_fdw扩展，它可以让你连接到其它Postgres服务器。例如，你可以移动一张大表到其它服务器，同时在本地建立一张虚表(正确的术语叫做"外部表"):

-- install the extension (required only once)
CREATE EXTENSION postgres_fdw;

-- big_server is our big, remote server with the migrated table
CREATE SERVER big_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.1.2.3', dbname 'big_db');

-- create a user mapping for the app user
CREATE USER MAPPING FOR app_user SERVER big_server
  OPTIONS (user 'remote_user', password 'remote_pass');

-- and finally create the virtual table invoices -> big_db.public.invoices
CREATE FOREIGN TABLE invoices (
  invoice_num int NOT NULL PRIMARY KEY
  -- other columns omitted for brevity
) SERVER big_server;

这个 Wiki 有一个很好的列表列出了许多FDW的有效实现。

除了可以从其它服务器存取数据，FDW也被用作实现交互存储层，比如 cstore_fdw .

还有一个 dblink 扩展，它是另一种用来存取远程PostgreSQL数据的实现.

从版本 10 开始，PostgreSQL 原生支持将一个表拆分成多个子表，其拆分基于对一列或多列数据的计算来进行。这一功能可以让一个巨大的表在物理上存储于多个表中，改善DML性能和存储管理。

下面演示了如何创建拆分表，该演示会为每个月的数据增加一张表：

-- the parent table
CREATE TABLE invoices (
    invoice_number   int  NOT NULL,
    issued_on        date NOT NULL DEFAULT now()
) PARTITION BY RANGE (issued_on);
  
-- table for the month of May 2018
CREATE TABLE invoices_2018_05 PARTITION OF invoices
  FOR VALUES FROM ('2018-05-01') TO ('2018-06-01');

-- table for the month of June 2018
CREATE TABLE invoices_2018_06 PARTITION OF invoices
  FOR VALUES FROM ('2018-06-01') TO ('2018-07-01');

子表必须由人工或通过程序创建，这个创建过程不会自动发生。

你可以在父级表中查询或插入数据，PostgreSQL 会自动到子表中去进行操作，来看一下：

先插入两行数据：

test=# INSERT INTO invoices VALUES (10042, '2018-05-15');
INSERT 0 1
test=# INSERT INTO invoices VALUES (43029, '2018-06-15');
INSERT 0 1

可以看到数据实际被插入到了子表中：

test=# SELECT * FROM invoices_2018_05;
 invoice_number | issued_on
----------------+------------
          10042 | 2018-05-15
(1 row)

test=# SELECT * FROM invoices_2018_06;
 invoice_number | issued_on
----------------+------------
          43029 | 2018-06-15
(1 row)

但在父表中也可以完成查询，返回合并的结果：

test=# SELECT * FROM invoices;
 invoice_number | issued_on
----------------+------------
          10042 | 2018-05-15
          43029 | 2018-06-15
(2 rows)

拆分方法与继承相似（在父表级别查询），但也存在一些区别（比如在拆分父表中没有保存数据）。你可以在这个文档中阅读到更多相关内容。

已经进入 Beta 阶段的 PostgreSQL 11 对这一功能会有所改进，这篇文章对此进行了叙述。

区间类型

你以前与温度范围、日程表、价格区间或类似的数值范围打过交道吗？如果是，那你就会有这样的经验：看似简单的问题总会导致你抓耳挠腮并且经常深夜调试bug。以下是一个包含区间列的表和一些数值：

CREATE TABLE prices (
    item  text,
    price int4range -- int4range is a range of regular integers
);

INSERT INTO prices VALUES ('mouse',    '[10,16)');
INSERT INTO prices VALUES ('keyboard', '[20,31)');
INSERT INTO prices VALUES ('joystick', '[35,56)');

在错配方括号中的数值代表半开区间。以下是一个查询语句，它可以找出在价格区间15$~30$中的所有项，使用了&&操作符(区间交错):

test=# SELECT * FROM prices WHERE price && int4range(15,30);
   item   |  price
----------+---------
 mouse    | [10,16)
 keyboard | [20,31)
(2 rows)

为了让你印象深刻，你可以尝试一下使用无区间类型的查询语句有多难(试试就好)。

区间类型非常强大 --- 这里还有操作符、函数，你也可以定义你自己的区间类型，甚至还可以索引它们。

为了学习更多关于区间的知识，你可以看看这篇文章，还有这篇。

数组类型

PostgreSQL很久以前就已经支持数组类型了。数组类型可以精简应用代码并可以简化查询操作。以下是一个在表中使用数组列的例子：

CREATE TABLE posts (
    title text NOT NULL PRIMARY KEY,
    tags  text[]
);

假设每一行代表一篇博客，每篇博客又都有一个标签集，下面是我们如何列出所有带“postgres”和"go"标签的博客的代码：

test=# SELECT title, tags FROM posts WHERE '{"postgres", "go"}' <@ tags;
               title               |          tags
-----------------------------------+------------------------
 Writing PostgreSQL Triggers in Go | {postgres,triggers,go}
(1 row)

这里数组类型的使用使我们的数据模型更精确，同时也简化了查询操作。Postgres数组总是与操作符和函数一起出现，其中也包括集合函数。你也可以基于数组表达式创建索引。这里有一篇关于如何在Go语言中使用数组的文章。

触发器

当对表中的行进行插入、更新或删除操作时，你能请求PostgreSQL执行一个特殊的函数，这个函数甚至可以在插入过程中修改值。你可以点击这里了解更多关于触发器的信息。以下是一个例子：当创建用户时，触发器发出通知并写入稽核日志。

-- a table of users
CREATE TABLE users (
  username text NOT NULL PRIMARY KEY
);

-- an audit log
CREATE TABLE audit_log (
  at          timestamptz NOT NULL DEFAULT now(),
  description text NOT NULL
);

-- the actual function that is executed per insert
CREATE FUNCTION on_user_added() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    -- add an entry into the audit log
    INSERT INTO audit_log (description)
        VALUES ('new user created, username is ' || NEW.username);
    -- send a notification
    PERFORM pg_notify('usercreated', NEW.username);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- set the function as an insert trigger
CREATE TRIGGER on_user_added AFTER INSERT ON users
  FOR EACH ROW EXECUTE PROCEDURE on_user_added();

现在，如果你尝试增加一个新用户，一个稽核日志记录将会被自动添加。

test=# INSERT INTO users VALUES ('alice');
INSERT 0 1
test=# SELECT * FROM audit_log;
                at             |             description
-------------------------------+-------------------------------------
 2018-06-19 04:00:30.672947+00 | new user created, username is alice
(1 row)

pg_stat_statements

pg_stat_statements是一个扩展插件，默认在PostgreSQL分发包中就已经包含了，只是默认没有启用。这个扩展记录了每条执行语句的健康信息，包括执行时长、内存使用、磁盘IO初始化等。对于需要了解和调试查询性能的场景它是不可或缺的一个扩展。

安装和启用这个扩展的开销非常小，它也非常易于使用，因此没有理由不在你的生产server中使用这个扩展。

哈希， GIN 还有 BRIN 索引

PostgreSQL中默认的索引类型是B-Tree，有记录表示也有其他类型。其他索引类型在非常不常见的情况下非常有用。特别是设置散列，GIN和BRIN类型的索引可能只是解决您的性能问题：

散列：与具有固有排序的B树索引不同，散列索引是无序的，只能执行相等匹配（查找）。然而，散列索引占用更小的空间并且比平等匹配的B树更快。（另外，请注意，在PostgreSQL 10之前，不可能复制散列索引;它们未被记录。）
GIN：GIN是一个倒排索引，它基本上允许单个键的多个值。 GIN索引对索引数组，JSON，范围，全文搜索等非常有用。
BRIN：如果您的数据具有特定的自然顺序（例如时间序列数据），并且您的查询通常只适用于其中的一小部分范围，那么BRIN索引可以以很小的开销加快查询速度。 BRIN索引维护每个数据块的范围，允许优化器跳过包含不会被查询选中的行的块。

在这里开始阅读关于PostgreSQL索引类型。

全文本搜索

PostgreSQL也很好地支持全文本搜索，甚至支持除英语之外的语言。这里有一篇文章教你如何基于PostgreSQL用Go语言一步步创建一个全文本搜索查询。

发布/订阅通知

外部数据包装器

区间类型

数组类型

触发器

pg_stat_statements

全文本搜索

Recommend

选错专业悔四年，选错城市毁一生

GitHub - Licoy/A-Joke: A-Joke { 一个笑话，目的就是想让你在工作之余能够博君一笑...

GitHub - perplexingpegasus/ProGAN

【车主停地库的奥迪被多次开走陌生人与其错峰用车】据杨某交代，当时他正处于求职碰...

德国小组垫底惨遭淘汰、西班牙 1/8 决赛被俄罗斯点球淘汰，传控的威力为何大不如前？...

Google 向 KaiOS 投资 2200 万美元

今天继续加仓，还有谁？

长痘痘的那脸原来那么脏！你有痘，你先看…

COLING 2018 最佳论文解读：序列标注经典模型复现

反式(p-i-n)钙钛矿太阳能电池里程碑，Voc 1.21 V

About Joyk