1

table tenk1: source

create table    tenk_copy (like tenk1 INCLUDING ALL);
insert into     tenk_copy select * from tenk1;
alter table     tenk_copy set (autovacuum_enabled = false);
ALTER TABLE     tenk_copy   SET (autovacuum_vacuum_scale_factor = 0.9);
select relname, relowner::regrole, reloptions   from pg_class pc    where pc.relname = 'tenk_copy';

return

  relname  | relowner |                          reloptions
-----------+----------+---------------------------------------------------------------
 tenk_copy | test     | {autovacuum_enabled=false,autovacuum_vacuum_scale_factor=0.9}
(1 row)
--1
begin;
update tenk_copy set unique1 = unique1 + 1;
update tenk_copy set unique1 = unique1 - 1;

update tenk_copy set unique1 = unique1 + 1;
update tenk_copy set unique1 = unique1 - 1;

update tenk_copy set unique1 = unique1 + 1;
update tenk_copy set unique1 = unique1 - 1;

update tenk_copy set unique1 = unique1 + 1;
update tenk_copy set unique1 = unique1 - 1;

update tenk_copy set unique1 = unique1 + 1;
update tenk_copy set unique1 = unique1 - 1;
commit;
select  * from pgstattuple('tenk_copy') \gx

return

-[ RECORD 1 ]------+---------
table_len          | 31080448
tuple_count        | 10000
tuple_len          | 2680000
tuple_percent      | 8.62
dead_tuple_count   | 100000
dead_tuple_len     | 26800000
dead_tuple_percent | 86.23
free_space         | 614216
free_percent       | 1.98

run the --1 query again. then query the pgstattuple.

select  * from pgstattuple('tenk_copy') \gx
-[ RECORD 1 ]------+---------
table_len          | 59326464
tuple_count        | 10000
tuple_len          | 2680000
tuple_percent      | 4.52
dead_tuple_count   | 100000
dead_tuple_len     | 26800000
dead_tuple_percent | 45.17
free_space         | 28363688
free_percent       | 47.81

with 20 updates dead_tuple_count only 100000. I guess it means more unused space created, how can I config to make it actually have more dead tuples.

2
  • What is the structure of tenk (or tenk_copy)? Please put this information as text in the question itself and not as a comment - notify me by comment.
    – Vérace
    May 5, 2023 at 12:29
  • @Vérace in the first link.
    – jian
    May 5, 2023 at 12:30

2 Answers 2

2

Once no one cares about the tuple anymore, its space can be freed. The line pointer pointing to the tuple can't be freed, it is just turned into a tombstone (so that indexes pointing to it know to ignore it). A vacuum is not needed to convert the tuple into a tombstone, it is just needed to remove the tombstone itself. Apparently pgstattuple doesn't count tombstones as being part of dead_tuple_count.

how can I config to make it actually have more dead tuples.

Do more updates in the same transaction, or open a new session and begin a transaction which holds its snapshot and then keep it open. But why do you want to?

1

VACUUM was run on the table. If indeed autovacuum_enabled=false (double check!), it could only be one of these:

  • someone ran VACUUM on the table manually

  • autovacuum_freeze_max_age was set to such a ridiculously low value that anti-wraparound autovacuum was triggered (which runs even when autovacuum is disabled). This should leave a log entry if log_autovacuum_min_duration = 0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.