SET columnar.compression TO 'none'; SELECT count(distinct storage_id) AS columnar_table_count FROM columnar.stripe \gset CREATE TABLE t(a int, b int) USING columnar; CREATE VIEW t_stripes AS SELECT * FROM columnar.stripe a, pg_class b WHERE a.storage_id = columnar.get_storage_id(b.oid) AND b.relname='t'; SELECT count(*) FROM t_stripes; count --------------------------------------------------------------------- 0 (1 row) INSERT INTO t SELECT i, i * i FROM generate_series(1, 10) i; INSERT INTO t SELECT i, i * i FROM generate_series(11, 20) i; INSERT INTO t SELECT i, i * i FROM generate_series(21, 30) i; SELECT sum(a), sum(b) FROM t; sum | sum --------------------------------------------------------------------- 465 | 9455 (1 row) SELECT count(*) FROM t_stripes; count --------------------------------------------------------------------- 3 (1 row) select version_major, version_minor, reserved_stripe_id, reserved_row_number from columnar_test_helpers.columnar_storage_info('t'); version_major | version_minor | reserved_stripe_id | reserved_row_number --------------------------------------------------------------------- 2 | 0 | 4 | 450001 (1 row) -- vacuum full should merge stripes together VACUUM FULL t; SELECT * FROM columnar_test_helpers.chunk_group_consistency; consistent --------------------------------------------------------------------- t (1 row) SELECT sum(a), sum(b) FROM t; sum | sum --------------------------------------------------------------------- 465 | 9455 (1 row) SELECT count(*) FROM t_stripes; count --------------------------------------------------------------------- 1 (1 row) select version_major, version_minor, reserved_stripe_id, reserved_row_number from columnar_test_helpers.columnar_storage_info('t'); version_major | version_minor | reserved_stripe_id | reserved_row_number --------------------------------------------------------------------- 2 | 0 | 2 | 150001 (1 row) -- test the case when all data cannot fit into a single stripe ALTER TABLE t SET (columnar.stripe_row_limit = 1000); INSERT INTO t SELECT i, 2 * i FROM generate_series(1,2500) i; SELECT sum(a), sum(b) FROM t; sum | sum --------------------------------------------------------------------- 3126715 | 6261955 (1 row) SELECT count(*) FROM t_stripes; count --------------------------------------------------------------------- 4 (1 row) VACUUM FULL t; select version_major, version_minor, reserved_stripe_id, reserved_row_number from columnar_test_helpers.columnar_storage_info('t'); version_major | version_minor | reserved_stripe_id | reserved_row_number --------------------------------------------------------------------- 2 | 0 | 4 | 3001 (1 row) SELECT * FROM columnar_test_helpers.chunk_group_consistency; consistent --------------------------------------------------------------------- t (1 row) SELECT sum(a), sum(b) FROM t; sum | sum --------------------------------------------------------------------- 3126715 | 6261955 (1 row) SELECT count(*) FROM t_stripes; count --------------------------------------------------------------------- 3 (1 row) -- VACUUM FULL doesn't reclaim dropped columns, but converts them to NULLs ALTER TABLE t DROP COLUMN a; SELECT stripe_num, attr_num, chunk_group_num, minimum_value IS NULL, maximum_value IS NULL FROM columnar.chunk a, pg_class b WHERE a.storage_id = columnar.get_storage_id(b.oid) AND b.relname='t' ORDER BY 1, 2, 3; stripe_num | attr_num | chunk_group_num | ?column? | ?column? --------------------------------------------------------------------- 1 | 1 | 0 | f | f 1 | 2 | 0 | f | f 2 | 1 | 0 | f | f 2 | 2 | 0 | f | f 3 | 1 | 0 | f | f 3 | 2 | 0 | f | f (6 rows) VACUUM FULL t; SELECT stripe_num, attr_num, chunk_group_num, minimum_value IS NULL, maximum_value IS NULL FROM columnar.chunk a, pg_class b WHERE a.storage_id = columnar.get_storage_id(b.oid) AND b.relname='t' ORDER BY 1, 2, 3; stripe_num | attr_num | chunk_group_num | ?column? | ?column? --------------------------------------------------------------------- 1 | 1 | 0 | t | t 1 | 2 | 0 | f | f 2 | 1 | 0 | t | t 2 | 2 | 0 | f | f 3 | 1 | 0 | t | t 3 | 2 | 0 | f | f (6 rows) -- Make sure we cleaned-up the transient table metadata after VACUUM FULL commands SELECT count(distinct storage_id) - :columnar_table_count FROM columnar.stripe; ?column? --------------------------------------------------------------------- 1 (1 row) -- do this in a transaction so concurrent autovacuum doesn't interfere with results BEGIN; SAVEPOINT s1; SELECT count(*) FROM t; count --------------------------------------------------------------------- 2530 (1 row) SELECT pg_size_pretty(pg_relation_size('t')); pg_size_pretty --------------------------------------------------------------------- 40 kB (1 row) INSERT INTO t SELECT i FROM generate_series(1, 10000) i; SELECT pg_size_pretty(pg_relation_size('t')); pg_size_pretty --------------------------------------------------------------------- 120 kB (1 row) SELECT count(*) FROM t; count --------------------------------------------------------------------- 12530 (1 row) ROLLBACK TO SAVEPOINT s1; -- not truncated by VACUUM or autovacuum yet (being in transaction ensures this), -- so relation size should be same as before. SELECT pg_size_pretty(pg_relation_size('t')); pg_size_pretty --------------------------------------------------------------------- 120 kB (1 row) COMMIT; -- vacuum should truncate the relation to the usable space VACUUM VERBOSE t; INFO: statistics for "t": storage id: xxxxx total file size: 122880, total data size: 10754 compression rate: 1.00x total row count: 2530, stripe count: 3, average rows per stripe: 843 chunk count: 3, containing data for dropped columns: 0, none compressed: 3 INFO: "t": truncated 15 to 5 pages SELECT pg_size_pretty(pg_relation_size('t')); pg_size_pretty --------------------------------------------------------------------- 40 kB (1 row) SELECT count(*) FROM t; count --------------------------------------------------------------------- 2530 (1 row) -- add some stripes with different compression types and create some gaps, -- then vacuum to print stats BEGIN; ALTER TABLE t SET (columnar.chunk_group_row_limit = 1000, columnar.stripe_row_limit = 2000, columnar.compression = pglz); SAVEPOINT s1; INSERT INTO t SELECT i FROM generate_series(1, 1500) i; ROLLBACK TO SAVEPOINT s1; INSERT INTO t SELECT i / 5 FROM generate_series(1, 1500) i; ALTER TABLE t SET (columnar.compression = none); SAVEPOINT s2; INSERT INTO t SELECT i FROM generate_series(1, 1500) i; ROLLBACK TO SAVEPOINT s2; INSERT INTO t SELECT i / 5 FROM generate_series(1, 1500) i; COMMIT; VACUUM VERBOSE t; INFO: statistics for "t": storage id: xxxxx total file size: 57344, total data size: 18808 compression rate: 1.25x total row count: 5530, stripe count: 5, average rows per stripe: 1106 chunk count: 7, containing data for dropped columns: 0, none compressed: 5, pglz compressed: 2 select version_major, version_minor, reserved_stripe_id, reserved_row_number from columnar_test_helpers.columnar_storage_info('t'); version_major | version_minor | reserved_stripe_id | reserved_row_number --------------------------------------------------------------------- 2 | 0 | 18 | 21001 (1 row) SELECT * FROM columnar_test_helpers.chunk_group_consistency; consistent --------------------------------------------------------------------- t (1 row) SELECT count(*) FROM t; count --------------------------------------------------------------------- 5530 (1 row) -- check that we report chunks with data for dropped columns ALTER TABLE t ADD COLUMN c int; INSERT INTO t SELECT 1, i / 5 FROM generate_series(1, 1500) i; ALTER TABLE t DROP COLUMN c; VACUUM VERBOSE t; INFO: statistics for "t": storage id: xxxxx total file size: 73728, total data size: 31372 compression rate: 1.15x total row count: 7030, stripe count: 6, average rows per stripe: 1171 chunk count: 11, containing data for dropped columns: 2, none compressed: 9, pglz compressed: 2 -- vacuum full should remove chunks for dropped columns -- note that, a chunk will be stored in non-compressed for if compression -- doesn't reduce its size. ALTER TABLE t SET (columnar.compression = pglz); VACUUM FULL t; VACUUM VERBOSE t; INFO: statistics for "t": storage id: xxxxx total file size: 57344, total data size: 15728 compression rate: 1.96x total row count: 7030, stripe count: 4, average rows per stripe: 1757 chunk count: 8, containing data for dropped columns: 0, none compressed: 2, pglz compressed: 6 SELECT * FROM columnar_test_helpers.chunk_group_consistency; consistent --------------------------------------------------------------------- t (1 row) DROP TABLE t; DROP VIEW t_stripes; -- Make sure we cleaned the metadata for t too SELECT count(distinct storage_id) - :columnar_table_count FROM columnar.stripe; ?column? --------------------------------------------------------------------- 0 (1 row) -- A table with high compression ratio SET columnar.compression TO 'pglz'; SET columnar.stripe_row_limit TO 1000000; SET columnar.chunk_group_row_limit TO 100000; CREATE TABLE t(a int, b char, c text) USING columnar; INSERT INTO t SELECT 1, 'a', 'xyz' FROM generate_series(1, 1000000) i; VACUUM VERBOSE t; INFO: statistics for "t": storage id: xxxxx total file size: 630784, total data size: 604480 compression rate: 33.71x total row count: 1000000, stripe count: 1, average rows per stripe: 1000000 chunk count: 30, containing data for dropped columns: 0, pglz compressed: 30 SELECT * FROM columnar_test_helpers.chunk_group_consistency; consistent --------------------------------------------------------------------- t (1 row) DROP TABLE t;