How to check if TOAST works in a specific table in postgres

Question

I have a table containing two text fields that contain a lot of text. For some reason, our table began to grow exponentially. I suspect TOAST (compression for text fields in postgres) does not work automatically. In our table definition, we did not define any condition for the forced compression of these fields. Is there a way to check if compression in this table works or not?

Answer 1

From the documents .,.

If any of the columns in the table is TOAST-capable, the table will have a related TOAST table whose OID is stored in the pg_class.reltoastrelid table. Inactive TOASTED values ​​are stored in the TOAST table, as described in more detail below.

This way you can determine if the TOAST table exists by querying the pg_class system directory. This should bring you closer to what you are 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 will use the pg_class system directory as an example; You must 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 | | 

If the storage is "expanded", PostgreSQL will try to reduce the size of the row by first compressing and then storing the data outside the row. Where Storage is the "primary" (not shown), PostgreSQL will try to compress.

In your particular case, you may find it useful to track 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 Administrator Functions Contains information about what each function includes in its calculations.

Answer 2

You can see which psql queries are running by running it with the -E option, then running the usual commands:

In this case, the one that came down first looked at your oid table:

 SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(YOUR_TABLE_NAME_HERE)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; 

then he does this to find more statistics about this:

 SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation, NULL AS indexdef, NULL AS attfdwoptions, a.attstorage, CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attrelid = '57692' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; 

x.attrstorage is what you care about p is PLAIN, x ADVANCED. I put.

Answer 3

If a table vacuum cleaner packs it in 19 GB with 80 GB, then what you probably see is MVCC at work: the dead rows take up space until they are dusted or reused.

http://wiki.postgresql.org/wiki/MVCC

Answer 4

It is old, but I have recently achieved some success in a similar problem. ANALYZE VERBOSE showed that several of our tables grew to> 1 page per tuple, and EXPLAIN ANALYZE showed that sequential scanning took up to 30 seconds on a table of 27 thousand lines. Estimates of the number of active rows are declining more and more.

After a long search, I found out that rows can only be cleared if there is no transaction that has been open since they were updated. This table was rewritten every 3 minutes, and there was a connection that was “inactive in a transaction” that was 3 days old. You can do the math.

In this case, we had to

• kill open transaction connection
• reconnect to the database. Unfortunately, the maximum transaction ID for rows that can be evacuated is currently (since 9.3) stored in the connection, so the vacuum will not work.
• VACUUUM FULL is your table (this will result in an ACCESS EXCLUSIVE lock, which locks everything, including reading. You might want to start VACUUM the first time (without locking) to speed up the time required by VACUUM FULL).

Perhaps this was not your problem, but if you want to see if the tables in your own database have suffered, I wrote a query to sort the tables by the average number of tuples stored on the disk page. Tables with large rows should be at the top - ANALYZE VERBOSE should give you an idea of ​​the ratio of dead to living tuples in these tables. Valid for 9.3 - this will probably require some small tweaks for other versions:

 SELECT rolname AS owner, nspname AS schemaname , relname AS tablename , relpages, reltuples, (reltuples::FLOAT / relpages::FLOAT) AS tuples_per_page FROM pg_class JOIN pg_namespace ON relnamespace = pg_namespace.oid JOIN pg_roles ON relowner = pg_roles.oid WHERE relkind = 'r' AND relpages > 20 AND reltuples > 1000 AND nspname != 'pg_catalog' ORDER BY tuples_per_page;