Skip site navigation (1) Skip section navigation (2)

Re: autovacuum: recommended?

From: tv(at)fuzzy(dot)cz
To: "Decibel!" <decibel(at)decibel(dot)org>
Cc: tv(at)fuzzy(dot)cz, "Tobias Brox" <tobias(at)nordicbet(dot)com>, Gábor Farkas <gabor(at)nekomancer(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: autovacuum: recommended?
Date: 2007-11-19 09:53:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
> FWIW, 20k rows isn't all that big, so I'm assuming that the
> descriptions make the table very wide. Unless those descriptions are
> what's being updated frequently, I suggest you put those in a
> separate table (vertical partitioning). That will make the main table
> much easier to vacuum, as well as reducing the impact of the high
> churn rate.

Yes, you're right - the table is quite wide, as it's a catalogue of a
pharmacy along with all the detailed descriptions and additional info etc.
So I guess it's 50 MB of data or something like that. That may not seem
bad, but as I already said the table grew to about 12x the size during the
day (so about 500MB of data, 450MB being dead rows). This is the 'central'
table of the system, and there are other quite heavily used databases as
well. Add some really stupid queries on this table (for example LIKE
searches on the table) and you easily end up with 100MB of permanent I/O
during the day.

The vertical partitioning would be overengineering in this case - we
considered even that, but proper optimization of the update process
(updating only those rows that really changed), along with a little bit of
autovacuum tuning solved all the performance issues.


In response to

pgsql-performance by date

Next:From: Jean-David BeyerDate: 2007-11-19 13:39:08
Subject: Re: autovacuum: recommended?
Previous:From: Decibel!Date: 2007-11-19 04:14:09
Subject: Re: autovacuum: recommended?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group