首页 / POSTGRCSQL / PostgreSQL中的heap-only-tuples updates

PostgreSQL中的heap-only-tuples updates

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL中的heap-only-tuples updates，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9883字，纯文字阅读大概需要15分钟。

内容图文

为什么要这么做呢？因为数据库必须考虑一个关键问题：并发性。被更新的行可能还在被之前的事务使用。

为了解决这个问题：rdbms采用了不同技术：

·修改行，并将原来的行版本放置到另外一个地方。比如oracle中的undo

·duplicate该行，通过行的可见性映射信息来标明行对哪个事务可见。这就需要一个清理机制来清理那些对所有事务都不再需要的行。这是通过pg中的vacuum来完成的。

下面借助pageinspect扩展来示例：

postgres=# create table t(id int);
CREATE TABLE
postgres=# insert into t values(1);
INSERT 0 1
postgres=# SELECT lp,t_data FROM  heap_page_items(get_raw_page(‘t‘,0));
 lp |   t_data   
----+------------
  1 | \x01000000
(1 row)

postgres=# UPDATE t SET id = 2 WHERE id = 1;
UPDATE 1
postgres=# SELECT lp,t_data FROM  heap_page_items(get_raw_page(‘t‘,0));
 lp |   t_data   
----+------------
  1 | \x01000000
  2 | \x02000000
(2 rows)

postgres=# vacuum t;
VACUUM
postgres=# SELECT lp,t_data FROM  heap_page_items(get_raw_page(‘t‘,0));
 lp |   t_data   
----+------------
  1 | 
  2 | \x02000000
(2 rows)

postgres=#

从结果可以看到，引擎duplicate了两行，vacuum清除的位置。

heap-only-tuple机制

pg在8.3中，加入了hot技术。使用hot技术后，若所有索引属性都没有被修改(索引键是否修改是在执行时逐行判断的，因此如果一条update修改了某属性，但前后值相同则认为没有修改)，且新版本与原来版本存在一个页面上则不会产生新的索引记录，因此这些记录被称为hot(heap only tuple)。

hot会被打上heap_only_tuple标志，而hot的上一个版本会被打上heap_hot_updated标志，然后顺着版本链向后找，直到遇到hot为止。限制heap_only_tuple版本与hot在同一页面的目的是为了通过版本链向后找时不产生额外的io操作从而影响性能。因此，hot技术消除了拥有完全相同键值的索引记录，减少了索引的大小。

让我们来一个更复杂的案例：

postgres=# create table t2(c1 int,c2 int);
CREATE TABLE
postgres=# create index on t2(c1);
CREATE INDEX
postgres=# insert into t2(c1,c2) values(1,1);
INSERT 0 1
postgres=# insert into t2(c1,c2) values(2,2);
INSERT 0 1                           ^
postgres=# select ctid,* from t2;
 ctid  | c1 | c2
-------+----+----
 (0,1) |  1 |  1
 (0,2) |  2 |  2
(2 rows)

postgres=#

再读取表的块：

postgres=# SELECT * FROM  bt_page_items(get_raw_page(‘t2_c1_idx‘,1));
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
(2 rows)

postgres=#

表里含有两列，索引也含有两条记录指向对应的块(ctid)。

如果更新表的c1，对应的索引也会更新。

那如果更新表的c2，c1上的索引会被更新么？

乍一看，我们可能会说no，因为c1并没有被修改。

但是因为MVCC的存在，在理论上，回答应该是yes：从上面的例子可以看到数据库会duplicate记录行，因此物理位置会发生变化。

来看一下代码：

postgres=# SELECT lp,t_data,t_ctid FROM  heap_page_items(get_raw_page(‘t2‘,0));
 lp |       t_data       | t_ctid 
----+--------------------+--------
  1 | \x0100000001000000 | (0,1)
  2 | \x0200000002000000 | (0,2)
(2 rows)

postgres=# update t2 set c2=3 where c1=1;
UPDATE 1
postgres=# SELECT * FROM  bt_page_items(get_raw_page(‘t2_c1_idx‘,1));
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
(2 rows)

postgres=# SELECT lp,t_data,t_ctid FROM  heap_page_items(get_raw_page(‘t2‘,0));
 lp |       t_data       | t_ctid 
----+--------------------+--------
  1 | \x0100000001000000 | (0,3)
  2 | \x0200000002000000 | (0,2)
  3 | \x0100000003000000 | (0,3)
