10个PostgreSQL不应错过的功能 - pgDash

栏目: 数据库 · PostgreSQL · 发布时间: 5年前

内容简介：PostgreSQL包含许多强大的功能，其中许多都非常有名，但是其他可能非常有用，但没有得到广泛认可。以下是我们可能没有见过的几个优秀PostgreSQL功能，它们可以帮助您更快地将代码投入生产，使运维变得更容易，并且通常可以用更少的代码和汗水来完成工作。PostgreSQL附带一个简单的非持久的基于主题的发布 - 订阅通知系统。它不是Kafka，但功能确实能支持常见用例。

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"}');

表继承

假设有一个名为“发票invoice”的表。现在要支持“政府发票”，这些都是发票，但还有一些额外的字段。你会如何建模这个？不是将可为空的列添加到发票中，也不是将单个可空的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也列在父表中。这有助于算法只针对在父发票表上发票进行计算，可忽略任何子表。

您可以在文档中了解有关PostgreSQL中表继承的更多信息。

外部数据包装

(像ORACL的dblink)可以拥有来自另一个PostgreSQL实例的数据的虚拟表，甚至是 SQLite 文件，MongoDB，Redis等等，此功能称为外部数据包装器，它提供了一种标准化方法来访问和操作存储在您连接到的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)

分区在某些方面类似于继承（在父级别进行查询），但也存在差异（例如，分区父级不包含数据）。您可以在文档中阅读有关分区的更多信息。

目前处于测试阶段的PostgreSQL 11改进了这一功能。本文概述了这些改进。

Range类型

你曾经使用温度范围，日历安排，价格范围等？间隔Range工作是一种看似简单的事情，可以轻柔地引导你进入深夜慢慢调试。

这是一个包含Range列的表，以及一些值：

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)');

不匹配括号中的数字表示half-open间隔。以下是使用&&运算符（范围重叠）查找价格范围$ 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发行版中默认存在的扩展名，但未启用。此扩展记录了有关每个执行语句的大量信息，包括所用的时间，使用的内存和启动的磁盘I / O. 它对于理解和调试查询性能问题是不可或缺的。

安装和启用此扩展的开销很小，使用起来非常简单，没有理由不在生产服务器上运行它！阅读文档了解更多信息。

哈希，GIN和BRIN索引

PostgreSQL中的默认索引类型是B-Tree，但也有其他类型，看这里记录。其他索引类型在实际上并不罕见的情况下非常有用。特别是，设置散列，GIN和BRIN类型的索引可能只是解决您的性能问题的方法：

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

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

全文检索

PostgreSQL可以很好地进行全文搜索，包括英语以外的语言。这里有一个文章引导您完成创建过的Postgres在 Go 全文查询搜索应用程序的步骤。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

Head First HTML5 Programming

Eric Freeman、Elisabeth Robson / O'Reilly Media / 2011-10-18 / USD 49.99

What can HTML5 do for you? If you're a web developer looking to use this new version of HTML, you might be wondering how much has really changed. Head First HTML5 Programming introduces the key featur......一起来看看《Head First HTML5 Programming》这本书的介绍吧!

码农工具

图片转BASE64编码

在线图片转Base64编码工具

HSV CMYK 转换工具

HSV CMYK互换工具