df -h
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 5;
SELECT oid, pg_size_pretty(pg_total_relation_size(loid)) as size
FROM pg_largeobject_metadata
ORDER BY size DESC
LIMIT 5;
SELECT schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND NOT EXISTS (
SELECT 1 FROM pg_constraint WHERE conindid = ui.indexrelid
)
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 5;
VACUUM (VERBOSE, ANALYZE);
DELETE FROM your_table_name WHERE your_date_column < 'YYYY-MM-DD';
DROP TABLE your_table_name;
Execute these steps cautiously, especially when dropping data or tables, to avoid losing important information. Ensure you have appropriate backups before making permanent changes.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
