> read the entry on pg_stat_all_tables
yeah, it's running ... vacuum'ed last night
it's odd, to me, that the performance would degrade so extremely
(noticeably) over the course of one year on a table which has few
insertions, no deletions,and daily updates of an integer non null
column (stock level).
is there some way to view the level of "bloat that needs full" in each
table, so i could write a script that alerts me to the need of a
"vacuum full" without waiting for random queries to "get slow"?
looking at the results of the "bloat query", i still can't see how to
know whether bloat is getting bad in an objective manner.
http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html
on the machines that perform well 30MB of bloat seems to be fine, and
i don't knwo what the badly performing table's bloat was, since i
already vac'ed it.
.......
there is one table i have with 2GB of bloat ... but it's performance
(since all querys are on a clustered index) is more than adequate.
also, it's so big i'm afraid my server would be down for 24 hours on
that on vacuum
it's a rolling "cookie table" with millions of random-id'ed entries
that expire after a few months ... i think i'm going to copy the most
recent 6 months worth of rows to a new table, then just drop the old
one..... seems easier to me.than the scary unknown of running "vaccum
full", and then i won't have to worry about the system being down on a
table lock.
Seems like "VACUUM FULL" could figure out to do that too depending on
the bloat-to-table-size ratio ...
- copy all rows to new table
- lock for a millisecond while renaming tables
- drop old table.
Locking a whole table for a very long time is scary for admins.
- erik