(3 rows)

postgres=#

从表块信息可以看到，已经有了duplicated的行。看t_data就可以发现。

但是通过索引块来看，内容并没有改变。如果检索where c1=1,索引还是指向记录(0,1)，对应老的记录。那这里究竟发生了什么呢？

事实上，我们刚才提到了heap-only-tuple机制。当一个列被更新，没有索引指向这个列，记录被插入相同的块，pg只是在老的记录和新的记录之间建立一个指针。这样就避免了更新索引，从而避免了：

1.避免读写操作

2.减少索引碎片和因为索引碎片导致的索引太大

通过上面的表的块查询结果，第一行的列t_ctid指向(0,3)。如果该行继续被更新，表的第一行会指向(0,3)，而行(0,3)会指向(0,4),从而形成一个链条。vacuum会清空释放空间。

修改一行后，索引不会被修改：

postgres=# UPDATE t2 SET c2 = 4 WHERE c1=1;
UPDATE 1
postgres=# SELECT lp,t_data,t_ctid FROM  heap_page_items(get_raw_page(‘t2‘,0));
 lp |       t_data       | t_ctid 
----+--------------------+--------
  1 | \x0100000001000000 | (0,3)
  2 | \x0200000002000000 | (0,2)
  3 | \x0100000004000000 | (0,4)
  4 | \x0100000004000000 | (0,4)
(4 rows)

postgres=# SELECT * FROM  bt_page_items(get_raw_page(‘t2_c1_idx‘,1));
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
(2 rows)

postgres=#

使用vacuum清空：

postgres=# vacuum t2;
VACUUM
postgres=# SELECT lp,t_data,t_ctid FROM  heap_page_items(get_raw_page(‘t2‘,0));
 lp |       t_data       | t_ctid 
----+--------------------+--------
  1 |                    | 
  2 | \x0200000002000000 | (0,2)
  3 |                    | 
  4 | \x0100000004000000 | (0,4)
(4 rows)

postgres=# SELECT * FROM  bt_page_items(get_raw_page(‘t2_c1_idx‘,1));
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
(2 rows)

一个更新会重利用第二个位置，但是索引仍然没有被修改。看下面的t_ctid列:

postgres=# UPDATE t2 SET c2 = 5 WHERE c1=1;
UPDATE 1
postgres=# SELECT lp,t_data,t_ctid FROM  heap_page_items(get_raw_page(‘t2‘,0));
 lp |       t_data       | t_ctid 
----+--------------------+--------
  1 |                    | 
  2 | \x0200000002000000 | (0,2)
  3 | \x0100000005000000 | (0,3)
  4 | \x0100000004000000 | (0,3)
(4 rows)

postgres=# SELECT * FROM  bt_page_items(get_raw_page(‘t2_c1_idx‘,1));
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
(2 rows)

postgres=#

第一行是空的，pg利用了第三行的位置？实际上，pageinspect中没有包含一个信息，可以直接从pg_filedump中看出。

注意：你必须先请求一个checkpoint，否则块可能没有被写入磁盘

pg_filedump  11/main/base/16606/8890510

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      40 (0x0028)
 Block: Size 8192  Version    4            Upper    8096 (0x1fa0)
 LSN:  logid     52 recoff 0xc39ea148      Special  8192 (0x2000)
 Items:    4                      Free Space: 8056
 Checksum: 0x0000  Prune XID: 0x0000168b  Flags: 0x0001 (HAS_FREE_LINES)
 Length (including item array): 40

<Data> ------
 Item   1 -- Length:    0  Offset:    4 (0x0004)  Flags: REDIRECT
 Item   2 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
 Item   3 -- Length:   32  Offset: 8096 (0x1fa0)  Flags: NORMAL
 Item   4 -- Length:   32  Offset: 8128 (0x1fc0)  Flags: NORMAL

第一行包含Flags：REDIRECT，表示这行对应一个HOT重定向。可以从文档src/include/storage/itemid.h看出：

/*
 * lp_flags has these possible states.  An UNUSED line pointer is available     
 * for immediate re-use, the other states are not.                              
 */                                                                             
#define LP_UNUSED       0       /* unused (should always have lp_len=0) */      
#define LP_NORMAL       1       /* used (should always have lp_len>0) */        
#define LP_REDIRECT     2       /* HOT redirect (should have lp_len=0) */       
#define LP_DEAD         3       /* dead, may or may not have storage */

