Ok that makes sense.  I am a bit concerned that even though the update is a single statement, it must touch a lot of rows, but it should be infrequent enough to not be a problem.

My next question is regarding the very common operation of determining whether an output has been spent.  Each output could be spent in a single transaction that belongs to multiple blocks (in separate forks) or by multiple transactions (also in separate forks).

So it seems that to determine if an output has been spent in a particular chain, you will have to query for all the inputs that spend the output, find (JOIN) the transactions that contain those inputs, find (JOIN) the blocks that contain those transactions, and see if any of those blocks have a span range that contains the chain in question.  I know databases are supposed to be good at this kind of thing but it sure sounds like a lot of work.  On the other hand, most of the transactions will not have a double-spend, so all the joining shouldn't make a difference in the most common case where there are no inputs that spend a txout.  All the joining might slow it down a bit when the blockchain forks, but a few extra indexed lookups shouldn't be that big a deal I guess.

I'm thinking I'll switch to your schema with the spans, because it bugs me how my version has dynamic fields marking whether a block is part of the 'best chain' and whether a txout has been spent in the current best chain.  It is a real headache to add a block to a fork other than the current best.