On 6/23/06, Mark Woodward <[EMAIL PROTECTED]> wrote:
I, for one, see a particularly nasty unscalable behavior in the implementation of MVCC with regards to updates.
I think this is a fairly common acceptance. The overhead required to perform an UPDATE in PostgreSQL is pretty heavy. Actually, it's not really PostgreSQL's implementation, but anything that employs basic multi-version timestamp ordering (MVTO) style MVCC. Basically, MVTO-style systems require additional work to be done in an UPDATE so that queries can find the most current row more quickly.
This is a very pessimistic behavior
Yes, and that's basically the point of MVTO in general. The nice thing about MVTO-style MVCC is that it isn't super complicated. No big UNDO strategy is needed because the old versions are always there and just have to satisfy a snapshot.
I still think an in-place indirection to the current row could fix the problem and speed up the database, there are some sticky situations that need to be considered, but it shouldn't break much.
I agree, but should make clear that moving to an in-place update isn't a quick-fix; it will require a good amount of design and planning. What I find in these discussions is that we always talk about over complicating vacuum in order to fix the poor behavior in MVCC. Fixing autovacuum does not eliminate the overhead required to add index entries and everything associated with performing an UPDATE... it's just cleaning up the mess after the fact. As I see it, fixing the root problem by moving to update-in-place may add a little more complication to the core, but will eliminate a lot of the headaches we have in overhead, performance, and manageability. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | [EMAIL PROTECTED] Iselin, New Jersey 08830 | http://www.enterprisedb.com/ ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq