Re: Weird indices

Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> Why? There is a mechanism for keeping track of which heap tuples are
> valid, why not index tuples? It is the nature of indices to be updated
> on inserts, why not deletes?

An index is a hint: these tuples *might* be of interest to your
transaction. It's OK for an index to point to some irrelevant tuples,
but it's useless if it fails to point to all the possibly relevant
tuples. Therefore, it's OK to insert an index entry at the earliest
possible instant (as soon as a yet-uncommitted heap tuple is inserted);
and contrariwise the index entry can't be deleted until the heap tuple
can be proven to be no longer of interest to any still-alive transaction.

Currently, proving that a heap tuple is globally no-longer-of-interest
and removing it and its associated index tuples is the task of VACUUM.

Intermediate state transitions (eg, this tuple has been deleted by a
not-yet-committed transaction) are recorded in the heap tuple, but we
don't try to look around and update all the associated index tuples at
the same time. Maintaining that same state in all the index tuples
would be expensive and would bloat the indexes. An index that's not
a lot smaller than the associated heap is of little value, so extra
bits in an index entry are to be feared.

These are very fundamental system design decisions. If you'd like
to show us the error of our ways, step right up to the plate and swing
away; but unsubstantiated suggestions that these choices are wrong are
not going to be taken with any seriousness. Postgres has come pretty
far on the basis of these design choices.

regards, tom lane