知識星球來自：開源中國社群
連結：https://www.oschina.net/translate/postgres-features
原文：https://pgdash.io/blog/postgres-features.html
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"}'
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語言一步步建立一個全文字搜尋查詢。
●編號365，輸入編號直達本文
●輸入m獲取文章目錄
Web開發
更多推薦《18個技術類公眾微信》
涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。
