Обсуждение: How to keep pg_largeobject from growing endlessly
Hi all
I'm routinely vacuumlo'ing to reap orphan OIDs. Is it necessary to manually vacuum pg_largobject or is it handled by autovacuum?
In a system where large objects are constantly added (and *some* rarely deleted, so it grows every day), would I gain space (freed to the OS) by VACUUM FULL it?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På onsdag 15. april 2015 kl. 04:43:47, skrev Venkata Balaji N <nag1010@gmail.com>:
The amount of reclaimed space will depend on the volume of deletions happening. If the DELETES are rare and are not deleting much, then frequent VACUUM FULL is not ideal.I'm routinely vacuumlo'ing to reap orphan OIDs. Is it necessary to manually vacuum pg_largobject or is it handled by autovacuum?It is handled by autovacuum. What we do is, we schedule a manual VACUUM ANALYZE nightly job on bigger tables to avoid burden on the autovacuum during the business time.In a system where large objects are constantly added (and *some* rarely deleted, so it grows every day), would I gain space (freed to the OS) by VACUUM FULL it?
In other words: Does vacuumlo cause diskspace used by pg_largeobject to be freed to the OS (after eventually vacuumed by autovacuum)?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
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.
But that shouldn't matter in your scenario: if you create more large
objects than you delete, you aren't wasting space anyway.
Ok. Out of curiousity; When does it get freed, when VACUUM FULL'ed?
A longer-term problem that may apply in your scenario: pg_largeobject
can't grow beyond your tablespace's disk size. Unlike other tables,
it's very hard to move pg_largeobject to a new database/tablespace
without downtime. If your table is constantly growing and you're
worrying about how much space it's taking, other storage strategies
(bytea, S3, NFS, etc) might inspire more confidence. I had this
problem a few months ago; since then, I only use pg_largeobject in
prototyping and low-growth situations.
http://www.postgresql.org/message-id/CAMWjz6GF9TM+vWM_0ymQYPi4Xk_bv2nYaREMWR1EcsqBS404vw@mail.gmail.com
I'm aware of this but I haven't found an alternate solution which provides streaming of large BLOBs and TX-safety.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På onsdag 15. april 2015 kl. 16:05:22, skrev Adam Hooper <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".
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På fredag 17. april 2015 kl. 21:11:05, skrev Jim Nasby <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.
Would crowd-funding help here?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På mandag 20. april 2015 kl. 20:27:39, skrev Jim Nasby <Jim.Nasby@BlueTreble.com>:
[snip]
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.
This seems like a chicken-and-egg thing. It seems storing large stuff in PG (using the LO-API) is not recommended and many cheat by only storing a path to the file and store the file elsewhere, sacrificing transactional integrity. Because the support for it is not so good few people use it, and because few people use it the community doesn't think it's a problem worth spending time on fixing...
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
