Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- CHECK TIMINGS ON ACTIVE AND EXPENSIVE QUERIES
SELECT activity.*
FROM (
 SELECT 
   pid, 
   CASE WHEN state = 'active' THEN AGE(clock_timestamp(), query_start) 
        ELSE AGE(state_change, query_start)
   END as query_duration, -- This is how long the most recent query was running (or is running so far, if still active)
   AGE(clock_timestamp(), xact_start) as xact_duration, -- Same, but for the currently active transaction
   CASE WHEN state = 'active' THEN INTERVAL '0'
        ELSE AGE(clock_timestamp(), state_change)
   END as sleep_age, -- For idle connections, how long ago the last query completed.
   state, 
   query,
   client_addr,
   query_start,
   state_change
 FROM pg_stat_activity) activity
WHERE (activity.sleep_age < INTERVAL '5 minutes' AND activity.query_duration > INTERVAL '5 ms') OR activity.state = 'active'
ORDER BY query_duration DESC, xact_duration DESC;


-- THIS KILLS THE QUERY - kill long-running quries.
SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, client_addr, query
FROM pg_stat_activity 
WHERE state = 'active' 
  AND query NOT ILIKE '%pg_stat_activity%' 
  AND AGE(clock_timestamp(), query_start) > INTERVAL '10 seconds'
ORDER BY age desc;


-- Forcibly kill idle connections or queries, or mid-transaction statement batches.
SELECT pg_terminate_backend(procpid);

-- Below are less custom tailored for us, but useful references --

-- show running queries
SELECT pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
FROM pg_stat_activity 
WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY age desc;

-- Show outstanding connections
SELECT client_hostname, client_addr, COUNT(*)
FROM pg_stat_activity 
GROUP BY client_hostname, client_addr
ORDER BY count DESC;

-- count of running queries
SELECT COUNT(*)
FROM pg_stat_activity 
WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 

-- count expensive or stalled queries
SELECT COUNT(*)
FROM pg_stat_activity 
WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 
  AND AGE(clock_timestamp(), query_start) > INTERVAL '30 seconds'

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill long running active queries
SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
FROM pg_stat_activity 
WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 
  AND AGE(clock_timestamp(), query_start) > INTERVAL '1 minute'
ORDER BY age desc;



-- Below are much less useful for us --
-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- ???
select * from pg_stat_activity where current_query not like '<%';

-- all database users
select * from pg_user;

-- all databases and their sizes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

-- all tables and their size, with/without indexes
???

-- 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 99.0)
SELECT 
  relname as tablename, 
  10000 * idx_scan / (seq_scan + idx_scan) / 100.0 as percent_of_times_index_used, 
  n_live_tup as rows_in_table, 
  (seq_scan + idx_scan) as accesses
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;

-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > dump.sql

-- Import dump into existing database
$ psql -d newdb -f dump.sql