3

I have a medium sized database cluster running on PostgreSQL 8.3.

The database stores digital files (images) as LOBs.

There is a fair bit of activity in the database cluster, a lot of content is created and deleted in an ongoing manner.

Even though the application table which hosts the OIDs, gets maintained properly by the application (when an image file is deleted), the size of the database cluster grows continuously.

Auto-vacuuming is active so this shouldn't happen.

sola
  • 1,498
  • 14
  • 23
  • [Consider an upgrade to a supported version. 8.3 is unsupported by now. It's too old.](http://www.postgresql.org/support/versioning/) – Erwin Brandstetter Jun 26 '13 at 18:56
  • Absolutely. However, this problem seems to be the same on 9.2 (vacuumlo is still in the documentation) – sola Jun 28 '13 at 10:13

1 Answers1

5

LOBs are NOT deleted from the database when the rows of your application table (containing the OIDs) get deleted.

This also means that space will NOT be reclaimed by the VACUUM process.

In order to get rid of unused LOBs, you have run VACUUMLO on the databases. Vacuumlo will delete all of the unreferenced LOBs from a database.

Example call:

vacuumlo -U postgres -W -v <database_name>

(I only included the -v to make vacuumlo a bit more verbose so that you see how many LOBs it removes)

After vacuumlo has deleted the LOBs, you can run VACUUM FULL (or let the auto-vacuum process run).

sola
  • 1,498
  • 14
  • 23
  • 1
    Sadly AWS RDS is not currently allowing it. But find [here](https://dba.stackexchange.com/q/174663/48347) my workaround for that disallowance. – 1737973 May 26 '17 at 12:17