好文翻譯丨PostgreSQL 的一些你可能不知道但應該嘗試的功能

知識 07-03

歡迎下載開源中國APP獲取更多優質文章

參與翻譯 (5人) : Rhys_Lee, xiaoaiwhc1, 邊城, ljwheyxy, lnovonl

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 列：

為它添加數據行就跟獨立表一樣：

INSERT INTO invoices (invoice_number) VALUES (100);
INSERT INTO government_invoices
(invoice_number, department_id) VALUES (101, "DOD");

不過觀察一下 SELECT 時的情況：

子表添加的編號為 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語言一步步創建一個全文本搜索查詢。

對於技術達人來說，廣納知識點是進步的源泉。通過閱讀技術文章我們可以學到業務技能，也能了解行業動態。開源中國翻譯頻道旨在每天為用戶推薦並翻譯優質的外網文章。再也不用怕因為英語不過關，被擋在許多技術文章的門外。歡迎點擊「了解更多」，閱讀原文。

好文翻譯丨PostgreSQL 的一些你可能不知道但應該嘗試的功能

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！

請您繼續閱讀更多來自 OSC開源社區 的精彩文章:

※PYPL 6月IDE指數榜：IntelliJ追上SublimeText，PyCharm反超Xcode
※從 Stack Overflow 2018 調查報告看機器學習發展趨勢

TAG:OSC開源社區 |