0

I plan on archiving messages, and I'm not sure which I should use.

It should be able to hold a maximum of 20 billions rows (that's what I expect the total number to tend to)

Each row will have three columns : user_id, message, date

The user_id is a string of 30 characters. The message is anywhere between 1 - 20 000 characters. On average I expect it to be 140 characters. (UTF-8, it should allow emojis, different alphabets, etc)

I only want an index for the user_id, not for the message/date.

I only plan on doing INSERT queries, and very simple SELECT * WHERE user_id = XXXXX There will be very little SELECTs, I expect at its peak 10 / minute. The SELECTs don't need to be fast, anything between 1-20 seconds is fine.

But there will be a lot of INSERTs. Probably about 5000-10000 per seconds.

My server will have: CPU: AMD Ryzen™ 9 7950X3D RAM: 128 GB DDR5 ECC Drives: 1x 7.68 TB NVMe SSD Datacenter (From Hetzner)

3
  • How many unique user IDs do you expect? Do you realise that SELECT * WHERE user_id = XXXXX will return rows in an arbitrary order?
    – mustaccio
    Nov 27, 2023 at 20:31
  • @mustaccio Between 100 millions and 1 billion~, something in the middle being more likely, but something slightly above 1 billion still being a possibility(something like 10% chance). I do realize that the order will be arbitrary and it's fine
    – lyeaf
    Nov 27, 2023 at 21:52
  • @bobflux I'm fully respecting any terms of use of any service I may interact with. For full-text searches I don't really need it. It will really be simple queries, not even UNIONs or UPDATE etc. The index will only be for the VARCHAR(30) user_id. Ideally yes the search on user_id needs to be case sensitive, but if not it's an issue I transform the IDs to have only lower case IDs
    – lyeaf
    Nov 27, 2023 at 21:57

2 Answers 2

3

Let's count 180 bytes per message (including user_id, timestamp and overhead), with 20B messages that's 3.6 Tb, already quite substantial chunk of data.

I only plan on doing INSERT queries, and very simple SELECT * WHERE user_id = XXXXX

In order to avoid random IO, I'd favor a database that can cluster rows with the same index keys (in this case user_id) close together in storage. That will speed up your SELECTs, and if you order by (user_id,timestamp) while the data is already stored in that order, no sorting will be required.

Postgresql would require an extra index which would duplicate the data.

InnoDB on MySQL/MariaDB automatically clusters based on the primary key, so if you define (user_id,timestamp) as PK then that condition is fulfilled. InnoDB also supports page compression with modern algorithms like lz4. If possible, when inserting a batch of rows, you should order them by primary key. Everyone knows MySQL so I won't elaborate.

Another choice would be Clickhouse. Although it speaks SQL, it's not exactly a relational database like the usual ones. For example it doesn't do UPDATEs or foreign keys. It's meant for OLAP/data warehouse, which is what you're doing. It does fulfill both conditions: using the MergeTree engine, data is automatically stored in the order you specify so it will be clustered, and it supports compression. It's a bit finicky and difficult to configure. Performance is generally ridiculous if you use it for what it's supposed to do. Let's try a very dumb query:

SELECT max(value) FROM mqtt_store;
┌──max(value)─┐
│ 25378554360 │
└─────────────┘
1 row in set. Elapsed: 1.614 sec. 
Processed 1.41 billion rows, 11.25 GB (871.46 million rows/s., 6.97 GB/s.)

As for INSERTs, you should do about 1-10 per second, in bulk. Besides it's never a good idea to make thousands INSERT statements per second when you can do just one with all the rows grouped. The python interface supports bulk insertions with arrays. Example:

CREATE TABLE foo
(
    `id` Int32 NOT NULL CODEC(Delta, LZ4),
    `x` String CODEC(LZ4)
)
ENGINE = MergeTree PRIMARY KEY (x, y)

python

x = list(range(100000000))                                                        
y = ["Hello I am number %d"%_ for _ in x]                                             
t=time.time(); clickhouse.execute("INSERT INTO foo VALUES", (x,y), columnar=True); time.time()-t
37.523436546325684

Compression stats:

┌─name─┬─compression_codec────┬─uncompressed─┬─compressed─┬─ratio─┐
│ x    │ CODEC(Delta(4), LZ4) │    404000044 │   25015435 │  16.2 │
│ y    │ CODEC(LZ4)           │   2713777985 │  518720198 │   5.2 │
└──────┴──────────────────────┴──────────────┴────────────┴───────┘

In that simple example we get 2.6M rows inserted per second with a compression ratio of about 5 on this admittedly easy to compress text. Python maxed one core for 37.5s sending the data, while the database used 21s of cpu time. Basically, it wasn't doing much, and the rest of the cores were idling.

1
  • That's exactly the answer I was looking for and I'm thankful to you. I will give Clickhouse a try
    – lyeaf
    Nov 28, 2023 at 22:09
1

What database to store 20~ billions rows

Any.

Most modern database systems don't have a data limit by row count. Size of data at rest doesn't impact performance.

You have a very simple and defined schema. You also have very simple use cases. So likely any modern RDBMS would be a good choice. If you want one with a lot of user support and features while also free, PostgreSQL is a great choice. If paid and enterprise level software is on the table SQL Server is an excellent choice too. Other fine options are MariaDB, Oracle SQL, and MySQL.

FWIW, I've worked with storing large amounts of Message data (email messages specifically) in an RDBMS (SQL Server) in the past. The main table was 10s of billions of rows big, and we were inserting thousands of rows per second, during high burst times. Querying small amounts of data from that table was quick (under a second) as well. The hardware behind the server was much less provisioned than what you plan on using too.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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