Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@jbouzekri

This comment has been minimized.

jbouzekri commented Sep 2, 2016

Hi, could you add something like : show long running queries (more than 2 minutes for example) ? Great gist. I starred it. Thanks.

@scott-mead

This comment has been minimized.

scott-mead commented Sep 22, 2016

(On 9.2+)

SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;
@indigoviolet

This comment has been minimized.

indigoviolet commented Oct 11, 2016

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

@indigoviolet

This comment has been minimized.

indigoviolet commented Oct 11, 2016

select relname, n_dead_tup, last_vacuum, last_autovacuum from 
pg_catalog.pg_stat_all_tables
where n_dead_tup > 0 and relname =  ’table1' order by n_dead_tup desc;
@indigoviolet

This comment has been minimized.

indigoviolet commented Oct 17, 2016

Tables and views used by a given view:

with recursive view_tree(parent_schema, parent_obj, child_schema, child_obj, ind, ord) as 
(
  select vtu_parent.view_schema, vtu_parent.view_name, 
    vtu_parent.table_schema, vtu_parent.table_name, 
    '', array[row_number() over (order by view_schema, view_name)]
  from information_schema.view_table_usage vtu_parent
  where vtu_parent.view_schema = '<SCHEMA NAME>' and vtu_parent.view_name = '<VIEW NAME>'
  union all
  select vtu_child.view_schema, vtu_child.view_name, 
    vtu_child.table_schema, vtu_child.table_name, 
    vtu_parent.ind || '  ', 
    vtu_parent.ord || (row_number() over (order by view_schema, view_name))
  from view_tree vtu_parent, information_schema.view_table_usage vtu_child
  where vtu_child.view_schema = vtu_parent.child_schema 
  and vtu_child.view_name = vtu_parent.child_obj
) 
select tree.ind || tree.parent_schema || '.' || tree.parent_obj 
  || ' depends on ' || tree.child_schema || '.' || tree.child_obj txt, tree.ord
from view_tree tree
order by ord;
@indigoviolet

This comment has been minimized.

indigoviolet commented Nov 15, 2016

@dantheman213

This comment has been minimized.

dantheman213 commented Jan 16, 2017

how can you see a list of queries or functions that have run within the last X minutes? I'm looking to do something equivalent to what SQL Profiler can do for MS SQL Server.

@Onumis

This comment has been minimized.

Onumis commented Feb 24, 2017

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

-- table index usage rates (should not be less than 0.99)
SELECT relname, 
  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,
  n_live_tup AS rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;
@metaodi

This comment has been minimized.

metaodi commented Mar 2, 2017

Check the size (as in disk space) of all databases:

SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
    ELSE 'No Access' 
  END AS SIZE 
FROM pg_catalog.pg_database d 
ORDER BY 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL 
  END;

Check the size (as in disk space) of each table:

SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
   AND C.relkind <> 'i'
   AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC;
@mfrr1118

This comment has been minimized.

mfrr1118 commented May 21, 2017

This is great. Thanks!

@vishaldba

This comment has been minimized.

vishaldba commented Jun 21, 2017

How to monitor long running queries on a postgres OLAP server and kill them after a specific interval say 1 hour.Please share some ideas or scripts.

@YangBaohust

This comment has been minimized.

YangBaohust commented Jul 6, 2017

There is a problem confuse me. When I inquiry the pg_stat_activity, the sql text is too long to show completly in the column current_query. How to show it completly? Wait for answer......

@jdbeckerle

This comment has been minimized.

jdbeckerle commented Jul 15, 2017

I have the same question as YangBaohust. I need to see the entire text of a running "query" (an update statement in my case). The snippet provided in the query field of pg_stat_activity is too short for me to be useful.

@jdbeckerle

This comment has been minimized.

jdbeckerle commented Jul 15, 2017

One answer to my question is to simply look in the log files in the /data/pg_log, where you will see the full text of the queries (depending on logging level?). That is only updated after a select query runs I think, but I think it is updated with a DML statement before it runs.

@hglus

This comment has been minimized.

hglus commented Aug 21, 2017

