/ SQL

Quick guide: PostgreSQL Queries for Usage & Monitoring

Below are some useful Postgres queries for both troubleshooting and analytics purpose.

Get Current Running Queries in PostgreSQL

The below display currently running queries, and a granted column indicate whether the query has actually started running (or still waiting to acquire locks)

SELECT
  S.pid,
  age(clock_timestamp(), query_start),
  usename,
  query,
  L.mode,
  L.locktype,
  L.granted
FROM pg_stat_activity S
inner join pg_locks L on S.pid = L.pid 
group by 1, 2, 3, 4
order by L.granted, L.pid DESC

Cancel Running Queries

To kill a particular query, simply get its pid (using the above query) and run:

SELECT pg_cancel_backend(__pid__);

This will send a SIGINT to the current process. Learn more.

Show Biggest PostgreSQL Tables/Indexes And Their Size

SELECT
  nspname || '.' || relname AS "relation",
  pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;

Show All PostgreSQL Databases And Their Size

select
  datname as db,
  pg_size_pretty(pg_database_size(datname)) as size
from pg_database
order by pg_database_size(datname) desc;

Sample Output:

  db       | size    
-----------+---------
 prod      | 9852 MB
 postgres  | 7581 kB
 template1 | 7449 kB
 template0 | 7449 kB
(4 rows)

— — — — — — — — — — — —

Having problems finding a simple and affordable data reporting system for your startups? Check us out at holistics.io.

Huy Nguyen

Huy Nguyen

Original creator and cofounder of Holistics, a data platform for tech companies. Holistics’ customers are tech startups like Grab, Traveloka, ShopBack, 99co, Tech In Asia and alike.

Read More