Re: How to keep pg_largeobject from growing endlessly

On 4/15/15 9:22 AM, Andreas Joseph Krogh wrote:
> På onsdag 15. april 2015 kl. 16:05:22, skrev Adam Hooper
> <adam(at)adamhooper(dot)com <mailto:adam(at)adamhooper(dot)com>>:
>
> On Wed, Apr 15, 2015 at 9:57 AM, Andreas Joseph Krogh
> <andreas(at)visena(dot)com> wrote:
> >
> > På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper
> <adam(at)adamhooper(dot)com>:
> >
> > On Wed, Apr 15, 2015 at 4:49 AM, Andreas Joseph Krogh
> > <andreas(at)visena(dot)com> wrote:
> > >
> > > In other words: Does vacuumlo cause diskspace used by
> pg_largeobject to be freed to the OS (after eventually vacuumed by
> autovacuum)?
> >
> > No.
> >
> > Ok. Out of curiousity; When does it get freed, when VACUUM FULL'ed?
>
> Yes. VACUUM FULL or CLUSTER will free the space. (Of course, you need
> a lot of free disk space to perform those operations.)
>
> I'm sure there's a good reason for why VACUUM FULL needs to rewreite the
> whole table and cannot "just free the unused space to the OS".

I think mostly because no one's written something to incrementally
delete the old data as it's moved. That would be a non-trivial amount of
work though, because none of the internal APIs are really setup the way
you'd need them to be to allow for this.

Also, I think there's some mis-information about vacuum returning space
to the filesystem. It definitely WILL return space to the filesystem,
but only under a very strict set of conditions:

- There must be a sufficient amount of free space *at the end of the
relation*
- It must be able to quickly acquire the correct lock
- It will start truncating pages off the relation until it detects
someone else is blocked on the lock it's holding. At that point it stops
what it's doing

So when the right set of circumstances occur, a plain vacuum will return
free space; but on a heavily hit table it's very hard for that to happen
in practice.

What you might want to do here is essentially re-create the large object
interface but allow it to hit any table instead of being force to use
the system one. That would open up the possibility of using tools like
pg_repack and table partitioning. You could do this in pure SQL, but the
community might welcome a patch that adds the ability to use different
tables to the existing large object API.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com