其实，通过pageinspect的lp_flags也可以看出：

SELECT lp,lp_flags,t_data,t_ctid FROM  heap_page_items(get_raw_page(‘t2‘,0));
 lp | lp_flags |       t_data       | t_ctid
----+----------+--------------------+--------
  1 |        2 |                    |
  2 |        1 | \x0200000002000000 | (0,2)
  3 |        1 | \x0100000005000000 | (0,3)
  4 |        1 | \x0100000004000000 | (0,3)
(4 rows)

如果我们继续更新，执行vacuum，并执行一个checkpoint：

SELECT lp,lp_flags,t_data,t_ctid FROM  heap_page_items(get_raw_page(‘t2‘,0));
 lp | lp_flags |       t_data       | t_ctid
----+----------+--------------------+--------
  1 |        2 |                    |
  2 |        1 | \x0200000002000000 | (0,2)
  3 |        0 |                    |
  4 |        0 |                    |
  5 |        1 | \x0100000006000000 | (0,5)
(5 rows)

CHECKPOINT;

pg_filedump  11/main/base/16606/8890510

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      44 (0x002c)
 Block: Size 8192  Version    4            Upper    8128 (0x1fc0)
 LSN:  logid     52 recoff 0xc39ea308      Special  8192 (0x2000)
 Items:    5                      Free Space: 8084
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0005 (HAS_FREE_LINES|ALL_VISIBLE)
 Length (including item array): 44

<Data> ------
 Item   1 -- Length:    0  Offset:    5 (0x0005)  Flags: REDIRECT
 Item   2 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
 Item   3 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item   4 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item   5 -- Length:   32  Offset: 8128 (0x1fc0)  Flags: NORMAL


*** End of File Encountered. Last Block Read: 0 ***

pg继续保留第一行，并写入了新的第五行。

但是有些场景，pg并不能使用这种机制：

1.如果块已满，必须写入别的块。(HOT可以减少碎片)

2.如果更新的列上面有索引。这时，pg必须更新索引

PostgreSQL中的heap-only-tuples updates

标签：cti create rdb 理论 io操作 rac offset 建立指针

本文系统来源：https://www.cnblogs.com/abclife/p/13194713.html

内容总结

以上是互联网集市为您收集整理的PostgreSQL中的heap-only-tuples updates全部内容，希望文章能够帮你解决PostgreSQL中的heap-only-tuples updates所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/524894.html

来源：【匿名】

【上一篇】PostgreSQL逻辑订阅中的复制状态查看【下一篇】PDO扩展连接PostgreSQL对象关系数据库步骤详解

更多 ►

【PostgreSQL中的heap-only-tuples updates】教程文章相关的互联网学习教程文章

postgresql 数据库 update更新慢的原因（已解决）【代码】【图】

w1.pid as 等待进程, w1.mode as 等待锁模式, w2.usename as 等待用户, w2.query as 等待会话, b1.pid as 锁的进程, b1.mode 锁的锁模式, b2.usename as 锁的用户, b2.query as 锁的会话, b2.application_name 锁的应用, b2.client_addr 锁的IP地址, b2.query_start 锁的语句执行时间 from pg_locks w1 join pg_stat_activity w2 on w1.pid=w2.pid join pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pid join...

postgresql update returning【代码】

: UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULTWHERE city = ‘San Francisco‘ AND date = ‘2003-07-03‘RETURNING temp_lo, temp_hi, prcp;update returning的是更新后的内容postgresql update returning标签：and cisco return where form postgres lis postgre list 本文系统来源：http://www.cnblogs.com/cdyboke/p/7490077.html

PostgreSQL之INSERT,DELETE,UPDATE

插入数据每一列都赋值的话，可以省略列名 INSERT INTO Product VALUES(value1,value2,DEFAULT,value3,NULL) 只对部分列赋值，需要加上列名。被省略列名的列，如果存在默认值则默认值，如果不存在默认值则NULL。如果既不存在默认值又有非空约束，则执行失败 INSERT INTO Product (column1,column2,column3) VALUES(value1,value2,value3) 删除数据清空表而不删除表 DELETE FROM 表名删除表 DROP TABLE 表名更新表 UPDATE 表名 S...

PostgreSQL的MVCC(5)--In-page vacuum and HOT updates【代码】

