| \set HIDE_TOAST_COMPRESSION false |
| -- ensure we get stable results regardless of installation's default |
| SET default_toast_compression = 'pglz'; |
| -- test creating table with compression method |
| CREATE TABLE cmdata(f1 text COMPRESSION pglz); |
| CREATE INDEX idx ON cmdata(f1); |
| INSERT INTO cmdata VALUES(repeat('1234567890', 1000)); |
| \d+ cmdata |
| Table "public.cmdata" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+------+-----------+----------+---------+----------+-------------+--------------+------------- |
| f1 | text | | | | extended | pglz | | |
| Indexes: |
| "idx" btree (f1) |
| |
| CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4); |
| INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004)); |
| \d+ cmdata1 |
| Table "public.cmdata1" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+------+-----------+----------+---------+----------+-------------+--------------+------------- |
| f1 | text | | | | extended | lz4 | | |
| |
| -- verify stored compression method in the data |
| SELECT pg_column_compression(f1) FROM cmdata; |
| pg_column_compression |
| ----------------------- |
| pglz |
| (1 row) |
| |
| SELECT pg_column_compression(f1) FROM cmdata1; |
| pg_column_compression |
| ----------------------- |
| lz4 |
| (1 row) |
| |
| -- decompress data slice |
| SELECT SUBSTR(f1, 200, 5) FROM cmdata; |
| substr |
| -------- |
| 01234 |
| (1 row) |
| |
| SELECT SUBSTR(f1, 2000, 50) FROM cmdata1; |
| substr |
| ---------------------------------------------------- |
| 01234567890123456789012345678901234567890123456789 |
| (1 row) |
| |
| -- copy with table creation |
| SELECT * INTO cmmove1 FROM cmdata; |
| \d+ cmmove1 |
| Table "public.cmmove1" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+------+-----------+----------+---------+----------+-------------+--------------+------------- |
| f1 | text | | | | extended | | | |
| |
| SELECT pg_column_compression(f1) FROM cmmove1; |
| pg_column_compression |
| ----------------------- |
| pglz |
| (1 row) |
| |
| -- copy to existing table |
| CREATE TABLE cmmove3(f1 text COMPRESSION pglz); |
| INSERT INTO cmmove3 SELECT * FROM cmdata; |
| INSERT INTO cmmove3 SELECT * FROM cmdata1; |
| SELECT pg_column_compression(f1) FROM cmmove3; |
| pg_column_compression |
| ----------------------- |
| pglz |
| lz4 |
| (2 rows) |
| |
| -- test LIKE INCLUDING COMPRESSION |
| CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION); |
| \d+ cmdata2 |
| Table "public.cmdata2" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+------+-----------+----------+---------+----------+-------------+--------------+------------- |
| f1 | text | | | | extended | lz4 | | |
| |
| DROP TABLE cmdata2; |
| -- try setting compression for incompressible data type |
| CREATE TABLE cmdata2 (f1 int COMPRESSION pglz); |
| ERROR: column data type integer does not support compression |
| -- update using datum from different table |
| CREATE TABLE cmmove2(f1 text COMPRESSION pglz); |
| INSERT INTO cmmove2 VALUES (repeat('1234567890', 1004)); |
| SELECT pg_column_compression(f1) FROM cmmove2; |
| pg_column_compression |
| ----------------------- |
| pglz |
| (1 row) |
| |
| UPDATE cmmove2 SET f1 = cmdata1.f1 FROM cmdata1; |
| SELECT pg_column_compression(f1) FROM cmmove2; |
| pg_column_compression |
| ----------------------- |
| lz4 |
| (1 row) |
| |
| -- test externally stored compressed data |
| CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS |
| 'select array_agg(md5(g::text))::text from generate_series(1, 256) g'; |
| CREATE TABLE cmdata2 (f1 text COMPRESSION pglz); |
| INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000); |
| SELECT pg_column_compression(f1) FROM cmdata2; |
| pg_column_compression |
| ----------------------- |
| pglz |
| (1 row) |
| |
| INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000); |
| SELECT pg_column_compression(f1) FROM cmdata1; |
| pg_column_compression |
| ----------------------- |
| lz4 |
| lz4 |
| (2 rows) |
| |
| SELECT SUBSTR(f1, 200, 5) FROM cmdata1; |
| substr |
| -------- |
| 01234 |
| 8f14e |
| (2 rows) |
| |
| SELECT SUBSTR(f1, 200, 5) FROM cmdata2; |
| substr |
| -------- |
| 8f14e |
| (1 row) |
| |
| DROP TABLE cmdata2; |
| --test column type update varlena/non-varlena |
| CREATE TABLE cmdata2 (f1 int); |
| \d+ cmdata2 |
| Table "public.cmdata2" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+---------+-----------+----------+---------+---------+-------------+--------------+------------- |
| f1 | integer | | | | plain | | | |
| |
| ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; |
| \d+ cmdata2 |
| Table "public.cmdata2" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- |
| f1 | character varying | | | | extended | | | |
| |
| ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer; |
| \d+ cmdata2 |
| Table "public.cmdata2" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+---------+-----------+----------+---------+---------+-------------+--------------+------------- |
| f1 | integer | | | | plain | | | |
| |
| --changing column storage should not impact the compression method |
| --but the data should not be compressed |
| ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; |
| ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION pglz; |
| \d+ cmdata2 |
| Table "public.cmdata2" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- |
| f1 | character varying | | | | extended | pglz | | |
| |
| ALTER TABLE cmdata2 ALTER COLUMN f1 SET STORAGE plain; |
| \d+ cmdata2 |
| Table "public.cmdata2" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- |
| f1 | character varying | | | | plain | pglz | | |
| |
| INSERT INTO cmdata2 VALUES (repeat('123456789', 800)); |
| SELECT pg_column_compression(f1) FROM cmdata2; |
| pg_column_compression |
| ----------------------- |
| |
| (1 row) |
| |
| -- test compression with materialized view |
| CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1; |
| \d+ compressmv |
| Materialized view "public.compressmv" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+------+-----------+----------+---------+----------+-------------+--------------+------------- |
| x | text | | | | extended | | | |
| View definition: |
| SELECT cmdata1.f1 AS x |
| FROM cmdata1; |
| |
| SELECT pg_column_compression(f1) FROM cmdata1; |
| pg_column_compression |
| ----------------------- |
| lz4 |
| lz4 |
| (2 rows) |
| |
| SELECT pg_column_compression(x) FROM compressmv; |
| pg_column_compression |
| ----------------------- |
| lz4 |
| lz4 |
| (2 rows) |
| |
| -- test compression with partition |
| CREATE TABLE cmpart(f1 text COMPRESSION lz4) PARTITION BY HASH(f1); |
| CREATE TABLE cmpart1 PARTITION OF cmpart FOR VALUES WITH (MODULUS 2, REMAINDER 0); |
| CREATE TABLE cmpart2(f1 text COMPRESSION pglz); |
| ALTER TABLE cmpart ATTACH PARTITION cmpart2 FOR VALUES WITH (MODULUS 2, REMAINDER 1); |
| INSERT INTO cmpart VALUES (repeat('123456789', 1004)); |
| INSERT INTO cmpart VALUES (repeat('123456789', 4004)); |
| SELECT pg_column_compression(f1) FROM cmpart1; |
| pg_column_compression |
| ----------------------- |
| lz4 |
| (1 row) |
| |
| SELECT pg_column_compression(f1) FROM cmpart2; |
| pg_column_compression |
| ----------------------- |
| pglz |
| (1 row) |
| |
| -- test compression with inheritance, error |
| CREATE TABLE cminh() INHERITS(cmdata, cmdata1); |
| NOTICE: merging multiple inherited definitions of column "f1" |
| ERROR: column "f1" has a compression method conflict |
| DETAIL: pglz versus lz4 |
| CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); |
| NOTICE: merging column "f1" with inherited definition |
| ERROR: column "f1" has a compression method conflict |
| DETAIL: pglz versus lz4 |
| -- test default_toast_compression GUC |
| SET default_toast_compression = ''; |
| ERROR: invalid value for parameter "default_toast_compression": "" |
| HINT: Available values: pglz, lz4. |
| SET default_toast_compression = 'I do not exist compression'; |
| ERROR: invalid value for parameter "default_toast_compression": "I do not exist compression" |
| HINT: Available values: pglz, lz4. |
| SET default_toast_compression = 'lz4'; |
| SET default_toast_compression = 'pglz'; |
| -- test alter compression method |
| ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4; |
| INSERT INTO cmdata VALUES (repeat('123456789', 4004)); |
| \d+ cmdata |
| Table "public.cmdata" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+------+-----------+----------+---------+----------+-------------+--------------+------------- |
| f1 | text | | | | extended | lz4 | | |
| Indexes: |
| "idx" btree (f1) |
| |
| SELECT pg_column_compression(f1) FROM cmdata; |
| pg_column_compression |
| ----------------------- |
| pglz |
| lz4 |
| (2 rows) |
| |
| ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default; |
| \d+ cmdata2 |
| Table "public.cmdata2" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- |
| f1 | character varying | | | | plain | | | |
| |
| -- test alter compression method for materialized views |
| ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4; |
| \d+ compressmv |
| Materialized view "public.compressmv" |
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
| --------+------+-----------+----------+---------+----------+-------------+--------------+------------- |
| x | text | | | | extended | lz4 | | |
| View definition: |
| SELECT cmdata1.f1 AS x |
| FROM cmdata1; |
| |
| -- test alter compression method for partitioned tables |
| ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz; |
| ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4; |
| -- new data should be compressed with the current compression method |
| INSERT INTO cmpart VALUES (repeat('123456789', 1004)); |
| INSERT INTO cmpart VALUES (repeat('123456789', 4004)); |
| SELECT pg_column_compression(f1) FROM cmpart1; |
| pg_column_compression |
| ----------------------- |
| lz4 |
| pglz |
| (2 rows) |
| |
| SELECT pg_column_compression(f1) FROM cmpart2; |
| pg_column_compression |
| ----------------------- |
| pglz |
| lz4 |
| (2 rows) |
| |
| -- VACUUM FULL does not recompress |
| SELECT pg_column_compression(f1) FROM cmdata; |
| pg_column_compression |
| ----------------------- |
| pglz |
| lz4 |
| (2 rows) |
| |
| VACUUM FULL cmdata; |
| SELECT pg_column_compression(f1) FROM cmdata; |
| pg_column_compression |
| ----------------------- |
| pglz |
| lz4 |
| (2 rows) |
| |
| -- test expression index |
| DROP TABLE cmdata2; |
| CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4); |
| CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2)); |
| DETAIL: Distribution key column "f1" is not included in the constraint. |
| ERROR: UNIQUE index must contain all columns in the table's distribution key |
| INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM |
| generate_series(1, 50) g), VERSION()); |
| -- check data is ok |
| SELECT length(f1) FROM cmdata; |
| length |
| -------- |
| 10000 |
| 36036 |
| (2 rows) |
| |
| SELECT length(f1) FROM cmdata1; |
| length |
| -------- |
| 10040 |
| 12449 |
| (2 rows) |
| |
| SELECT length(f1) FROM cmmove1; |
| length |
| -------- |
| 10000 |
| (1 row) |
| |
| SELECT length(f1) FROM cmmove2; |
| length |
| -------- |
| 10040 |
| (1 row) |
| |
| SELECT length(f1) FROM cmmove3; |
| length |
| -------- |
| 10000 |
| 10040 |
| (2 rows) |
| |
| CREATE TABLE badcompresstbl (a text COMPRESSION I_Do_Not_Exist_Compression); -- fails |
| ERROR: invalid compression method "i_do_not_exist_compression" |
| CREATE TABLE badcompresstbl (a text); |
| ALTER TABLE badcompresstbl ALTER a SET COMPRESSION I_Do_Not_Exist_Compression; -- fails |
| ERROR: invalid compression method "i_do_not_exist_compression" |
| DROP TABLE badcompresstbl; |
| \set HIDE_TOAST_COMPRESSION true |
