Space is not reclaimed after ROLLBACK in postgres script

Question

I have a postgres script that ran on an RDS instance using the psql tool in pgadmin:

BEGIN;

update table set mytimecolumn = 'this';
update table set mytimecolumn = 'that';
update table set mydatecolumn = 'this';
update table set mydatecolumn = 'that';

COMMIT;

I ran out of space in the middle of the script on the instance and ran ROLLBACK; My space was not reclaimed even though the script definitely rolled back

Was expecting space to be reclaimed nearly immediately. Can someone explain why it was not reclaimed?

Answer 1

You need to run vacuum full;

Because of MVCC, an update is really an insert that writes a whole new value and marks the old one as outdated. Only if you commit the operation and no other transaction is open with a snapshot of the old values, the space occupied by the old version of the row will be reclaimed by autovacuum, table rewrite or someone's manually issued vacuum.

Table pages that will get emptied entirely will be returned to the system, but without fully rewriting the table the empty space on any other page will be still assigned to the table and available for its exclusive use.