Hi,
I need help on deciding my vacuuming strategy. I need to know if I ever need to do 'vacuum full' for my tables.
Tables1: Following is the query patterns on 4 high traffic table in my database:
1. Every 5 minutes about 50000 rows in the table are updated. And for a given clock hour the same 50000 rows are updated again and again.
2. When a new hour begins, another 50000 rows get inserted and they get updated every 5 minutes.
3. Every night a days worth of old stats are deleted. So this would be 24 * 50000 = 1.2M records that get deleted every night.
Tables2: Another 39 tables have the following pattern:
1. Every 5 minutes 2000 rows are updated. For a given clock hour he same rows are updated again and again.
2. When a new hour begins another 2000 rows get inserted and they get updated every 5 minutes.
2. Every night 48000 rows get deleted.
Tables3: Another 4 tables have the following pattern:
1. Every 1 hour 50000 rows get updated. For a given day the same 50000 rows are updated again and again.
2. When a new day begins, another 50000 rows get inserted and they get updated every hour.
3. Every night 1.2M records get deleted.
Tables4: Another 39 tables have the following pattern:
1. Every 1 hour 2000 rows gets updated. For a given day the same 2000 rows are updated again and again.
2. When a new day begins, another 2000 rows get inserted and they get updated every hour.
3. Every night 48000 rows get deleted.
With the above query pattern with intensive updates and deletes, I need to do some aggressive vacuuming.
Current strategy:I am running with default autovacuum settings (postgres 9.1.9) and I tried doing a 'vacuum full' for the 8 high traffic tables (Tables1 and Tables3) every night. But after a point, the 'vacuum full's started timing out (with 4min timeout) every night. I think this is because the table is growing bigger (~5GB) and doing a vacuum full every night is probably not feasible.
Going with the default autovacuum settings and not doing 'vacuum full' at all is also not enough for my usecase. Whenever vacuum full succeeded every night, it did seem to reclaim a considerable amount of space. So I assume, autovacuum is not able to reclaim all space.
What approach should I take? Do I require 'vacuum full'? What autovaccum settings should I tweak so that I can avoid vacuum full, if possible, and maintain a steady state without bloating the tables?
Thanks,
Elan.
