TOAST - The Oversized-Attribute Storage Technique

Giriş

Açıklaması şöyle

If the row/tuple is too big, Postgres will compress and (maybe) move the data out of the main table. The process is called TOAST (The Oversized-Attribute Storage Technique). Queries involving large data objects stored in TOAST tables can be slower than those with smaller data objects.

PG_STATIO_USER_TABLES Sistem Tablosu veri tabanındaki tabloları için I/O kullanımını gösterir

Örnek

Şöyle yaparız

-- list of TOAST tables and indexes
SELECT *
FROM   pg_catalog.pg_class
WHERE  relname LIKE 'pg_toast%';

Şöyle yaparız

-- list of table that use TOAST
SELECT 
  schemaname,
  relname,
  toast_blks_read,  -- Number of disk blocks read from this table's TOAST table (if any)
  toast_blks_hit,   -- Number of buffer hits in this table's TOAST table (if any)
  tidx_blks_read,   -- Number of disk blocks read from this table's TOAST table indexes (if any)
  tidx_blks_hit     -- Number of buffer hits in this table's TOAST table indexes (if any)
FROM pg_catalog.pg_statio_user_tables
WHERE toast_blks_read + toast_blks_hit + tidx_blks_read + tidx_blks_hit > 0;

Şöyle yaparız

-- show storage/toast strategy
-- m = Main: This means no compression, no out of line storage. This is for data types which are not TOASTable at all.
-- p = Plain: Compression, but no out of line storage.
-- x = Extended: Compression and out of line storage.
-- e = External: No compression, but out of line storage.
SELECT psut.schemaname, psut.relname, pa.attname, atttypid :: regtype, attstorage 
FROM pg_catalog.pg_statio_user_tables psut
  join pg_catalog.pg_attribute pa ON psut.relid = pa.attrelid
WHERE psut.toast_blks_read > 0;