Re: tuple data size and compression

On 12/15/2016 07:48 AM, Tom DalPozzo wrote:
> 2016-12-15 16:23 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>
> On 12/15/2016 07:17 AM, Tom DalPozzo wrote:
>
> Hi,
>
> it's not clear to me when tuple data (max 1000 bytes total) get
>
> compressed on disk and when not.
>
> I tried with pg_column_size to find the tuple's size but I
> get ambiguous
>
> results.
>
>
> What is the table definition?
>
>
> Maybe this will help?:
>
>
> https://www.postgresql.org/docs/9.5/static/storage-toast.html
> <https://www.postgresql.org/docs/9.5/static/storage-toast.html>
>
>
> It seems to work but sometimes the tuples seem compressed
> and sometime not.
>
> I tried both with constant data and random bytes.
>
>
> Thanks
>
> Pupillo
>
>
>
>
>
> --
>
> Adrian Klaver
>
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
> Hi,
> definition: create table stato (id bigint, dati bytea);
> then I populate the dati field with 100 bytes.

https://www.postgresql.org/docs/9.5/static/storage-toast.html

"The TOAST management code is triggered only when a row value to be
stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2
kB). The TOAST code will compress and/or move field values out-of-line
until the row value is shorter than TOAST_TUPLE_TARGET bytes (also
normally 2 kB) or no more gains can be had. During an UPDATE operation,
values of unchanged fields are normally preserved as-is; so an UPDATE of
a row with out-of-line values incurs no TOAST costs if none of the
out-of-line values change."

> Pupillo

>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com