Re: TOASTed size

I'm thinking that there isn't any way currently of working out how big a
compressed toast object is?

All existing functions decompress the object before we do anything to
it, AFAICS. Am I missing something?

So there's no way currently of working out how good your compression is
for individual values or when you have multiple toasted columns, other
than writing a new function?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> I'm thinking that there isn't any way currently of working out how big a
> compressed toast object is?

pg_column_size() ?

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>> I'm thinking that there isn't any way currently of working out how big a
>> compressed toast object is?
>
> pg_column_size() ?

I was going to send the same thing but I think he's looking for the compressed
size of *external* data.

In fact there isn't really any convenient way to find out something is stored
external. pg_column_size reports the toast raw size of externally stored data.

There does seem to be a need for a more general pg_column_info which returns a
tuple (external bool, rawsize integer, storedsize integer).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

On Wed, 2007-12-05 at 08:24 +0000, Gregory Stark wrote:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> >> I'm thinking that there isn't any way currently of working out how big a
> >> compressed toast object is?
> >
> > pg_column_size() ?
>
> I was going to send the same thing but I think he's looking for the compressed
> size of *external* data.
>
> In fact there isn't really any convenient way to find out something is stored
> external. pg_column_size reports the toast raw size of externally stored data.
>
> There does seem to be a need for a more general pg_column_info which returns a
> tuple (external bool, rawsize integer, storedsize integer).

That sounds more like what I was after.

So let me check my understanding: For TOASTed data pg_column_size()
tells you how many bytes the column value occupies when decompressed. So
there isn't any way of finding out how many bytes a column value
actually occupies when it is both compressed and external?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs wrote:
>
> That sounds more like what I was after.
>
> So let me check my understanding: For TOASTed data pg_column_size()
> tells you how many bytes the column value occupies when decompressed. So
> there isn't any way of finding out how many bytes a column value
> actually occupies when it is both compressed and external?
>
>

I dimly recall getting confused by this when writing this guy:

From what I can see: pg_column_size calls toast_datum_size for any
variable length attribute - and then gets the external pointer and
returns its va_extsize component (which looks to me like the
*compressed* size.)

Cheers

Mark

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:

> On Wed, 2007-12-05 at 08:24 +0000, Gregory Stark wrote:
>> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>
>> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>> >> I'm thinking that there isn't any way currently of working out how big a
>> >> compressed toast object is?
>> >
>> > pg_column_size() ?
>>
>> I was going to send the same thing but I think he's looking for the compressed
>> size of *external* data.
>>
>> In fact there isn't really any convenient way to find out something is stored
>> external. pg_column_size reports the toast raw size of externally stored data.
>>
>> There does seem to be a need for a more general pg_column_info which returns a
>> tuple (external bool, rawsize integer, storedsize integer).
>
> That sounds more like what I was after.
>
> So let me check my understanding: For TOASTed data pg_column_size()
> tells you how many bytes the column value occupies when decompressed.

Wait, no, it's supposed to be the actual size on disk. *checks* yeah, it's the
extsize which is the size of the datum in the toast table. So you could find
the compression ratio by calling length() and pg_column_size() at least for
text data.

I still think a single function returning those columns would be a nice thing
to have to make the api complete.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

What it turns out is hard to determine is whether the column was stored
externally. To do that you have to rely on the trick of checking
pg_column_size(table.*) and that only works if it's the only column likely to
be stored externally.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!