1

I'm struggling with a bloated table that I'm unable to shrink. It has just 6 rows but its size is 140MB+ and it's continously updated\deleted by quick transactions. I tried using VACUUM and VACUUM FULL but there's no result.

These are the table structure and the related statistics:

\d bloated_table
         COLUMN          |            TYPE             | Collation | Nullable | DEFAULT
-------------------------+-----------------------------+-----------+----------+---------
 col1                    | BIGINT                      |           | NOT NULL |
 <omissis>               | CHARACTER varying(100)      |           |          |
 <omissis>               | CHARACTER varying(50)       |           |          |
 <omissis>               | TIMESTAMP WITHOUT TIME ZONE |           |          |
 <omissis>               | BIGINT                      |           |          |
 <omissis>               | BIGINT                      |           |          |
 <omissis>               | BIGINT                      |           |          |
 <omissis>               | TEXT                        |           |          |
INDEXES:
    "<omissis>" PRIMARY KEY, btree (col1)
Referenced BY:
    TABLE "<omissis>" CONSTRAINT "<omissis>" FOREIGN KEY (col1) REFERENCES <omissis>(col1)

SELECT ROUND(n_dead_tup::NUMERIC/NULLIF(n_live_tup::NUMERIC,0),2), * 
FROM pg_catalog.pg_stat_user_tables 
WHERE n_dead_tup>0 
ORDER BY 1 DESC NULLS LAST
FETCH FIRST ROW ONLY;

   round   |  relid   | schemaname           |          relname           | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-----------+----------+----------------------+----------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+--------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
 215689.17 | 59328017 | db_bloated_table     | bloated_table              | 27462255 |    279950918 |      679 |           691 |        20 |  25895488 |        14 |      25476514 |          6 |    1294135 |                   7 |                  0 | 2022-07-06 07:32:24.031073+00 | 2022-07-06 07:39:54.601903+00 | 2022-07-05 22:06:37.492046+00 | 2022-07-06 07:39:54.657717+00 |           30 |            39195 |            26 |             38875

The table size is 143MB:

SELECT pg_size_pretty(pg_total_relation_size('bloated_table'::regclass));
 pg_size_pretty
----------------
 143 MB

UPDATE HERE sorry, I badly pasted the VACUUM output: Following the VACUUM output:

> vacuum (verbose) bloated_table;
INFO:  vacuuming "argodb.bloated_table"
INFO:  "bloated_table": found 0 removable, 25570 nonremovable row versions in 343 out of 343 pages
DETAIL:  25564 dead row versions cannot be removed yet, oldest xmin: 87657915
There were 16 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "pg_toast.pg_toast_59328017"
INFO:  "pg_toast_59328017": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 87657915
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

> vacuum (full,verbose) bloated_table;
INFO:  vacuuming "argodb.bloated_table"
INFO:  "bloated_table": found 0 removable, 29068 nonremovable row versions in 389 pages
DETAIL:  29062 dead row versions cannot be removed yet.
CPU: user: 0.03 s, system: 0.01 s, elapsed: 0.06 s.
VACUUM

And finally, there aren't any long-last opened transaction, abandoned replication slots nor orphaned prepared transactions:

--Abandoned replication slots
>SELECT slot_name, slot_type, DATABASE, xmin
FROM pg_replication_slots
ORDER BY AGE(xmin) DESC;
 slot_name | slot_type | DATABASE | xmin
-----------+-----------+----------+------
(0 ROWS)
 
 
--Orphaned prepared transactions
> SELECT gid, PREPARED, OWNER, DATABASE, TRANSACTION AS xmin
FROM pg_prepared_xacts
ORDER BY AGE(TRANSACTION) DESC;
 gid | PREPARED | OWNER | DATABASE | xmin
-----+----------+-------+----------+------
(0 ROWS)
 

My environment is the following: PG 13.6 on MS Azure Flexible Server

Thank you in advance for your help.

  • 1
    did you check idle_in_transaction sessions? – Nikhil B Jul 06 '22 at 20:59
  • 1
    I would recommend killing all sessions with "select pg_terminate_backend(pid) from pg_stat_activity where state like 'idle%';" and making sure their are no active queries running for more than few minutes and then run vacuum verbose – Nikhil B Jul 06 '22 at 21:02
  • 1
    Hi @NikhilB. There aren't any idle in transaction session in our environment and terminating all the pids doesn't apply well to my use case as we're on production environment, but for sure I'll keep in mind at the first occasion. – Giorgio Morina Jul 06 '22 at 21:13
  • There *must* be something that holds back `VACUUM` progress. `VACUUM (VERBOSE)` will give you a clue of the "xmin horizon". The cause could also be a standby server with `hot_standby_feedback = on` and a long running query. – Laurenz Albe Jul 07 '22 at 06:13
  • 1
    @LaurenzAlbe thank you. I updated the `VACUUM (VERBOSE)` output. Could you advice on how could I take advantage of `xmin horizon` info? – Giorgio Morina Jul 07 '22 at 07:27
  • One of open transactions, prepared transactions, replication slots or standby servers must be holding the xmin horizon back. Read [this](https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/), it contains queries to determine the source of the problem. – Laurenz Albe Jul 07 '22 at 09:01

0 Answers0