I am running a development database copied from production database.
In the last month the size of one of the tables has increased dramatically (500 MB to 10 GB) 4 times. I have not worked on that table in a week and it continues to grow. Before I was running some scripts which were doing 'selects' on that table, but in the last couple of weeks it has still grown. also one of the indexes is growing.
if I do a vacuum on the table, it's size will go down. The index size doesn't diminish and the only way I know to descrease the index size is to rebuild it.
Any ideas on what is happening, how I can monitor for activity on this table?
Whenever you execute UPDATE, DELETE statements on a table,those rows marks as dead rows and new rows will created. For reclaiming dead rows space in a table,you have to perform Routine Maintenance activity i.e VACUUM ANALYZE on PostgreSQL cluster.
Schedule manual VACUUM ANALYZE on every day once,so that dead rows space will be reused while inserting new records into a table.
You can monitor Dead & live rows count using below command:
select relname,n_live_tup,n_dead_tup from pg_stat_user_tables;