MVCC, undo log, and HOT
Those who have been with the community from long ago might remember
discussion about implementing a undo log. The big advantage of this is
that it allows UPDATE to _replace_ rows and limits the amount of cleanup
required for UPDATEs.
I am hoping that with HOT we will no longer have any need to even
consider undo.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote:
Those who have been with the community from long ago might remember
discussion about implementing a undo log. The big advantage of this is
that it allows UPDATE to _replace_ rows and limits the amount of cleanup
required for UPDATEs.I am hoping that with HOT we will no longer have any need to even
consider undo.
We were considering it?
I certainly wasn't. I've enough experience with Oracle and InnoDB to
see that an undo log is its own set of performance issues. No thanks.
Hopefully the "multi-thread the backend" idea is dead, too.
--Josh
Josh Berkus wrote:
Bruce Momjian wrote:
Those who have been with the community from long ago might remember
discussion about implementing a undo log. The big advantage of this is
that it allows UPDATE to _replace_ rows and limits the amount of cleanup
required for UPDATEs.I am hoping that with HOT we will no longer have any need to even
consider undo.We were considering it?
I don't ever remember us considering it seriously.
I certainly wasn't. I've enough experience with Oracle and InnoDB to
see that an undo log is its own set of performane issues. No thanks.
It certainly does.
Joshua D. Drake
Joshua D. Drake wrote:
Josh Berkus wrote:
Bruce Momjian wrote:
Those who have been with the community from long ago might remember
discussion about implementing a undo log. The big advantage of this is
that it allows UPDATE to _replace_ rows and limits the amount of cleanup
required for UPDATEs.I am hoping that with HOT we will no longer have any need to even
consider undo.We were considering it?
I don't ever remember us considering it seriously.
I certainly wasn't. I've enough experience with Oracle and InnoDB to
see that an undo log is its own set of performane issues. No thanks.It certainly does.
We never actually considred undo, but high UPDATE activity was one of
the areas we historically handled poorly compared to undo systems, and
undo would have been one way to improve that area. I think with HOT we
have improved high UPDATE activity enough that the undo benefits are no
longer attractive (and of course the undo disadvantages were never
attractive).
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce,
We never actually considred undo, but high UPDATE activity was one of
the areas we historically handled poorly compared to undo systems, and
undo would have been one way to improve that area. I think with HOT we
have improved high UPDATE activity enough that the undo benefits are no
longer attractive (and of course the undo disadvantages were never
attractive).
If you're asking if we should remove the TODO (is there one?) and
replace it with a FAQ item called "Why PostgreSQL doesn't have an UNDO
LOG", then my vote is "yes".
--Josh
Josh Berkus wrote:
Bruce,
We never actually considred undo, but high UPDATE activity was one of
the areas we historically handled poorly compared to undo systems, and
undo would have been one way to improve that area. I think with HOT we
have improved high UPDATE activity enough that the undo benefits are no
longer attractive (and of course the undo disadvantages were never
attractive).If you're asking if we should remove the TODO (is there one?) and
replace it with a FAQ item called "Why PostgreSQL doesn't have an UNDO
LOG", then my vote is "yes".
No, it never got close to being a TODO item. It was more a limitation
we had that is now fixed.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote:
Josh Berkus wrote:
Bruce,
We never actually considred undo, but high UPDATE activity was one of
the areas we historically handled poorly compared to undo systems, and
undo would have been one way to improve that area. I think with HOT we
have improved high UPDATE activity enough that the undo benefits are no
longer attractive (and of course the undo disadvantages were never
attractive).If you're asking if we should remove the TODO (is there one?) and
replace it with a FAQ item called "Why PostgreSQL doesn't have an UNDO
LOG", then my vote is "yes".No, it never got close to being a TODO item. It was more a limitation
we had that is now fixed.
HOT is cool, but it really doesn't solve the whole problem. It works for
a significant class of problems, but for example it won't have any
significant effect on the app I'm currently working on which is very
index-rich. It would be a major mistake to think there's no work left to
do in improving update performance.
cheers
andrew
"Andrew Dunstan" <andrew@dunslane.net> writes:
HOT is cool, but it really doesn't solve the whole problem. It works for a
significant class of problems, but for example it won't have any significant
effect on the app I'm currently working on which is very index-rich. It would
be a major mistake to think there's no work left to do in improving update
performance.
Another use case it doesn't address is updating a large fraction of the table.
If you do a single unconstrained "UPDATE foo SET x=y" your table and indexes
will double in size and never shrink back.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
On Mon, 2007-10-22 at 11:00 -0400, Bruce Momjian wrote:
Joshua D. Drake wrote:
Josh Berkus wrote:
Bruce Momjian wrote:
Those who have been with the community from long ago might remember
discussion about implementing a undo log. The big advantage of this is
that it allows UPDATE to _replace_ rows and limits the amount of cleanup
required for UPDATEs.I am hoping that with HOT we will no longer have any need to even
consider undo.We were considering it?
I don't ever remember us considering it seriously.
I certainly wasn't. I've enough experience with Oracle and InnoDB to
see that an undo log is its own set of performane issues. No thanks.It certainly does.
We never actually considred undo
I did, but eventually ruled it out during the HOT design process. But
then I considered a ton of other things and ruled them out also.
Can't see a reason to bring it up again, so perhaps we should add it to
the definitely don't want list. Don't *need* would be better.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
On 10/23/07, Andrew Dunstan <andrew@dunslane.net> wrote:
It would be a major mistake to think there's no work left to
do in improving update performance.
Agreed. That would be a very short-sighted move.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
On 10/23/07, Simon Riggs <simon@2ndquadrant.com> wrote:
We never actually considred undo
I did, but eventually ruled it out during the HOT design process. But
then I considered a ton of other things and ruled them out also.Can't see a reason to bring it up again, so perhaps we should add it to
the definitely don't want list. Don't *need* would be better.
Actually, my initial UNDO design and prototype had several other
advantages over HOT (better performance, inherent time-travel, smaller
on-disk footprint, and *no* vacuum). The major issue was due to the
amount of architecture changes Postgres would've required and Bruce's
advice regarding the community's inability to accept such a large and
complex change.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
On 10/23/07, Simon Riggs <simon@2ndquadrant.com> wrote:
Don't *need* would be better.
Forgot to mention I agree. What's done is done. I'm not beating that
UNDO horse anymore; it's long past dead.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
On Tue, 23 Oct 2007 09:07:52 -0400
"Jonah H. Harris" <jonah.harris@gmail.com> wrote:
On 10/23/07, Andrew Dunstan <andrew@dunslane.net> wrote:
It would be a major mistake to think there's no work left to
do in improving update performance.Agreed. That would be a very short-sighted move.
I don't think anyone is suggesting that.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/