PostgreSQL performance tips you have never seen before - 筆記

發表於 更新於 分類於 Disqus： 文章字數： 6.5k 所需閱讀時間 ≈ 12 分鐘

幾天前看到了這個影片，是有名的 CYBERTEC 公司的 CEO 在 Citus Con 的演講。CYBERTEC 公司寫的有關 PostgreSQL 的相關技術文章品質是很高的，值得收藏看。

這個影片主要是分享不常見的 performance tips for PostgreSQL，所以我也來筆記一下。

Network socket VS Unix Socket

這個算是基本的知識，作者之所以帶出來這個是要告訴大家 network latency 對於 performance 的影響是很重大的！常常有可以 query 來回慢，並不是因為 query 寫的不好很有可能是 network latency 的原因。這就是為什麼會有 batch insert 的概念，因為可以省下多次的 network round trip。

而作者用一個例子來說明 network latency 對於 performance 的差別：

假設有個簡單的 query 如下：

1
2
cat /tmp/query.sql
SELECT 1;

接著用 pgbench 去跑：

1
pgbench -c 10 -T 10 -j 10 test -h localhost -f /tmp/query.sql

這些參數代表的意思是：

  1. -c：幾個 clients，意思就是會開幾個 connection
  2. -T：benchmark total 時間為幾秒
  3. -j：pgbench 會開多少的 worker threads，clients 會分散在不同的 thread 去跑
  4. -h：postgres server host name

結果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
pgbench (16rc1)
starting vacuum...pgbench: error: ERROR:  relation "pgbench_branches" does not exist
pgbench: detail: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_tellers" does not exist
pgbench: detail: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_history" does not exist
pgbench: detail: (ignoring this error and continuing anyway)
end.
transaction type: /tmp/query.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 1180172
number of failed transactions: 0 (0.000%)
latency average = 0.085 ms
initial connection time = 16.167 ms
tps = 118151.609271 (without initial connection time)

可以看到 tps 是 11K 左右在我的 Macbook M2 Pro 上。

如果將 -h 選項拿掉，再重新 benchmark：

1
pgbench -c 10 -T 10 -j 10 test -f /tmp/query.sql

結果會是：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
pgbench (16rc1)
starting vacuum...pgbench: error: ERROR:  relation "pgbench_branches" does not exist
pgbench: detail: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_tellers" does not exist
pgbench: detail: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_history" does not exist
pgbench: detail: (ignoring this error and continuing anyway)
end.
transaction type: /tmp/query.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 2078638
number of failed transactions: 0 (0.000%)
latency average = 0.048 ms
initial connection time = 15.403 ms
tps = 208093.535263 (without initial connection time)

tps 多了快兩倍。

原因在於 localhost 是需要經過網路協定，tcp 連線、握手等步驟，但 unix sockets 是不需要的，只需要在同一個主機下從另一個 process 傳輸到另一個 process 的 IPC 機制。

在 localhost 就會這樣的 overhead，可想而知如果是從外網進來，overhead 又會提升。

Storing Data

Column Alignment

在 PG 開 table 時，column 的順序是有可能會影響 table size 的。

例如：

1
2
3
4
5
6
7
8
CREATE TABLE t_test (
	v1 varchar(100), 1,
  i1 int,
  v2 varchar(100),
  i2 int,
  v3 varchar(100),
  i3 int
)

用 varchar type 跟 int type 來開欄位，並且交錯的順序。

接著 insert 大量資料進去：

1
INSERT INTO t_test SELECT 'abcd', 10, 'abcd', 20, 'abcd', 30 FROM generate_series(1, 1000000);

接著 select table size

1
2
3
4
5
test=# SELECT pg_size_pretty(pg_relation_size('t_test'));
 pg_size_pretty 
----------------
 65 MB
(1 row)

如果我們將 column 順序調換一下，將 int type 的 column 都往前放：

1
2
3
4
5
6
7
8
9
CREATE TABLE t_test_2 (
	i1 int,
  i2 int,
  i3 int,
  v1 varchar(100),
  v2 varchar(100),
  v3 varchar(100)
)
INSERT INTO t_test_2 SELECT 10, 20, 30, 'abcd', 'abcd', 'abcd' FROM generate_series(1, 1000000);

可以發現 table size 會變小：

1
2
3
4
5
test=# SELECT pg_size_pretty(pg_relation_size('t_test_2'));
 pg_size_pretty 
----------------
 57 MB
(1 row)

原因在於 alignment。

因為 CPU 再拿資料的時候，以現在都是 64 bit 來看，CPU 一次可以抓 64 bits 的資料也就是 8 bytes，而相對的 memory 也會以 8 bytes 來將對應資料存在 memory address 上。因此如果 CPU 只需要從 8 bytes 就能拿到想要的資料就會更快。

回過頭來說，這就是 Postgres 內部在存 tuple 的時候，會根據 table 上所定義的 column type 及順序去存，但是不同的 column type 會有其對齊的長度。

