Skip site navigation (1) Skip section navigation (2)

Re: Reduction in WAL for UPDATEs

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kenneth Marshall <ktm(at)rice(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Reduction in WAL for UPDATEs
Date: 2007-03-28 15:14:02
Message-ID: 460A863A.2000903@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Gregory Stark wrote:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> 
>> Yeah, this illustrates my concern that the proposal is too narrowly
>> focused on a specific benchmark.
> 
> A lot of the recently proposed changes don't really fit in the "optimizations"
> category very well at all. I think of them more as "avoiding pitfalls".
> 
> Currently Postgres works quite well if your application is designed around its
> performance profile. But as soon as you do something "strange" you run the
> risk of running into various pitfalls.

I would go a step further. Once you get into real life scenarios with 
real life work loads, you run into various pitfalls.

> 
> If you keep a long-running transaction open you suddenly find your tables
> bloating. If your table grows too large vacuum takes too long to complete and
> your tables bloat. If you update the same record many times instead of
> batching updates and performing a single update your table bloats. 

Long-running transaction is a big problem. I wish I knew how to solve it.

> 
> This one is similar, if you keep a bunch of static data attached to some small
> dynamic data your WAL and table bloats. Certainly you could have engineered
> your system not to fall into this pitfall, but only if you knew about it and
> only if it was worth the effort and other possible costs of doing so.
> 

It seems to me the solution could be useful. We have lots of tables that 
fall into the category that the test table presented.

Sincerely,

Joshua D. Drake



-- 

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-03-28 15:17:32
Subject: Re: Reduction in WAL for UPDATEs
Previous:From: Gregory StarkDate: 2007-03-28 15:05:58
Subject: Re: Reduction in WAL for UPDATEs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group