Useful Postgres queries

useful_pg_queries.sql

-- List running queries
--
select query,* from pg_stat_activity;

-- Show cache hit ratio
--
SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;

-- Kill running queries
-- Need to commit to make it happen...
--
SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'analytics';
--COMMIT;

-- list installed extensions
--
SHOW rds.extensions;

-- Manipulate all tables matching pattern e.g. drop or truncate.
-- Test then comment out RAISE and comment in EXECUTE to actually do it
--
DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_schema) || '.'
        || quote_ident(table_name)      -- escape identifier and schema-qualify!
    FROM   information_schema.tables
    WHERE  table_name LIKE 'your_table_prefix_' || '%'  -- your table name prefix
    AND    table_schema NOT LIKE 'pg_%'     -- exclude system schemas
LOOP
   RAISE NOTICE '%',
   -- put whatever you want here using _tbl for the table name e.g.
   --EXECUTE 'CREATE INDEX IF NOT EXISTS ' || _tbl || '_idx_date ON ' || _tbl || '(date desc)';
   --EXECUTE 'DROP TABLE ' || _tbl;
END LOOP;
END
$do$;


-- Show size of tables and indexes
--
SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;