Search Postgresql Archives

Re: [TLM] Re: How to insert on duplicate key?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, 28 Dec 2007, Robert Treat wrote:

A given transaction doesn't have a way to determine if there are live transaction looking at the row, that would require quite a bit of knowledge about what else is occuring in the system to be able to determine that. That level of knowledge/complexity is what vacuum takes care of.

One reason it doesn't happen automatically is that it would slow down the client doing the update. Sorting through to figure out which rows can be seen by which clients is better done later for a number of reasons; some samples:

-It's more likely there won't be any transactions still referencing the original row as time moves forward. If you do it the instant the row is dead, odds are higher there's still somebody using the original one and you can't prune it yet anyway.

-It's more efficicent to sort through a bunch of these in bulk than to do them one at a time.

-You need to have a similar vacuum process happening regularly anyway to analyze your tables and keep statistics about them up to date, so might as well do both of those things at once.

The downside is that vacuum can have a relatively high impact on the system, but the answer there is to do it more often so that any individual vacuum is less difficult.

It'a also worth mentioning that some of this update row reuse happens more automatically in V8.3 with a new feature called HOT, so in some cases this particular issue has already has a workaround everyone can get in the near future.

--
* Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux