Skip to content

Instantly share code, notes, and snippets.

@araqnid
Last active February 16, 2021 10:29
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save araqnid/1298358 to your computer and use it in GitHub Desktop.
Save araqnid/1298358 to your computer and use it in GitHub Desktop.
example script demonstrating heap-only tuples on postgresql; you need the pageinspect extension installed
create temp table t1(t1_id serial primary key, reference varchar(16) not null unique, value varchar(16) not null);
copy t1(reference, value) from stdin;
FOO foo
BAR bar
QUUX quux
\.
create temp view t1_combined as
select t1_id, reference, value, ctid, lp_flags, lp_off, case when t_ctid <> ctid then t_ctid end as t_ctid,
t_xmin, xmin_visible, case when t_xmax::text <> '0' then t_xmax end as t_xmax, xmax_visible,
xmin_visible and (xmax_visible is null or not xmax_visible or t_locked <> '') as visible, t_hot_updated, t_heap_only
from (select *,
t_xmin_valid and txid_visible_in_snapshot(t_xmin::text::bigint, txid_current_snapshot()) as xmin_visible,
t_xmax_valid and txid_visible_in_snapshot(t_xmax::text::bigint, txid_current_snapshot()) as xmax_visible
from (select ('(' || 0 || ',' || lp || ')')::tid as ctid,
lp, lp_off, case lp_flags when 0 then 'UNUSED' when 1 then 'NORMAL' when 2 then 'REDIRECT' when 3 then 'DEAD' end as lp_flags,
lp_len, t_xmin, t_xmax, t_field3, t_ctid, (t_infomask&1)<>0 as t_hasnull, (t_infomask&2)<>0 as t_hasvarwidth,
(t_infomask&4)<>0 as t_hasexternal, (t_infomask&8)<>0 as t_hasoid, (t_infomask&32)<>0 as t_combocid,
case t_infomask & 192 when 64 then 'EXCL' when 128 then 'SHARE' when 0 then '' when 192 then 'INVALID' end as t_locked,
(t_infomask&256)<>0 as t_xmin_committed, (t_infomask&512)=0 as t_xmin_valid,
(t_infomask&1024)<>0 as t_xmax_committed, (t_infomask&2048)=0 as t_xmax_valid,
(t_infomask&4096)<>0 as t_xmax_is_multi, (t_infomask&8192)<>0 as t_updated,
(t_infomask&16384)<>0 as t_moved_off, (t_infomask&32768)<>0 as t_moved_in,
t_infomask2&2047 as t_natts, (t_infomask2&16384)<>0 as t_hot_updated,
(t_infomask2&32768)<>0 as t_heap_only,
t_hoff, t_bits, t_oid
from heap_page_items(get_raw_page('t1', 0))) format_heap_page_items
) heap
full outer join (select ctid, * from t1) t1 using (ctid);
create temp view t1_indices as
select ctid, pkey_content.itemoffset as pkey_itemoffset, pkey_content.data as pkey_data, auxkey_content.itemoffset as auxkey_itemoffset, auxkey_content.data as auxkey_data
from bt_page_items('t1_pkey', 1) pkey_content
full outer join bt_page_items('t1_reference_key', 1) auxkey_content using (ctid);
\echo ********************************************************************************
\echo * Initial table
\echo
select * from t1_combined;
select * from t1_indices;
\echo ********************************************************************************
\echo * Update non-indexed column
\echo * - index entries untouched
\echo * - old tuple at ctid (0,1) has t_hot_updated set
\echo * - new tuple at ctid (0,4) has t_heap_only set
\echo * - t_ctid of (0,1) points to (0,4)
\echo
begin;
update t1 set value = 'mumble' where t1_id = 1;
end;
select * from t1_combined;
select * from t1_indices;
\echo ********************************************************************************
\echo * Update non-indexed column again
\echo * - tuple at ctid (0,4) now just points to ctid (0,5) and is redundant
\echo
begin;
update t1 set value = 'womble' where t1_id = 1;
end;
select * from t1_combined;
select * from t1_indices;
\echo ********************************************************************************
\echo * Vacuum table
\echo * - line pointer ctid (0,1) converted to REDIRECT since index entries still point to it
\echo * - redundant tuple at ctid (0,4) reclaimed for reuse
\echo
vacuum t1;
select * from t1_combined;
select * from t1_indices;
\echo ********************************************************************************
\echo * Update indexed column
\echo * - New index entries written for new tuple at ctid (0,4) which is now reused
\echo
update t1 set reference = 'WOMBLE' where t1_id = 1;
select * from t1_combined;
select * from t1_indices;
\echo ********************************************************************************
\echo * Update indexed column to contain same value
\echo * - even though indexed column is mentioned in update, this makes a heap-only change
\echo * - current version is now (0,6) but indices still indicate (0,4)
\echo
update t1 set reference = 'WOMBLE', value = 'womble2' where t1_id = 1;
select * from t1_combined;
select * from t1_indices;
\echo ********************************************************************************
\echo * Vacuum table
\echo * - ctid (0,1) now reclaimed, index entries pointing to it removed
\echo * - ctid (0,5) reclaimed too, it never had index entries pointing to it
\echo
vacuum t1;
select * from t1_combined;
select * from t1_indices;
psql:pg_hot_example.sql:1: NOTICE: CREATE TABLE will create implicit sequence "t1_t1_id_seq" for serial column "t1.t1_id"
psql:pg_hot_example.sql:1: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
psql:pg_hot_example.sql:1: NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_reference_key" for table "t1"
CREATE TABLE
CREATE VIEW
CREATE VIEW
********************************************************************************
* Initial table
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only
───────┼───────────┼───────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼─────────────
1 │ FOO │ foo │ (0,1) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8072 │ │ 30268 │ t │ │ f │ t │ f │ f
(3 rows)
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data
───────┼─────────────────┼─────────────────────────┼───────────────────┼─────────────────────────
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00
(3 rows)
********************************************************************************
* Update non-indexed column
* - index entries untouched
* - old tuple at ctid (0,1) has t_hot_updated set
* - new tuple at ctid (0,4) has t_heap_only set
* - t_ctid of (0,1) points to (0,4)
BEGIN
UPDATE 1
COMMIT
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼─────────────
│ │ │ (0,1) │ NORMAL │ 8152 │ (0,4) │ 30268 │ t │ 30271 │ t │ f │ t │ f
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8072 │ │ 30268 │ t │ │ f │ t │ f │ f
1 │ FOO │ mumble │ (0,4) │ NORMAL │ 8032 │ │ 30271 │ t │ │ f │ t │ f │ t
(4 rows)
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data
───────┼─────────────────┼─────────────────────────┼───────────────────┼─────────────────────────
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00
(3 rows)
********************************************************************************
* Update non-indexed column again
* - tuple at ctid (0,4) now just points to ctid (0,5) and is redundant
BEGIN
UPDATE 1
COMMIT
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼─────────────
│ │ │ (0,1) │ NORMAL │ 8152 │ (0,4) │ 30268 │ t │ 30271 │ t │ f │ t │ f
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8072 │ │ 30268 │ t │ │ f │ t │ f │ f
│ │ │ (0,4) │ NORMAL │ 8032 │ (0,5) │ 30271 │ t │ 30272 │ t │ f │ t │ t
1 │ FOO │ womble │ (0,5) │ NORMAL │ 7992 │ │ 30272 │ t │ │ f │ t │ f │ t
(5 rows)
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data
───────┼─────────────────┼─────────────────────────┼───────────────────┼─────────────────────────
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00
(3 rows)
********************************************************************************
* Vacuum table
* - line pointer ctid (0,1) converted to REDIRECT since index entries still point to it
* - redundant tuple at ctid (0,4) reclaimed for reuse
VACUUM
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼─────────────
│ │ │ (0,1) │ REDIRECT │ 5 │ │ │ │ │ │ │ │
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f
│ │ │ (0,4) │ UNUSED │ 0 │ │ │ │ │ │ │ │
1 │ FOO │ womble │ (0,5) │ NORMAL │ 8072 │ │ 30272 │ t │ │ f │ t │ f │ t
(5 rows)
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data
───────┼─────────────────┼─────────────────────────┼───────────────────┼─────────────────────────
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00
(3 rows)
********************************************************************************
* Update indexed column
* - New index entries written for new tuple at ctid (0,4) which is now reused
UPDATE 1
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼─────────────
│ │ │ (0,1) │ REDIRECT │ 5 │ │ │ │ │ │ │ │
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f
1 │ WOMBLE │ womble │ (0,4) │ NORMAL │ 8024 │ │ 30273 │ t │ │ f │ t │ f │ f
│ │ │ (0,5) │ NORMAL │ 8072 │ (0,4) │ 30272 │ t │ 30273 │ t │ f │ f │ t
(5 rows)
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data
───────┼─────────────────┼─────────────────────────┼───────────────────┼─────────────────────────
(0,1) │ 2 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00
(0,2) │ 3 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00
(0,3) │ 4 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00
(0,4) │ 1 │ 01 00 00 00 00 00 00 00 │ 4 │ 0f 57 4f 4d 42 4c 45 00
(4 rows)
********************************************************************************
* Update indexed column to contain same value
* - even though indexed column is mentioned in update, this makes a heap-only change
* - current version is now (0,6) but indices still indicate (0,4)
UPDATE 1
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only
───────┼───────────┼─────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼─────────────
│ │ │ (0,1) │ REDIRECT │ 5 │ │ │ │ │ │ │ │
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f
│ │ │ (0,4) │ NORMAL │ 8024 │ (0,6) │ 30273 │ t │ 30274 │ t │ f │ t │ f
│ │ │ (0,5) │ NORMAL │ 8072 │ (0,4) │ 30272 │ t │ 30273 │ t │ f │ f │ t
1 │ WOMBLE │ womble2 │ (0,6) │ NORMAL │ 7976 │ │ 30274 │ t │ │ f │ t │ f │ t
(6 rows)
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data
───────┼─────────────────┼─────────────────────────┼───────────────────┼─────────────────────────
(0,1) │ 2 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00
(0,2) │ 3 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00
(0,3) │ 4 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00
(0,4) │ 1 │ 01 00 00 00 00 00 00 00 │ 4 │ 0f 57 4f 4d 42 4c 45 00
(4 rows)
********************************************************************************
* Vacuum table
* - ctid (0,1) now reclaimed, index entries pointing to it removed
* - ctid (0,5) reclaimed too, it never had index entries pointing to it
VACUUM
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only
───────┼───────────┼─────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼─────────────
│ │ │ (0,1) │ UNUSED │ 0 │ │ │ │ │ │ │ │
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f
│ │ │ (0,4) │ REDIRECT │ 6 │ │ │ │ │ │ │ │
│ │ │ (0,5) │ UNUSED │ 0 │ │ │ │ │ │ │ │
1 │ WOMBLE │ womble2 │ (0,6) │ NORMAL │ 8064 │ │ 30274 │ t │ │ f │ t │ f │ t
(6 rows)
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data
───────┼─────────────────┼─────────────────────────┼───────────────────┼─────────────────────────
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 2 │ 0b 51 55 55 58 00 00 00
(0,4) │ 1 │ 01 00 00 00 00 00 00 00 │ 3 │ 0f 57 4f 4d 42 4c 45 00
(3 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment