Re: Column storage (EXTERNAL/EXTENDED) settings for bytea/text column

On Apr 12, 2011, at 10:33 AM, Bill Moran wrote:

> In response to Joel Stevenson <jstevenson(at)bepress(dot)com>:
>
>> select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1, pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
>> o1 | otoast1 | o2 | otoast2
>> -------+---------+-------+---------
>> 40960 | 32768 | 40960 | 32768
>
> I'm not an expert, but it looks like you're not storing enough data to
> actually see the difference, since the actual sizes of the tables will
> always be rounded to an even page size. With only 1 row, it's always
> going to take a minimum amount.
>
> Also, are you sure you're storing compressible data? For example, if
> you're putting PNG or JPEG images in there, they're not going to compress
> any.
>

Thanks for the reply, Bill.

The data is very compressible, the raw data is 33392 bytes long and gzips down to 6965 bytes. As far as not storing enough, the description of the 'SET STORAGE' clause and the TOAST strategy it sounds like the TOASTer will try to compress anything that doesn't fit into the PG page ( default 8Kb ) so I would've thought that compression would be used for the EXTENDED column and not used for the EXTERNAL column since my single-row data is larger than that.

To be certain I stored 10 rows of that data and rechecked the reported size after a vacuum full:

select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1, pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
--------+---------+--------+---------
147456 | 139264 | 147456 | 139264

So, again from the outside, the column storage settings don't appear to be behaving as I'd expect them too.

Stumped.

- Joel