lightdb=# insert into test_xmin_xmax values(1);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(2);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(3);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(4);
INSERT 0 1
lightdb=# insert into test_xmin_xmax values(5);
INSERT 0 1
lightdb=# select * from heap_page_items(get_raw_page('test_xmin_xmax',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 5670928 | 0 | 0 | (0,1) | 1 | 2048 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 5670936 | 0 | 0 | (0,2) | 1 | 2048 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 5670956 | 0 | 0 | (0,3) | 1 | 2048 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 5670968 | 0 | 0 | (0,4) | 1 | 2048 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 5670976 | 0 | 0 | (0,5) | 1 | 2048 | 24 | | | \x05000000
(5 rows)
lightdb=# delete from test_xmin_xmax where id = 3;
DELETE 1
postgres=# select * from heap_page_items(get_raw_page('test_xmin_xmax',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+---------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 5670928 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 5670936 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 5670956 | 5671778 | 0 | (0,3) | 8193 | 256 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 5670968 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 5670976 | 0 | 0 | (0,5) | 1 | 2304 | 24 | | | \x05000000
(5 rows)
t_xmin表示插入动作时的tranction id，t_xmax表示delete动作时的 tranction id
session1：
开启一个事务
lightdb=# begin; BEGIN lightdb=*# insert into test values(1); INSERT 0 1 lightdb=*#
session2:
lightdb=# analyze test; ANALYZE lightdb=# \dt test List of relations Schema | Name | Type | Owner --------+------+-------+--------- public | test | table | lightdb (1 row) lightdb=# \dt+ test List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+------+-------+---------+-------------+---------+------------- public | test | table | lightdb | permanent | 1610 MB | (1 row) lightdb=# delete from test ; DELETE 19999998 lightdb=# \dt+ test List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+------+-------+---------+-------------+---------+------------- public | test | table | lightdb | permanent | 1610 MB | (1 row) lightdb=# vacuum test; VACUUM lightdb=# \dt+ test List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+------+-------+---------+-------------+-------+------------- public | test | table | lightdb | permanent | 24 kB | (1 row)
看到可以通过vacuum收缩表
/* * HeapTupleSatisfiesVacuum * *Determine the status of tuples for VACUUM purposes. Here, what *we mainly want to know is if a tuple is potentially visible to *any* *running transaction. If so, it can't be removed yet by VACUUM. * * OldestXmin is a cutoff XID (obtained from GetOldestXmin()). Tuples * deleted by XIDs >= OldestXmin are deemed "recently dead"; they might * still be visible to some open transaction, so we can't remove them, * even if we see that the deleting transaction has committed. */ HTSV_Result HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin, Buffer buffer) ... if (!TransactionIdPrecedes(xmax, OldestXmin)) return HEAPTUPLE_RECENTLY_DEAD; ...
但是在PG14中
已经不错在这部分逻辑内容，可以收缩表