Read/write activity on tables – PostgreSQL

Posted on November 3, 2007
Filed Under PostgreSQL | Leave a Comment

If you ever wanted to know which tables are actively being hit on your database, please pay attention to: http://www.postgresql.org/docs/8.1/static/monitoring-stats.html

postgres@server:~$ psql testdb  -c “select relname, idx_tup_fetch as seeks, n_tup_ins + n_tup_upd + n_tup_del as writes from pg_stat_user_tables order by writes desc limit 5;”
relname        |  seeks   | writes
——————+———+——–
user                    |  364963 | 355314
employee          |     232     | 282747
class                   |  587978 | 190938
task                    |      79      | 125255
project               |                | 117282
(5 rows)

Based on these kind of output you might want to reconsider some… Maybe reconsider how your database is setup? Put most active tables as a separate database? Move them to a separate discs? etc…

Comments

Leave a Reply