0 like 0 dislike
278 views

Please log in or register to answer this question.

2 Answers

0 like 0 dislike
answered by (4k points)  

VACUUM can only remove those row versions (also known as “tuples”) that are not needed any more. A tuple is not needed if the transaction ID of the deleting transaction (as stored in the xmax system column) is older than the oldest transaction still active in the PostgreSQL database (or the whole cluster for shared tables).

This value (22300 in the VACUUM output above) is called the “xmin horizon”.

There are three things that can hold back this xmin horizon in a PostgreSQL cluster:

  1. Long-running transactions:

    You can find those and their xmin value with the following query:

    SELECT pid, datname, usename, state, backend_xmin
    FROM pg_stat_activity
    WHERE backend_xmin IS NOT NULL
    ORDER BY age(backend_xmin) DESC;
    You can use the pg_terminate_backend() function to terminate the database session that is blocking your VACUUM
    .
  2. Abandoned replication slots:

    replication slot is a data structure that keeps the PostgreSQL server from discarding information that is still needed by a standby server to catch up with the primary.

    If replication is delayed or the standby server is down, the replication slot will prevent VACUUM from deleting old rows.

    You can find all replication slots and their xmin value with this query:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

    Note: This can only happen with physical replication if hot_standby_feedback = on. For logical replication there is a similar hazard, but only system catalogs are affected. Examine the column catalog_xmin in that case.

    Use the pg_drop_replication_slot() function to drop replication slots that are no longer needed.
  3. Orphaned prepared transactions:

    During two-phase commit, a distributed transaction is first prepared with the PREPARE statement and then committed with the COMMIT PREPARED statement.

    Once a transaction has been prepared, it is kept “hanging around” until it is committed or aborted. It even has to survive a server restart! Normally, transactions don’t remain in the prepared state for long, but sometimes things go wrong and a prepared transaction has to be removed manually by an administrator.

    You can find all prepared transactions and their xmin value with the following query:

 

SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

Use the ROLLBACK PREPARED SQL statement to remove prepared transactions.

0 like 0 dislike
answered by (4k points)  
Use vacuum full after getting down time .

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
0 answers
asked Oct 10, 2018 by Admin J (970 points)  
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked Feb 19, 2019 by junaith (1.5k points)  
0 like 0 dislike
2 answers
0 like 0 dislike
0 answers
...