On Mon, Apr 11, 2011 at 03:19:23PM -0700, Joel Stevenson wrote:
> I'm trying to do some comparisons between the EXTERNAL and the EXTENDED
> storage methods on a bytea column and from the outside the setting doesn't
> appear to affect the value stored on initial insert, but perhaps I'm looking
> at the wrong numbers. If I create two new tables with a single bytea column
> and set one of them to external storage, then insert an existing bytea value
> from another table into each one, they appear to be of exactly the same size.
> This is using PG 9.0.3 on Debian Lenny, using the backports-sloppy deb
> package of PG 9.
>
> (I've verified that the first table has "extended" storage via pg_attribute
> and that the second table has external.)
>
> create table obj1 ( object bytea );
> create table obj2 ( object bytea );
> alter table obj2 alter column object set storage external;
> insert into obj1 ( object ) select object from serialized_content where id =
> 12345;
> insert into obj2 ( object ) select object from obj1;
If the value that shows up for insertion is already compressed, EXTERNAL storage
will not decompress it. Change this line to
insert into obj2 ( object ) select object || '' from obj1;
to observe the effect you seek.
Given the purpose of EXTERNAL storage, this might qualify as a bug.
> 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
> -------+---------+-------+---------
> 65536 | 57344 | 65536 | 57344
> Can I use the relation size like this to determine whether or not compression
> is happening for these toast columns? If not, is there a way that I can
> confirm that it is or isn't active? The results appear to be similar for
> text columns.
Yes; the sizes you're seeing through that method should be accurate.
nm
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
