Re: XID wraparound with huge pg_largeobject - Mailing list pgsql-general

I am working with a client who has a 9.1 database rapidly approaching XID wraparound.  They also have an exceedingly large pg_largeobject table (4217 GB) that has never been vacuumed.  An attempt to vacuum this on a replica has run for days and never succeeded.  (Or more accurately, never been allowed to succeed because we needed to get the replica back on-line.)

...

Any other ideas about how we can do this?

David,

My gut reaction was maybe dump/restore ... but it's pg_largeobject.  I have read the list for years and my memory tells me that it is a problem child in that arena. (e.g. as you found out w Slony...)  and at 4000Gb, not something that can sandbox very well. 

Because it's v9.1... and you hadn't gotten any responses (until Jeff)... and I had the time... I did some digging in the archives...

The most promising alternate idea... Last February Adam Hooper was migrating to SSD, Bill Moran suggesting trying to CLUSTER pg_largeobject instead of VACUUM FULL.  (full topic: on 2/3/2015 entitled "VACUUM FULL pg_largeobject without (much) downtime?")

CLUSTER has been referenced in the list other times to collapse unused space.... (sometime in 2010-2011):

As a last resort this week, I'm going to get 500+GB of extra file store
added, add a tablespace and move pg_largeobjects to this area. Then use
CLUSTER to rebuild pg_largeobjects back in the default tablespace. This
should fix things I hope, and if needed I'll use Cluster regularly.

It's "an" other idea...  I've no idea whether it will work any better than biting the bullet and just running VACUUM FULL.

other bits and pieces...

In 2010, Tom suggested REINDEX then VACUUM on pg_largeobject for an 8.? system.  That peaked my interest because we found with 9.1 that weekly reindexing helped with performance.  However the person who used it didn't find any performance improvement with his VACUUM.  I think reindexing was added to VACUUM FULL in the 9.0 release (but would have to search the release notes to find it).

I remember reading somewhere during this (but can't find the reference <sigh>) that an interrupted VACUUM FREEZE does capture "some" data, so multiples of those actually incrementally improves the speed of the next - but again I can't find the reference, so I've no idea who, when, version, and whether my memory is faulty or misapplied.

There are miscellaneous improvements in the actual running of VACUUM FULL (and more often autovacuum) suggested through tweaking the vacuum parameters "vacuum_cost_delay" being a high priority target.    Jeff's questions all point an identifying any limitations that are costing you time due to configuration.

Totally not part of this specific problem... You have run or know of vacuumlo for deleting orphaned LOs...?  Might be worth running it before you collect your free space.  [just in case you didn't or hadn't... twice the bang, half the pain - but only if you do it before collecting your free space]

Roxanne