PostgreSQL HOT与PHOT有哪些区别
目录
- 1、HOT概述
- 2、HOT实现技术细节
- 3、何时进行修剪
- 4、HOT的不足
- 5、PHOT概述
- 6、PHOT实例
- 总结
1、HOT概述
PostgreSQL中，由于其多版本的特性，当我们进行数据更新时，实际上并不是直接修改元数据，而是通过新插入一行数据来进行间接的更新。而当表上存在索引时，由于新插入了数据，那么索引必然也需要同步进行更新，这在索引较多的情况下，对于更新的性能影响必然很大。
为了解决这一问题，pg从8.3版本开始就引入了HOT（Heap Only Tuple）机制。其原理大致为，当更新的不是索引字段时，我们通过将旧元组指向新元组，而原先的索引不变，仍然指向旧元组，但是我们可以通过旧元组作为间接去访问到新的元组，这样就不用再去更新索引了。
2、HOT实现技术细节
要使用HOT进行更新，需要满足两个前提：
- 新的元组和旧元组必须在同一个page中；
- 索引字段不能进行更新。
当我们进行HOT更新时，首先是分别设置旧元组的t_informask2标志位为HEAP_HOT_UPDATED和新元组为HEAP_ONLY_TUPLE。
更新如下图所示：
我们更新tuple1为tuple2，分别设置这两行元组的t_informask2标志位，然后tuple1的ctid指向tuple2，而tuple2指向自己。
但是这样存在一个明显的问题，我们都知道pg会定期进行vacuum清理那些死元组，那么我们这里如果通过tuple1去访问tuple2的话，tuple1这个死元组被清理了又该怎么办呢？
所以pg会在合适的时机进行行指针的重定向，将旧元组的行指针指向新元组的行指针，这一过程称为修剪。于是在修剪之后，我们通过HOT机制访问数据便成了这样：
1、通过索引元组找到旧元组的行指针1；
2、通过重定向的行指针1找到行指针2；
3、通过行指针2找到新元组tuple2。
这样即使旧元组tuple1被清理掉也没有影响了。
HOT对应的wal日志实现：
对于HOT的update操作，其wal日志中记录的信息主要是由xl_heap_update结构存储。
如果新的元组存储在 block_id 为 0 的块上，如果不是 XLOG_HEAP_HOT_UPDATE，那么旧的元组将会存储在 block_id 为 1 的块上。反之如果block_id 为 1 的块没有被使用，那么则认为是 XLOG_HEAP_HOT_UPDATE。
3、何时进行修剪
前面我们提到了，旧行的行指针会重定向到新行的行指针，这一过程称之为修剪。那么什么时候会发生修剪呢？
一般来说，当我们执行select、update、insert、delete这些命令时均有可能触发修剪，其触发机制大致有两种情况：
- 上一次进行update时无法在本page找到足够的空间；
- 当前page上剩余空间小于fill-factor的值，最多小于10%
除此之外，当进行修剪时，还会选择合适的时机进行死元组的清理，这一操作称为碎片整理。碎片整理发生在当我们对元组进行检索时发现空闲空间少于10%时，和修剪不同的是，碎片整理不会发生在insert时，因为该操作并不会检索行。
相较于普通的vacuum操作，碎片清理并不涉及索引元组的清理，开销相对于常规的清理要小很多，是通过PageRepairFragmentation函数来实现的。
这也是为什么HOT要求新旧元组需要在同一个page中，虽然从理论上来说我们可以将行指针的链表指向不同page，但是这样我们便不能使用page-local的操作来进行碎片清理了。
4、HOT的不足
前面我们提到了HOT的前提条件之一就是：更新的列不能是索引列。需要注意，当更新的列是索引列时并不仅仅是会修改该列上的索引，整张表上所有的索引均会被修改。
例子：
创建测试表：
bill=# create table a(id int, c1 int, c2 int, c3 int); CREATE TABLE bill=# insert into a select generate_series(1,10), random()*100, random()*100, random()*100; INSERT 0 10 bill=# create index idx_a_1 on a (id); CREATE INDEX bill=# create index idx_a_2 on a (c1); CREATE INDEX bill=# create index idx_a_3 on a (c2); CREATE INDEX
观察索引页内容：
bill=# SELECT * FROM bt_page_items('idx_a_1',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+--------+---------+-------+------+-------------------------+------+--------+------
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 | f | (0,1) |
2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 | f | (0,2) |
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (0,3) |
4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (0,4) |
5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 | f | (0,5) |
6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 | f | (0,6) |
7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 | f | (0,7) |
8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 | f | (0,8) |
9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 | f | (0,9) |
10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 | f | (0,10) |
(10 rows)
bill=# SELECT * FROM bt_page_items('idx_a_2',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+-----------+---------+-------+------+-------------------------+------+--------+-------------------
1 | (0,5) | 16 | f | f | 00 00 00 00 00 00 00 00 | f | (0,5) |
2 | (0,4) | 16 | f | f | 05 00 00 00 00 00 00 00 | f | (0,4) |
3 | (0,8) | 16 | f | f | 0e 00 00 00 00 00 00 00 | f | (0,8) |
4 | (0,9) | 16 | f | f | 25 00 00 00 00 00 00 00 | f | (0,9) |
5 | (16,8194) | 32 | f | f | 30 00 00 00 00 00 00 00 | f | (0,1) | {"(0,1)","(0,3)"}
6 | (0,10) | 16 | f | f | 58 00 00 00 00 00 00 00 | f | (0,10) |
7 | (0,6) | 16 | f | f | 60 00 00 00 00 00 00 00 | f | (0,6) |
8 | (0,7) | 16 | f | f | 62 00 00 00 00 00 00 00 | f | (0,7) |
9 | (0,2) | 16 | f | f | 63 00 00 00 00 00 00 00 | f | (0,2) |
(9 rows)
bill=# SELECT * FROM bt_page_items('idx_a_3',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+--------+---------+-------+------+-------------------------+------+--------+------
1 | (0,6) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (0,6) |
2 | (0,3) | 16 | f | f | 12 00 00 00 00 00 00 00 | f | (0,3) |
3 | (0,5) | 16 | f | f | 15 00 00 00 00 00 00 00 | f | (0,5) |
4 | (0,9) | 16 | f | f | 1a 00 00 00 00 00 00 00 | f | (0,9) |
5 | (0,4) | 16 | f | f | 33 00 00 00 00 00 00 00 | f | (0,4) |
6 | (0,7) | 16 | f | f | 3d 00 00 00 00 00 00 00 | f | (0,7) |
7 | (0,10) | 16 | f | f | 4e 00 00 00 00 00 00 00 | f | (0,10) |
8 | (0,1) | 16 | f | f | 4f 00 00 00 00 00 00 00 | f | (0,1) |
9 | (0,2) | 16 | f | f | 5c 00 00 00 00 00 00 00 | f | (0,2) |
10 | (0,8) | 16 | f | f | 5d 00 00 00 00 00 00 00 | f | (0,8) |
(10 rows)
更新索引列c2：
bill=# update a set c2=c2+1 where id=1 returning ctid,*; ctid | id | c1 | c2 | c3 --------+----+----+----+---- (0,11) | 1 | 19 | 66 | 86 (1 row) UPDATE 1
再观察索引内容：
可以看到3个索引的索引页均发生了变化。
bill=# SELECT * FROM bt_page_items('idx_a_1',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+--------+---------+-------+------+-------------------------+------+--------+------
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 | f | (0,1) |
2 | (0,11) | 16 | f | f | 01 00 00 00 00 00 00 00 | f | (0,11) |
3 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 | f | (0,2) |
4 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (0,3) |
5 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (0,4) |
6 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 | f | (0,5) |
7 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 | f | (0,6) |
8 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 | f | (0,7) |
9 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 | f | (0,8) |
10 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 | f | (0,9) |
11 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 | f | (0,10) |
(11 rows)
bill=# SELECT * FROM bt_page_items('idx_a_2',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+--------+---------+-------+------+-------------------------+------+--------+------
1 | (0,6) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (0,6) |
2 | (0,9) | 16 | f | f | 0b 00 00 00 00 00 00 00 | f | (0,9) |
3 | (0,1) | 16 | f | f | 13 00 00 00 00 00 00 00 | f | (0,1) |
4 | (0,11) | 16 | f | f | 13 00 00 00 00 00 00 00 | f | (0,11) |
5 | (0,2) | 16 | f | f | 19 00 00 00 00 00 00 00 | f | (0,2) |
6 | (0,5) | 16 | f | f | 1d 00 00 00 00 00 00 00 | f | (0,5) |
7 | (0,8) | 16 | f | f | 1e 00 00 00 00 00 00 00 | f | (0,8) |
8 | (0,4) | 16 | f | f | 21 00 00 00 00 00 00 00 | f | (0,4) |
9 | (0,3) | 16 | f | f | 28 00 00 00 00 00 00 00 | f | (0,3) |
10 | (0,10) | 16 | f | f | 3a 00 00 00 00 00 00 00 | f | (0,10) |
11 | (0,7) | 16 | f | f | 4d 00 00 00 00 00 00 00 | f | (0,7) |
(11 rows)
bill=# SELECT * FROM bt_page_items('idx_a_3',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+--------+---------+-------+------+-------------------------+------+--------+------
1 | (0,2) | 16 | f | f | 17 00 00 00 00 00 00 00 | f | (0,2) |
2 | (0,7) | 16 | f | f | 18 00 00 00 00 00 00 00 | f | (0,7) |
3 | (0,5) | 16 | f | f | 33 00 00 00 00 00 00 00 | f | (0,5) |
4 | (0,6) | 16 | f | f | 37 00 00 00 00 00 00 00 | f | (0,6) |
5 | (0,4) | 16 | f | f | 38 00 00 00 00 00 00 00 | f | (0,4) |
6 | (0,1) | 16 | f | f | 41 00 00 00 00 00 00 00 | f | (0,1) |
7 | (0,11) | 16 | f | f | 42 00 00 00 00 00 00 00 | f | (0,11) |
8 | (0,9) | 16 | f | f | 4d 00 00 00 00 00 00 00 | f | (0,9) |
9 | (0,8) | 16 | f | f | 58 00 00 00 00 00 00 00 | f | (0,8) |
10 | (0,3) | 16 | f | f | 62 00 00 00 00 00 00 00 | f | (0,3) |
11 | (0,10) | 16 | f | f | 63 00 00 00 00 00 00 00 | f | (0,10) |
(11 rows)
这也意味着当我们无法使用HOT更新时，每更新一条数据都会更新所有的索引，那么这个对性能的影响可想而知。而我们简单想想其实并没有这个必要，因为对于没有被更新的索引列而言，只是ctid发生了变化，其索引列指向的值并没有变化。那么有没有办法让我们更新索引列时只修改该索引列的索引呢？PHOT应运而生。
5、PHOT概述
PHOT（Partial Heap Only Tuples），正如我们前面所说，PHOT是为了解决HOT更新索引列时会修改索引列的弊端。目前，PG中还不支持该功能，不过社区中已经有相关的讨论了，预计可能会在PG15中和大家见面。
PHOT的设计思路主要是：通过在t_infomask2标志位新加入两种类型HEAP_HOT_UPDATED 和HEAP_ONLY_TUPLE用来表示PHOT更新，类似于HOT。当我们对索引列进行修改时，通过一个PHOT的bitmap位图来记录哪些索引列被更新了，然后，当我们对索引列修改时，只需要修改这个bitmap位图中的列即可。
#define HEAP_HOT_UPDATED 0x4000 /* tuple was HOT-updated */ #define HEAP_ONLY_TUPLE 0x8000 /* this is heap-only tuple */
6、PHOT实例
我们通过下面的例子来看看PHOT和HOT的不同之处。
构建环境：
CREATE TABLE test (a INT, b INT, c INT); CREATE INDEX ON test (a); CREATE INDEX ON test (b); CREATE INDEX ON test (c); INSERT INTO test VALUES (0, 0, 0); UPDATE test SET a = 1, b = 1; UPDATE test SET b = 2, c = 2; UPDATE test SET a = 2; UPDATE test SET a = 3, c = 3;
不使用PHOT：
可以看到，不使用PHOT的情况下，每次更新都会产生新的索引元组。
在这些更新之后，有 15 个索引元组、5 个行指针和 5 个堆元组。
test_a_idx 0 1 1 2 3 test_b_idx 0 1 2 2 2 test_c_idx 0 0 2 2 3 lp 1 2 3 4 5 heap (0,0,0) (1,1,0) (1,2,2) (2,2,2) (3,2,3)
PHOT：
在使用PHOT后，通过增加了一个bitmap位图来记录被更新的列。当执行完上述更新后，有 10 个索引元组、5 个行指针、5 个堆元组和 4 个位图。
test_a_idx 0 1 2 3 test_b_idx 0 1 2 test_c_idx 0 2 3 lp 1 2 3 4 5 heap (0,0,0) (1,1,0) (1,2,2) (2,2,2) (3,2,3) bitmap xx- -xx x-- x-x
而前面的例子我们使用PHOT更新又是什么结果呢？
可以看到下图中，左边是使用PHOT进行更新的，只是被更新的索引列发生了变化，而右边非PHOT进行更新则是所有的索引列均发生变化。
性能测试：
通过简单的pgbench测试，TPS 提高了约 34%，其中每个表都有 5 个额外的文本列和每列上的索引。有了额外的索引和列，理论上 TPS 的提升应该会更高。对于没有表修改的短期 pgbench 运行，使用 PHOT 观察到 TPS 增加了约 2%，表明常规 pgbench 运行没有受到显着影响。
总结
PostgreSQL使用HOT机制来避免因为其多版本特性导致的每次更新数据均需要修改索引的情况。而当前的HOT机制，对于索引列的更新仍然存在比较明显的性能问题，因为所有的索引均需要发生修改，不过预计在PG15中，将会加入更为强大的PHOT功能，更新索引列再也不会影响其它索引了。
参考链接：
到此这篇关于PostgreSQL HOT与PHOT有哪些区别的文章就介绍到这了,更多相关PostgreSQL HOT与PHOT内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
相关推荐
-
PostgreSQL索引失效会发生什么
前段时间碰到个奇怪的索引失效的问题,实际情况类似下面这样: bill=# begin; BEGIN bill=*# create index idx_t1 on t1(id); CREATE INDEX bill=*# explain select * from t1 where id = 1; QUERY PLAN ---------------------------------------------------- Seq Scan on t1 (cost=0.00..25.88 rows
-
PostgreSQL HOT与PHOT有哪些区别
目录 1.HOT概述 2.HOT实现技术细节 3.何时进行修剪 4.HOT的不足 5.PHOT概述 6.PHOT实例 总结 1.HOT概述 PostgreSQL中,由于其多版本的特性,当我们进行数据更新时,实际上并不是直接修改元数据,而是通过新插入一行数据来进行间接的更新.而当表上存在索引时,由于新插入了数据,那么索引必然也需要同步进行更新,这在索引较多的情况下,对于更新的性能影响必然很大. 为了解决这一问题,pg从8.3版本开始就引入了HOT(Heap Only Tuple)机制.其原理大致为
-
postgresql rank() over, dense_rank(), row_number()用法区别
如下学生表student,学生表中有姓名.分数.课程编号,需要按照课程对学生的成绩进行排序 select * from jinbo.student; id | name | score | course ----+-------+-------+-------- 5 | elic | 70 | 1 4 | dock | 100 | 1 3 | cark | 80 | 1 2 | bob | 90 | 1 1 | alice | 60 | 1 10 | jacky | 80 | 2 9 | iri
-
Postgresql数据库character varying和character的区别说明
目录 Postgresql character varying和character的区别 SQL 标准 postgreSQL 字符类型 Postgresql的character varying = bytea问题 Postgresql character varying和character的区别 SQL 标准 SQL 定义了两种基本的字符类型:character varying(n) 和 character(n) ,这里的 n 是一个正整数.两种类型都可以存储最多 n 个字符的字符串. 试图存储
-
postgresql的now()与Oracle的sysdate区别说明
postgresql的now()为当前事务开始时间, 而Oracle的sysdate是当前时间. 区别在于事务. postgresql中的now(): postgres=# begin ; BEGIN postgres=# select now(); now ------------------------------- 2017-03-31 14:28:32.403869+08 (1 row) postgres=# select now(); now ---------------------
-
postgresql流复制原理以及流复制和逻辑复制的区别说明
流复制的原理: 物理复制也叫流复制,流复制的原理是主库把WAL发送给备库,备库接收WAL后,进行重放. 逻辑复制的原理: 逻辑复制也是基于WAL文件,在逻辑复制中把主库称为源端库,备库称为目标端数据库,源端数据库根据预先指定好的逻辑解析规则对WAL文件进行解析,把DML操作解析成一定的逻辑变化信息(标准SQL语句),源端数据库把标准SQL语句发给目标端数据库,目标端数据库接收到之后进行应用,从而实现数据同步. 流复制和逻辑复制的区别: 流复制主库上的事务提交不需要等待备库接收到WAL文件后的确认
-
MySQL用户管理与PostgreSQL用户管理的区别说明
一. MySQL用户管理 [例1.1]使用root用户登录到本地mysql服务器的test库中 mysql -uroot -p -hlocalhost test [例1.2]使用root用户登录到本地mysql服务器的test库中,执行一条查询语句 mysql -uroot -p -hlocalhost test -e "DESC person;" [例1.3]使用CREATE USER创建一个用户,用户名是jeffrey,密码是mypass,主机名是localhost CREATE
-
PostgreSQL 序列绑定字段与不绑定字段的区别说明
序列绑定字段与不绑定字段的区别 绑定字段 构造数据 drop sequence if exists test_id_seq; create sequence test_id_seq; drop table if exists test; create table test(id int default nextval('test_id_seq'), name text); alter sequence test_id_seq owned by test.id; 测试 test=# drop ta
-
PostgreSQL三种自增列sequence,serial,identity的用法区别
这三个对象都可以实现自增,这里从如下几个维度来看看这几个对象有哪些不同,其中功能性上看,大部分特性都是一致的或者类似的. 1.sequence在所有数据库中的性质都一样,它是跟具体的字段不是强绑定的,其特点是支持多个对个对象之间共享. sequence作为自增字段值的时候,对表的写入需要另外单独授权sequence(GRANT USAGE ON SEQUENCE test_old_id_seq;) sequence类型的字段表,在使用CREATE TABLE new_table LIKE old
-
浅谈MySQL和mariadb区别
MariaDB是MySQL源代码的一个分支,在意识到Oracle会对MySQL许可做什么后分离了出来(MySQL先后被Sun.Oracle收购).除了作为一个Mysql的"向下替代品",MariaDB包括的一些新特性使它优于MySQL. 这两个数据库究竟有什么本质的区别,我看mariadb文件夹BIN中还是mysql*.exe,除了MySQL会被ORACLE闭源外,而mariadb则开源,他俩之间到底还有什么本质区别没有? 区别一: MariaDB不仅仅是Mysql的一个替代品,它的主
-
Mac OS上安装PostgreSQL的教程
容我开头啰嗦一下.一直以来,我和 MySQL 这位久经考验的老朋友合作愉快.但自从了解了一点 PostgreSQL 后, 对其丰富的功能特性就十分着迷.比如字段类型原生支持 json, xml 和 array.跟 MySQL 比起来,感觉 PostgreSQL 更高级一些. 安装brew 官方文档: http://mxcl.github.com/homebrew/ 先安装Git,打开一个shell: cd /usr/local sudo mkdir homebrew curl -L https: