Tuesday, February 12, 2019

Top 5 biggest tables in currently active scheme in PostgreSQL

SELECT C.relname                                     AS table_name,
       pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size,
       S.n_live_tup                                  AS total_rows
FROM pg_class C
       LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
       LEFT JOIN pg_stat_user_tables S ON (C.relname = S.relname)
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

Drop all active connections to PostgreSQL server

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'db' -- ! replace db with the target database name
  AND pid <> pg_backend_pid();