Re: pg_largeobject high overhead

On Wed, Jun 4, 2014 at 9:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>
> If your input data is uniformly incompressible, that's not too surprising.
> pg_largeobject tuples hold BLCKSZ/4 bytes of data, plus some overhead, so
> the only way that 4 of them will fit on a page is if compression saves
> more than the overhead. You only need a couple percent compression
> savings to make that work, but precompressed data might resist being
> compressed even that much. So you end up with only 3 tuples per page
> which makes it right about 33.33% overhead.
>
> I have several filters compressing the data (shuffle, nbit, gzip). I
could just disable gzip. Would this result in more efficient storage (ie
less total GB used) beyond the per-block utilization bump? If that works,
that would be counter intuitive and use more bandwidth (which I'm ok with)
so it's a strange tradeoff.

> According to the commentary in the source code (which you've evidently
> read to some extent), a smaller block size was chosen to reduce the
> overhead of partial updates of large-object data. You're probably not
> going to do that, but PG doesn't know it. In any case, you can't choose
> a LOBLKSIZE exceeding, or even equaling, the page size; so there's not
> room for a huge change here. According to the above analysis, if you
> want to pack more LO data per page, you'd actually be better off with
> a *smaller* LOBLKSIZE to limit the per-page wastage.
>

I only looked at a header briefly :-). The rest was in the docs ML
http://www.postgresql.org/docs/9.3/static/catalog-pg-largeobject.html

>
> What would likely be the best bet for you is to run a modified build with
> LOBLKSIZE held at 2K and BLCKSZ bumped up to 32K. That would cut the
> wastage from 2K per 8K to 2K per 32K. Operationally, it'd be safer since
> there *is* a pg_control cross-check on BLCKSZ, so you could not
> accidentally start a standard build against the database.
>
> Or you could just live with it. 10GB of disk is cheap insurance against
> human error, and you're definitely taking some risk of human error if
> you run a locally-patched build.
>
> regards, tom lane
>

The overhead is noticeable as storage for me is a little hard to come by
(budget limited R&D) but I respect that point. I think something should be
added in the future though to match os file storage efficiency given the
new lo limits are 4TB per entry - 30% is hefty overhead. Perhaps multiple
large object tables such that the user can change the respective tables
properties or whatever parameters needed being runtime specifiable to make
this more efficient / higher utilization.

I may experiment with some custom builds in the future just to see how it
goes.

-Jason