Thread: Faster Updates
Hello,Sometimes people complain that UPDATE is slow in postgres. UPDATE... - generates dead tuples which must be vacuumed.- needs to hit all indexes even if only one column was modified. From what I know UPDATE creates a new copy of the old row with the relevant C/TID's, then indexes it. On COMMIT the old version becomes dead but stays in the table and indexes until VACUUM.I propose a simple idea, which may be idiotic, but who knows. When a row is UPDATED, instead of storing a new copy of the entire row, only a differential is stored. The old row stays in the page anyway, so we might as well only store the binary encoded equivalent of "Use the row version number X and change column A to value Y".This is possible only if the differential fits in the free space on the page.In this case, a lot less dead space is generated. VACUUM would consolidate the differentials for commited transactions into a new base value for this row.While reading the page looking for a specific version of a row, all differences would need to be consolidated. This adds overhead, but it might be a win.With this method, it could be possible to avoid updating the indexes for unmodified columns. This is a big win. What do you think ?
PFC <lists@peufeu.com> writes:
> What do you think ?
Sounds enormously complicated and of very doubtful net win --- you're
moving a lot of overhead into SELECT in order to make UPDATE cheaper,
and on top of that the restriction to same-page will limit the
usefulness quite a lot (unless we deliberately keep pages less than
full, which costs a lot in distributed extra I/O).
Basically this is an extension of the notion of update tuple chains.
Anyone who's been around the project awhile will know that we've had
an unreasonably large number of corner-case bugs associated with tuple
chains (particularly in VACUUM FULL), so adding a second level of
complexity to 'em doesn't sound very appealing to me.
regards, tom lane
On Saturday 03 June 2006 17:27, Tom Lane wrote: > PFC <lists@peufeu.com> writes: > > [snip - complicated update logic proprosal] > > What do you think ? > > Sounds enormously complicated and of very doubtful net win --- you're > > [snip - ... bad idea reasoning] :) What if every backend while processing a transaction collected a list of touched records - probably with a max number of entries (GUC) collected per transaction. Then when transaction completes the list of touples are sent to pg_autovacuum or possible a new process that selectively only went for those tupples. Of course it should have some kind of logic connected so we don't visit the tupples for vacuum unless we are quite sure no running transactions would be blocking adding the blocks to the FSM. We might be able to actually queue up the blocks until a later time (GUC queue-max-time + queue-size-limit) if we cannot determine that it would be safe to FSM the blocks at current time. I guess this has probably been suggested before and there is probably a reason why it cannot be done or wouldn't be effective. But it could probably be a big win in for common workloads like webpages. Where it would be troublesome is systems with long-running transactions - it might as well just be disabled there. Best regards, Nicolai Petri
On Jun 3, 2006, at 10:27 AM, Tom Lane wrote: > PFC <lists@peufeu.com> writes: >> What do you think ? > > Sounds enormously complicated and of very doubtful net win --- you're > moving a lot of overhead into SELECT in order to make UPDATE cheaper, > and on top of that the restriction to same-page will limit the > usefulness quite a lot (unless we deliberately keep pages less than > full, which costs a lot in distributed extra I/O). A lot of CPU overhead, which in many cases won't really matter. If someone has interest in testing this to see what impact it has, how hard would it be to hack together enough code to test the base concept? I'm thinking only basic SELECT and UPDATE support, along with a means to leave a certain percentage of each page empty.
On Jun 3, 2006, at 2:05 PM, Nicolai Petri wrote: > On Saturday 03 June 2006 17:27, Tom Lane wrote: >> PFC <lists@peufeu.com> writes: >>> [snip - complicated update logic proprosal] >>> What do you think ? >> >> Sounds enormously complicated and of very doubtful net win --- you're >> >> [snip - ... bad idea reasoning] :) > > What if every backend while processing a transaction collected a > list of > touched records - probably with a max number of entries (GUC) > collected per > transaction. Then when transaction completes the list of touples > are sent to > pg_autovacuum or possible a new process that selectively only went > for those > tupples. Of course it should have some kind of logic connected so > we don't > visit the tupples for vacuum unless we are quite sure no running > transactions > would be blocking adding the blocks to the FSM. We might be able to > actually > queue up the blocks until a later time (GUC queue-max-time + > queue-size-limit) if we cannot determine that it would be safe to > FSM the > blocks at current time. > > I guess this has probably been suggested before and there is > probably a reason Yup. Search the archives for 'dead space map'. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461