那為什麼 t_test_ 2 的大小會比較小呢？原因在於前面三個 int，可以自然對齊，而不需要有 padding 機制。少了 padding 佔的空間自然就變小囉。

所以設計 db schema 時是可以考慮這一點的。

default value

在 Postgres 中如果 Create table 之後要加入新欄位並且想要有 default value，從 PG 11 之後就不需要 rewrite 整個 table，而是先將 default value 存在 system catalog 上。這也算是老生常談啦，常做 DDL change 就應該知道這點。

作者這邊是想表達，如果你今天先 insert 大量資料且同時又有添加新 column 的需求，對於既有的 table，你可以先 insert，在做新 column 的添加與 default value，而不要直接 insert default value 之類的。

這樣你就可以省下一些空間。

來看個例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE t_small (
	i1 int,
  i2 int
);
CREATE TABLE t_fat (
	i1 int,
  i2 int,
  i3 int
);

INSERT INTO t_small SELECT random() * 10000, random() * 10000 FROM generate_series(1, 10000000);

INSERT INTO t_fat SELECT random() * 10000, random() * 10000, 0 FROM generate_series(1, 10000000);

ALTER TABLE t_small ADD COLUMN i3 int DEFAULT 0;

t_small 透過 ADD COLUMN … DEFAULT 的方式添加，而 t_fat 則是透過 insert default value。

可以看到兩邊的 table size 的差距：

1
2
3
4
5
6
7
test=# \d+
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method |  Size  | Description 
--------+----------+-------+-------+-------------+---------------+--------+-------------
 public | t_fat    | table | kenny | permanent   | heap          | 422 MB | 
 public | t_small  | table | kenny | permanent   | heap          | 346 MB |

Index

在建立 index 的時候通常會需要很多空間，作者提出例子如果是 index email 欄位，因為 email 本身的性質關係通常都會佔不少空間，可以使用 functional index 的方式來減少空間：

1
2
3
4
5
6
CREATE TABLE t_email AS 
SELECT 'somewonderful_long_email' || id || '@whatever_is_really_long.com' AS email
FROM generate_series(1, 10000000) AS id;

CREATE INDEX ON t_email (email);
CREATE INDEX ON t_email (hashtext(email));

這邊就可以看出 index 大小差別了

1
2
3
4
5
6
7
test=# \di+ t_email*
                                              List of relations
 Schema |         Name         | Type  | Owner |  Table  | Persistence | Access method |  Size  | Description 
--------+----------------------+-------+-------+---------+-------------+---------------+--------+-------------
 public | t_email_email_idx    | index | kenny | t_email | permanent   | btree         | 825 MB | 
 public | t_email_hashtext_idx | index | kenny | t_email | permanent   | btree         | 214 MB | 
(2 rows)

除了減少空間，更重要的是這樣的空間是有機會可以塞到 memory 上的而不用去 disk 讀，因此效率通常會是比較好的。

當然，如果這樣做的話，你的 query 勢必就要這樣改：

1
2
3
4
5
SELECT * FROM t_email WHERE email = '[email protected]';

v.s

SELECT * FROM t_email WHERE hashtext(email) = hashtext('[email protected]') AND email = '[email protected]'

這就是 tradeoff~

Composite type

在 postgres 中你可以定義 custom structure for custom type，類似於 json 的結構這樣。作者以 pgstattuple extension 來解釋 query expansion 會影響 performance。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
test=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION
test=# \x
Expanded display is on.
test=# \timing
Timing is on.
test=# SELECT (pgstattuple('t_email')).* AS x;
-[ RECORD 1 ]------+----------
table_len          | 931135488
tuple_count        | 10000000
tuple_len          | 838888897
tuple_percent      | 90.09
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 7732296
free_percent       | 0.83

Time: 4774.793 ms (00:04.775)
test=# SELECT x.* FROM pgstattuple('t_email') AS x;
-[ RECORD 1 ]------+----------
table_len          | 931135488
tuple_count        | 10000000
tuple_len          | 838888897
tuple_percent      | 90.09
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 7732296
free_percent       | 0.83

Time: 528.490 ms

原因在於如果直接 SELECT function call，相當於就是對 record 裡面的每一個欄位都 call 一次 function，因此改成下面 SELECT FROM 的寫法才會呼叫一次，因此才會產生這樣的差距。

FDW

FDW 指的是 Foreign Data Wrapper，可以在 PG 設定 remote Server，並且對 remote Sever 進行 SELECT 等操作，看起來像是在 local 操作兩邊的 table。

作者提出怎麼加速撈 remote table 的方式：

1
ALTER FOREIGN TABLE t_email OPTIONS (fetch_size '10000');

因為假設你是 SELECT * FROM remote_table，預設每一次的 round trip 都是撈 100 row，所以就需要很多 network round trip，為了節省，你可以修改 remote table 的 fetch_size 所以自然就能提高 performance。

總結

去年 Citus Con 看起來有不少不錯的演講內容，有機會是該找時間好好看過一輪～