Thread: dead tuples

dead tuples

From
"Oren Mazor"
Date:
Hi all, I'm doing some database optimization work right now, and I'm
wondering, is there any way to remove the dead tuple functionality? Right
now I have a vacuum run regularly, but if there was some way I could just
get rid of the whole problem, that would be great.

I'm still an amateur when it comes to pg, so I might be missing something
really obvious that I didnt notice in the docs. If I am, I apologize for
the list spam :)

cheers
Oren

--
Oren Mazor // Developer, Sysadmin, Explorer
GPG Key: http://www.grepthemonkey.org/secure
"Ut sementem feceris, ita metes"

Re: dead tuples

From
Bruno Wolff III
Date:
On Fri, Jul 22, 2005 at 10:02:55 -0400,
  Oren Mazor <oren.mazor@gmail.com> wrote:
> Hi all, I'm doing some database optimization work right now, and I'm
> wondering, is there any way to remove the dead tuple functionality? Right
> now I have a vacuum run regularly, but if there was some way I could just
> get rid of the whole problem, that would be great.

What problem are you trying to solve?

The way Postgres implements MVCC leaves deleted tuples for later clean up
after they aren't visible to any currently open transaction. This clean up
is done with vacuum.

Re: dead tuples

From
"Oren Mazor"
Date:
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

On Fri, 22 Jul 2005 13:29:19 -0400, Bruno Wolff III <bruno@wolff.to> wrote:

> On Fri, Jul 22, 2005 at 10:02:55 -0400,
>   Oren Mazor <oren.mazor@gmail.com> wrote:
<snip>
>
> What problem are you trying to solve?
>
> The way Postgres implements MVCC leaves deleted tuples for later clean up
> after they aren't visible to any currently open transaction. This clean
> up
> is done with vacuum.



--
Oren Mazor // Developer, Sysadmin, Explorer
GPG Key: http://www.grepthemonkey.org/secure
"Ut sementem feceris, ita metes"

Re: dead tuples

From
Bruno Wolff III
Date:
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.

Re: dead tuples

From
tgoodaire@linux.ca (Tim Goodaire)
Date:
On Fri, Jul 22, 2005 at 01:31:50PM -0400, Oren Mazor 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. There isn't a way to circumvent this. Just set up a cron job to
regularly vacuum your database and you won't have to worry about your
database getting big due to dead tuples.

Tim

>
> On Fri, 22 Jul 2005 13:29:19 -0400, Bruno Wolff III <bruno@wolff.to> wrote:
>
> >On Fri, Jul 22, 2005 at 10:02:55 -0400,
> >  Oren Mazor <oren.mazor@gmail.com> wrote:
> <snip>
> >
> >What problem are you trying to solve?
> >
> >The way Postgres implements MVCC leaves deleted tuples for later clean up
> >after they aren't visible to any currently open transaction. This clean
> >up
> >is done with vacuum.
>
>
>
> --
> Oren Mazor // Developer, Sysadmin, Explorer
> GPG Key: http://www.grepthemonkey.org/secure
> "Ut sementem feceris, ita metes"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

--

Attachment

Re: dead tuples

From
"Oren Mazor"
Date:
Thats exactly what I did. the catch is that I have a perpetual connection
to the database. that's a problem.

oh well. I've fixed it already, i was just wondering if there was any way
to circumvent...

in answer to the other question, switching to another dbase is not an
option at this point in the dev process (more than a handful of years
investment).

thanks for the prompt answers guys :)

oren

On Fri, 22 Jul 2005 13:43:48 -0400, Tim Goodaire <tgoodaire@linux.ca>
wrote:

> On Fri, Jul 22, 2005 at 01:31:50PM -0400, Oren Mazor wrote:
<snip>
>
> No. There isn't a way to circumvent this. Just set up a cron job to
> regularly vacuum your database and you won't have to worry about your
> database getting big due to dead tuples.
>
> Tim
>
<snip>
>



--
Oren Mazor // Developer, Sysadmin, Explorer
GPG Key: http://www.grepthemonkey.org/secure
"Ut sementem feceris, ita metes"