Re: How to keep pg_largeobject from growing endlessly

On 4/17/15 4:29 PM, Andreas Joseph Krogh wrote:
> På fredag 17. april 2015 kl. 21:11:05, skrev Jim Nasby
> <Jim(dot)Nasby(at)BlueTreble(dot)com <mailto:Jim(dot)Nasby(at)BlueTreble(dot)com>>:
>
> 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
>
> Thanks for the info.
> There seems to be not much happening with the large-object API (and
> pg_largeobject's restriction being a system-catalog). Are there any
> plans to improve it. I see 2 (for me) obvious enhancements; 1. Being
> able to move the LO-table (for now pg_largeobject) to another tablespace
> without restarting the cluster in single-user mode, and 2, improvements
> to free space to the OS.

ISTM what would be better is allowing people to define new LO tables, so
we're not stuck trying to cram all LOs into a single table.

As for returning free space, that's a bit of a challenge period, for all
tables.

> Would crowd-funding help here?

Possibly. The first thing is getting the community to agree that there's
a problem that needs to be fixed. Once that's accomplished crowd funding
would be a good way to get it actually built.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com