cluster、update、delete在死元组清理上的作用

简介

在 PostgreSQL 中，为了支持 MVCC（多版本并发控制），对表中行的更新和删除并不会立刻移除旧的数据行，而是：
更新操作：创建一个新的行版本（tuple），原来的行变成“死元组”。
删除操作：标记该行为删除（也是变成死元组），但仍然保留在表中一段时间。
因此，死元组就是对其他事务来说已经无效的旧数据行。
很多人可能不知道，除了vacuum会清理死元组，实际上update、delete 也会清理死元组。

update

update在特定情况下也会将死元组清理掉 ，创建一个填充率为100的，并禁用其autovacuum

DROP TABLE IF EXISTS test_fillfactor;

CREATE TABLE test_fillfactor (
    id SERIAL PRIMARY KEY,
    content TEXT
)  WITH (fillfactor = 100,autovacuum_enabled = false, toast.autovacuum_enabled = false);  
复制

向其中插入刚刚分页的数据量

INSERT INTO test_fillfactor (content)
SELECT repeat('a', 111) || generate_series(1 ,57);

复制

我们使用pg_dirtyread去看其死元组情况。

create extension pg_dirtyread;
SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) order by ctid desc limit 5 ;

复制

此时我们更新第一页的最后一条数据和新页第一条数据（id=55和id=56），由于填充因子是100 此时新的数据并不会追加的页末尾。

checkpoint; --执行检查点，让其fsync
UPDATE test_fillfactor SET content = repeat('b', 111)||'(1.1)' WHERE id=56;
UPDATE test_fillfactor SET content = repeat('b', 111)||'(0.55)' WHERE id=55;
SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) order by ctid desc limit 5 ;  --此时的死元组依然存在。
复制

当执行一段查询之后

SELECT txid_current(),xmin, xmax, ctid, * FROM test_fillfactor  order by ctid desc limit 5 ;
SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT)  order by ctid desc limit 5 ;  --此时的死元组id=55的便消失。

复制

此时通过系统视图查看其仍然有两条死元组,其中一条update 是n_tup_hot_upd，另一条并没有追加到也末尾，(0.55)数据追加到了(1,4）。

select  *  from pg_stat_user_tables where  relname ='test_fillfactor';
复制

我们再通过pageinspect跟踪看一下0页的数据情况

SELECT * FROM heap_page_items(get_raw_page('test_fillfactor', 0)) order by t_ctid desc limit 5 ;;
复制

此时的末页数据已经被擦掉

通过数据文件再次观察0页面数据情况。

SELECT pg_relation_filepath('test_fillfactor');
\! hexdump -C  base/5/32995 | less
复制

文件中依然可以看到55这条数据存在

此时再对0页末尾数据进行update观察其他其数据是否成为hot_update数据
再进行数据插入操作，观察是否进行末尾填充

UPDATE test_fillfactor SET content = repeat('b', 111)||'(0.55)' WHERE id=54;

INSERT INTO test_fillfactor (content)
SELECT repeat('a', 111) || '58' ;
复制

此时新更新的数据成为n_tup_hot_upd ，表明之前的(0.55)的死元组已经被情况并腾出了空间被复用。
update清理死元组空间
此时的表文件已经没有之前最老的55数据

delete

这里我们重建测试表数据。同样的将update改成delete 进行测试

DROP TABLE IF EXISTS test_fillfactor;

CREATE TABLE test_fillfactor (
    id SERIAL PRIMARY KEY,
    content TEXT
)  WITH (fillfactor = 100,autovacuum_enabled = false, toast.autovacuum_enabled = false);  
INSERT INTO test_fillfactor (content)
SELECT repeat('a', 111) ||   generate_series(1 ,57);
SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) order by ctid desc limit 5 ;
复制

进行delete 删掉叶尾、页头数据

delete from test_fillfactor WHERE id=56;
delete from test_fillfactor  WHERE id=55;
SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) order by ctid desc limit 5 ;

复制

此时表中数据仍然还在数据页中

 SELECT * FROM heap_page_items(get_raw_page('test_fillfactor', 0))  order by t_ctid desc limit 7;  --只是被flag了对xmax不可见。

复制

此时数据末尾页面的数据还没被擦掉。当我们此时对表进行插入数据和数据更新时

UPDATE test_fillfactor SET content = repeat('b', 111)||'(0.54)' WHERE id=54;

INSERT INTO test_fillfactor (content)
SELECT repeat('a', 111) || '58';
复制

我们更新了54，新的数据进入了页末尾成了n_tup_hot_upd。并且此时id=55的旧数据已被擦掉。

SELECT * FROM heap_page_items(get_raw_page('test_fillfactor', 0))  order by t_ctid desc limit 7;  
select  *  from pg_stat_user_tables where  relname ='test_fillfactor';
复制

此时查看数据文件55旧数据已经失效

SELECT pg_relation_filepath('test_fillfactor');
\! hexdump -C  base/5/33004| less
复制

cluster

当然本文讲解主要不是讲update、delete对死元组的清理作用。主要是cluster对死元组清理的功能
再次创建测试表

DROP TABLE IF EXISTS test_fillfactor;

CREATE TABLE test_fillfactor (
    id SERIAL PRIMARY KEY,
    content TEXT
)  WITH (fillfactor = 100,autovacuum_enabled = false, toast.autovacuum_enabled = false);  
INSERT INTO test_fillfactor (content)
SELECT repeat('a', 111) ||   generate_series(1 ,57);
checkpoint;
SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) order by ctid desc limit 5 ;
 SELECT txid_current(),xmin, xmax, ctid, * FROM test_fillfactor  order by ctid desc limit 5 ;
复制

同样的，我们依然更新页尾页首更一条数据

UPDATE test_fillfactor SET content = repeat('b', 111)||'(1.1)' WHERE id=56;
UPDATE test_fillfactor SET content = repeat('b', 111)||'(0.55)' WHERE id=55;
SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) order by ctid desc limit 7;
复制

cluster 需要借助索引键进行聚族，

 cluster test_fillfactor using test_fillfactor_pkey;
 SELECT txid_current(),xmin, xmax, ctid, * FROM test_fillfactor  order by ctid desc limit 10 ;
 SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) where dead=true order by ctid desc limit 10 ;  
  SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT)  order by ctid desc limit 10 ;  
复制

可以看到cluster 之后，其对表数据进行重新聚族并清空死元组位置。由于最开始的（1，4）推进了两位到（1，2）
再次插入数据，此时数据会依次插入在数据页中

INSERT INTO test_fillfactor (content)
SELECT repeat('a', 111) || '58';
INSERT INTO test_fillfactor (content)
SELECT repeat('a', 111) || '59';
 SELECT txid_current(),xmin, xmax, ctid, * FROM test_fillfactor  order by ctid desc limit 10 ;
  SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT) where dead=true order by ctid desc limit 10 ;  
 SELECT txid_current(),t.* FROM pg_dirtyread('test_fillfactor') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id integer,content TEXT)  order by ctid desc limit 10 ;  
复制

我们此时再次查看数据文件内容

SELECT pg_relation_filepath('test_fillfactor');
\! hexdump -C  base/5/33030| less
复制

此时55旧的数据位置已经被更新掉。