It occurs to me that if you handle LIMIT during fetch, you'll add complexity the fetch, and might only see run time gains in the cases where the number of desired rows is small.
If a column contains unique data it should be marked as such in whatever way your RDBMS requires (e.g. UNIQUE constraint and/or UNIQUE index)
The big innovation of Lehman and Yao was that their right link technique obviates the need for "latch coupling", or in Postgres terms the crabbing of buffer locks. In other words, the Postgres implementation need only lock one page/buffer at a time as an index scan descends the B-Tree, rather than alternately locking a single page, then its child, then unlocking the parent, and once again locking the child (the grandchild of the original page). Certain other B-Tree implementations must do this until their descent reaches a leaf page, and locking multiple pages at once just to service index scans can hurt concurrency a lot (I think that they might need to be exclusive locks for writes, which is particularly poor).
These low-level locks are not to be confused with lock manager locks that have deadlock detection and so on. They're low-level Readers–writer locks, and are far more lightweight (hence the Postgres term "LWLock").
The basic idea of Lehman and Yao is that index scans of all types may detect and recover from a concurrent page split my following a right-link (having found that the page high key is logically less than the scankey value). It won't matter that the parent page was observed to not have the new downlink that is inserted after a page split, except that we need to look right.
"indices is generally preferred in mathematical, financial, and technical contexts, while indexes is relatively common in general usage"
B-tree means "binary tree" and are not inherently balanced. There are plenty of specific types of self balancing tree algorithms, but I believe the default implementation of b-trees by definition are not self-balanced.
Edit: this comment is incorrect. There is a difference between b-trees and binary trees.
After a talk at CPM 2013 (24th Annual Symposium on Combinatorial Pattern Matching, Bad Herrenalb, Germany, June 17–19, 2013), Ed McCreight answered a question on B-tree's name by Martin Farach-Colton saying: "Bayer and I were in a lunch time where we get to think a name. And we were, so, B, we were thinking… B is, you know… We were working for Boeing at the time, we couldn't use the name without talking to lawyers. So, there is a B. It has to do with balance, another B. Bayer was the senior author, who did have several years older than I am and had many more publications than I did. So there is another B. And so, at the lunch table we never did resolve whether there was one of those that made more sense than the rest. What really lives to say is: the more you think about what the B in B-trees means, the better you understand B-trees."
"The B-tree is a generalization of a binary search tree in that a node can have more than two children (Comer 1979, p. 123). Unlike self-balancing binary search trees, the B-tree is optimized for systems that read and write large blocks of data."
Long ago in one of my CS classes, I wrote binary tree, 2-3, 2-3-4, red black, and AVL tree algorithms. So I guess I learned about binary vs self-balancing. I did not know about b-trees. I made the mistake of thinking that b-tree meant binary tree.