The SQL account history plugin currently generates 967GB of storage when we run the 49M block reindex. This is more than 2x the size of rocksdb-based storage...
Doesn't this make it more expensive to run a node on commodity hardware? What would happen down the road when the blockchain's size goes 2x or more? Is the tradeoff worth it?
I am not that well versed in how the different node types interact with each other so my questions may be coming from a place of ignorance.
Not really, as disk storage is one of the cheaper things associated with running a node. In fact some of the main improvements in hived have centered around moving things that don't need to be in memory out to cheaper disk-based storage.