On 6/23/06, Mark Woodward <[EMAIL PROTECTED]> wrote:
Vacuum is findimentally inefficient
The theory of database cleanup (VACUUM) is good, but has nothing to do
with the overhead we currently encounter in performing an update.
I have been ranting about a first row strategy, one where the first
version of a row is the top of a linked list of versions.
(1) The indexes point to the first "key" row.
I'm with ya.
(2) When a row is updated, [...] If it is not found [in the index], the
old version of the row is orphaned and behaves as PostgreSQL
aka current bad update behavior.
(3) If the row is not orphaned, its "last version" reference is updated.
The *last version* reference? Where is this, another entry on the
tuple header (extra size overhead on every tuple) or updating the
index (additional locking+WAL)? Following something like the ctid?
How is that really any different than performing a sequential scan
from the index as a starting point? That could still encounter
multiple physical I/Os to get to the current row and is potentially
very slow. Sure, we can add extra free space to each block to limit
tuple version migration across blocks... combined with constant
vacuuming and FSM updates we'd get some great performance.
Of course, we could just avoid the overcomplication and locking issues
of having to tune block-level freespace, vacuum, and tweaking FSM for
each type of application, but where's the fun in that? (yes, that was
a badly placed sarchastic joke)
For the most part, this should only affect updates where the index entries
don't change. If the index value is always change, PostgreSQL will behave
as it currently does. If the index values do not change, updates will be
faster to do and won't impact queries.
I like the idea of a new strategy when index entries do not change.
But, like Tom said, there is no free lunch; in your example you're
moving the high cost of an UPDATE to even more costly SELECTs.
This is something that requires a good amount of planning and design,
and I'm really glad to see some ideas being thrown into this
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 6: explain analyze is your friend