https://www.postgresql.org/docs/9.6/static/runtime-config-statistics.html

track_activity_query_size (integer)
Specifies the number of bytes reserved to track the currently executing command for each active session, for the pg_stat_activity.query field. The default value is 1024. This parameter can only be set at server start.

@beregond

This comment has been minimized.

beregond commented Sep 12, 2017

Modification of @scott-mead for 9.4+

SELECT pid, now() - query_start as "runtime", usename, datname, waiting, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval and state = 'active'
 ORDER BY runtime DESC;
@brandonros

This comment has been minimized.

brandonros commented Oct 6, 2017

@Onumis

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

-- table index usage rates (should not be less than 0.99)
SELECT relname,
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,
n_live_tup AS rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

I've got a ton of tables with percent_of_times_index_used | 0.00000000000000000000. Any advice?

@gmoeller

This comment has been minimized.

gmoeller commented Nov 10, 2017

@scott-mead I would like to use your query,but it gives me this error...suggestions? removing waiting will allow it to run, but there are no entries in the results.
ERROR: column "waiting" does not exist
LINE 1: ...w() - query_start as "runtime", usename, datname, waiting, s...
^
SQL state: 42703
Character: 60

@Raymondd

This comment has been minimized.

Raymondd commented Nov 13, 2017

@gmoeller
You are probably using Postgres 9.6 which no longer has a waiting column in pg_stat_activity.
Try replacing waiting with wait_event.
More details about this column can be found here.

@edsonhoraciojunior

This comment has been minimized.

edsonhoraciojunior commented Dec 5, 2017

Nice! Just a correction though, the code below measure databases size, not tables.

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

This comment has been minimized.

MylesFowler commented Dec 20, 2017

Super helpful. Thanks!

@pazaricha

This comment has been minimized.

pazaricha commented Jan 13, 2018

This is amazing! Thanks!!

@ZiFFeL1992

This comment has been minimized.

ZiFFeL1992 commented Feb 26, 2018

Is there a way to print the whole query? Mine seems to be long and gets trunked :/

@arnehuang

This comment has been minimized.

arnehuang commented Mar 14, 2018

See all locks:

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted 
FROM pg_locks l, pg_stat_all_tables t 
WHERE l.relation = t.relid ORDER BY relation asc;
@staribelli

This comment has been minimized.

staribelli commented Mar 15, 2018

Very handy, thank you!

@ravthiru

This comment has been minimized.

ravthiru commented Apr 5, 2018

Estimated Row count of table, when table becomes too large to execute count(*)

SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables where relname ='table_name'

@sudheerkondisetty

This comment has been minimized.

sudheerkondisetty commented Apr 30, 2018

Hi,

Please help me with the below request. We need to fill idle connections in the postgresql but its killing the active connections too. Could someone please help me in resolving this. Below is the code using for killing the idle sessions.

WITH pg_stat_activity AS (
SELECT
procpid,query_start,datname,
rank() over (partition by client_addr order by backend_start ASC) as rank
FROM
pg_stat_activity
WHERE
procpid<>pg_backend_pid( )
AND
application_name !~ '(?:psql)|(?:pgAdmin.+)'

AND

( usename in ('zaraffas_postgres','arag_postgres','univfans_postgres','rcg_postgres','loreilly_postgres','mm_postgres','amsl_postgres','westend_postgres','soulfresh_postgres') OR (usename='postgres' and datname='ancol'))
AND
current_query in ('')
AND

    (current_timestamp - query_start) &gt; interval '10 minutes'

)
SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE
rank < 1

@linusliu

This comment has been minimized.

linusliu commented Apr 30, 2018

Is there anyway to show CPU usage of each query?

@davidwfranco

This comment has been minimized.

davidwfranco commented May 7, 2018

Pretty handy to have around.
Thanks a lot man.

@wy-z

This comment has been minimized.

wy-z commented May 28, 2018

https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

@DedicatedTools

This comment has been minimized.

DedicatedTools commented Jun 1, 2018

