My only question is: what does the query interface look like for something like this? I've only ever accessed indexed data via SQL (or, I suppose, key value data). The main tradeoff discussed is indexing time in psql, but you give up the flexibility of sql (WHERE, joins, etc).
As you point out, there are definitely trade offs with choosing Lavastone instead of a SQL database. For instance you can’t easily share concurrent read/write access to the same Lavastone data with multiple processes. However, you can copy a fixed Lavastone data store (ie the underlying LevelDB store) and distribute it to multiple server nodes. That’s how we used it anyways!