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:
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;
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.
Subscribe to Holistics Blog
Get the latest posts delivered right to your inbox
From SQL Queries To Beautiful Charts
Connect to your database and build beautiful charts with Holistics BILearn More
"Holistics is the solution to the increasingly many and complex data requests from the operational teams"
Tang Yee Jie
Senior Data Analyst, Grab