Thread: dead tuples
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"
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.
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"
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.
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
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"