This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find a list of Temp tables with their size and user information in PostgreSQL.
We all aware of the temp tables and many times developers are creating temp table for their ad-hoc testing purpose.
But unfortunately sometimes, they forget to drop unused temp tables, and temp tables keep active in those idle connections. It also requires unnecessary utilization of disk space.
So now, DBAs are responsible for identifying unused temp tables and then inform to developers so that they can drop their unused temp tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT n.nspname as SchemaName ,c.relname as RelationName ,CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as RelationType ,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner ,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','s') AND (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%') ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC |
Leave a Reply