在常规更新期间执行in-page vacuum 当因为读取或更新访问页面时，如果PostgreSQL知道该页面空间不足，则可以执行一次快速的in-page vacuum。发生在以下两种情况下： 1.此页面上之前的更新找不到足够的空间来在同一页面中分配新的行版本。在page header中会记录这种情况，下一次将会对页面vacuum。2.该页面已超过fillfactor设定的百分比。在这种情况下，立即执行vacuum，而不会推迟到下一次。 fillfactor是可以为表（和索引）定义的...

PostgreSQL中的heap-only-tuples updates【代码】

为什么要这么做呢？因为数据库必须考虑一个关键问题：并发性。被更新的行可能还在被之前的事务使用。为了解决这个问题：rdbms采用了不同技术：修改行，并将原来的行版本放置到另外一个地方。比如oracle中的undo duplicate该行，通过行的可见性映射信息来标明行对哪个事务可见。这就需要一个清理机制来清理那些对所有事务都不再需要的行。这是通过pg中的vacuum来完成的。下面借助pageinspect扩展来示例：postgres=# create tab...

postgresql saveOrUpdate实现方式

在postgresql9.5以上提供了conflict() 来判断sql语句插入时，是否发生重复 1. saveOrUpdate实现方式主键重复后，执行update操作INSERT INTO user_pages (user_id, page_id, enabled) VALUES (1, 1, TRUE) ON CONFLICT (user_id, page_id) DO UPDATE SET enabled = 0;2.saveOrNothing 主键重复后，不执行任何操作INSERT INTO user_pages (user_id, page_id, enabled) VALUES (1, 1, TRUE) ON CONFLICT (user_id, page_id) ...

PostgreSQL批量update与oracle差异【代码】

此文转载自：https://blog.csdn.net/weixin_39540651/article/details/112259645#commentBox当我们在数据库中有这样的需求时：需要以某列的当前值为判断对象，将其更新成其它值。例如下面一张表：ID INFO ---------- ----------2 a1 b3 c4 d5 e我们最简单的方式就是通过多个update来完成： update t1 set id= 2where id = 1;update t1 set id = 1where id = 2;......看上去很简单，但是上面的更新却存在一个很大的问题，当我们第...

PostgreSQL批量update与oracle差异【代码】

此文转载自：https://blog.csdn.net/weixin_39540651/article/details/112259645当我们在数据库中有这样的需求时：需要以某列的当前值为判断对象，将其更新成其它值。例如下面一张表：ID INFO ---------- ----------2 a1 b3 c4 d5 e我们最简单的方式就是通过多个update来完成： update t1 set id= 2where id = 1;update t1 set id = 1where id = 2;......看上去很简单，但是上面的更新却存在一个很大的问题，当我们第一次执行完u...

postgresql 并发update下导致的死锁问题【代码】【图】

一、死锁问题背景在收据批量打印时，由于采用异步并发触发打印，同时触发打印（九千多数据每隔50ms触发一次），导致了并发执行引起在接口更新打印次数时postgresql发生死锁问题，具体报错如下： 1 ### The error occurred while setting parameters2 ### SQL: update t_sc_receipt set print_num = coalesce(print_num,0) + 1 ,print_ts=?3 ### Cause: org.postgresql.util.PSQLException: ERROR: deadlo...

PostgreSQL之INSERT,DELETE,UPDATE

PostgreSQL UPDATE相当于MySQL查询【代码】

我有一个简单的MySQL查询,我想转换为PostgreSQL. 3天后我终于退出了,因为我不明白这里有什么不妥：UPDATE webUsers u, (SELECT IFNULL(count(s.id),0) AS id, p.associatedUserId FROM pool_worker p LEFT JOIN shares s ON p.username=s.username WHERE s.our_result='Y' GROUP BY p.associatedUserId) a SET shares_this_round = a.id WHERE u.id = a.associatedUserId我试图转换它,但它说SET上的错误.这是我的查询：UPDATE w...

首页 / POSTGRCSQL / PostgreSQL中的heap-only-tuples updates

PostgreSQL中的heap-only-tuples updates

内容导读

内容图文

heap-only-tuple机制

内容总结

内容备注

内容手机端

【PostgreSQL中的heap-only-tuples updates】教程文章相关的互联网学习教程文章

POSTGRESQL - 相关标签

UPDATE - 相关标签

POSTGRCSQL - 技术教程分类

POSTGRCSQL - 最新教程

POSTGRCSQL - 最热教程