> Okay, again, what kind of scale are we talking about here? 100ks of writes/second
You seem to be confusing writes/second with concurrent write transactions/second. Nvme won't rescue you if Postgres locks up only allowing each write txn to proceed sequentially when what you need is SSD write saturation on separate write transactions on multiple shards in a single machine.
> You'd be way better off going all NVMe today as your first step to "scaling" (in fact you are insane if you are using anything else for an OLTP database), and just that is likely to immediately solve whatever problem you have already.
This is hilarious. So we should all start building based on your prescription.
> what exactly is the benefit of manually implementing what it already does well?
Really? Is that a serious argument. Postgres is not a magic database that does everything that everybody wants very well.
In systems engineering, the trade offs are obvious. But if it is not clear at this point, I don't think I can offer much help.
I will offer one last advice.
Just write a snippet of code inserting 100M small keys and values into, say 1k chunks/shards of separate kv engine (LMDB, whatever). Do normal hashing for shard selection. Now store secondary indexes likewise. Now do parallel fanout for secondary indexes for selecting some 100k data. Collect the index data and do another parallel fanout and fetch the final data.
Now go see the latency results (excluding lmdb env setup time). Additionally observe the latency for writes :-)
Rich index metadata is just icing on the cake. Cheers.
You seem to be confusing writes/second with concurrent write transactions/second. Nvme won't rescue you if Postgres locks up only allowing each write txn to proceed sequentially when what you need is SSD write saturation on separate write transactions on multiple shards in a single machine.
> You'd be way better off going all NVMe today as your first step to "scaling" (in fact you are insane if you are using anything else for an OLTP database), and just that is likely to immediately solve whatever problem you have already.
This is hilarious. So we should all start building based on your prescription.
> what exactly is the benefit of manually implementing what it already does well?
Really? Is that a serious argument. Postgres is not a magic database that does everything that everybody wants very well.
In systems engineering, the trade offs are obvious. But if it is not clear at this point, I don't think I can offer much help.
I will offer one last advice.
Just write a snippet of code inserting 100M small keys and values into, say 1k chunks/shards of separate kv engine (LMDB, whatever). Do normal hashing for shard selection. Now store secondary indexes likewise. Now do parallel fanout for secondary indexes for selecting some 100k data. Collect the index data and do another parallel fanout and fetch the final data. Now go see the latency results (excluding lmdb env setup time). Additionally observe the latency for writes :-)
Rich index metadata is just icing on the cake. Cheers.