Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

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

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

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)

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

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;

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 != '{}';

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

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

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.

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.

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

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;

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

@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

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

@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

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

@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