R: R: space taken by a row & compressed data

"Leonardo Francalanci" <lfrancalanci@simtel.ie> writes:

Could you point me to some docs on this TOAST-mechanism?
For instance: what kind of method is used to compress data?

When in doubt, read the code ;-)
src/backend/utils/adt/pg_lzcompress.c
src/include/utils/pg_lzcompress.h

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Leonardo Francalanci" <lfrancalanci@simtel.ie> writes:

Could you point me to some docs on this TOAST-mechanism?
For instance: what kind of method is used to compress data?

When in doubt, read the code ;-)
src/backend/utils/adt/pg_lzcompress.c
src/include/utils/pg_lzcompress.h

Are toasted values stored in the table itself or in a separate table?

My understanding was that it was the latter, which leads me to wonder whether
he'll actually gain anything by having all the records in his table be
toasted. It'll mean every record lookup has to traverse two indexes, and a
sequential scan loses the sequential read performance boost.

Or am I wrong and toasted values can be stored inline?

--
greg

Greg Stark wrote:

Are toasted values stored in the table itself or in a separate table?

In a separate table if they exceed a threshold.

My understanding was that it was the latter, which leads me to wonder whether
he'll actually gain anything by having all the records in his table be
toasted. It'll mean every record lookup has to traverse two indexes, and a
sequential scan loses the sequential read performance boost.

Or am I wrong and toasted values can be stored inline?

They can be, but are not by default. See:
http://www.postgresql.org/docs/current/static/sql-altertable.html

SET STORAGE

This form sets the storage mode for a column. This controls whether
this column is held inline or in a supplementary table, and whether the
data should be compressed or not. PLAIN must be used for fixed-length
values such as integer and is inline, uncompressed. MAIN is for inline,
compressible data. EXTERNAL is for external, uncompressed data, and
EXTENDED is for external, compressed data. EXTENDED is the default for
all data types that support it. The use of EXTERNAL will, for example,
make substring operations on a text column faster, at the penalty of
increased storage space.

Joe

On 8/26/2004 4:13 PM, Greg Stark wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Leonardo Francalanci" <lfrancalanci@simtel.ie> writes:

Could you point me to some docs on this TOAST-mechanism?
For instance: what kind of method is used to compress data?

When in doubt, read the code ;-)
src/backend/utils/adt/pg_lzcompress.c
src/include/utils/pg_lzcompress.h

Are toasted values stored in the table itself or in a separate table?

Some here and some there. Toast tries by default to get a row under 2K
size. As long as that isn't the case, it compresses the largest varlen
attribute. If there are no more uncompressed varlen attributes, it takes
the largest value and stores it in slices in the toast table, again only
until the main row is under 2K.

My understanding was that it was the latter, which leads me to wonder whether
he'll actually gain anything by having all the records in his table be
toasted. It'll mean every record lookup has to traverse two indexes, and a
sequential scan loses the sequential read performance boost.

Or am I wrong and toasted values can be stored inline?

Depends on the type of query. Queries that really access a lot of
toasted values lose. Queries that shovel around a lot of rows but don't
touch most of the toasted values win.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Joe Conway <mail@joeconway.com> writes:

Greg Stark wrote:

Are toasted values stored in the table itself or in a separate table?

In a separate table if they exceed a threshold.

How do you check to see how many records, or ideally which records, are being
toasted and/or stored externally?

--
greg

Greg Stark wrote:

How do you check to see how many records, or ideally which records, are being
toasted and/or stored externally?

I don't know of a builtin way to do that from SQL, but the attached
seems to work for me.

Joe

Joe Conway wrote:

Greg Stark wrote:

How do you check to see how many records, or ideally which records,
are being
toasted and/or stored externally?

I don't know of a builtin way to do that from SQL, but the attached
seems to work for me.

FWIW, this version has a bit more robust type checking. The last one
would crash if passed a non-varlena column.

regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
f2 | text |

regression=# select checktoast(f1), checktoast(f2) from foo;
checktoast | checktoast
---------------------+---------------------
inline,uncompressed | inline,uncompressed
inline,uncompressed | inline,compressed
(2 rows)

Joe

Joe Conway <mail@joeconway.com> writes:

I don't know of a builtin way to do that from SQL, but the attached seems to
work for me.

Cool. Thank you.

--
greg