|
|
|
|
移动端

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

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

作者:佚名来源:开源中国|2018-07-03 14:59

【新品产上线啦】51CTO播客,随时随地,碎片化学习

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

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

发布/订阅通知

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

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

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

  1. NOTIFY'foo_events''{“userid”:42,“action”:“grok”}' 

或者 pg_notify() 函数:

  1. SELECT pg_notify('foo_events''{“userid”:42,“action”:“grok”}'); 

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

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

  1. CREATE TABLE invoices ( 
  2.     invoice_number   int  NOT NULL PRIMARY KEY
  3.     issued_on        date NOT NULL DEFAULT now() 
  4. ); 
  5.  
  6. CREATE TABLE government_invoices ( 
  7.     department_id    text NOT NULL 
  8. ) INHERITS (invoices); 

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

  1. test=# \d invoices 
  2.                   Table "public.invoices" 
  3.      Column     |  Type   | Collation | Nullable | Default 
  4. ----------------+---------+-----------+----------+--------- 
  5.  invoice_number | integer |           | not null | 
  6.  issued_on      | date    |           | not null | now() 
  7. Indexes: 
  8.     "invoices_pkey" PRIMARY KEY, btree (invoice_number) 
  9. Number of child tables: 1 (Use \d+ to list them.) 
  10.  
  11. test=# \d government_invoices 
  12.             Table "public.government_invoices" 
  13.      Column     |  Type   | Collation | Nullable | Default 
  14. ----------------+---------+-----------+----------+--------- 
  15.  invoice_number | integer |           | not null | 
  16.  issued_on      | date    |           | not null | now() 
  17.  department_id  | text    |           | not null | 
  18. Inherits: invoices 

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

  1. INSERT INTO invoices (invoice_number) VALUES (100); 
  2.  
  3. INSERT INTO government_invoices 
  4.     (invoice_number, department_id) VALUES (101, 'DOD'); 

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

  1. test=# SELECT * FROM government_invoices; 
  2.  invoice_number | issued_on  | department_id 
  3. ----------------+------------+--------------- 
  4.             101 | 2018-06-19 | DOD 
  5. (1 row) 
  6.  
  7. test=# SELECT * FROM invoices; 
  8.  invoice_number | issued_on 
  9. ----------------+------------ 
  10.             100 | 2018-06-19 
  11.             101 | 2018-06-19 
  12. (2 rows

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

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

外部数据包装器

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

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

  1. -- install the extension (required only once) 
  2. CREATE EXTENSION postgres_fdw; 
  3.  
  4. -- big_server is our big, remote server with the migrated table 
  5. CREATE SERVER big_server FOREIGN DATA WRAPPER postgres_fdw 
  6.   OPTIONS (host '10.1.2.3', dbname 'big_db'); 
  7.  
  8. -- create a user mapping for the app user 
  9. CREATE USER MAPPING FOR app_user SERVER big_server 
  10.   OPTIONS (user 'remote_user'password 'remote_pass'); 
  11.  
  12. -- and finally create the virtual table invoices -> big_db.public.invoices 
  13. CREATE FOREIGN TABLE invoices ( 
  14.   invoice_num int NOT NULL PRIMARY KEY 
  15.   -- other columns omitted for brevity 
  16. ) SERVER big_server; 

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

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

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

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

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

  1. -- the parent table 
  2. CREATE TABLE invoices ( 
  3.     invoice_number   int  NOT NULL
  4.     issued_on        date NOT NULL DEFAULT now() 
  5. ) PARTITION BY RANGE (issued_on); 
  6.    
  7. -- table for the month of May 2018 
  8. CREATE TABLE invoices_2018_05 PARTITION OF invoices 
  9.   FOR VALUES FROM ('2018-05-01'TO ('2018-06-01'); 
  10.  
  11. -- table for the month of June 2018 
  12. CREATE TABLE invoices_2018_06 PARTITION OF invoices 
  13.   FOR VALUES FROM ('2018-06-01'TO ('2018-07-01'); 

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

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

先插入两行数据:

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

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

  1. test=# SELECT * FROM invoices_2018_05; 
  2.  invoice_number | issued_on 
  3. ----------------+------------ 
  4.           10042 | 2018-05-15 
  5. (1 row) 
  6.  
  7. test=# SELECT * FROM invoices_2018_06; 
  8.  invoice_number | issued_on 
  9. ----------------+------------ 
  10.           43029 | 2018-06-15 
  11. (1 row) 

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

  1. test=# SELECT * FROM invoices; 
  2.  invoice_number | issued_on 
  3. ----------------+------------ 
  4.           10042 | 2018-05-15 
  5.           43029 | 2018-06-15 
  6. (2 rows

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

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

区间类型

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

  1. CREATE TABLE prices ( 
  2.     item  text, 
  3.     price int4range -- int4range is a range of regular integers 
  4. ); 
  5.  
  6. INSERT INTO prices VALUES ('mouse',    '[10,16)'); 
  7. INSERT INTO prices VALUES ('keyboard''[20,31)'); 
  8. INSERT INTO prices VALUES ('joystick''[35,56)'); 

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

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

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

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

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

数组类型

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

  1. CREATE TABLE posts ( 
  2.     title text NOT NULL PRIMARY KEY
  3.     tags  text[] 
  4. ); 

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

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

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

触发器

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

  1. -- a table of users 
  2. CREATE TABLE users ( 
  3.   username text NOT NULL PRIMARY KEY 
  4. ); 
  5.  
  6. -- an audit log 
  7. CREATE TABLE audit_log ( 
  8.   at          timestamptz NOT NULL DEFAULT now(), 
  9.   description text NOT NULL 
  10. ); 
  11.  
  12. -- the actual function that is executed per insert 
  13. CREATE FUNCTION on_user_added() RETURNS TRIGGER AS $$ 
  14. BEGIN 
  15.   IF (TG_OP = 'INSERT'THEN 
  16.     -- add an entry into the audit log 
  17.     INSERT INTO audit_log (description) 
  18.         VALUES ('new user created, username is ' || NEW.username); 
  19.     -- send a notification 
  20.     PERFORM pg_notify('usercreated', NEW.username); 
  21.   END IF; 
  22.   RETURN NULL
  23. END
  24. $$ LANGUAGE plpgsql; 
  25.  
  26. -- set the function as an insert trigger 
  27. CREATE TRIGGER on_user_added AFTER INSERT ON users 
  28.   FOR EACH ROW EXECUTE PROCEDURE on_user_added(); 

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

  1. test=# INSERT INTO users VALUES ('alice'); 
  2. INSERT 0 1 
  3. test=# SELECT * FROM audit_log; 
  4.                 at             |             description 
  5. -------------------------------+------------------------------------- 
  6.  2018-06-19 04:00:30.672947+00 | new user created, username is alice 
  7. (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语言一步步创建一个全文本搜索查询。

【编辑推荐】

  1. PgSQL 主要贡献者对 PostgreSQL 内置分片功能的看法
  2. PostgreSQL查询优化器详解(逻辑优化篇)
  3. PostgreSQL查询优化器详解(物理优化篇)
  4. 问题来了,PostgreSQL的好处都有啥?
  5. 7 月全球数据库排名:MongoDB 紧追 PostgreSQL
【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

SOA 原理•方法•实践

本书并不是关于Web服务的又一本开发手册,抑或是开发技术的宝典之类的读物。本书的作者来自于IBM软件开发中心的SOA技术中心,作为最早的一...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