0

I have setup two test systems for primary/stanbdy replication. On the bigger VM with more CPU power I did default_toast_compression = lz4 and wal_compression=on. When i created tables on that VM I could see the compression method in the pg_column_compression. Now I added a second VM with only 2 vCPUs that plays the role of primary in replication wal_level=replica and also default LZ4 but when i create tables it does not show up as compressed. Not even if I use pglz or lz4.

test01=# \d+ sales_record
                                                Table "public.sales_record"
     Column     |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 region         | character varying |           |          |         | extended | lz4         |              |
 country        | character varying |           |          |         | extended | lz4         |              |
 item_type      | character varying |           |          |         | extended | lz4         |              |
 sales_channel  | character varying |           |          |         | extended | lz4         |              |
 order_priority | character(1)      |           |          |         | extended | lz4         |              |
 order_date     | date              |           |          |         | plain    |             |              |
 order_id       | integer           |           |          |         | plain    |             |              |

I can see that the table has LZ4 but when I issue

SELECT pg_column_compression(country) from public.sales_record;

I see only NULL values.

test01=# SELECT pg_column_compression(region) from public.sales_record limit 10;
 pg_column_compression
-----------------------










(10 rows)

What have i missed here? I just wanted to test out LZ4. Is it because I only used COPY to import CSV file ? I inserted data into sales_record using:

COPY sales_record FROM '/var/lib/pgsql/14/import/50000SalesR.csv' CSV HEADER;

Additional test when I do simple INSERT on another table I see that it is being compressed.

CREATE TABLE tbl (id int,
                  col1 text COMPRESSION pglz,
                  col2 text COMPRESSION lz4,
                  col3 text);
INSERT INTO tbl VALUES (1, repeat('abc',1000), repeat('abc',1000),repeat('abc',1000));

Then I double check:

test01=# SELECT pg_column_compression(col3) from public.tbl limit 5;
 pg_column_compression
-----------------------
 lz4
 lz4
 lz4
 lz4
 lz4
(5 rows)

test01=# SELECT pg_column_compression(col1) from public.tbl limit 5;
 pg_column_compression
-----------------------
 pglz
 pglz
 pglz
 pglz
 pglz
(5 rows)

So it seems I need to use some switches if COPY command can use compression during import of CSV data ?

Improve this question
5
  • 2
    A column named "country" sounds as if the values are too small to be compressed at all.
    – user1822
    Commented Nov 5, 2022 at 9:25
  • 1
    This is a mess. Which queries are you running on which server, and why do the column names keep changing randomly from paragraph to paragraph? Do the same things consistently on each server and summarize the results clearly.
    – jjanes
    Commented Nov 5, 2022 at 16:16
  • Hi. The columns are being compressed when I do inserts but when I bulk load data with COPY they do not. They get a Null value in pg_column_compression. Maybe the COPY command cannot compress or I need some kind of parameter.
    – ultimo_frogman
    Commented Nov 6, 2022 at 14:13
  • A value that is shorter than the compression threshold will not be compressed regardless of the method on how you insert the rows. As the threshold is 2KB, I can't imagine that values in a column named "country" will ever be compressed
    – user1822
    Commented Nov 7, 2022 at 6:58
  • Hi @a_horse_with_no_name thanks for the answer. How can i here contribute it to you?
    – ultimo_frogman
    Commented Nov 7, 2022 at 8:21

1 Answer 1

Reset to default
0

Thank @a_horse_with_no_name for the reminder of 2KB Tuple treshold. That answered my question.

INSERT INTO test123 values ('123test543');
INSERT INTO test123 values (repeat('abc',1000)); 
test01=# SELECT pg_column_compression(test) from test123;
 pg_column_compression
-----------------------

 lz4
(2 rows)

We can see that second row is compressed because I had more then 2KB values inside the row. COPY command has nothing to do with this.

Kind regards.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.