How to check if TOAST is working on a particular table in postgres

Question

I have a table that contains two text fields which hold a lot of text. For some reason our table have started growing exponentially. I suspect that TOAST (compression for text fields in postgres) is not working automatically. In our table definition we have not defined any clause to force compression of these fields. Is there a way to check if compression is working on that table or not?

Answer 1

From the docs . . .

If any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table's pg_class.reltoastrelid entry. Out-of-line TOASTed values are kept in the TOAST table, as described in more detail below.

So you can determine whether a TOAST table exists by querying the pg_class system catalog. This should get you close to what you're looking for.

select t1.oid, t1.relname, t1.relkind, t2.relkind, t2.relpages, t2.reltuples
from pg_class t1
inner join pg_class t2
on t1.reltoastrelid = t2.oid
where t1.relkind = 'r'
  and t2.relkind = 't';

In psql, you can use \d+. I'll use the pg_class system catalog as an example; you'd use your own table name.

sandbox=# \d+ pg_class
     Column     |   Type    | Modifiers | Storage  | Stats target | Description 
----------------+-----------+-----------+----------+--------------+-------------
 relname        | name      | not null  | plain    |              | 
 relnamespace   | oid       | not null  | plain    |              | 
 [snip]
 relacl         | aclitem[] |           | extended |              | 
 reloptions     | text[]    |           | extended |              | 

Where Storage is 'extended', PostgreSQL will try to reduce row size by compressing first, then by storing data out of line. Where Storage is 'main' (not shown), PostgreSQL will try to compress.

In your particular case, you might find it useful to monitor changes in size over time. You can use this query, and save the results for later analysis.

select table_catalog, table_schema, table_name, 
       pg_total_relation_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_total_relation_size,
       pg_relation_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_relation_size,
       pg_table_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_table_size
from information_schema.tables

PostgreSQL admin functions has details about what each function includes in its calculations.