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:
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 - Business Intelligence & Data Engineering
Get the latest posts delivered right to your inbox