Eric Guo's blog.cloud-mes.com

Hoping writing JS, Ruby & Rails and Go article, but fallback to DevOps note

My Maintain PostgreSQL Commands

Permalink

SELECT schemaname AS table_schema,
relname AS table_name,
PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid)) AS total_size,
PG_SIZE_PRETTY(PG_RELATION_SIZE(relid)) AS data_size,
PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid) - PG_RELATION_SIZE(relid))
AS external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY PG_TOTAL_RELATION_SIZE(relid) DESC,
PG_RELATION_SIZE(relid) DESC
LIMIT 100;

List all table size. origin

for db in $(psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"); do
echo "Maintaining database: $db"
psql -d "$db" -c "VACUUM ANALYZE;"
done

table optimization and analyzing for all databases.

Comments