Re: [HACKERS] Faster Updates
On Jun 3, 2006, at 2:05 PM, Nicolai Petri wrote: On Saturday 03 June 2006 17:27, Tom Lane wrote: PFC [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Faster Updates
On Jun 3, 2006, at 10:27 AM, Tom Lane wrote: PFC [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 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 ? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Faster Updates
PFC [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Faster Updates
On Saturday 03 June 2006 17:27, Tom Lane wrote: PFC [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
