Thread: space taken by a row & compressed data

space taken by a row & compressed data

From
"Leonardo Francalanci"
Date:
With mysql I know how much space a row will take, based on the datatype
of it columns. I also (approximately) know the size of indexes.
Is there a way to know that in postgresql?

Is there a way to pack (compress) data, as with myisampack for mysql?



Thank you

Re: space taken by a row & compressed data

From
Bruce Momjian
Date:
Leonardo Francalanci wrote:
> With mysql I know how much space a row will take, based on the datatype
> of it columns. I also (approximately) know the size of indexes.
> Is there a way to know that in postgresql?

We have an FAQ item about this.

> Is there a way to pack (compress) data, as with myisampack for mysql?

Long data values are automatically compressed.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: space taken by a row & compressed data

From
Tino Wildenhain
Date:
Hi Leonardo,

Am Do, den 26.08.2004 schrieb Leonardo Francalanci um 15:51:
> With mysql I know how much space a row will take, based on the datatype
> of it columns. I also (approximately) know the size of indexes.
> Is there a way to know that in postgresql?
>
> Is there a way to pack (compress) data, as with myisampack for mysql?

in the contrib dir you will find something (dbsize or so)
to check for sizes of objects (at least tables)

Due to the MVCC you happen to have multiple versions
of an updated row at the same time so the numbers arent
very exact all the time.

Large objects such as text columns are stored in compressed
form already.

HTH
Tino Wildenhain


R: space taken by a row & compressed data

From
"Leonardo Francalanci"
Date:
> We have an FAQ item about this.

Damn! I didn't see that one! Sorry...

> Long data values are automatically compressed.

The reason I'm asking is:
we have a system that stores 200,000,000 rows per month
(other tables store 10,000,000 rows per month)
Every row has 400 columns of integers + 2 columns (date+integer) as index.

Our system compresses rows before writing them to a binary file on disk.
Data don't usually need to be updated/removed.
We usually access all columns of a row (hence compression on a per-row basis
makes sense).

Is there any way to compress data on a per-row basis? Maybe with
a User-Defined type?


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

From
Bruce Momjian
Date:
Leonardo Francalanci wrote:
> > We have an FAQ item about this.
>
> Damn! I didn't see that one! Sorry...
>
> > Long data values are automatically compressed.
>
> The reason I'm asking is:
> we have a system that stores 200,000,000 rows per month
> (other tables store 10,000,000 rows per month)
> Every row has 400 columns of integers + 2 columns (date+integer) as index.
>
> Our system compresses rows before writing them to a binary file on disk.
> Data don't usually need to be updated/removed.
> We usually access all columns of a row (hence compression on a per-row basis
> makes sense).
>
> Is there any way to compress data on a per-row basis? Maybe with
> a User-Defined type?

Ah, we only compress long row values, which integers would not be.  I
don't see any way to compress an entire row even with a user-defined
type unless you put multiple values into a single column and compress
those as a single value.  In fact, if you used an array or some special
data type it would become a long value and would be automatically
compressed.

However, as integers, there would have to be a lot of duplicate values
before compression would be a win.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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

From
Tom Lane
Date:
"Leonardo Francalanci" <lfrancalanci@simtel.ie> writes:
> we have a system that stores 200,000,000 rows per month
> (other tables store 10,000,000 rows per month)
> Every row has 400 columns of integers + 2 columns (date+integer) as index.

> Our system compresses rows before writing them to a binary file on disk.
> Data don't usually need to be updated/removed.
> We usually access all columns of a row (hence compression on a per-row basis
> makes sense).

> Is there any way to compress data on a per-row basis? Maybe with
> a User-Defined type?

If you just stuck all the integers into a single integer-array column,
it would be 1600 bytes wide, which is ... hmm ... not quite wide enough
to trigger the toast logic.  Perhaps it would be worthwhile for you to
run a custom build with TOAST_TUPLE_THRESHOLD/TOAST_TUPLE_TARGET set
to half their standard values (see src/include/access/tuptoaster.h).
You'd not need to write any specialized code that way.

Note that if you sometimes search on the values of one of the non-index
columns, this might be a bad idea.

            regards, tom lane

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

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> However, as integers, there would have to be a lot of duplicate values
> before compression would be a win.

Not necessarily.  If for instance most of the values fit in int2, then
the upper zero bytes would be fodder for compression.  (If they *all*
fit in int2 then of course he's missing a trick...)  The fact that they
are successfully using row compression on their old platform indicates
that there's some win available there.

            regards, tom lane

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

From
"Leonardo Francalanci"
Date:
> If you just stuck all the integers into a single integer-array column,
> it would be 1600 bytes wide, which is ... hmm ... not quite wide enough
> to trigger the toast logic.  Perhaps it would be worthwhile for you to
> run a custom build with TOAST_TUPLE_THRESHOLD/TOAST_TUPLE_TARGET set
> to half their standard values (see src/include/access/tuptoaster.h).

Could you point me to some docs on this TOAST-mechanism?
I only found http://postgresql.zadnik.org/projects/devel-toast.html
but seems very old.
For instance: what kind of method is used to compress data?

> You'd not need to write any specialized code that way.
Great!

> Note that if you sometimes search on the values of one of the non-index
> columns, this might be a bad idea.


No, this never happens.

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

From
Tom Lane
Date:
"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

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

From
Greg Stark
Date:
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

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

From
Joe Conway
Date:
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

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

From
Jan Wieck
Date:
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 #

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

From
Greg Stark
Date:
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

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

From
Joe Conway
Date:
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



Attachment

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

From
Joe Conway
Date:
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

Attachment

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

From
Greg Stark
Date:
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