Base table columns where TOAST-ing strategy has been changed

Back to Catalog
Goal Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal.
Notes Explanation of the toasting strategy.

  • p (plain): Values must always be stored plain (non-varlena types always use this value).
  • e (external): Values can be stored in a secondary “TOAST” relation (if relation has one, see pg_class.reltoastrelid).
  • m (main): Values can be compressed and stored inline.
  • x (extended): Values can be compressed and/or moved to a secondary relation.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source system catalog only
SQL Query 
SELECT n.nspname AS table_schema,
c.relname AS table_name,
at.attname AS column_name,
tp.typname AS column_data_type_name,
tbt.typname AS column_base_data_type_name,
CASE WHEN tp.typstorage='p' THEN 'plain'
WHEN tp.typstorage='e' THEN 'external'
WHEN tp.typstorage='m' THEN 'main'
WHEN tp.typstorage='x' THEN 'extended'
END AS type_toasting_strategy,
CASE WHEN at.attstorage ='p' THEN 'plain'
WHEN at.attstorage='e' THEN 'external'
WHEN at.attstorage='m' THEN 'main'
WHEN at.attstorage='x' THEN 'extended'
END AS column_toasting_strategy
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS tp ON at.atttypid=tp.oid
LEFT JOIN pg_type AS tbt ON tp.typbasetype=tbt.oid 
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r')
AND at.attisdropped='f'
AND at.attnum>0
AND tp.typlen = -1
AND tp.typstorage<>at.attstorage
ORDER BY table_schema, table_name, at.attnum;
Collections

This query belongs to the following collections:

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories

This query is classified under the following categories:

NameDescription
Data at the database physical levelQueries of this category provide information about the disk usage.
TOASTQueries of this category provide information about The Oversized-Attribute Storage Technique.