if you request row 42, row 43 will be in memory by the time you need it
True, but this is rarely the case for OLTP workloads. What happens when there is a credit card transaction with a user ID 100731, followed by a credit card transaction with a user ID 8762592? Even for range queries, what you're saying is true only if you're walking through the primary index. The second you start walking through the secondary indices, you're back to random read land (my Facebook friends, for example, are extremely unlikely to be stored in the user table sequentially).
SSDs are better for random writes
Random writes are very tricky on SSDs because of the slow erase operation. The FTL controllers are getting much better at this on micro benchmarks, but it's very difficult to measure random write performance profile over different timelines and different disk space utilization scenarios.
Comments
if you request row 42, row 43 will be in memory by the time you need it
True, but this is rarely the case for OLTP workloads. What happens when there is a credit card transaction with a user ID 100731, followed by a credit card transaction with a user ID 8762592? Even for range queries, what you're saying is true only if you're walking through the primary index. The second you start walking through the secondary indices, you're back to random read land (my Facebook friends, for example, are extremely unlikely to be stored in the user table sequentially).
SSDs are better for random writes
Random writes are very tricky on SSDs because of the slow erase operation. The FTL controllers are getting much better at this on micro benchmarks, but it's very difficult to measure random write performance profile over different timelines and different disk space utilization scenarios.