Re: Question about updates and MVCC

On Sat, Jan 10, 2009 at 08:00:29AM -0800, mailinglists(at)net-virtual(dot)com wrote:
> 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?

Yes. There are various ways to work wih this, depending on your
capabilties.

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

How wide is the row otherwise. Each status update will make a copy of
the complete row. One thought is to think about how often you need the
status anyway and decide if it's worthwhile to split the status off
into a seperate table, which will be much smaller and vacuum quicker.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.