Skip to content

Instantly share code, notes, and snippets.

@snewcomer
Last active May 1, 2023 16:44
Show Gist options
  • Save snewcomer/1a4a7140b65ed5a3dc0f933653ec270b to your computer and use it in GitHub Desktop.
Save snewcomer/1a4a7140b65ed5a3dc0f933653ec270b to your computer and use it in GitHub Desktop.
toast queries
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname ~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 150;
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 150;
SELECT
relname AS jobs,
pg_size_pretty(pg_total_relation_size(relid)) AS total,
pg_size_pretty(pg_relation_size(relid)) AS internal,
pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
# get oid
select oid, reltoastrelid::regclass from pg_class where relname='global_audits_default';
# toast tables
SELECT oid::regclass,
reltoastrelid::regclass,
pg_relation_size(reltoastrelid) AS toast_size
FROM pg_class
WHERE relkind = 'r'
AND reltoastrelid <> 0
ORDER BY 3 DESC;
select '790554320'::regclass
select relname from pg_class where oid='790554320';
select relname, reltoastrelid::regclass from pg_class where relname = 'global_audits_controls';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment