PostgreSQL TOAST not compressing values

1
votes

I was doing some test to check the best way to store encrypted data on my PostgreSQL database.

Now I have file contents stored on text table. I was 100% sure this data are compressed by TOAST https://www.postgresql.org/docs/9.3/storage-toast.html What I found - it is not true. All my 'files' or even normal text (checked with 1M rows of random 1000 chars text) are not compressed at all. I tried to SET STORAGE EXTENDED manually, but that didn't work.

Experiment:

  • create file with random text. 1 line = 1000 characters text. File size: ~1GB
  • insert data from file 1 line = 1 row. Database size: ~1.1GB (Toast 0 or null, table 1.1GB)

(checks were done on TEXT, TEXT(1001) and VARCHAR(1001). The only difference was toast size null for varchar tables)

Measured using query from this site: https://wiki.postgresql.org/wiki/Disk_Usage

My question is: why a long text is not compressed by Postgres by default like it is described in the manual (https://www.postgresql.org/docs/9.3/storage-toast.html) and what should I do to make it work?

1
postgresql
Besides @a_horse_with_n_name's spot on answer (As usual) you can check the toastiness of your table by querying that pg_class table you are hitting up for your table size but check out the reltoastrelid column. It will probably be set to 0 meaning your bread's not getting toasted. – JNevill

1 Answers

4
votes

Quote from the manual

The TOAST management code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB).

With a single column that has a maximum length of 1000 characters (=1KB unless you are using really fancy UTF-8 characters) you don't exceed the TOAST threshold of 2KB and thus the data is not compressed. You need to either use multiple columns, or a larger value in the single column