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@xxxxxxxxxxxxxx <mailto:Jim.Nasby@xxxxxxxxxxxxxx>>: On 4/15/15 9:22 AM, Andreas Joseph Krogh wrote: > På onsdag 15. april 2015 kl. 16:05:22, skrev Adam Hooper > <adam@xxxxxxxxxxxxxx <mailto:adam@xxxxxxxxxxxxxx>>: > > On Wed, Apr 15, 2015 at 9:57 AM, Andreas Joseph Krogh > <andreas@xxxxxxxxxx> wrote: > > > > På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper > <adam@xxxxxxxxxxxxxx>: > > > > On Wed, Apr 15, 2015 at 4:49 AM, Andreas Joseph Krogh > > <andreas@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
