Re: dead tuples - Mailing list pgsql-novice

From Bruno Wolff III
Subject Re: dead tuples
Date
Msg-id 20050722174012.GC29782@wolff.to
Whole thread Raw
In response to Re: dead tuples  ("Oren Mazor" <oren.mazor@gmail.com>)
List pgsql-novice
On Fri, Jul 22, 2005 at 13:31:50 -0400,
  Oren Mazor <oren.mazor@gmail.com> wrote:
> what happens is that my database files grow significantly. say i have a
> table filled with people names, and i modify each one, then my database
> seems to double. this is because (afaik) pg marks the old ones as 'dead'
> but doesnt delete them. you run vacuum to reclaim it.
>
> which is what i do. but i'm wondering if there's any way to circumvent the
> entire process of marking them as 'dead' and just deleting things right
> off when they get updated

No because concurrent transactions still can see the old versions of the
tuples. The deletes need to be delayed until all tranasctions that started
before the updates were committed have committed or rolled back.

If you don't need MVCC for your application then you might consider using
other database systems such as perhaps SQL Lite that don't do that. The
downside is that you will need stronger locks when updating tuples which
may or may not be a problem for you.

There may be some tricks you can do to trade off disk space for performance
but generally you are better off just buying more disk space.

pgsql-novice by date:

Previous
From: "Oren Mazor"
Date:
Subject: Re: dead tuples
Next
From: tgoodaire@linux.ca (Tim Goodaire)
Date:
Subject: Re: dead tuples