Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The fundamental point IMHO is that indexes are more complex and
> much more fragile than heaps. This is obviously true
> theoretically and we have years of experience that proves it to be
> true in the field as well. Broken comparison functions are just
> one of the possible hazards; there are many others.
>
> Now with standard indexes you can always recover from any problem
> via REINDEX; no matter how badly the index is messed up, your data
> is still there and not damaged. (Well, maybe it will fail a
> unique constraint check or something, but at least it's still
> there.)
>
> With an IOT I don't understand how you get out of index corruption
> without data loss. That's a showstopper for practical use, I
> think.
Having used the IOT implementation ("clustered indexes") in SQL
Server and then Sybase ASE starting with SQL Server 1.0, I can
relate my experiences on that. In about 18 years with over 100
databases we had maybe five or ten times that such damage made it
difficult to recover data -- typically the result of hardware
problems. This implementation had a double linked list of pointers
through the leaf level pages, so normally a query which generated a
full table scan would follow these and work. When said pointers
were damaged we would query through the index tree to see what we
could reach. There were usually other indexes on tables, which
would give us other paths to the data in these leaf pages. It was
sometimes necessary to subdivide a range in which we were getting an
error to find the "edges" of the damaged area.
There were sometimes small areas we could not reach, for which we
had to look to backups or source documents. There's clearly no
database technology which guarantees you will never have to do that
in the face of a hardware failure. To the extent that such a
technique reduces the redundant storage of values, it clearly
affects recovery options.
All in all, I suspect that it would be underrating the talent pool
available for PostgreSQL development to say we can't get to a
feature which SQL server had in version 1.0 and maintains through
their conversion to MVCC. Where it fits in the scheme of priorities
and cost/benefit is certainly a valid question. It does provide
significant benefits for some use cases, but it's certainly not
trivial to implement.
-Kevin