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

"Show running queries" does not show running queries.

Correct request is

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

or maybe

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state not like 'idle%' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

depending on what you want. First request shows current query or last query in uncompleted transaction

For Postgres 9.3
Script to kill all running connections of a current database

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();

Find cardinality of index:

SELECT relname, relkind, reltuples as cardinality, relpages 
FROM pg_class 
WHERE relname LIKE 'tableprefix%';

Connection type and count:

SELECT count(*),
       state
FROM pg_stat_activity
GROUP BY 2;

Hi, @rgreenjr
It's better to replace new line characters in query to spaces replace(query, E'\n', ' ') or It will explode your brain on multiline queries.

Restart all sequences

SELECT 'ALTER SEQUENCE ' || relname || ' RESTART;' FROM pg_class WHERE relkind = 'S';

This is very awesome and useful. I've used this enough times that I had to stop by and say thanks!

Suppose I have a function with a parameter and is called from any backend language. How can I know what parameter is sent in PostgreSQL?
pg_stat_activity is only giving me what query is run. How do I know which value in parameter is used in the PostgreSQL function?

Please help me?
Thanks in advance.

same thinks can be easily done with SQL profiler in the SQL server.

Image attached below explaining what I like to know?

I used pg_stat_activity view to get the PID of the postgres session. How do you find the program that opened that session and its corresponding operating system process id.

I used pg_stat_activity view to get the PID of the postgres session. How do you find the program that opened that session and its corresponding operating system process id.

I believe its not possible, you can get only the database user that is running it, not the application nor OS.
It is a good practice to have different database users for each application that you allow access to your database exactly for this reason.

Find cardinality of index:

SELECT relname, relkind, reltuples as cardinality, relpages 
FROM pg_class 
WHERE relname LIKE 'tableprefix%';

a slightly neater way of presenting the query above..

-- Find cardinality of index
SELECT schema_name,
       object_name,
       object_type,
       cardinality,
       pages
FROM (
       SELECT pg_catalog.pg_namespace.nspname AS schema_name,
              relname                         as object_name,
              relkind                         as object_type,
              reltuples                       as cardinality,
              relpages                        as pages
       FROM pg_catalog.pg_class
              JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
  and schema_name <> 'information_schema'
  --and schema_name = '$schema_name'
  --and object_name = '$object_name'
ORDER BY pages DESC, schema_name, object_name
;

Suppose I have a function with a parameter and is called from any backend language. How can I know what parameter is sent in PostgreSQL?
pg_stat_activity is only giving me what query is run. How do I know which value in parameter is used in the PostgreSQL function?

Please help me?
Thanks in advance.

same thinks can be easily done with SQL profiler in the SQL server.

Image attached below explaining what I like to know?

@Diwas777, I am facing same request, did you get a solution to get parameter values? Thanks~!

Hi
Can anybody provide me the query to fetch of CPU utilization for each query. From pg_stat_activity I can get the pid and from top or ps I can get the CPU utilization. I want one query to fetch the details

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