Why isn't the Amazon Redshift VACUUM operation reclaiming disk space?

Short description

Disk space might not be reclaimed if there are long-running transactions that remain active. When rows are deleted, a hidden metadata identity column, DELETE_XID, is marked with the transaction ID that deleted the row. If there is an active long-running transaction that began before the deletion, VACUUM can't clean up the rows. This means that disk space can't be reclaimed. For more information about the DELETE_XID column, see Optimizing storage for narrow tables.

Resolution

1.    To check for long-running transactions on the cluster, run the following query:

rsdb=# select *,datediff(s,txn_start,getdate())/86400||' days '||datediff(s,txn_start,getdate())%86400/3600||' hrs '||datediff(s,txn_start,getdate())%3600/60||' mins '||datediff(s,txn_start,getdate())%60||' secs' duration from svv_transactions where lockable_object_type='transactionid' and  pid<>pg_backend_pid() order by 3;

The following output shows that xid 50341 has been active for 19 minutes and 37 seconds:

txn_owner  | txn_db |  xid  |  pid  |         txn_start          |   lock_mode   | lockable_object_type | relation | granted |           duration
-----------+--------+-------+-------+----------------------------+---------------+----------------------+----------+---------+------------------------------
 superuser | rsdb   | 50341 | 21612 | 2019-08-19 20:20:33.147622 | ExclusiveLock | transactionid        |          | t       | 0 days 0 hrs 19 mins 37 secs
(1 row)

2.    Run the following query to confirm whether rows were deleted from the Amazon Redshift table:

select a.query, a.xid, trim(c.name) tablename, b.deleted_rows, a.starttime, a.endtime
from stl_query a 
join (select query, tbl, sum(rows) deleted_rows from stl_delete group by 1,2) b 
on a.query = b.query
join (select id, name from stv_tbl_perm group by 1,2) c 
on c.id = b.tbl 
where a.xid in (select distinct xid from stl_commit_stats)
and trim(c.name) = 'tablename'
order by a.starttime;

The following output shows that the transaction marked for row deletion (xid 50350) started after the long-running transaction (xid 50341):

query  |  xid  | tablename | deleted_rows |         starttime          |          endtime
-------+-------+-----------+--------------+----------------------------+----------------------------
 18026 | 50350 | test      |            5 | 2019-08-19 20:20:48.137594 | 2019-08-19 20:20:50.125609
(1 rows)

To allow VACUUM DELETE to reclaim these deleted rows, choose one of the following options, and then rerun the VACUUM operation:

• Wait for the long-running transaction to complete.
• Use the PG_TERMINATE_BACKEND statement to terminate the session that's holding the long-running transaction.

Investigate the long-running transactions

Query the SVL_STATEMENTTEXT view to check the activity in a long-running transaction:

rsdb=# select pid, xid, trim(label), starttime, endtime, trim(text) from svl_statementtext where xid = 50341 order by starttime , sequence;

Here's an example output:

pid  |  xid  |  btrim  |         starttime          |          endtime           |          btrim
-------+-------+---------+----------------------------+----------------------------+--------------------------
 21612 | 50341 | default | 2019-08-19 20:20:31.733843 | 2019-08-19 20:20:31.733844 | begin;
 21612 | 50341 | default | 2019-08-19 20:20:33.146937 | 2019-08-19 20:20:35.020556 | select * from sometable;
(2 rows)

Query the STV_INFLIGHT view to check whether queries are running in the transaction:

rsdb=# select query, xid, pid, starttime, trim(text) from stv_inflight where xid = 50341;

Here's an example output:

query | xid | pid | starttime | btrim
-------+-----+-----+-----------+-------
(0 rows)

Common issues that cause long-running transactions

The following behavior can result in long-running transactions:

• A user starts an implicit transaction from a client where automatic commit is disabled. The transaction remains active until the user explicitly closes the transaction with the COMMIT or ROLLBACK command, or until the session is terminated.
• A user starts a transaction explicitly using BEGIN, but never closes the transaction with the COMMIT or ROLLBACK command.

Related information

Vacuuming tables

Managing vacuum times