Useful PostgreSQL queries

pg.sql

-- Connection limits by role
SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit <> -1;

-- Change connection limit for a role
ALTER USER $role WITH CONNECTION LIMIT 64;

-- Current activity
SELECT *
FROM pg_stat_activity 
ORDER BY state_change ASC;

-- Connections by role
SELECT usename, COUNT(*) AS connections
FROM pg_stat_activity
GROUP BY 1;

-- Tables sizes
SELECT
  nspname AS "schema",
  relname AS "table",
  pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC;