But since the arbitrary SQL that gets run will update every other record in a thread (in this example), I'm interested to see how ActiveRecord handles the results, particularly with respect to caching and the like.

At the very least you don't have to run code that would cause every object in the thread to save itself individually, which is good.

Note: just about any database can support a nested set tree. If it can't, then it's not much of a database. The only difference would be whether the logic can be placed in a stored procedure or not, but in the end that's not a big deal for the purposes of this example.