HouseKeeping and vacuum Questions

From: Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: HouseKeeping and vacuum Questions
Date: 2007-12-14 03:13:40
Message-ID: 1197602020.2590.11.camel@neuromancer.home.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm starting to perform some basic housekeeping to try to trim some big
tables (~200 million rows - ~50GB+indexes) into separate partitions (via
inheritance).

The current issue which prompted me to do such housekeeping is due to
long database wide vacuum time. (it went from 2 hours to 4 hours to 7
hours)

My current strategy is to keep only 1 month of data in the base table
and make a job to automatically insert/delete 1 days worth of data into
the child partition tables.

Currently, I've moved 3 such large tables into separate partitions and
my vacuum time is still slow. (I suspect it's because I bulk
insert/deleted the tuples from the main_tables in 1 go and then the
vacuum had to deal with vacuum off the MVCC for the past few months's
data)

I'm already batching my housekeep into 6 hours timeframes (eg: insert
into foo_child select * from fooo from hour1 to hour6)

So, my question is now

1. Should I perform a vacuum after each 6 hour batch? or
2. perform a vacuum after 1 day batch? (4x 6hour sessions)

and what should I do with the few tables which I've not started to
partition? There's 4 months worth of data which is still sitting in the
respective main tables.

Appreciate pointers etc.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ken Johanson 2007-12-14 04:14:51 Re: RETURNING clause: how to specifiy column indexes?
Previous Message Kris Jurka 2007-12-14 02:08:38 Re: RETURNING clause: how to specifiy column indexes?