useful postgresql queries - when your postgres went crazy

bloated_indices

SELECT
    c.relname AS index_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_bloat_size,
    ROUND((pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) / pg_relation_size(c.oid)::numeric * 100, 2) AS bloat_percentage
FROM
    pg_class c
JOIN
    pg_namespace n ON c.relnamespace = n.oid
WHERE
    c.relkind = 'i'  -- Only indexes
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    bloat_percentage DESC;

corrupted_indices

SELECT
    indexrelid::regclass AS index_name,
    indrelid::regclass AS table_name,
    indisvalid
FROM
    pg_index
WHERE
    NOT indisvalid;

fix bloated index

REINDEX INDEX index_name;

fix bloated table

VACUUM FULL my_table_name;

tables_size

SELECT
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_table_size
FROM
    pg_stat_user_tables
ORDER BY
    n_dead_tup DESC;
tradetracker=> SELECT
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_table_size
FROM
    pg_stat_user_tables
ORDER BY
    n_dead_tup DESC;