> 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. Tomas ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
