2

I have a postgres (v10) database hosted on an Amazon AWS instance (which was running very low on 'Free Storage Space'). It had a 50GB table on it which I then dropped (pgAdmin - right click, delete/drop table).

I then checked the monitoring page on our AWS console for this instance. The Free Storage Space graph did not show a 50GB jump in free space. It remained static, even after waiting a while, refreshing the page etc.

Did dropping the 50GB table actually do anything at all?

Theo F
  • 1,197
  • 1
  • 11
  • 18
  • 2
    Try using `vacuum` to recover the disk space. – Gordon Linoff Apr 04 '19 at 10:41
  • @GordonLinoff but the table doesn't exist anymore, so I can't VACUUM tablename; – Theo F Apr 04 '19 at 10:42
  • 2
    @TheoF - Tablename is optional on Vacuum. – Andrew Apr 04 '19 at 10:53
  • 1
    @GordonLinoff: `vacuum` does not apply here. When a table is dropped, the corresponding files are removed from the filesystem (once the DDL statement is committed). And if there is no file, there is nothing to clean up. At least that's how "normal" Postgres works. I don't know if Amazon did some changes to that in their cloud offering –  Apr 04 '19 at 11:03
  • @a_horse_with_no_name I thought that too: how can I vacuum 'nothing'? – Theo F Apr 04 '19 at 11:13
  • @GordonLinoff I ran VACUUM on the database. It took 15mins and no space was freed up. Even after checking back 10mins later. – Theo F Apr 04 '19 at 11:15
  • lo and behold I closed pgAdmin and a few minutes later the free space on my aws instance rocketed back up by 50GB. Not sure if my actions were related to that or not... – Theo F Apr 04 '19 at 12:51
  • 3
    It may be that pgAdmin held the transaction open until you closed it. – jmelesky Apr 04 '19 at 12:55
  • 1
    @jmelesky . . . That transaction or another one. – Gordon Linoff Apr 04 '19 at 18:45

1 Answers1

3

Close pgAdmin or end the transactions on the database in the 'Dashboard' tab of pgAdmin. This will cut the transactions putting a lock on the database, and the free space will appear.

Theo F
  • 1,197
  • 1
  • 11
  • 18