Skip to content

Instantly share code, notes, and snippets.

@rgreenjr
Last active April 15, 2024 03:28
Show Gist options
  • Save rgreenjr/3637525 to your computer and use it in GitHub Desktop.
Save rgreenjr/3637525 to your computer and use it in GitHub Desktop.
Useful PostgreSQL Queries and Commands
-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), 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(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;
-- 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
@tychodaimon
Copy link

tychodaimon commented May 18, 2022

@HariSekhon You right, i not sure from which version it started, I get it on 11, 12 and also on 13, but condition query != '<IDLE>' not filter idle queries, only state != 'idle' give you list filtered from idle. (sorry I was thinking that You are author of this page)

@tychodaimon
Copy link

tychodaimon commented May 18, 2022

@rgreenjr And thanks for this page! I use Your queries for many years, today I put comment because just tired to fix condition after copy and paste all the time

@HariSekhon
Copy link

@tychodaimon yes I used state != 'idle' in that query too.

I documented my scripts with the versions of Postgres they work on.

You can then copy/paste or source from live psql prompt for the version of Postgres you're running.

The default scripts work on the latest versions of Postgres while the version suffixed scripts are maintained for older versions.

I also have other scripts there to run the latest scripts all on new versions to check if anything has changed/broken.

@tychodaimon
Copy link

@HariSekhon Thank you very much. You did a great job! I am studying your repository now. I commented here because this is the page that google gives me for "postgres running queries" which I am used to

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment