I'd like to store several thousand events per second into a database. The events come in from Kafka (i.e. they can be batched) and consist of 5 64-bit integers each. One of those 5 values is a timestamp. The planned retention is about 2 months.
A fitting SQL schema (just to show the structure) could look like this:
CREATE TABLE entities (a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
ts INT NOT NULL);
CREATE INDEX a_idx ON entities (a);
CREATE INDEX b_idx ON entities (b);
CREATE INDEX c_idx ON entities (c);
CREATE INDEX ts_idx ON entities (ts);
The indexes are generated because the DB needs to answer a few thousand queries like the following per second:
SELECT * FROM entities WHERE a = 1234567;
SELECT * FROM entities WHERE b = 2345678;
SELECT * FROM entities WHERE c = 3456789;
The database should be distributed (scaling horizontally) and tolerate node failure. Ideally it would be open source.
I did some experiments with CockroachDB, but did not achieve the needed insert performance when the table grew with it.
My tests with MongoDB showed fast enough inserts but slow queries.
Regarding Cassandra-like DBs: ScyllaDB does match the performance requirements, but manually maintaining multiple tables (for fast look-up by partition key) seems like a sub-optional solution to me.
Is there perhaps some not-so-well-know special-purpose database out there perfectly fitting my use-case that you would recommend?
