How to know table fragmentation in postgresql database


psql -e -f /usr/share/postgresql/8.3/contrib/pgstattuple.sql $MYDB

psql $MYDB -c "CREATE TEMP TABLE fragmentation_stats(table_name text, tuple_count integer, tuple_len integer, tuple_percent float, dead_tuple_count integer, dead_tuple_len integer, dead_tuple_percent float, free_space integer, free_percent float);"

for i in `psql $MYDB -qt -c "select tablename from pg_tables where schemaname = '$MYSCHEMA' order by tablename;"`;do
echo $i;
psql $MYDB -c "insert into fragmentation_stats (select $i, pg_size_pretty(table_len), tuple_count, tuple_len, tuple_percent, dead_tuple_count, dead_tuple_len, dead_tuple_percent, pg_size_pretty(free_space), free_percent from pgstattuple('$i'));"
done

psql $MYDB -c "DROP TABLE fragmentation_stats;"


Advertisement