I am looking for a DB for a very specific use: lots of inserts, pretty small records, each record has an SHA-256 id that must be unique. The only use is inserts: I want the insert to fail if the hash already exist, meaning I will have a primary key or a more efficient index.
The DB will be approached from several services and hosts meaning it has to support async approaches.
I started doing performance checks on PostgreSQL, trying to insert also in batches of different sizes.
Important to know: the DB is meant go grow and grow with out deleting data (maybe there will be retention every few years). The scale will reach a few Terabytes after a few months or a year, so it should support big data. But I don't need any aggregations or efficient manipulations of data, only fast inserts with index on binary/string field of hash.
The questions are the following:
1) Does PostgreSQL match my use case? Considering the big amount of data, and the need for as fast as possible performance of inserts.
2) Is there a limit for PostgreSQL data size where it starts gradually lowering performance? I have been told by colleagues that after about 2TB there will be a significant decrease of performance, but they heard it from someone else and without a valid reason.
3) Through my performance checks, I filled my PostgreSQL DB with about 150GB of data, then tested inserts of batches of 10,000 records, and managed at the best to get about 1-1.5 ms per record. This still isn't very satisfying. Is it logical or I have to config the DB somehow in a different way?
4) Any recommendations for another DB that could match the case? We thought also about MongoDB, and maybe Cassandra, but we read that Cassandra doesn't support locks and in our use case async support is required. (Meaning if two services try to insert two batches at the same time, one of them should wait so the two batches will be compared vs each other as well to avoid hash collision).
Thanks in advance