It seems vacuum is behaving somewhat weird on postgres database , observing below HINTS on the vacuum logs
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
Below is the auto-vacuum status on the bloated tables:
=> SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
/ (n_live_tup
* current_setting('autovacuum_vacuum_scale_factor')::float8
+ current_setting('autovacuum_vacuum_threshold')::float8)
DESC
LIMIT 10;
schemaname | relname | n_live_tup | n_dead_tup | last_autovacuum
------------+---------------------+------------+------------+-------------------------------
pg_catalog | pg_statistic | 136 | 37563 | 2022-04-18 04:00:21.045089+00
public | test1 | 209405206 | 126752908 | 2022-04-18 03:59:43.013758+00
public | test2 | 513770985 | 49258312 | 2022-04-18 04:00:23.24043+00
public | test3 | 90853150 | 4090146 | 2022-04-18 04:00:25.868147+00
pg_catalog | pg_shdepend | 153 | 29 | 2022-04-08 12:16:02.816631+00
pg_catalog | pg_index | 73 | 18 |
pg_toast | pg_toast_2619 | 16 | 12 | 2022-03-13 23:01:54.334003+00
pg_catalog | pg_class | 425 | 19 | 2022-03-01 13:15:57.534378+00
pg_catalog | pg_proc | 2457 | 48 |
pg_toast | pg_toast_2618 | 252 | 10 |
i tried to vacuum the the first table pg_statistic , Below is the log
postgres=> VACUUM (VERBOSE) pg_statistic;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
INFO: aggressively vacuuming "pg_catalog.pg_statistic"
INFO: "pg_statistic": found 0 removable, 37699 nonremovable row versions in 6331 out of 6351 pages
DETAIL: 37563 dead row versions cannot be removed yet, oldest xmin: 648320155
There were 3340 unused item identifiers.
Skipped 0 pages due to buffer pins, 20 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
INFO: aggressively vacuuming "pg_toast.pg_toast_2619"
INFO: "pg_toast_2619": found 0 removable, 16 nonremovable row versions in 3 out of 11 pages
DETAIL: 12 dead row versions cannot be removed yet, oldest xmin: 648320155
There were 11 unused item identifiers.
Skipped 0 pages due to buffer pins, 8 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
Table is getting vacuumed but not able to remove the dead tuples because of oldest xmin: 648320155 , but the mentioned xim is not associated with long running quries or stale replication slots or prepared transactions.
Long running:
postgres=> SELECT now()-query_start,pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
?column? | pid | datname | usename | state | backend_xmin
-----------------+-------+-------------------+---------+--------+--------------
00:00:29.910155 | 539 | postgres | | active | 832858371
00:00:23.766305 | 1211 | postgres | | active | 832858509
00:00:00.756961 | 2151 | postgres | | active | 832859484
00:00:00.060784 | 30833 | postgres | root | active | 832859508
00:00:00.004473 | 29270 | postgres | root | active | 832859508
00:00:00.009809 | 29271 | postgres | root | active | 832859508
00:00:00.015169 | 27145 | postgres | root | active | 832859508
00:00:00 | 1450 | postgres | postgres | active | 832859508
00:00:00.010672 | 544 | postgres | root | active | 832859508
00:00:00.034516 | 19940 | postgres | root | active | 832859508
(10 rows)
stale replication slots:
postgres=> SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
slot_name | slot_type | database | xmin
-----------+-----------+----------+------
(0 rows)
Prepared transaction's :
postgres=> SELECT gid, prepared, owner, database, transaction AS xmin
postgres-> FROM pg_prepared_xacts
postgres-> ORDER BY age(transaction) DESC;
gid | prepared | owner | database | xmin
-----+----------+-------+----------+------
(0 rows)
Checked for long running queries on replica side , but haven't found any
postgres=> show hot_standby_feedback ;
hot_standby_feedback
----------------------
on
(1 row)
postgres=> SELECT pid, age(current_timestamp, xact_start),usename ,state,left(query,100)
FROM pg_stat_activity
WHERE state <> 'idle' and pid<>pg_backend_pid();
;
pid | age | usename | state | left
-----+-----+---------+-------+------
(0 rows)
postgres=> select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
Regards,
BK