Is there a way to look "inside" a function (see it's current action step) while it's being executed?
For example, let's say pg_stat_activity shows "select * from long_running_batch()" as active for a while. How can I find out what's that long_running_batch() function executing now?

@ofirnk

This comment has been minimized.

ofirnk commented Jun 6, 2018

Get all table sizes:

(Followed by query to get _schema_s sizes)

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC
;

Get schema s sizes

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname                AS schema_name,
         sum(pg_relation_size(pg_catalog.pg_class.oid)) AS schema_size
       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
       group by 1
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY schema_size DESC;
@svetlyak40wt

This comment has been minimized.

svetlyak40wt commented Jun 20, 2018

In first two queries age function is used incorrectly and produces a negative result. You need to swap it's arguments like that:

age(clock_timestamp(), query_start)
@pj0064387

This comment has been minimized.

pj0064387 commented Jun 28, 2018

HI All,

I need queries for below metrics urgently, can someone pleaese help me here. We are working on postgres from last month and need to expose the metrics through a middleware. For which we need to generate values for below metrics using postgres queries. I searched almost 100 s of sites but no luck. Can someone help me.

  • Memory Utilization
  • CPU Utilization
  • Disk I/O rate
  • File Descriptor usage
  • Network Load in terms of received and transmitted bytes rate
  • File system fullness

Regards
Pankaj

@vshswetali

This comment has been minimized.

vshswetali commented Jul 17, 2018

Hi ,
I am running following query to find out block sessions in backend. it showing no result. but when I am trying to drop one problematic schema ,which was stuck on one query ,but it is not dropping .I have kill that query session .No any query running on that schema. so Schema should be drop but its not dropping . It means it has block somewhere else at back end. So please help me how can find out block schema.

Block id Query:
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
now() - blocked_activity.query_start
AS blocked_duration,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
now() - blocking_activity.query_start
AS blocking_duration,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks AS blocked_locks
JOIN pg_catalog.pg_stat_activity AS blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks AS blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity AS blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

@gopivalusa

This comment has been minimized.

gopivalusa commented Aug 2, 2018

Hi,
I have been manually performing " vacuum(full, analyse) " or ' vacuum full schema1."FeatureClass1" ' but when I am issuing " select * from pg_stat_all_tables " it is showing that all column values as zero(0) and time_stamp as "blank"
Help me how can I resolve this issue.

Regards,
Gopi

@alegallegos

This comment has been minimized.

alegallegos commented Aug 10, 2018

SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
FROM pg_stat_activity
WHERE now() - query_start > '2 minutes'::interval
ORDER BY runtime DESC;

For this query you need to filter by active state, because the query could be idle

@rgreenjr

This comment has been minimized.

Owner

rgreenjr commented Aug 12, 2018

Thanks @svetlyak40wt

@NARKOZ

This comment has been minimized.

NARKOZ commented Aug 28, 2018

Show unused indexes:

SELECT relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY pg_relation_size(indexrelname::regclass) DESC;
@nagaraju11

This comment has been minimized.

nagaraju11 commented Sep 5, 2018

Hello,
Is there a way to find out daily base tuple in/out transactions. If please share query.
Appreciated for your help.

Thank you

@bharatbhamare

This comment has been minimized.

bharatbhamare commented Sep 21, 2018

Hi,

Please help me with the below request. We need to fill idle connections in the postgresql but its killing the active connections too. Could someone please help me in resolving this. Below is the code using for killing the idle sessions.

WITH pg_stat_activity AS (
SELECT
procpid,query_start,datname,
rank() over (partition by client_addr order by backend_start ASC) as rank
FROM
pg_stat_activity
WHERE
procpid<>pg_backend_pid( )
AND
application_name !~ '(?:psql)|(?:pgAdmin.+)'

AND

( usename in ('zaraffas_postgres','arag_postgres','univfans_postgres','rcg_postgres','loreilly_postgres','mm_postgres','amsl_postgres','westend_postgres','soulfresh_postgres') OR (usename='postgres' and datname='ancol'))
AND
current_query in ('')
AND

    (current_timestamp - query_start) &gt; interval '10 minutes'

)
SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE
rank < 1

Hey here is solution to killing the idle sessions.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'DatabaseName'
AND pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE;

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