这一系列文章将以11版本的新特性作为焦点。在开发这个版本的过程中，一个特性引起了我的注意。它可以在发行说明中找到。
当表达式的值不变时，允许对表达式索引进行heap-only-tuple (HOT)更新(Konstantin Knizhnik)。
这个特性需要一些关于PostgreSQL如何工作的知识，我将通过三篇文章来解释:
MVCC如何工作的和更新heap-only-tuples。
什么时候PostgreSQL不使用heap-only-tuples更新和v11中新特性的介绍。
对性能的影响。
这个特性在11.1版本中被禁用，因为它可能导致实例崩溃。之所以选择发表这些文章，是因为它们有助于理解HOT更新的机制以及这个特性可能带来的好处。
1、 对性能的影响
这里有一个简单的测试来展示这个特性的好处。因为PostgreSQL避免了更新索引，我们可以预期性能的增加，并且在索引大小方面，就像上面说的一样，避免了碎片化：
CREATE TABLE t5 (c1 jsonb, c2 int,c3 int);
CREATE INDEX ON t5 ((c1->>'prenom')) ;
CREATE INDEX ON t5 (c2);
INSERT INTO t5 VALUES ('{ "prenom":"adrien" , "valeur" : "1"}'::jsonb,1,1);
INSERT INTO t5 VALUES ('{ "prenom":"guillaume" , "valeur" : "2"}'::jsonb,2,2);
\dt+ t5
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
public | t5 | table | postgres | 16 kB |
(1 row)
\di+ t5*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+----------+-------+-------+-------------
public | t5_c2_idx | index | postgres | t5 | 16 kB |
public | t5_expr_idx | index | postgres | t5 | 16 kB |
(2 rows)复制
看一下pgbench的测试脚本：
\set id random(1, 100000)
\set id2 random(1, 100000)
UPDATE t5 SET c1 = '{"valeur": ":id", "prenom": "guillaume"}' WHERE c2=2;
UPDATE t5 SET c1 = '{"valeur": ":id2", "prenom": "adrien"}' WHERE c2=1;复制
然后设置 recheck_on_update=on (默认)，执行120秒：
pgbench -f test.sql -n -c6 -T 120
transaction type: test.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 120 s
number of transactions actually processed: 2743163
latency average = 0.262 ms
tps = 22859.646914 (including connections establishing)
tps = 22859.938191 (excluding connections establishing)
\dt+ t5*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | t5 | table | postgres | 376 kB |
(1 row)
\di+ t5*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+----------+-------+-------+-------------
public | t5_c2_idx | index | postgres | t5 | 16 kB |
public | t5_expr_idx | index | postgres | t5 | 32 kB |
(2 rows)
SELECT * from pg_stat_user_tables where relname = 't5';
-[ RECORD 1 ]-------+------------------------------
relid | 8890622
schemaname | public
relname | t5
seq_scan | 4
seq_tup_read | 0
idx_scan | 7999055
idx_tup_fetch | 7999055
n_tup_ins | 4
n_tup_upd | 7999055
n_tup_del | 0
n_tup_hot_upd | 7998236
n_live_tup | 2
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum | 2018-09-19 06:29:37.690575+00
last_analyze |
last_autoanalyze | 2018-09-19 06:29:37.719911+00
vacuum_count | 0
autovacuum_count | 5
analyze_count | 0
autoanalyze_count | 5复制
现在设置recheck_on_update=off，为什么数据集和以前一样但是这次索引是用这个顺序创建的 : CREATE INDEX ON t5 ((c1->>'prenom')) WITH (recheck_on_update=off);
pgbench -f test.sql -n -c6 -T 120
transaction type: test.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 120 s
number of transactions actually processed: 1065688
latency average = 0.676 ms
tps = 8880.679565 (including connections establishing)
tps = 8880.796478 (excluding connections establishing)
\dt+ t5
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | t5 | table | postgres | 9496 kB |
(1 row)
\di+ t5*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+----------+-------+--------+-------------
public | t5_c2_idx | index | postgres | t5 | 768 kB |
public | t5_expr_idx | index | postgres | t5 | 58 MB |
(2 rows)
select * from pg_stat_user_tables where relname = 't5';
-[ RECORD 1 ]-------+------------------------------
relid | 8890635
schemaname | public
relname | t5
seq_scan | 2
seq_tup_read | 0
idx_scan | 2131376
idx_tup_fetch | 2131376
n_tup_ins | 2
n_tup_upd | 2131376
n_tup_del | 0
n_tup_hot_upd | 19
n_live_tup | 2
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum | 2018-09-19 06:34:42.045905+00
last_analyze |
last_autoanalyze | 2018-09-19 06:34:42.251183+00
vacuum_count | 0
autovacuum_count | 3
analyze_count | 0
autoanalyze_count | 3复制
测试结果：
| recheck_on_update | on | off | Gain |
|---|---|---|---|
| TPS | 22859 | 8880 | 157% |
| t5 size | 376 kB | 9496 kB | -96% |
| t5_c2_idx size | 16 kB | 768 kB | -98% |
| t5_expr_idx size | 32 kB | 58 MB | -99.9% |
性能的差异是非常大的，以及表和索引的大小。
禁用autovacuum后重复做第一个测试的结果：
pgbench -f test.sql -n -c6 -T 120
transaction type: test.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 120 s
number of transactions actually processed: 2752479
latency average = 0.262 ms
tps = 22937.271749 (including connections establishing)
tps = 22937.545872 (excluding connections establishing)
select * from pg_stat_user_tables where relname = 't5';
-[ RECORD 1 ]-------+--------
relid | 8890643
schemaname | public
relname | t5
seq_scan | 2
seq_tup_read | 0
idx_scan | 5504958
idx_tup_fetch | 5504958
n_tup_ins | 2
n_tup_upd | 5504958
n_tup_del | 0
n_tup_hot_upd | 5504258
n_live_tup | 2
n_dead_tup | 2416
n_mod_since_analyze | 5504960
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
\di+ t5*
List of relations
-[ RECORD 1 ]------------
Schema | public
Name | t5_c2_idx
Type | index
Owner | postgres
Table | t5
Size | 16 kB
Description |
-[ RECORD 2 ]------------
Schema | public
Name | t5_expr_idx
Type | index
Owner | postgres
Table | t5
Size | 40 kB
Description |
\dt+ t5
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | t5
Type | table
Owner | postgres
Size | 1080 kB
Description |复制
然后做第二个测试：
pgbench -f test.sql -n -c6 -T 120
transaction type: test.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 120 s
number of transactions actually processed: 881434
latency average = 0.817 ms
tps = 7345.208875 (including connections establishing)
tps = 7345.304797 (excluding connections establishing)
select * from pg_stat_user_tables where relname = 't5';
-[ RECORD 1 ]-------+--------
relid | 8890651
schemaname | public
relname | t5
seq_scan | 2
seq_tup_read | 0
idx_scan | 1762868
idx_tup_fetch | 1762868
n_tup_ins | 2
n_tup_upd | 1762868
n_tup_del | 0
n_tup_hot_upd | 23
n_live_tup | 2
n_dead_tup | 1762845
n_mod_since_analyze | 1762870
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
\di+ t5*
List of relations
-[ RECORD 1 ]------------
Schema | public
Name | t5_c2_idx
Type | index
Owner | postgres
Table | t5
Size | 600 kB
Description |
-[ RECORD 2 ]------------
Schema | public
Name | t5_expr_idx
Type | index
Owner | postgres
Table | t5
Size | 56 MB
Description |
\dt+ t5*
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | t5
Type | table
Owner | postgres
Size | 55 MB
Description |复制
测试结果：
| recheck_on_update | on | off | Gain |
|---|---|---|---|
| TPS | 22937 | 7345 | 212% |
| t5 size | 1080 kB | 55 MB | -98% |
| t5_c2_idx size | 16 kB | 600 kB | -97% |
| t5_expr_idx size | 40 kB | 56 MB | -99.9% |
这一次，性能差距是显著的，正如表和索引大小一样，我们还注意到autovacuum的重要性。
为什么我们在索引和表上有这么大的差异？
对于索引来说，这是由于上面解释的机制。PostgreSQL能通过链接这些记录避免更新索引。索引大小不过是轻微的增加，这或许是发生在PostgreSQL不能用HOT机制的地方。例如数据块中没有更多的空间了。
至于表的大小，在打开autovacuum的测试过程中，当HOT机制是禁用的，autovacuum的执行有更大的困难，索引的增长，意味着需要清理的更多。在关闭autovacuum的测试过程中, 差异可以用一个简单的SELECT就可以彻底的更新指针来解释。
请记住，该特性在版本11.1中已经被删除。这些文章是在11.0版本发布后不久后写的，发布它们是为了更好的解释HOT是如何工作的。希望这个特性在将来的版本中是固定的。
2、 原文链接
https://blog.anayrat.info/en/2018/11/26/postgresql-and-heap-only-tuples-updates-part-3/
敬请关注飞象数据
