Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- check indexes in a table
select *
from pg_indexes
where tablename like 'my_table';

-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show long-running queries (greater than 5 minutes)
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes' ORDER BY duration DESC;

-- show blocked queries
SELECT pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- show locks
SELECT locktype, relation::regclass, mode, pid FROM pg_locks

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- all database users
select * from pg_stat_activity where current_query not like '<%';

-- all databases and their sizes
select * from pg_user;

-- all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

-- size of tables and indexes

SELECT schemaname as table_schema,
       relname as table_name,
       pg_size_pretty(pg_total_relation_size(relid)) as total_size,
       pg_size_pretty(pg_relation_size(relid)) as data_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) 
        as external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) desc,
         pg_relation_size(relid) desc;
  

-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

-- correlation of data for a given table
SELECT attname, correlation
FROM pg_stats WHERE tablename = 'orders_70'
ORDER BY abs(correlation) DESC;

-- check how many pages are visible for a given table
SELECT relpages, relallvisible
FROM pg_class WHERE relname = 'orders_70';

-- Copy the results of a query in a local csv (Client)
\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

-- Check vacuum progress
SELECT * FROM pg_stat_progress_vacuum;

-- Query for tables with a given column
SELECT DISTINCT c.table_schema, 
                c.table_name, 
                c.column_name 
FROM information_schema.columns c
LEFT JOIN pg_inherits i
  ON c.table_name = i.inhrelid::regclass::text 
  AND c.table_schema = 'public' -- or your specific schema
WHERE c.column_name LIKE '%source_account_id%'
  AND i.inhparent IS NULL;
  
-- Manual PG:OUTLIERS
select
	interval '1 millisecond' * total_exec_time as total_exec_time,
	to_char((total_exec_time / sum(total_exec_time) over()) * 100,
	'FM90D0') || '%' as prop_exec_time,
	to_char(calls,
	'FM999G999G999G990') as ncalls,
	interval '1 millisecond' * (blk_read_time + blk_write_time) as sync_io_time,
	case
		when length(query) <= 40 then query
		else substr(query,
		0,
		39) || '…'
	end as query
from
	pg_stat_statements
where
	userid = (
	select
		usesysid
	from
		pg_user
	where
		usename = current_user
	limit 1)
order by
	total_exec_time desc
limit 
    10;

  
-- Use Papertrail to check who destroyed a record
SELECT * FROM users WHERE email LIKE 'email';

SELECT * FROM versions WHERE item_type='User' AND event='destroy' ORDER BY created_at DESC;

SELECT
	*
FROM
	versions
WHERE
	item_type = 'User'
	AND object->>'email' = 'email'
ORDER BY
	created_at DESC;

SELECT * FROM versions ORDER BY created_at DESC LIMT 1;

This repository is very useful as well => https://github.com/HariSekhon/SQL-scripts