techgaun · May 7, 2024 14:44
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
 -- show running queries (pre 9.2)
 SELECT procpid, age(query_start, clock_timestamp()), usename, current_query 
 FROM pg_stat_activity 
 WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY query_start desc;

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

 -- kill running query
 SELECT pg_cancel_backend(procpid);

 -- kill idle query
 SELECT pg_terminate_backend(procpid);

 -- vacuum command
 VACUUM (VERBOSE, ANALYZE);

 -- 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;

 -- 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;

 -- 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

 -- top unused indexes
 $ SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
 FROM pg_stat_user_indexes ui
 JOIN pg_index i ON ui.indexrelid = i.indexrelid
 WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
 pg_relation_size(i.indexrelid) DESC;

 -- completely unused indexes
 SELECT relid::regclass as table, indexrelid::regclass as index
     , pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
  JOIN pg_index
 USING (indexrelid)
 WHERE idx_scan = 0
   AND indisunique IS FALSE order by pg_relation_size(indexrelid)
                                       
 -- postgres db size at rest
 select pg_size_pretty(pg_database_size('zego'));

 -- postgres table size
 select pg_size_pretty(pg_relation_size('users'));
                                       
 -- postgres index size
 select pg_size_pretty(pg_relation_size('users_pkey'));
                                       
 -- postgres table size including indexes
 select pg_size_pretty(pg_total_relation_size('users'));
	-- show running queries (pre 9.2)
	SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
	FROM pg_stat_activity
	WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

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

	-- kill running query
	SELECT pg_cancel_backend(procpid);

	-- kill idle query
	SELECT pg_terminate_backend(procpid);

	-- vacuum command
	VACUUM (VERBOSE, ANALYZE);

	-- 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;

	-- 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;

	-- 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

	-- top unused indexes
	$ SELECT
	schemaname \|\| '.' \|\| relname AS table,
	indexrelname AS index,
	pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
	idx_scan as index_scans
	FROM pg_stat_user_indexes ui
	JOIN pg_index i ON ui.indexrelid = i.indexrelid
	WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
	ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
	pg_relation_size(i.indexrelid) DESC;

	-- completely unused indexes
	SELECT relid::regclass as table, indexrelid::regclass as index
	, pg_size_pretty(pg_relation_size(indexrelid))
	FROM pg_stat_user_indexes
	JOIN pg_index
	USING (indexrelid)
	WHERE idx_scan = 0
	AND indisunique IS FALSE order by pg_relation_size(indexrelid)

	-- postgres db size at rest
	select pg_size_pretty(pg_database_size('zego'));

	-- postgres table size
	select pg_size_pretty(pg_relation_size('users'));

	-- postgres index size
	select pg_size_pretty(pg_relation_size('users_pkey'));

	-- postgres table size including indexes
	select pg_size_pretty(pg_total_relation_size('users'));