Last active
February 29, 2024 06:54
-
-
Save paulmallon/0e483af85b84c83d7566bdae566941dd to your computer and use it in GitHub Desktop.
Some PostgreSQL maintenance queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--- Get the names of databases and the age of their oldest unfrozen transaction ID (datfrozenxid) | |
SELECT datname, age(datfrozenxid) | |
FROM pg_database | |
ORDER BY age(datfrozenxid) desc | |
limit 20; | |
--- Get list of regular tables, including their size, age of the oldest unfrozen transaction ID (xid_age), | |
-- number of dead tuples, and timestamps for the last manual and automatic VACUUM and ANALYZE operations. | |
SELECT ns.nspname AS schema_name, | |
cls.relname AS table_name, | |
cls.relkind as type, | |
-- pg_size_pretty(pg_relation_size(cls.oid)) AS table_size, | |
age(cls.relfrozenxid) AS xid_age, | |
stat.n_dead_tup AS dead_tuples, | |
stat.last_vacuum, | |
stat.last_autovacuum, | |
stat.last_analyze, | |
stat.last_autoanalyze | |
FROM pg_stat_user_tables stat | |
JOIN pg_class cls ON stat.relid = cls.oid | |
JOIN pg_namespace ns ON cls.relnamespace = ns.oid | |
where cls.relkind = 'r' | |
--order by pg_relation_size(cls.oid) desc; | |
--- Retrieves detailed information about active VACUUM processes, | |
SELECT act.pid as pid, | |
usename as user, | |
state as state, | |
current_timestamp - xact_start AS xact_runtime, | |
query as query, | |
phase as phase, | |
wait_event as event, | |
round(prog.heap_blks_scanned::float / nullif(prog.heap_blks_total::float, 0) * 100) || ' %' as heap_scan_prog, | |
prog.heap_blks_scanned as blocks_scanned, | |
prog.heap_blks_total as blocks_total, | |
prog.heap_blks_vacuumed as blocks_vacuumed, | |
prog.index_vacuum_count as index_vacuum_count, | |
prog.num_dead_tuples as dead_tuples_collected, | |
prog.max_dead_tuples as max_dead_tuples | |
FROM pg_stat_activity act | |
left join pg_stat_progress_vacuum prog on act.pid = prog.pid | |
WHERE upper(query) LIKE '%VACUUM%' | |
ORDER BY xact_start; | |
-- Retrieves information on tables, including schema and name, age of the oldest unfrozen transaction ID autovacuum | |
-- parameters, and statistics on dead tuples, filtered by certain autovacuum threshold conditions. | |
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold | |
FROM pg_settings | |
WHERE name = 'autovacuum_vacuum_threshold'), | |
vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor | |
FROM pg_settings | |
WHERE name = 'autovacuum_vacuum_scale_factor'), | |
fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'), | |
sto AS (select opt_oid, | |
split_part(setting, '=', 1) as param, | |
split_part(setting, '=', 2) as value | |
from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) | |
SELECT '"' || ns.nspname || '"."' || c.relname || '"' as relation, | |
pg_size_pretty(pg_table_size(c.oid)) as table_size, | |
age(relfrozenxid) as xid_age, | |
coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age, | |
(coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + | |
coalesce(cvsf.value::float, autovacuum_vacuum_scale_factor::float) * c.reltuples) | |
AS autovacuum_vacuum_tuples, | |
n_dead_tup as dead_tuples | |
FROM pg_class c | |
join pg_namespace ns on ns.oid = c.relnamespace | |
join pg_stat_all_tables stat on stat.relid = c.oid | |
join vbt on (1 = 1) | |
join vsf on (1 = 1) | |
join fma on (1 = 1) | |
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid | |
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid | |
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid | |
WHERE c.relkind = 'r' | |
and nspname <> 'pg_catalog' | |
AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) | |
OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + | |
coalesce(cvsf.value::float, autovacuum_vacuum_scale_factor::float) * | |
c.reltuples <= n_dead_tup) | |
ORDER BY age(relfrozenxid) DESC | |
LIMIT 50; | |
--- Retrieves statistics for non-unique indexes in a PostgreSQL database that are not used for enforcing constraints | |
-- and do not contain any expression-based columns. | |
SELECT s.schemaname, | |
s.relname AS tablename, | |
s.indexrelname AS indexname, | |
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size, | |
idx_scan, | |
idx_tup_fetch, | |
idx_tup_read | |
FROM pg_catalog.pg_stat_user_indexes s | |
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid | |
WHERE 1 = 1 | |
-- and s.idx_scan = 0 -- has never been scanned | |
AND 0 <> ALL (i.indkey) -- no index column is an expression | |
AND NOT i.indisunique -- is not a UNIQUE index | |
AND NOT EXISTS -- does not enforce a constraint | |
(SELECT 1 | |
FROM pg_catalog.pg_constraint c | |
WHERE c.conindid = s.indexrelid) | |
ORDER BY pg_relation_size(s.indexrelid) DESC; | |
-- Show blocked querys | |
SELECT | |
blocked_locks.pid AS blocked_pid, | |
blocked_activity.usename AS blocked_user, | |
blocking_locks.pid AS blocking_pid, | |
blocking_activity.usename AS blocking_user, | |
blocked_activity.query AS blocked_query, | |
blocking_activity.query AS blocking_query, | |
blocked_activity.application_name AS blocked_application, | |
blocking_activity.application_name AS blocking_application | |
FROM pg_catalog.pg_locks blocked_locks | |
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid | |
JOIN pg_catalog.pg_locks blocking_locks | |
ON blocking_locks.locktype = blocked_locks.locktype | |
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE | |
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation | |
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page | |
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple | |
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid | |
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid | |
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid | |
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid | |
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid | |
AND blocking_locks.pid != blocked_locks.pid | |
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid | |
WHERE NOT blocked_locks.GRANTED; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment