The list of all the queries

Base table columns where TOASTing is possible

Query goal: Find all base table columns in case of which the system can use TOAST technique.
Notes about the query: 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.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

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 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
ORDER BY table_schema, table_name;

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
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.

Reference materials for further reading

Reference
https://postgrespro.com/docs/postgrespro/current/storage-toast
https://wiki.postgresql.org/wiki/TOAST
https://www.postgresql.org/docs/current/storage-toast.html

The list of all the queries