Re: Doubt with physical storage being used by postgres when storing LOBs - Mailing list pgsql-general

From Merlin Moncure
Subject Re: Doubt with physical storage being used by postgres when storing LOBs
Date
Msg-id CAHyXU0w_tNxBFHuWGHKCZF7GkE_jUQaT0f+dNcgTX8yx0+z3ew@mail.gmail.com
Whole thread Raw
In response to Doubt with physical storage being used by postgres when storing LOBs  (Víctor Cosqui <victor.cosqui@gmail.com>)
Responses Re: Doubt with physical storage being used by postgres when storing LOBs
List pgsql-general
On Wed, Oct 2, 2013 at 11:08 AM, Víctor Cosqui <victor.cosqui@gmail.com> wrote:
> Hi all
>
>
> I am developing an application which uses postgres 9.2 to store binaries as
> oid objects.
>
> CREATE TABLE content (contentname text, contentoid oid);
>
> I am making some tests to evaluate how much HD space I will need to allocate
> these objects.
>
> To measure the space used by postgres I have used two different tools, both
> with the same results
>
> 1.- Checking physical HD space by making a "sudo du -sb
> /opt/PostgreSQL/9.2/data/base/" before and after inserting the data
>
> 2.- Asking directly postgres about the tables size estimation "select
> pg_size_pretty(pg_relation_size('pg_largeobject'))"
>
> I have tested with different binaries and I am getting different results,
> for example when I put the content of a zipped file of 17MB size, the
> increment of the disk space is of 24MB. The reason for this increment seems
> to be an index created on the table "pg_largeobject". The index is
> "pg_largeobject_loid_pn_index"
>
> In other hand when I put let's say many zeroes (same 17Mb) the increase of
> HD usage is much smaller.
>
> I think it could be caused because TOAST compresses the content stored, se
> he can compress the zeroes but not the previously compressed zip content.
>
> My question is: Is this increase of ~40% normal? Has someone else
> experienced this?

TOAST will compress data if it thinks it can (you can disable this
behavior and arguably should if your data is pre-compressed).  40% for
the index seems high but it may be accurate.  Personally, I prefer
bytea storage to LOB although LOB is a little bit faster.

merlin


pgsql-general by date:

Previous
From: Steve Crawford
Date:
Subject: Re: Timestamp with and without timezone conversion confusion.
Next
From: bricklen
Date:
Subject: Re: partitioning for speed, but query planner ignores