27479

How to reclaim space occupied by unused LOBs in PostgreSQL

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.

Answer1:

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).

Recommend

  • Known effort to update MGSplitViewController for iOS5 and Storyboards?
  • Debugging an ongoing .net process
  • Android: Notification action to dismiss the notification
  • Can capistrano v3 specify HOSTS in command line like v2 does?
  • Outlook 365 add-in only appears in Outlook 2013 client
  • No server chosen by WritableServerSelector from cluster
  • Database for Full Text Search and 200M+ Records
  • Newtonsoft inline formatting for subelement while serializing
  • Why is RAM in powers of 2?
  • Visual basic auto imports namespaces
  • WPF Listbox commands
  • 'doc_del_count' bigger than 'doc_count' on CouchDB
  • Javascript Array, Object, Date not defined
  • How can I get the choice “H2” back in the H2 consol?
  • Upload file that is in the cpan database
  • how does System.Web.HttpRequest::PathInfo work?
  • Not able to aggregate on nested fields in elasticsearch
  • Change multiple background-images with jQuery
  • Android screen density dpi vs ppi
  • C# - Serializing and deserializing static member
  • DotNetZip - Calculate final zip size before calling Save(stream)
  • Incrementing object id automatically JS constructor (static method and variable)
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Sending data from AppleScript to FileMaker records
  • Change an a tag attribute in JavaScript based on screen width
  • Delete MySQLi record without showing the id in the URL
  • Why winpcap requires both .lib and .dll to run?
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • How do I rollback to a specific git commit
  • Unanticipated behavior
  • Comma separated Values
  • How to include full .NET prerequisite for Wix Burn installer
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Free memory of cv::Mat loaded using FileStorage API
  • Trying to get generic when generic is not available
  • coudnt use logback because of log4j
  • Is it possible to post an object from jquery to bottle.py?
  • unknown Exception android