Skip to content

Instantly share code, notes, and snippets.

@rgreenjr
Last active May 14, 2024 11:33
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
@JonEngle
Copy link

Hey Ron, small world.....I was looking for a few postgres queries and you popped up to the top of list!

@HariSekhon
Copy link

You guys might also be interested in my SQL-scripts repo which contain lots of SQL scripts for PostgreSQL and MySQL tested across all versions of PostgreSQL / MySQL / MariaDB - with fully documented headers showing version support and variations to account for version differences:

https://github.com/HariSekhon/SQL-scripts

There are also Google BigQuery SQL queries in there, as well as AWS Athena CloudTrail logs integration DDL, and I have Snowflake queries pending adding there too.

Additionally, in my adjacent DevOps Bash tools repo I've put my advanced .psqlrc and written scripts for one-touch test environments that boot any version of PostgreSQL, MySQL and MariaDB available in docker and drop you straight in to a psql / mysql shell with all the SQL scripts available in $PWD for fast easy testing and sourcing, as well as scripts to test any SQL script(s) against a list of versions of PostgreSQL / MySQL / MariaDB to determine its compatibility across versions (I used this to determine the version compatibility for every interesting SQL query which is documented in the headers in each SQL script)

@alamk6043
Copy link

How to find list of tables,which have not been accessed and their last accessed time

@robfordww
Copy link

robfordww commented Jun 15, 2021

Seems like the "[IDLE]" syntax is deprecated, perhaps this is better for active queries on modern Postgres:

SELECT age(clock_timestamp(), query_start), *
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

@rodrigues-pedro
Copy link

rodrigues-pedro commented Jun 15, 2021

Very Usefull, Sadly I allways come looking for this one:

select pid, pg_blocking_pids(pid) as blocked_by, query as blocked_query
from pg_stat_activity
where pg_blocking_pids(pid)::text != '{}';

@mencargo
Copy link

Great post, but outdated, I found myself creating my own version of the scripts often, so I decided to make a gist:
https://gist.github.com/mencargo/79447185034ebabcb49087008fbdc266

@HariSekhon
Copy link

@mencargo did you see my SQL-scripts repo mentioned above? I have different versions of these SQL queries for different versions of PostgreSQL / MySQL / MariaDB which are programmatically tested on all the different versions.

@mencargo
Copy link

@mencargo did you see my SQL-scripts repo mentioned above? I have different versions of these SQL queries for different versions of PostgreSQL / MySQL / MariaDB which are programmatically tested on all the different versions.

Yep, great collection, a bit advanced for my level.
Mine is just a single page, basic but useful stuff to have at hand, focused only on recent PostgreSQL.

@HariSekhon
Copy link

HariSekhon commented Jun 22, 2021

If you need SQL queries that work on a specific DB version they're all tested and documented in the headers of each script there all the way up to recent Postgres 13.

They're also mostly PostgreSQL because there are just so many useful queries and interesting things exposed to query in postgres SQL.

@tallpeak
Copy link

Possible division by zero when scanning index usage rates, fix:

  CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan) 
    ELSE 0
  END AS percent_of_times_index_used,

I usually use x/NULLIF(possible0, 0) for this. Whether MSSQL or PG, etc.

@HariSekhon
Copy link

Possible division by zero when scanning index usage rates, fix:

  CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan) 
    ELSE 0
  END AS percent_of_times_index_used,

I usually use x/NULLIF(possible0, 0) for this. Whether MSSQL or PG, etc.

That case statement pre-checks for zero and only runs that division if it's non-zero.

Checking my scripts, I've done it like this, which I think is more concise:

100 * idx_scan / GREATEST(seq_scan + idx_scan, 1)

taken from:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_tables_index_usage.sql

@singals
Copy link

singals commented Mar 5, 2022

top 10 tables with their size:

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

@tychodaimon
Copy link

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

@HariSekhon
Copy link

@tychodaimon that query almost identical to one I have here which is currently tested on PostgreSQL 9.2 - 13.0:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_running_queries.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