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.Nasby@BlueTreble.com <mailto:Jim.Nasby@BlueTreble.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@adamhooper.com <mailto:adam@adamhooper.com>>:
>      >
>      >     On Wed, Apr 15, 2015 at 9:57 AM, Andreas Joseph Krogh
>      >     <andreas@visena.com> wrote:
>      >      >
>      >      > På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper
>      >     <adam@adamhooper.com>:
>      >      >
>      >      > On Wed, Apr 15, 2015 at 4:49 AM, Andreas Joseph Krogh
>      >      > <andreas@visena.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