Useful PostgreSQL Queries For Monitoring Purpose
Below are some useful Postgres queries for both troubleshooting, monitoring and analytics purpose.
Note: if you're looking for a simple tool to run queries & visualize PostgreSQL results, check out Holistics.io
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
This query returns list of the largest (in file sizes) tables and indexes in your database
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
This query returns list of the largest databases in your cluster.
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)
Show Table Bloats
Overtime due to MVCC, your table will grow in size (called table bloat) - this is why regular VACUUM is needed. This query will show you list of tables and indexes with the most bloats.
The value represents the number of "wasted bytes", or the difference between what is actually used by the table and index, and what we compute that it should be.
The way it works is it estimates the optimized size of the table/index by a calculation from each row sizes times total rows, and compare that against the actual table size. Do note that this is an estimate, not an actual figure.
with foo as (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+COUNT(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::NUMERIC) AS bs,
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
), rs as (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
(maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM foo
), sml as (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
FROM rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
)
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM sml
ORDER BY wastedbytes DESC
Query extracted but rewrote from checkpostgres
Sample Output:
current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes
------------------+------------+---------------------------------+--------+-------------+---------------------------------------------------------------+--------+--------------
dashboard | public | job_logs | 1.1 | 4139507712 | job_logs_pkey | 0.2 | 0
dashboard | public | job_logs | 1.1 | 4139507712 | index_job_logs_on_job_id_and_created_at | 0.4 | 0
dashboard | public | events | 1.1 | 3571736576 | events_pkey | 0.1 | 0
dashboard | public | events | 1.1 | 3571736576 | index_events_on_tenant_id | 0.1 | 0
dashboard | public | events | 1.1 | 3571736576 | index_events_on_event_type | 0.2 | 0
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_status | 0.0 | 0
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_tag | 0.3 | 0
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_tenant_id | 0.2 | 0
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_created_at | 0.2 | 0
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_created_at_queued_or_running | 0.0 | 21086208
You should be looking at:
- tbloat: table bloat, ratio between what it current is, and what it can be optimized to.
- wastedbytes: number of bytes wasted
- ibloat & wastedibytes: same as above, but for indexes.
When you see a table with high bloats, then consider running VACUUM ANALYZE on it.
Conclusion
I hope the above queries are useful for PostgreSQL monitoring purpose.
One last note, if you're looking for a simple tool to run queries & visualize PostgreSQL results, check out our product Holistics.io.