From: | mailinglists(at)net-virtual(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Question about updates and MVCC |
Date: | 2009-01-10 16:00:29 |
Message-ID: | 59917.69.109.177.118.1231603229.squirrel@69.109.177.118 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2009-01-10 19:15:16 | Rename a constraint |
Previous Message | Andrew | 2009-01-10 10:14:48 | Re: Adding Arabic dictionary for TSearch2.. to_tsvector('arabic'...) doesn't work.. |