Create a gist now

Instantly share code, notes, and snippets.

Embed
Useful PostgreSQL Queries and Commands
-- 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
@jbouzekri

This comment has been minimized.

Show comment
Hide comment
@jbouzekri

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

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.

Show comment
Hide comment
@scott-mead

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

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.

Show comment
Hide comment
@indigoviolet

indigoviolet Oct 11, 2016

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

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.

Show comment
Hide comment
@indigoviolet

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

Show comment
Hide comment
@indigoviolet

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

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.

Show comment
Hide comment
@indigoviolet

indigoviolet Nov 15, 2016

@dantheman213

This comment has been minimized.

Show comment
Hide comment
@dantheman213

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

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.

Show comment
Hide comment
@Onumis

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

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.

Show comment
Hide comment
@metaodi

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

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.

Show comment
Hide comment
@mfrr1118

mfrr1118 May 21, 2017

This is great. Thanks!

This is great. Thanks!

@vishaldba

This comment has been minimized.

Show comment
Hide comment
@vishaldba

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

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.

Show comment
Hide comment
@YangBaohust

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

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.

Show comment
Hide comment
@jdbeckerle

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

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.

Show comment
Hide comment
@jdbeckerle

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

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.

Show comment
Hide comment
@hglus

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

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.

Show comment
Hide comment
@beregond

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

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.

Show comment
Hide comment
@brandonros

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

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.

Show comment
Hide comment
@gmoeller

gmoeller 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

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.

Show comment
Hide comment
@Raymondd

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

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.

Show comment
Hide comment
@edsonhoraciojunior

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

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.

Show comment
Hide comment
@MylesFowler

MylesFowler Dec 20, 2017

Super helpful. Thanks!

Super helpful. Thanks!

@pazaricha

This comment has been minimized.

Show comment
Hide comment
@pazaricha

pazaricha Jan 13, 2018

This is amazing! Thanks!!

This is amazing! Thanks!!

@ZiFFeL1992

This comment has been minimized.

Show comment
Hide comment
@ZiFFeL1992

ZiFFeL1992 Feb 26, 2018

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

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

@arnehuang

This comment has been minimized.

Show comment
Hide comment
@arnehuang

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

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.

Show comment
Hide comment
@staribelli

staribelli Mar 15, 2018

Very handy, thank you!

Very handy, thank you!

@ravthiru

This comment has been minimized.

Show comment
Hide comment
@ravthiru

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

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.

Show comment
Hide comment
@sudheerkondisetty

sudheerkondisetty 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

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.

Show comment
Hide comment
@linusliu

linusliu Apr 30, 2018

Is there anyway to show CPU usage of each query?

Is there anyway to show CPU usage of each query?

@davidwfranco

This comment has been minimized.

Show comment
Hide comment
@davidwfranco

davidwfranco May 7, 2018

Pretty handy to have around.
Thanks a lot man.

Pretty handy to have around.
Thanks a lot man.

@wy-z

This comment has been minimized.

Show comment
Hide comment
@wy-z

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

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.

Show comment
Hide comment
@DedicatedTools

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

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.

Show comment
Hide comment
@ofirnk

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

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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment