Hi all,
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;
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
(1 row)
Now at this point if I perform a vacuum full on one or both, they'll both
shrink down to a bit over half that size:
vacuum full obj1;
vacuum full obj2;
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
This shrinkage doesn't occur with a normal vacuum, in fact the reported sizes
increase if I do a normal vacuum at that point, but that's not germane to my
question AFAICT.
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.
Thanks,
Joel
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
