Postgresql

A Postgres Receipts

A Postgres Receipts

Dead tuples.sql

SELECT psut.relname,
     to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
     to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
     to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
     to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
     to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
         + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
            * pg_class.reltuples), '9G999G999G999') AS av_threshold,
     CASE
         WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
             + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
                * pg_class.reltuples) < psut.n_dead_tup
         THEN '*'
         ELSE ''
     END AS expect_av
 FROM pg_stat_user_tables psut
     JOIN pg_class on psut.relid = pg_class.oid
 ORDER BY 1 ;

hot_tables.sql

#A similar query worth monitoring is how often "HOT" is being used to update rows, instead of a less efficient regular update:
SELECT relname,n_tup_upd,n_tup_hot_upd,cast(n_tup_hot_upd AS numeric) / n_tup_upd AS hot_pct 
FROM pg_stat_user_tables 
WHERE n_tup_upd>0 ORDER BY hot_pct;

locks.sql

SELECT l.locktype,
       l.relation::regclass,
       l.mode,
       l.transactionid AS tid,
       l.virtualtransaction AS vtid,
       l.pid,
       l.granted,
       a.query
FROM pg_catalog.pg_locks l
INNER JOIN pg_stat_activity a ON (a.pid = l.pid)
WHERE l.pid <> pg_backend_pid();

not_used_indexes.sql

SELECT S.relname,
       S.indexrelname,
       round(100 * pg_relation_size(I.indexrelid) / pg_relation_size(I.indrelid)) / 100 AS index_ratio,
       pg_size_pretty(pg_relation_size(I.indexrelid)) AS index_size,
       pg_size_pretty(pg_relation_size(I.indrelid)) AS table_size
FROM pg_index I
LEFT JOIN pg_class C ON (C.oid = I.indexrelid)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT JOIN pg_stat_all_indexes S ON (S.indexrelid = I.indexrelid)
WHERE nspname NOT IN ('pg_catalog',
                      'information_schema',
                      'pg_toast')
    AND C.relkind='i'
    AND pg_relation_size(indrelid) > 0
    AND S.schemaname='public'
    AND NOT S.relname LIKE 'django_%'
    AND NOT S.relname LIKE 'celery_%'
    AND NOT S.relname LIKE 'auth%'
    AND NOT S.relname LIKE 'djcelery%'
    AND S.idx_scan = 0
ORDER BY index_size;

Размер таблиц.sql

 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;

Размер пространств имен.sql

SELECT nspname, pg_size_pretty(CAST(SUM(pg_total_relation_size (C.oid)) as BIGINT)) AS "total_size" 
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace )
WHERE nspname NOT IN ('pg_catalog','information_schema')
AND C.relkind <>'i'
AND nspname !~'^pg_toast'
GROUP BY nspname;

Размер таблиц с индексами и toasted data.sql

SELECT nspname ||'.'|| relname AS "relation", 
pg_size_pretty ( pg_total_relation_size (C.oid) ) AS "total_size" 
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace )
WHERE nspname NOT IN ('pg_catalog','information_schema')
AND C.relkind <>'i'
AND nspname !~'^pg_toast'
ORDER BY pg_total_relation_size (C.oid ) DESC
LIMIT 20 ;