Re: TOASTed size

"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!