On Tue, Jul 7, 2009 at 10:42 AM, Jennifer
Spencer<jenniferm411@hotmail.com> wrote:
> We are using v. 8.3.1 at present. We anticipate a terabyte of data each
> year starting in November, and I am concerned about what happens
> maintenance-wise a couple of years down the line. I think that we won't be
> able to do vacuuming/reindexing with the machine online and serving users if
> the database is over a certain size. Am I wrong?
I wanted to comment a bit more on this. At my last job I had a head
dev guy who's experience with pgsql was back in the 7.0 days or so,
and his standard phrase was "vacuum in postgresql isn't fast enough."
The problem is that vacuum in postgres, to him, was full and locking
vacuum and it was being run on a machine with a mirrored set of PATA
hard drives that could read / write in the low megs / second range.
Enter 2009. Individual hard drives can read / write in the 100MB/s
range, and a good RAID array controller can aggregate many drives and
get 400 to 800 MB/s easily. Now the issue is controlling vacuum so
that it doesn't eat up all of your IO bandwidth, which is why you have
autovacuum_vacuum_cost_delay, and other settings, that let you tone
down vacuum so it's not in the way.
So the real issue for you will be do you have enough IO bandwidth to
handle all your users AND autovacuum at the same time. Vacuuming can
run just fine with users online, as long as you've set your autovac
cost parameters to not make it get in the way, and as long as you have
enough IO bandwidth to handle your load. Routine reindexing and
vacuum full and such are not generally recommended.
So, what class machines are these, and specifically how much ram, what
kind of RAID controllers, and how many hard drives are you throwing at
the problem?