Question about updates and MVCC

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

Responses

Browse pgsql-general by date

  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..