How to keep pg_largeobject from growing endlessly

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?

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.

Regards,
Venkata Balaji N

Fujitsu Australia

På onsdag 15. april 2015 kl. 04:43:47, skrev Venkata Balaji N <nag1010@gmail.com
<mailto:nag1010@gmail.com>>:   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?     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.   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 andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt; <https://www.visena.com&gt;  

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.

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.
/messages/by-id/CAMWjz6GF9TM+vWM_0ymQYPi4Xk_bv2nYaREMWR1EcsqBS404vw@mail.gmail.com

Enjoy life,
Adam

--
Adam Hooper
+1-613-986-3339
http://adamhooper.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper <adam@adamhooper.com
<mailto: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.

/messages/by-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 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;  

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.)

Enjoy life,
Adam

--
Adam Hooper
+1-613-986-3339
http://adamhooper.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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".   -- Andreas Joseph Krogh CTO / Partner
- Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;  

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.  
Would crowd-funding help here?   Thanks.   -- Andreas Joseph Krogh CTO / Partner
- Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;  

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På mandag 20. april 2015 kl. 20:27:39, skrev Jim Nasby <Jim.Nasby@BlueTreble.com
<mailto: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 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;