Hello,
I have a couple of questions regarding how MVCC (in postges 8.3.3 if it
makes a difference) affects vacuum.
#1. If I am doing an update to a row and none of the values have changed,
will that cause a "hole" that requires vacuum to reclaim?
#2. I have a column in my table (called "status", if you can believe
*that*). This contains 1 of 4 values:
-1: row is expired, but needs to be marked deleted from index
0: row is expired, and has been indexed
1: row is active, and has been indexed
2: row is new or updated, and needs to be indexed
.. The point of all this is that when a new row is added, or updated, it
goes into a status = 2, so the process that comes along later to build
search indexes, can quickly query any listings in status = 2 and
incrementally update the index. (Same with respect to status -1, except
those rows are no longer active and need to be deleted from the index)...
The issue with this is that it seems to be causing a lot of vacuum
work.... The total number of rows in the table are about 30 million, but
partitioned into about 130 segments, based on a category... I'm trying to
minimize the amount of vacuum work because not much else changes in the
table over time, but the status column will get fiddled with 4 times
during the life of a row...
Thanks, as always!
- Greg
