POSTGRES: How do I understand the real number of dead tuples in a given table-CodePudding

I have a database transactions:

                                                Table "public.transaction"
      Column       |           Type           | Collation | Nullable |                       Default
------------------- -------------------------- ----------- ---------- -----------------------------------------------------
 transaction_id    | bigint                   |           | not null | nextval('transaction_transaction_id_seq'::regclass)
 created_at        | timestamp with time zone |           | not null |
 source_type       | character varying        |           | not null |
 source_id         | character varying        |           | not null |
 source_event_type | character varying        |           | not null |
 source_event_id   | character varying        |           |          |
 ingested_at       | timestamp with time zone |           | not null | now()
 member_account_id | character varying        |           |          |
 reason            | character varying        |           |          |
 reversed_at       | timestamp with time zone |           |          |
 reverses          | bigint                   |           |          |
Indexes:
    "pk_transaction" PRIMARY KEY, btree (transaction_id)
    "ix_transaction_created_at" btree (created_at)
    "ix_transaction_ingested_at" btree (ingested_at)
    "ix_transaction_member_account_id" btree (member_account_id)
    "ix_transaction_reverses" btree (reverses)
    "ix_transaction_source_event_id" btree (source_event_id)
    "ix_transaction_source_event_type" btree (source_event_type)
    "ix_transaction_source_id" btree (source_id)
    "ix_transaction_source_type" btree (source_type)
Foreign-key constraints:
    "fk_transaction_reverses_transaction" FOREIGN KEY (reverses) REFERENCES transaction(transaction_id)
Referenced by:
    TABLE "distributor_transaction_extras_es" CONSTRAINT "fk_distributor_transaction_extras_es_transaction_id_transaction" FOREIGN KEY (transaction_id) REFERENCES transaction(transaction_id)
    TABLE "distributor_transaction_extras_tx" CONSTRAINT "fk_distributor_transaction_extras_tx_transaction_id_transaction" FOREIGN KEY (transaction_id) REFERENCES transaction(transaction_id)
    TABLE "entry" CONSTRAINT "fk_entry_transaction_id_transaction" FOREIGN KEY (transaction_id) REFERENCES transaction(transaction_id)
    TABLE "transaction" CONSTRAINT "fk_transaction_reverses_transaction" FOREIGN KEY (reverses) REFERENCES transaction(transaction_id)

This table bloat query suggests that the table is quite bloated.

current_database|tablename                        |tbloat|wastedbytes|
---------------- --------------------------------- ------ ----------- 
ledger          |transaction                      |   3.2| 3055730688|

I wanted to check when the AutoVacuum daemon last ran:

SELECT relname, last_vacuum, vacuum_count, last_autovacuum, autovacuum_count, last_analyze, analyze_count 
FROM pg_stat_user_tables 
WHERE relname = 'transaction';
> 
relname    |last_vacuum|vacuum_count|last_autovacuum|autovacuum_count|last_analyze|analyze_count|
----------- ----------- ------------ --------------- ---------------- ------------ ------------- 
transaction|           |           0|               |               0|            |            0|

This shows that the daemon never ran.

The docs say that the autovacuum daemon runs when:

num_obselete_tuples > threshold (scale_factor*num_dead_tuples)

My autovacuum settings are:

select name, setting from pg_settings where <...>;
> 
name                          |setting|
------------------------------ ------- 
autovacuum_vacuum_scale_factor|0.2    |
autovacuum_vacuum_threshold   |50     |

and the number of tuples is:

select relname, reltuples from pg_class where relname ilike 'transaction' order by relname;
> 
relname    | reltuples |
----------- ----------- 
transaction| 10,154,868|

so I'm expecting the autovacuum daemon to run when there are ~2,000,000 dead tuples.

This previous answer suggests two ways of determining the number of dead tuples but they give different numbers.

SELECT n_dead_tup FROM pg_stat_user_tables where relname = ‘transaction’;
> 
|n_dead_tup|
 ---------- 
|   1177157|

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('transaction'::regclass::oid);
>
table_len |tuple_count|tuple_len |tuple_percent|dead_tuple_count|dead_tuple_len|dead_tuple_percent|free_space|free_percent|
---------- ----------- ---------- ------------- ---------------- -------------- ------------------ ---------- ------------ 
4424474624|    8964244|1230033038|         27.8|           20024|       2893272|              0.07|3029812788|       68.48|

In addition inspection of the primary key sequence shows that the table has had very large delete activity.

select * from transaction_transaction_id_seq;
>
last_value|
---------- 
 487641810|


select count(*) from transaction;
>
count  |
------- 
8964244|

So the sequence count is ~400,000,000 but the number of rows is around 9,000,000.

What's going on here?

How can the table be bloated AND show a very large number of DELETES but the results for number of obsolete tuples are only ~1,000,000?

I would expect that very heavy DELETE activity would cause a large number of obsolete tuples and then trigger the AutoVacuum.

Why isn't the number of obsolete tuples in the 100's of millions?

Which number of obsolete tuples is correct from the two queries, and why are they different?

I know that I can change the AutoVacuum settings to force the daemon to be more aggressive but I want to know why the table thinks there aren't enough obsolete tuples to trigger the daemon, even though the available evidence suggests otherwise.

CodePudding user response：

The data in pg_stat_user_tables is not transactional. It is reset to zero/NULL whenever the system crashes or has an unclean shutdown (and can also be reset manually). That has presumably happened.

The high freepercent reported by pgstattuple would seem to indicate that the table has been vacuumed at some point, now forgotten about. You could check the freespace map to make sure PostgreSQL knows how to find this free space when it needs it.

CodePudding user response：

Good question. Basically, you can use below SQL to get all the "n_dead_tup, n_live_tup, n_tup_ins, n_tup_upd, n_tup_del" stats information. And you will know how many tuples are inserted, updated, deleted. Just as a proof.

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
    last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, 
    n_tup_ins, n_tup_upd, n_tup_del, vacuum_count, autovacuum_count
FROM information_schema.tables, pg_stat_all_tables stat
WHERE table_schema = 'public' and stat.schemaname = 'public' 
    and stat.relname = table_name 
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')

Here, you can update the SQL with your own target table name.

Hope it can help you.