Re: [GENERAL] 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.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com