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

4 minute read

I'm running a VACUUM FULL or VACUUM DELETE ONLY operation on an Amazon Redshift table that contains rows marked for deletion. The operation appears to complete successfully. Why isn't there any reclaimed 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.


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

AWS OFFICIALUpdated 2 years ago