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

closed as too broad by Laurenz Albe, Marcello Miorelli, dezso, John Eisbrener, LowlyDBA Sep 9 at 15:12

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    This is clearly too broad and opinion based, but here are my two cents: 1) you must plan how to get rid of old data right now, else you will be up against the wall at some point, and this is independent of the software you'll use 2) PostgreSQL has no problem running multi-TB databases; if they perform well depends on how they are designed and what you do with them 3) 1 ms per inserted record sounds like you commit each row separately. I can't think of another way to get PostgreSQL to be that slow. – Laurenz Albe Sep 9 at 9:12

Hash indexes do not support unique constraints. So you would need btree (over some value, possibly hash, that you provide). Every insert will dirty some random leaf page, which needs to get written. With a gigantic index, there will be limited opportunity for write combining or consolidation. Can your IO system handle 1000 random writes per second? (A hash index will have the same problem--dirtying one random leaf per insert. Btree also dirties intermediate pages, but these buffer well so don't really add to the problem)

Some databases have "fractal indexes" to address the problem, but PostgreSQL does not.

  • I don't think the OP meant "hash index", more like "index on a column containing a hash". – mustaccio Sep 9 at 19:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.