这个杀手不太冷里昂的盆栽是什么:揭秘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)

拆分方法與繼承相似（在父表級別查詢），但也存在一些區別（比如在拆分父表中沒有保存數據）。

區間類型

你以前與溫度范圍、日程表、價格區間或類似的數值范圍打過交道嗎？如果是，那你就會有這樣的經驗：看似簡單的問題總會導致你抓耳撓腮并且經常深夜調試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數組總是與操作符和函數一起出現，其中也包括集合函數。你也可以基于數組表達式創建索引。

觸發器

當對表中的行進行插入、更新或刪除操作時，你能請求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類型能解決你的性能問題：

A散列

與具有固有排序的B樹索引不同，散列索引是無序的，只能執行相等匹配（查找）。然而，散列索引占用更小的空間并且比平等匹配的B樹更快。（另外，請注意，在PostgreSQL 10之前，不可能復制散列索引;它們未被記錄。）

BGIN

GIN是一個倒排索引，它基本上允許單個鍵的多個值。 GIN索引對索引數組，JSON，范圍，全文搜索等非常有用。

CBRIN

如果您的數據具有特定的自然順序（例如時間序列數據），并且您的查詢通常只適用于其中的一小部分范圍，那么BRIN索引可以以很小的開銷加快查詢速度。 BRIN索引維護每個數據塊的范圍，允許優化器跳過包含不會被查詢選中的行的塊。

往期干貨推薦