Re: Doubt with physical storage being used by postgres when storing LOBs

On Wed, Oct 2, 2013 at 11:08 AM, Víctor Cosqui <victor(dot)cosqui(at)gmail(dot)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