The following bug has been logged on the website:
Bug reference: 13749
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: 9.4.5
Operating system: CentOS 6.x x64
Description:
postgres=# create table test_plain(c text);
CREATE TABLE
postgres=# alter table test_plain alter column c set storage external;
ALTER TABLE
postgres=# create table test_compress(c text);
CREATE TABLE
postgres=# alter table test_compress alter column c set storage extended;
ALTER TABLE
postgres=# insert into test_plain select repeat(md5(random()::text),10000)
from generate_series(1,1000);
INSERT 0 1000
postgres=# insert into test_compress select
repeat(md5(random()::text),10000) from generate_series(1,1000);
INSERT 0 1000
postgres=# select pg_size_pretty(pg_total_relation_size('test_compress'));
pg_size_pretty
----------------
4168 kB
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('test_plain'));
pg_size_pretty
----------------
317 MB
(1 row)
the above case use repeat generate a big data.
but when i use the other case, pgsql cann't compress the data, is the
compress algorithm bug?
postgres=# truncate test_compress ;
TRUNCATE TABLE
postgres=# truncate test_plain ;
TRUNCATE TABLE
postgres=# do language plpgsql $$
declare
begin
for i in 1..100 loop
insert into test_compress select string_agg(md5(random()::text),' ') v
from generate_series(1,10000);
end loop;
end;
$$;
DO
postgres=# do language plpgsql $$ declare begin for i in 1..100 loop insert
into test_plain select string_agg(md5(random()::text),' ') v from
generate_series(1,10000); end loop; end; $$;
DO
postgres=# select pg_size_pretty(pg_total_relation_size('test_compress'));
pg_size_pretty
----------------
33 MB
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('test_plain'));
pg_size_pretty
----------------
33 MB
(1 row)