A curated collection of diagnostic and maintenance SQL queries for PostgreSQL (10+). Each block can be copied with a single click. Queries are designed to run in psql or DBeaver without modification.
PostgreSQL is extensible by design: many optional features are available via CREATE EXTENSION (e.g. pg_stat_statements, pg_buffercache, pgstattuple, pgvector). Those are grouped under Optional Features.
SELECT version() AS full_version,
current_setting('server_version') AS short_version,
current_setting('server_version_num') AS version_num,
CASE WHEN trunc(current_setting('server_version_num')::int / 100)
IN (1600, 1700, 1800) THEN 'YES' ELSE 'NO'
END AS recent_major;
SELECT datname, oid, datdba::regrole::text AS owner,
pg_database_size(datname) AS size_bytes,
pg_size_pretty(pg_database_size(datname)) AS hr_size
FROM pg_database
WHERE NOT datistemplate
ORDER BY oid;
SELECT spcname AS name,
pg_catalog.pg_get_userbyid(spcowner) AS owner,
pg_catalog.pg_tablespace_location(oid) AS location,
pg_size_pretty(pg_tablespace_size(spcname)) AS hr_size
FROM pg_tablespace
ORDER BY spcname;
SELECT name,
CASE WHEN unit='kB' THEN pg_size_pretty(setting::bigint*1024)
WHEN unit='8kB' THEN pg_size_pretty(setting::bigint*8192)
WHEN unit='B' THEN pg_size_pretty(setting::bigint)
WHEN unit='MB' THEN pg_size_pretty(setting::bigint*1048576)
ELSE COALESCE(setting || ' ' || unit, setting)
END AS value,
min_val, max_val, context, short_desc
FROM pg_settings
WHERE name IN ('max_connections','shared_buffers','effective_cache_size',
'work_mem','maintenance_work_mem','wal_buffers',
'checkpoint_completion_target','synchronous_commit',
'random_page_cost','max_wal_size','min_wal_size',
'default_toast_compression','bgwriter_lru_maxpages')
ORDER BY context, name;
SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE 'lc%'
ORDER BY name;
SELECT nspname AS schema, rolname AS owner,
COUNT(*) FILTER (WHERE relkind='r') AS tables,
COUNT(*) FILTER (WHERE relkind='i') AS indexes,
COUNT(*) FILTER (WHERE relkind='p') AS part_tables,
COUNT(*) FILTER (WHERE relkind='I') AS part_indexes,
COUNT(*) FILTER (WHERE relkind='v') AS views,
COUNT(*) FILTER (WHERE relkind='S') AS sequences,
COUNT(*) FILTER (WHERE relkind='c') AS composite_types,
COUNT(*) FILTER (WHERE relkind='f') AS foreign_tables,
COUNT(*) FILTER (WHERE relkind='m') AS mat_views,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE relispartition) AS partitions,
COUNT(*) FILTER (WHERE relpersistence='u') AS unlogged,
COUNT(*) FILTER (WHERE relpersistence='t') AS temporary
FROM pg_class
JOIN pg_roles ON relowner = pg_roles.oid
JOIN pg_namespace ON relnamespace = pg_namespace.oid
WHERE rolname NOT IN ('enterprisedb','alloydbadmin','cloudsqladmin')
GROUP BY nspname, rolname
ORDER BY nspname, rolname;
SELECT nspname AS schema,
COUNT(*) FILTER (WHERE contype='p') AS primary_key,
COUNT(*) FILTER (WHERE contype='u') AS unique,
COUNT(*) FILTER (WHERE contype='f') AS foreign_key,
COUNT(*) FILTER (WHERE contype='c') AS check_constraint,
COUNT(*) FILTER (WHERE contype='x') AS exclusion,
COUNT(*) AS total
FROM pg_constraint
JOIN pg_namespace ON connamespace = pg_namespace.oid
WHERE nspname NOT IN ('information_schema','pg_catalog','sys')
GROUP BY nspname
ORDER BY nspname;
SELECT nspname AS schema, rolname AS owner,
COUNT(*) FILTER (WHERE prokind != 'p') AS functions,
COUNT(*) FILTER (WHERE prokind = 'p') AS procedures,
COUNT(*) AS total
FROM pg_proc
JOIN pg_roles ON proowner = pg_roles.oid
JOIN pg_language ON prolang = pg_language.oid
JOIN pg_namespace n ON pronamespace = n.oid
WHERE rolname NOT IN ('postgres','enterprisedb','alloydbadmin','cloudsqladmin')
GROUP BY nspname, rolname
ORDER BY nspname, rolname;
SELECT trigger_schema,
COUNT(*) FILTER (WHERE event_manipulation='INSERT') AS insert_triggers,
COUNT(*) FILTER (WHERE event_manipulation='UPDATE') AS update_triggers,
COUNT(*) FILTER (WHERE event_manipulation='DELETE') AS delete_triggers,
COUNT(*) FILTER (WHERE action_orientation='ROW') AS row_level,
COUNT(*) FILTER (WHERE action_orientation='STATEMENT') AS statement_level,
COUNT(*) FILTER (WHERE action_timing='BEFORE') AS before,
COUNT(*) FILTER (WHERE action_timing='AFTER') AS after,
COUNT(*) FILTER (WHERE action_timing='INSTEAD OF') AS instead_of,
COUNT(*) AS total
FROM information_schema.triggers
GROUP BY trigger_schema
ORDER BY trigger_schema;
SELECT evtevent AS event, evtname AS name,
evtowner::regrole::text AS owner,
evtfoid::regproc::text AS function,
CASE evtenabled WHEN 'A' THEN 'Always'
WHEN 'O' THEN 'Origin or Local'
WHEN 'R' THEN 'Replica'
WHEN 'D' THEN 'Disabled'
END AS enabled_mode,
p.prosecdef AS security_definer,
l.lanname AS language, evttags AS tags
FROM pg_event_trigger t
JOIN pg_proc p ON t.evtfoid = p.oid
JOIN pg_language l ON p.prolang = l.oid
ORDER BY evtevent, evtname;
Dead tuples and VACUUM are PostgreSQL peculiarities that need special handling. The bloat queries provide estimated waste based on statistics.
SELECT spcname AS tablespace,
COUNT(*) FILTER (WHERE relkind='r') AS tables,
COUNT(*) FILTER (WHERE relkind='i') AS indexes,
SUM(relpages) FILTER (WHERE relkind='r') * 8 AS table_kb,
SUM(relpages) FILTER (WHERE relkind='i') * 8 AS index_kb,
SUM(relpages) * 8 AS total_kb
FROM pg_class
LEFT JOIN pg_tablespace ON reltablespace = pg_tablespace.oid
GROUP BY spcname
ORDER BY spcname;
SELECT nspname AS schema, rolname AS owner,
COUNT(*) FILTER (WHERE relkind='r') AS tables,
SUM(GREATEST(reltuples,0)) FILTER (WHERE relkind='r') AS rows,
SUM(relpages) FILTER (WHERE relkind='r') * 8 AS table_kb,
SUM(relpages) FILTER (WHERE relkind='i') * 8 AS index_kb,
SUM(relpages) FILTER (WHERE relkind='t') * 8 AS toast_kb,
SUM(relpages) * 8 AS total_kb
FROM pg_class
JOIN pg_roles ON relowner = pg_roles.oid
JOIN pg_namespace ON relnamespace = pg_namespace.oid
WHERE rolname NOT IN ('enterprisedb','alloydbadmin','cloudsqladmin')
GROUP BY nspname, rolname
ORDER BY nspname, rolname;
SELECT COUNT(*) AS tables,
SUM(reltuples) AS total_rows,
SUM(relpages) * 8 AS total_relpages_kb,
SUM(pg_total_relation_size(oid))/1024 AS total_kb,
SUM(pg_relation_size(oid, 'main'))/1024 AS main_kb,
SUM(pg_relation_size(oid, 'fsm'))/1024 AS fsm_kb,
SUM(pg_relation_size(oid, 'vm'))/1024 AS vm_kb,
SUM(pg_relation_size(oid, 'init'))/1024 AS init_kb
FROM pg_class
WHERE relkind = 'r';
SELECT relname AS object,
CASE relkind WHEN 'r' THEN 'Table'
WHEN 'i' THEN 'Index'
WHEN 't' THEN 'TOAST Table'
ELSE relkind::text
END AS type,
rolname AS owner, n.nspname AS schema,
reltuples::bigint AS rows,
pg_size_pretty(pg_total_relation_size(pg_class.oid)) AS hr_total_size
FROM pg_class
JOIN pg_roles ON relowner = pg_roles.oid
JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
ORDER BY relpages DESC, reltuples DESC
LIMIT 20;
SELECT t.relname AS toast_name, rolname AS owner,
n.nspname || '.' || r.relname AS parent_table,
t.reltuples::bigint AS chunks,
pg_size_pretty(pg_relation_size(t.oid)) AS hr_size
FROM pg_class t
JOIN pg_roles ON t.relowner = pg_roles.oid
JOIN pg_catalog.pg_namespace n ON n.oid = r.relnamespace
JOIN pg_class r ON r.reltoastrelid = t.oid
WHERE t.relkind = 't' AND t.reltuples > 0
ORDER BY pg_relation_size(t.oid) DESC
LIMIT 10;
SELECT lomowner::regrole AS owner,
COUNT(DISTINCT loid) AS large_objects,
COUNT(*) AS total_pages,
MAX(pageno) + 1 AS pages_in_largest
FROM pg_largeobject l
JOIN pg_largeobject_metadata m ON l.loid = m.oid
GROUP BY lomowner;
SELECT schemaname || '.' || tblname AS table_name,
pg_size_pretty(bs * tblpages) AS real_size,
CASE WHEN tblpages - est_tblpages_ff > 0
THEN pg_size_pretty(((tblpages - est_tblpages_ff) * bs)::bigint)
ELSE '0'
END AS bloat_size,
CASE WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0
THEN round(100 * (tblpages - est_tblpages_ff) / tblpages::float)
ELSE 0
END AS bloat_pct
FROM (
SELECT ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100)))
+ ceil(toasttuples / 4) AS est_tblpages_ff,
tblpages, fillfactor, bs, tblid, schemaname, tblname,
heappages, toastpages, is_na
FROM (
SELECT (4 + tpl_hdr_size + tpl_data_size + (2*ma)
- CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
- CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END
) AS tpl_size,
bs - page_hdr AS size_per_block,
(heappages + toastpages) AS tblpages, heappages, toastpages,
reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname,
fillfactor, is_na
FROM (
SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname,
tbl.reltuples, tbl.relpages AS heappages,
COALESCE(toast.relpages, 0) AS toastpages,
COALESCE(toast.reltuples, 0) AS toasttuples,
COALESCE(SUBSTRING(array_to_string(tbl.reloptions, ' ')
FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
current_setting('block_size')::numeric AS bs,
CASE WHEN version()~'64-bit|x86_64|amd64' THEN 8 ELSE 4 END AS ma,
24 AS page_hdr,
23 + CASE WHEN MAX(COALESCE(s.null_frac,0)) > 0
THEN (7 + COUNT(s.attname)) / 8 ELSE 0::int END
+ CASE WHEN BOOL_OR(att.attname = 'oid' AND att.attnum < 0)
THEN 4 ELSE 0 END AS tpl_hdr_size,
SUM((1 - COALESCE(s.null_frac,0)) * COALESCE(s.avg_width,0))
AS tpl_data_size,
BOOL_OR(att.atttypid = 'pg_catalog.name'::regtype)
OR SUM(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END)
<> COUNT(s.attname) AS is_na
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname = ns.nspname
AND s.tablename = tbl.relname
AND s.inherited = false
AND s.attname = att.attname
LEFT JOIN pg_class toast ON tbl.reltoastrelid = toast.oid
WHERE NOT att.attisdropped
AND tbl.relkind IN ('r','m')
GROUP BY 1,2,3,4,5,6,7,8,9,10
) s
) s2
) s3
WHERE NOT is_na
AND tblpages - est_tblpages_ff > 1
AND est_tblpages_ff > 2
ORDER BY (tblpages - est_tblpages_ff) DESC
LIMIT 20;
SELECT schemaname || '.' || relname AS table_name,
n_live_tup, n_dead_tup,
round(100 * n_dead_tup / (n_live_tup + n_dead_tup)::float) AS dead_pct,
last_autovacuum, last_vacuum, last_autoanalyze, last_analyze
FROM pg_stat_all_tables
WHERE n_dead_tup > 1000
AND n_dead_tup > n_live_tup * 0.05
ORDER BY n_dead_tup DESC
LIMIT 20;
SELECT datname,
age(datfrozenxid) AS max_xid_age,
round(age(datfrozenxid)::numeric / 2000000000 * 100, 2) AS wraparound_pct
FROM pg_database
ORDER BY age(datfrozenxid) DESC
LIMIT 32;
WITH db_velocity AS (
SELECT CASE
WHEN extract(epoch FROM (now() - least(pg_postmaster_start_time(), stats_reset))) < 86400
THEN txid_current() / (extract(epoch FROM (now() - least(pg_postmaster_start_time(), stats_reset))) / 86400)
ELSE txid_current() / extract(day FROM (now() - least(pg_postmaster_start_time(), stats_reset)))
END AS xid_per_day
FROM pg_stat_database
WHERE datname = current_database()
)
SELECT n.nspname || '.' || c.relname AS object,
CASE c.relkind WHEN 'r' THEN 'Table'
WHEN 'm' THEN 'Mat. View'
WHEN 't' THEN 'TOAST'
END AS type,
age(c.relfrozenxid) AS xid_age,
round(100.0 * age(c.relfrozenxid)
/ current_setting('autovacuum_freeze_max_age')::int, 2) AS anti_wraparound_pct,
round((1600000000 - age(c.relfrozenxid)::numeric) / v.xid_per_day, 1) AS days_to_failsafe
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
CROSS JOIN db_velocity v
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;
SELECT p.pid, p.phase,
p.heap_blks_total, p.heap_blks_scanned,
round(p.heap_blks_scanned * 100.0 / nullif(p.heap_blks_total,0), 1) AS scan_pct,
p.heap_blks_vacuumed,
round(p.heap_blks_vacuumed * 100.0 / nullif(p.heap_blks_total,0), 1) AS vacuum_pct,
c.relname, a.state, a.wait_event_type, a.wait_event, a.query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid
JOIN pg_class c ON p.relid = c.oid;
-- Sessions by user
SELECT usename AS user, datname AS database,
count(*) AS sessions,
count(*) FILTER (WHERE state='active') AS active,
count(*) FILTER (WHERE state='idle in transaction') AS idle_tx
FROM pg_stat_activity
GROUP BY usename, datname
ORDER BY sessions DESC
LIMIT 16;
-- Sessions by host
SELECT coalesce(client_addr::text, 'local') AS address,
datname, count(*) AS sessions,
count(*) FILTER (WHERE state='active') AS active
FROM pg_stat_activity
GROUP BY client_addr, datname
ORDER BY sessions DESC
LIMIT 16;
-- Sessions by application
SELECT application_name, datname, count(*) AS sessions,
count(*) FILTER (WHERE state='active') AS active
FROM pg_stat_activity
GROUP BY application_name, datname
ORDER BY sessions DESC
LIMIT 16;
SELECT pid, datname, usename, client_addr,
to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS') AS session_start,
state, query_start, now() - query_start AS duration,
backend_type, application_name, query, query_id
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY CASE state WHEN 'active' THEN 0
WHEN 'idle in transaction' THEN 1
WHEN 'idle' THEN 3
ELSE 2
END, query_start;
SELECT pid, datname, usename, query_start, state,
wait_event, wait_event_type, backend_type, query, query_id
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state = 'active'
AND backend_type <> 'walsender'
ORDER BY query_start;
SELECT pid, locktype, datname, relname, mode,
granted, virtualtransaction, waitstart
FROM pg_locks l
LEFT JOIN pg_catalog.pg_database d ON d.oid = l.database
LEFT JOIN pg_catalog.pg_class r ON r.oid = l.relation
WHERE NOT granted
ORDER BY waitstart NULLS LAST, pid;
SELECT blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
blocked.query AS blocked_statement,
blocking.query AS blocking_statement,
lck.mode, lck.locktype, lck.waitstart
FROM pg_stat_activity blocked
JOIN pg_locks lck ON blocked.pid = lck.pid
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE NOT lck.granted
ORDER BY lck.waitstart;
SELECT locktype, datname, mode, granted, count(*) AS total
FROM pg_locks l
LEFT JOIN pg_catalog.pg_database d ON d.oid = l.database
LEFT JOIN pg_catalog.pg_class r ON r.oid = l.relation
GROUP BY locktype, datname, mode, granted
ORDER BY granted, datname, locktype, mode;
SELECT rolname AS role, rolcanlogin AS can_login, rolinherit,
rolsuper AS superuser, rolcreatedb AS create_db,
rolcreaterole AS create_role, rolbypassrls AS bypass_rls,
rolvaliduntil AS expiry_time, rolconnlimit AS max_connections,
rolconfig AS config
FROM pg_roles
ORDER BY rolcanlogin DESC, rolname;
SELECT member::regrole::text AS grantee,
admin_option,
string_agg(roleid::regrole::text, ', ' ORDER BY roleid) AS granted_roles
FROM pg_auth_members
GROUP BY member, admin_option
ORDER BY grantee;
SELECT type, database, user_name, address, netmask,
auth_method, options, error
FROM pg_hba_file_rules
ORDER BY line_number;
-- Weak known passwords
SELECT usename, passwd, 'Weak common password' AS note
FROM pg_shadow
WHERE substr(passwd,4) IN (
md5('postgres'||usename), md5('admin'||usename),
md5('password'||usename), md5('root'||usename),
md5('1234'||usename), md5('changeme'||usename)
)
UNION ALL
-- Password equals username
SELECT usename, passwd, 'Same as username'
FROM pg_shadow
WHERE substr(passwd,4) = md5(usename || usename)
UNION ALL
-- Unencrypted passwords
SELECT usename, passwd, 'Unencrypted'
FROM pg_shadow
WHERE passwd NOT LIKE 'md5%' AND passwd NOT LIKE 'SCRAM%'
UNION ALL
-- Null passwords
SELECT usename::text, '(null)', 'Empty password'
FROM pg_shadow
WHERE passwd IS NULL
ORDER BY 1, 3;
-- Additional superusers (excluding default admins)
SELECT rolname FROM pg_roles
WHERE rolcanlogin AND rolsuper
AND rolname NOT IN ('postgres','rdsadmin','enterprisedb',
'alloydbadmin','cloudsqladmin');
-- Users with sensitive role attributes
SELECT rolname, 'CREATEROLE' AS priv FROM pg_roles
WHERE rolcreaterole AND rolname NOT IN ('postgres','enterprisedb',
'alloydbadmin','cloudsqladmin')
UNION ALL
SELECT rolname, 'CREATEDB' FROM pg_roles
WHERE rolcreatedb AND rolname NOT IN ('postgres','enterprisedb',
'alloydbadmin','cloudsqladmin')
UNION ALL
SELECT rolname, 'BYPASSRLS' FROM pg_roles
WHERE rolbypassrls AND rolname NOT IN ('postgres','enterprisedb',
'alloydbadmin','cloudsqladmin');
SELECT datname, numbackends, xact_commit,
round(xact_commit / coalesce(
extract(epoch FROM (now() - stats_reset)),
extract(epoch FROM (now() - pg_postmaster_start_time()))
), 2) AS tps,
xact_rollback,
blks_read, blks_hit,
round(blks_hit * 100.0 / nullif(blks_read + blks_hit, 0), 2) AS hit_ratio,
tup_inserted, tup_updated, tup_deleted,
stats_reset
FROM pg_stat_database
WHERE datname NOT LIKE 'template%';
-- PG17+
SELECT num_timed AS timed_checkpoints,
num_requested AS requested_checkpoints,
buffers_written, buffers_clean, maxwritten_clean,
write_time, sync_time, stats_reset
FROM pg_stat_checkpointer;
-- PG16 and earlier
SELECT checkpoints_timed, checkpoints_req,
buffers_checkpoint, buffers_clean, buffers_backend,
buffers_alloc, maxwritten_clean,
round(checkpoint_write_time/1000) AS write_sec,
round(checkpoint_sync_time/1000) AS sync_sec,
stats_reset
FROM pg_stat_bgwriter;
SELECT 'Table' AS object_type,
sum(heap_blks_read) AS reads,
sum(heap_blks_hit) AS hits,
trunc(100 * sum(heap_blks_hit)
/ nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_ratio
FROM pg_statio_user_tables
UNION ALL
SELECT 'Index',
sum(idx_blks_read), sum(idx_blks_hit),
trunc(100 * sum(idx_blks_hit)
/ nullif(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2)
FROM pg_statio_user_indexes;
SELECT datname,
sum(calls) AS total_calls,
round(sum(total_exec_time)) AS total_ms,
round(sum(total_exec_time)
/ (extract(epoch FROM (now() - stats_reset)) * 1000), 5) AS dbcpu_ratio,
round(sum(calls)
/ extract(epoch FROM (now() - stats_reset)), 3) AS stmt_per_sec
FROM pg_stat_statements
JOIN pg_database ON pg_stat_statements.dbid = pg_database.oid
CROSS JOIN pg_stat_statements_info
WHERE toplevel
GROUP BY datname;
SELECT pg_get_userbyid(userid) AS user, calls,
round((total_exec_time / nullif(calls::numeric, 0)) / 1000, 3) AS avg_sec,
round(max_exec_time / 1000, 3) AS max_sec,
round(total_exec_time / 1000, 3) AS total_sec,
round(rows / nullif(calls::numeric, 0), 2) AS rows_per_call,
substring(query, 1, 120) AS query_preview,
queryid
FROM pg_stat_statements
WHERE calls > 0
ORDER BY max_exec_time DESC
LIMIT 10;
SELECT schemaname, relname,
n_live_tup AS rows,
seq_scan, idx_scan, seq_tup_read, idx_tup_fetch,
n_tup_ins, n_tup_upd, n_tup_hot_upd, n_tup_del,
coalesce(idx_scan * 100 / nullif(idx_scan + seq_scan, 0), -1) AS idx_usage_pct,
coalesce(n_tup_hot_upd * 100 / nullif(n_tup_upd, 0), -1) AS hot_update_pct
FROM pg_stat_user_tables
ORDER BY (coalesce(seq_tup_read,0) + coalesce(idx_tup_fetch,0)
+ coalesce(n_tup_ins,0) + coalesce(n_tup_upd,0)
+ coalesce(n_tup_del,0)) DESC
LIMIT 20;
SELECT tab.table_schema || '.' || tab.table_name AS table_name
FROM information_schema.tables tab
LEFT JOIN pg_indexes tco ON tab.table_schema = tco.schemaname
AND tab.table_name = tco.tablename
AND (tco.indexdef LIKE 'CREATE INDEX%'
OR tco.indexdef LIKE 'CREATE UNIQUE%')
WHERE tab.table_type = 'BASE TABLE'
AND tab.table_schema NOT IN ('pg_catalog','information_schema','sys')
AND tco.indexname IS NULL
ORDER BY tab.table_schema, tab.table_name;
SELECT tab.table_schema || '.' || tab.table_name AS table_name
FROM information_schema.tables tab
LEFT JOIN information_schema.table_constraints tco
ON tab.table_schema = tco.table_schema
AND tab.table_name = tco.table_name
AND tco.constraint_type = 'PRIMARY KEY'
WHERE tab.table_type = 'BASE TABLE'
AND tab.table_schema NOT IN ('pg_catalog','information_schema','sys')
AND tco.constraint_name IS NULL
ORDER BY tab.table_schema, tab.table_name;
SELECT ns.nspname AS schema, am.amname AS index_type,
count(*) AS total,
count(*) FILTER (WHERE idx.indisprimary) AS primary_key,
count(*) FILTER (WHERE idx.indisunique) AS unique,
round(avg(idx.indnkeyatts), 2) AS avg_keys,
max(idx.indnkeyatts) AS max_keys
FROM pg_index idx
JOIN pg_class cls ON cls.oid = idx.indexrelid
JOIN pg_class tbl ON tbl.oid = idx.indrelid
JOIN pg_am am ON am.oid = cls.relam
JOIN pg_namespace ns ON cls.relnamespace = ns.oid
WHERE ns.nspname NOT IN ('pg_catalog','sys','pg_toast')
AND ns.nspname NOT LIKE 'pg_toast_temp%'
GROUP BY ns.nspname, am.amname
ORDER BY ns.nspname, am.amname;
SELECT n.nspname AS schema,
c1.relname AS invalid_index,
c2.relname AS on_table
FROM pg_class c1
JOIN pg_index i ON i.indexrelid = c1.oid
JOIN pg_class c2 ON c2.oid = i.indrelid
JOIN pg_namespace n ON c1.relnamespace = n.oid
WHERE i.indisvalid = false;
SELECT s.schemaname, s.relname, s.indexrelname,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0
AND 0 <> ALL (i.indkey)
AND NOT i.indisunique
AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 64;
SELECT ni.nspname AS schema, ct.relname AS table_name,
ci.relname AS dup_index,
pg_get_indexdef(i.indexrelid) AS dup_def,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS dup_size,
cii.relname AS other_index,
pg_get_indexdef(ii.indexrelid) AS other_def,
pg_size_pretty(pg_relation_size(ii.indexrelid)) AS other_size
FROM pg_index i
JOIN pg_class ct ON i.indrelid = ct.oid
JOIN pg_class ci ON i.indexrelid = ci.oid
JOIN pg_namespace ni ON ci.relnamespace = ni.oid
JOIN pg_index ii ON ii.indrelid = i.indrelid
AND ii.indexrelid != i.indexrelid
AND array_to_string(ii.indkey, ' ') = array_to_string(i.indkey, ' ')
AND array_to_string(ii.indcollation, ' ') = array_to_string(i.indcollation, ' ')
AND array_to_string(ii.indclass, ' ') = array_to_string(i.indclass, ' ')
AND array_to_string(ii.indoption, ' ') = array_to_string(i.indoption, ' ')
AND NOT (ii.indkey::integer[] @> ARRAY[0])
AND NOT (i.indkey::integer[] @> ARRAY[0])
AND i.indpred IS NULL AND ii.indpred IS NULL
AND CASE WHEN i.indisunique THEN ii.indisunique
AND array_to_string(ii.indkey, ' ') = array_to_string(i.indkey, ' ')
ELSE true END
JOIN pg_class cii ON ii.indexrelid = cii.oid
WHERE ci.relname > cii.relname
AND NOT i.indisprimary
ORDER BY ni.nspname, ct.relname, ci.relname;
WITH fk_indexes AS (
SELECT n.nspname AS schema, c.relname AS table_name,
conname AS fk_name, confrelid::regclass AS parent_table,
array_agg(a.attname ORDER BY a.attnum) AS fk_cols,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size
FROM pg_constraint con
JOIN pg_class c ON con.conrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_attribute a ON a.attrelid = c.oid
AND a.attnum = ANY(con.conkey)
WHERE contype = 'f'
AND n.nspname NOT IN ('pg_catalog','information_schema','sys')
GROUP BY n.nspname, c.relname, conname, confrelid, c.oid
)
SELECT fi.schema, fi.table_name, fi.fk_name, fi.parent_table,
fi.fk_cols, fi.table_size
FROM fk_indexes fi
LEFT JOIN pg_index idx ON idx.indrelid = fi.table_name::regclass
WHERE NOT EXISTS (
SELECT 1 FROM pg_index idx2
JOIN pg_class ic ON ic.oid = idx2.indexrelid
WHERE idx2.indrelid = fi.table_name::regclass
AND (array_to_string(idx2.indkey, ' ') || ' ')
LIKE (SELECT string_agg(a.attnum::text, ' ') || '%'
FROM pg_attribute a
WHERE a.attrelid = fi.table_name::regclass
AND a.attname = ANY(fi.fk_cols))
)
ORDER BY pg_relation_size(fi.table_name::regclass) DESC;
SELECT t.relname AS partitioned_table,
n.nspname AS schema, rolname AS owner,
count(DISTINCT p.relname) AS partition_count,
to_char(sum(GREATEST(p.reltuples,0)), '999G999G999G999G999') AS est_rows,
min(p.relname) AS from_partition,
max(p.relname) AS to_partition
FROM pg_class t
JOIN pg_inherits i ON i.inhparent = t.oid
JOIN pg_class p ON p.oid = i.inhrelid
JOIN pg_roles r ON t.relowner = r.oid
JOIN pg_namespace n ON t.relnamespace = n.oid
WHERE p.relkind IN ('r', 'p')
GROUP BY nspname, rolname, t.relname
ORDER BY rolname, nspname, t.relname;
SELECT r.rolname AS owner, n.nspname AS schema,
t.relname AS partitioned_table,
count(DISTINCT p.oid) AS total_partitions,
count(*) FILTER (WHERE p.reltuples < 1000) AS low_row_partitions,
count(*) FILTER (WHERE p.relpages < 2) AS small_partitions,
count(*) FILTER (WHERE p.reltuples = 0) AS empty_partitions
FROM pg_class t
JOIN pg_inherits i ON i.inhparent = t.oid
JOIN pg_class p ON p.oid = i.inhrelid
JOIN pg_roles r ON t.relowner = r.oid
JOIN pg_namespace n ON t.relnamespace = n.oid
WHERE p.relkind IN ('r','p')
GROUP BY r.rolname, n.nspname, t.relname
ORDER BY total_partitions DESC
LIMIT 20;
SELECT archived_count, last_archived_wal, last_archived_time,
failed_count, last_failed_wal, last_failed_time,
stats_reset,
current_setting('archive_mode')::bool
AND (last_failed_wal IS NULL
OR last_failed_wal <= last_archived_wal) AS archiving_ok
FROM pg_stat_archiver;
SELECT client_addr, state, sync_state,
sent_lsn, write_lsn, flush_lsn, replay_lsn,
backend_start, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
SELECT slot_name, slot_type, active,
xmin, age(xmin) AS xmin_age,
catalog_xmin, restart_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
AS lag_size
FROM pg_replication_slots;
SELECT pid, status, conninfo,
latest_end_lsn, latest_end_time
FROM pg_stat_wal_receiver;
-- Publications summary
SELECT pubname, rolname AS owner,
puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication p
JOIN pg_roles a ON a.oid = p.pubowner
ORDER BY pubname;
-- Subscriptions
SELECT subname, datname, rolname AS owner,
subenabled AS enabled, subsynccommit,
subslotname AS slot_name
FROM pg_subscription s
JOIN pg_database d ON d.oid = s.subdbid
JOIN pg_roles a ON a.oid = s.subowner
ORDER BY subname;
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY installed_version IS NULL, name;
SELECT name,
CASE WHEN unit='kB' THEN pg_size_pretty(setting::bigint*1024)
WHEN unit='8kB' THEN pg_size_pretty(setting::bigint*8192)
WHEN unit='B' THEN pg_size_pretty(setting::bigint)
WHEN unit='MB' THEN pg_size_pretty(setting::bigint*1048576)
ELSE coalesce(setting || ' ' || unit, setting)
END AS value,
short_desc, source, setting AS raw_setting
FROM pg_settings
WHERE source NOT IN ('default', 'override', 'client')
ORDER BY name;
SELECT 'postgresql.conf' AS file, *
FROM pg_stat_file(current_setting('config_file'))
UNION ALL
SELECT 'postgresql.auto.conf', *
FROM pg_stat_file(current_setting('data_directory') || '/postgresql.auto.conf');
-- WAL files
SELECT count(*) AS wal_files,
pg_size_pretty(sum(size)) AS total_size
FROM pg_ls_waldir();
-- LOG files
SELECT count(*) AS log_files,
pg_size_pretty(sum(size)) AS total_size
FROM pg_ls_logdir();
SELECT w.oid, w.fdwname, a.rolname AS owner,
ph.proname AS handler, pv.proname AS validator,
fdwoptions AS options
FROM pg_foreign_data_wrapper w
JOIN pg_authid a ON w.fdwowner = a.oid
JOIN pg_proc ph ON w.fdwhandler = ph.oid
JOIN pg_proc pv ON w.fdwvalidator = pv.oid;
PostgreSQL can be extended with CREATE EXTENSION. The queries below require specific extensions to be installed.
CREATE EXTENSION pg_buffercache.SELECT c.relname,
CASE c.relkind WHEN 'r' THEN 'Table' WHEN 'i' THEN 'Index'
WHEN 't' THEN 'TOAST' WHEN 'm' THEN 'Mat. View'
ELSE c.relkind::text
END AS type,
count(*) AS buffers,
pg_size_pretty(count(*) * 8192) AS cache_size,
round(100.0 * count(*) / (SELECT setting::int
FROM pg_settings
WHERE name = 'shared_buffers'), 1) AS cache_pct,
pg_size_pretty(pg_relation_size(c.oid)) AS object_size,
round(avg(usagecount), 2) AS avg_usage
FROM pg_class c
JOIN pg_buffercache b ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_database d ON b.reldatabase = d.oid
AND d.datname = current_database()
WHERE pg_relation_size(c.oid) > 0
GROUP BY c.oid, c.relname, c.relkind
ORDER BY buffers DESC
LIMIT 20;
CREATE EXTENSION pgstattuple. Can be expensive on large tables.-- Quick approximate bloat for biggest tables
SELECT n.nspname || '.' || relname AS table_name,
relpages,
(pgstattuple_approx(pg_class.oid::regclass)).*
FROM pg_class
JOIN pg_roles r ON relowner = r.oid
JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
WHERE relkind = 'r'
AND r.rolname NOT IN ('enterprisedb','alloydbadmin','cloudsqladmin')
ORDER BY relpages DESC
LIMIT 10;
default_statistics_target to be set appropriately. PG12+ for extended statistics.-- Standard column statistics
SELECT schemaname || '.' || tablename AS table_name,
attname AS column, avg_width,
array_length(most_common_vals, 1) AS mcv_count,
null_frac
FROM pg_stats
WHERE schemaname NOT IN ('pg_catalog','information_schema')
AND tablename = 'your_table_name'
ORDER BY tablename, attname;
-- Extended statistics (PG12+)
SELECT stxname AS statistics_name,
stxrelid::regclass AS table_name,
pg_get_userbyid(stxowner) AS owner,
CASE WHEN 'd' = ANY(stxkind) THEN 'Ndistinct' END AS ndistinct,
CASE WHEN 'f' = ANY(stxkind) THEN 'Dependency' END AS dependency,
CASE WHEN 'e' = ANY(stxkind) THEN 'Expression' END AS expression,
CASE WHEN 'm' = ANY(stxkind) THEN 'MCV' END AS mcv
FROM pg_catalog.pg_statistic_ext
ORDER BY 1, 2;
CREATE EXTENSION sslinfo.SELECT ssl_is_used() AS ssl_used,
ssl_version() AS ssl_version,
ssl_cipher() AS ssl_cipher,
ssl_client_cert_present() AS client_cert_present;
CREATE EXTENSION amcheck. The query below checks indexes in pg_catalog; adjust the WHERE clause to target specific schemas.SELECT bt_index_check(index => c.oid, heapallindexed => i.indisunique),
n.nspname, c.relname, c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
AND c.relpersistence != 't'
AND i.indisready AND i.indisvalid
AND n.nspname = 'pg_catalog' -- Change to target schema
ORDER BY c.relpages DESC
LIMIT 10;
CREATE EXTENSION vector.-- Vector-type columns
SELECT o.rolname AS owner, n.nspname AS schema,
r.relname AS table_name, a.attname AS column_name,
t.typname AS vector_type, a.atttypmod
FROM pg_attribute a
JOIN pg_class r ON a.attrelid = r.oid
JOIN pg_roles o ON r.relowner = o.oid
JOIN pg_type t ON a.atttypid = t.oid
JOIN pg_catalog.pg_namespace n ON n.oid = r.relnamespace
WHERE r.relkind IN ('r','p')
AND NOT r.relispartition
AND a.attnum > 0 AND NOT a.attisdropped
AND n.nspname NOT IN ('information_schema','pg_catalog')
AND t.typname IN ('vector','halfvec','sparsevec','bit')
ORDER BY o.rolname, n.nspname, r.relname;
-- Vector (HNSW / IVFFlat) indexes
SELECT ns.nspname AS schema, tbl.relname AS table_name,
cls.relname AS index_name, am.amname AS index_type
FROM pg_index idx
JOIN pg_class cls ON cls.oid = idx.indexrelid
JOIN pg_class tbl ON tbl.oid = idx.indrelid
JOIN pg_am am ON am.oid = cls.relam
JOIN pg_namespace ns ON cls.relnamespace = ns.oid
WHERE am.amname IN ('hnsw', 'ivfflat')
AND ns.nspname NOT IN ('pg_catalog','sys')
ORDER BY ns.nspname, am.amname;
-- pg_trgm extension
SELECT name, installed_version
FROM pg_available_extensions
WHERE name = 'pg_trgm' AND installed_version IS NOT NULL;
-- GIN/GiST indexes (full-text, trigram)
SELECT pg_get_indexdef(indexrelid) AS index_definition
FROM pg_index
WHERE pg_get_indexdef(indexrelid) ~* 'USING (gin|gist) ';
-- tsvector columns
SELECT table_schema || '.' || table_name AS table_name,
column_name, data_type
FROM information_schema.columns
WHERE data_type = 'tsvector';
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC, wait_event_type, wait_event;
WITH global_min AS (
SELECT min(xmin_val) AS min_xmin FROM (
SELECT backend_xmin::text::bigint AS xmin_val
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT transaction::text::bigint FROM pg_prepared_xacts
) s
)
SELECT 'session' AS type, pid, state,
backend_xmin AS oldest_xmin,
age(backend_xmin) AS xmin_age,
now() - xact_start AS duration,
left(query, 40) AS identification
FROM pg_stat_activity, global_min
WHERE backend_xmin::text::bigint = global_min.min_xmin
AND pid <> pg_backend_pid()
UNION ALL
SELECT 'prepared tx', NULL, 'N/A',
transaction, age(transaction),
now() - prepared, 'GID: ' || gid
FROM pg_prepared_xacts, global_min
WHERE transaction::text::bigint = global_min.min_xmin;
SELECT gid, prepared, owner, database,
transaction AS xmin, now() - prepared AS age
FROM pg_prepared_xacts
ORDER BY age DESC;
SELECT queryid, calls, rows,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS avg_ms,
left(regexp_replace(query, E'[\\n\\r]+', ' ', 'g'), 60) AS query_preview
FROM pg_stat_statements
WHERE query ILIKE '%SAVEPOINT%'
OR query ILIKE '%EXCEPTION%'
OR query ILIKE '%ROLLBACK TO%'
ORDER BY calls DESC
LIMIT 10;
SELECT backend_type, object, context,
reads, writes, extends,
evictions, reuses, fsyncs
FROM pg_stat_io;
Generated from pg2html.sql v1.0.34 — github.com/meob/db2html