-- Dead rows of the column groups: VACUUM counts live and dead rows as it does for a heap (reltuples, pg_stat_user_tables), rewrites
-- a column segment alone once more than sift.table_dead_rewrite_pct percent of it is dead.
CREATE EXTENSION pg_sift;
-- (vacuum_truncate off: moving a segment down for the cut would also drop its dead rows)
CREATE TABLE d (id int, v text) USING sift_table WITH (autovacuum_enabled = false, vacuum_truncate = false);
CREATE VIEW dseg AS SELECT rows, dead_rows FROM sift.table_segments('d') WHERE kind = 'columns';
CREATE VIEW dstat AS SELECT c.reltuples, s.n_live_tup, s.n_dead_tup FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid WHERE c.relname = 'd';
INSERT INTO d SELECT g, 'v' || g FROM generate_series(1, 20000) g;
SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

VACUUM d;
SELECT * FROM dseg;
 rows  | dead_rows 
-------+-----------
 20000 |         0
(1 row)

SELECT * FROM dstat;
 reltuples | n_live_tup | n_dead_tup 
-----------+------------+------------
     20000 |      20000 |          0
(1 row)

-- 20 percent dead: under the default threshold (30) the segment stays, its dead rows are counted out
DELETE FROM d WHERE id % 10 < 2;
SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

VACUUM d;
SELECT * FROM dseg;
 rows  | dead_rows 
-------+-----------
 20000 |      4000
(1 row)

SELECT * FROM dstat;
 reltuples | n_live_tup | n_dead_tup 
-----------+------------+------------
     16000 |      16000 |          0
(1 row)

-- a lower threshold rewrites it alone, without its dead rows
SET sift.table_dead_rewrite_pct = 15;
VACUUM d;
SELECT * FROM dseg;
 rows  | dead_rows 
-------+-----------
 16000 |         0
(1 row)

RESET sift.table_dead_rewrite_pct;
-- 100 never rewrites; the default then does
SET sift.table_dead_rewrite_pct = 100;
DELETE FROM d WHERE id % 10 < 9;
VACUUM d;
SELECT * FROM dseg;
 rows  | dead_rows 
-------+-----------
 16000 |     14000
(1 row)

RESET sift.table_dead_rewrite_pct;
VACUUM d;
SELECT * FROM dseg;
 rows | dead_rows 
------+-----------
 2000 |         0
(1 row)

SELECT * FROM dstat;
 reltuples | n_live_tup | n_dead_tup 
-----------+------------+------------
      2000 |       2000 |          0
(1 row)

SELECT count(*), sum(id) FROM d;
 count |   sum    
-------+----------
  2000 | 20008000
(1 row)

-- every row deleted: the segment goes
DELETE FROM d;
VACUUM d;
SELECT count(*) AS segments FROM dseg;
 segments 
----------
        0
(1 row)

SELECT * FROM dstat;
 reltuples | n_live_tup | n_dead_tup 
-----------+------------+------------
         0 |          0 |          0
(1 row)

SHOW sift.table_dead_rewrite_pct;
 sift.table_dead_rewrite_pct 
-----------------------------
 30
(1 row)

SET sift.table_dead_rewrite_pct = 0;
ERROR:  0 is outside the valid range for parameter "sift.table_dead_rewrite_pct" (1 .. 100)
-- one VACUUM merges until nothing is left to merge: 40 groups, each 40 percent dead, none above the threshold after it
CREATE TABLE d2 (id int, v text) USING sift_table WITH (autovacuum_enabled = false, vacuum_truncate = false);
\set ECHO none
DELETE FROM d2 WHERE id % 10 < 4;
VACUUM d2;
SELECT count(*) FILTER (WHERE dead_rows * 100 > rows * 30) AS over_threshold, sum(rows - dead_rows) AS live FROM sift.table_segments('d2') WHERE kind = 'columns';
 over_threshold | live  
----------------+-------
              0 | 24000
(1 row)

SELECT count(*), sum(id) FROM d2;
 count |    sum    
-------+-----------
 24000 | 480036000
(1 row)

DROP TABLE d2;
DROP VIEW dseg, dstat;
DROP TABLE d;
DROP EXTENSION pg_sift;