Steps to free up storage space in postgres database after deleting rows?

Question

I'm over the GB limit on a postgres db on heroku.

I found a (very large) deprecated table and ran Plot.delete_all. Now the 17k row table has 0 rows in it.

I can now see a difference in the row number but no difference in GB. Here's the Data Size before (37.73 GB)

heroku pg:info
=== HEROKU_POSTGRESQL_ONYX_URL, DATABASE_URL
Plan:                  Hobby-basic
Connections:           2/20
PG Version:            11.14
Created:               2019-03-09 12:13 UTC
Data Size:             37.73 GB/10.00 GB (Write access revoked; Database deletion imminent)
Tables:                6
Rows:                  2649450/10000000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                postgresql-spherical-39745

and after (still 37.73 GB, despite rows decreasing 16610):

heroku pg:info
=== HEROKU_POSTGRESQL_ONYX_URL, DATABASE_URL
Plan:                  Hobby-basic
Connections:           3/20
PG Version:            11.14
Created:               2019-03-09 12:13 UTC
Data Size:             37.73 GB/10.00 GB (Write access revoked; Database deletion imminent)
Tables:                6
Rows:                  2649450/10000000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                postgresql-spherical-39745

This Plot.delete_all will delete all the rows in the plots table, but is it sufficient to free up the disk space that the db was occupying with those records?

What I've tried

• heorku restart made no difference.
• I waited (60 minutes), but since heroku's background workers update these statistics typically no less frequently than every 5-10 minutes I presume that the readings are accurate and the bloat is still there
• I ran the postgress CLUSTER command, but I think it only performs operations on tables that have previously been clustered, so it completed quickly and didn't seem to do anything nor affect disk space)
• I tried VACCUM but it finished quickly and didn't seem to affect disk space.
• I tried VACCUM FULL - this took about 1-2 hours and worked! See results here.

Things I haven't tried yet, but may try

• I may edit the application code to remove the entire model, then rake db:migrate etc, and that should remove the unnecessary table entirely.

Answer 1

Running VACUUM FULL reduced the database disk size from 36GB to 16GB.

Here's how

1. Put your app into maintenance mode:

heroku maintenance:on

2. Restart your app (this may not be necessary but I did it anyway)

heroku restart

3. Open the postgress console with

heroku pg:psql

4. Run this (note the ; at the end of every SQL statement):

VACUUM FULL;

... wait ...

5. Once it's done, turn maintenance mode off

heroku maintenance:off

Important notes

• This process may take a few hours (it took about 1 hour for me)

• This process will increase the size of your database before decreasing it! That's because it creates copies of the data. The 36GB db went up to at least 44GB, possibly much higher (from a few related tests, I suspect approximately double the original size), before coming down to 16GB.

• Further reading here and here

  • Note:

  The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.

Extra info that may be useful

heroku pg:vacuum-stats could be useful:

heroku pg:vacuum-stats
 schema |        table         |   last_vacuum    | last_autovacuum  |    rowcount    | dead_rowcount  | autovacuum_threshold | expect_autovacuum 
--------+----------------------+------------------+------------------+----------------+----------------+----------------------+-------------------
 public | listings             | 2022-01-27 01:31 | 2022-01-26 06:59 |      2,975,055 |              0 |        595,061       | 
 public | ar_internal_metadata | 2022-01-27 01:31 |                  |              1 |              0 |             50       | 
 public | metrics              | 2022-01-27 01:31 |                  |         17,016 |              0 |          3,453       | 
 public | plots                | 2022-01-27 01:31 | 2022-01-27 00:46 |              0 |              0 |             50       | 
 public | schema_migrations    | 2022-01-27 01:31 |                  |              7 |              0 |             51       | 
 public | packages             | 2022-01-27 01:31 |                  |         18,798 |              0 |          3,810       | 
(6 rows)

• These incredibly useful postgres queries can be used to show each table and how much disk space it consumes! (highly recommend starting here, is it can show a breakdown of each table's size in your database).

• VACUUM FULL; will do all tables in the database. Use VACUUM FULL plots; to do just the plots table (replace 'plots' with your table name). It will probably be much faster.