/ Lean Analytics

Useful PostgreSQL Queries For Monitoring Purpose

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

Get Running Queries (And Lock statuses) 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 
order by L.granted, L.pid DESC

Do note that this query will return multiple records for the same process, since it will list down all the corresponding locks that goes with the query.

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

From SQL Queries To Beautiful Charts

Connect to your database and build beautiful charts with Holistics BI

Learn More
Grab Logo

"Holistics is the solution to the increasingly many and complex data requests from the operational teams"


Tang Yee Jie - Senior Data Analyst, Grab

Tang Yee Jie

Senior Data Analyst, Grab