Search code examples
postgresqlcompression

Postgresql - How do I find compressed fields in a table?

Postgresql uses different storage techniques for field values. If values become large (e.g. are long texts), Postgresql will eventually apply compression and/or TOAST the values.

How do I find out which fields in a table are compressed?

(Backgound: I have database that stores tiny BLOBs in a some columns and I want to find out how much of it is compressed - if compression is hardly used, I want to turn it off, so Postgres won't waste CPU cycles on trying)

Solution

  • Starting in v14, there is the function pg_column_compression.

    select pg_column_compression(colname),count(*) from tablename group by 1;