Comment by Exuma | Hacker News Reader

Comment by Exuma | Hacker News Reader

Can someone help me with a suggestion?

Ive been researching databases now for several days straight, the choices are overwhelming but I've pretty much narrowed my use case down to an RDBMS system.

I need to essentially handle 100's of millions of "leads" (and 10s of millions per day) which can make up any number of user fields. over 1B total

I need to resolve duplicate leads either in realtime or near realtime. A duplication can occur across a combination of 1 or more fields, so basically OLTP type operations (select, update, delete on single rows)

I do need to run large OLAP queries as well across all data

I've looked at things like scylla and whatnot but they seem too heavy duty for my volume. it's not like i need to store trillions of messages like discord in some huge event log.

I was considering these 3 options...

1. planetscale

2. citus

3. cockroachdb

I havent really narrowed it down further than this, but i liked the idea of still having RDBMS features without needing to worry about storage and scaling with just sheer write volume.

It seemed i could then do my basic OLTP stuff that i need, and citus had a cool demo how some OLAP query on 1B rows ran in 20s with 10 nodes, and that also fits a reasonable time for queries (BI tools will be used for that)

Postgres with upserts (and triggers if your de-duplication logic can't be handled on the backend)?

OLAP works here, but is not great depending on how fast you need info to be available. If you're generating reports, Postgres is fine as long as your queries are properly optimized, and you can get them within minutes for massive workloads. If you need near-instant (sub 1s) results, I would recommend you sync your RDBMS to a columnar database like ClickHouse, and let the better data layout work in your favor, rather than trying to constrain a row-based DB to act like it's not.

Otherwise, both are rock solid and simple to use. I've dealt with more intensive workloads than you mentioned, with the same use-case and Postgres worked very well. ClickHouse never had a problem.

Awesome, thank you. That's kind of what I was thinking, I'm glad you confirmed it. How exactly did you sync or data from PG -> Clickhouse?

I was considering using something like Airbyte, but then I thought this may actually be complex if PG rows are updating/deleting it means I also need to sync single rows (or groups of rows) to clickhouse, and I wasn't sure how the support was for that.

What I did in my case was setup stream replication to send over the Postgres WAL to another service that would update a ClickHouse cluster. Essentially, every time the WAL file is closed, a batch of all the SQL commands that were committed are sent over the wire.

It might be easier to find some "change data capture" product that will do that for you though (like Airbyte). I can't give any recommendations here, however.

I can recommend a fellow Y member PeerDB [0] for this but I don't know if they support ClickHouse as a destination

[0] - https://www.peerdb.io/

Pretty much anything will work at that scale depending on your SLAs. You could use Mongo with the higher compression option, add a couple indexes, and be golden. Just do the reporting off a live secondary. I store billions of documents in Mongo on unimpressive hardware (64gb ram, GP3 EBS), adding millions a day. Mongo isn't super fast at aggregations, though...

What kind of aggregation queries? Can you do pre-aggregation?

Citus would probably be my pick if you want SQL.

Feel free to email.

Have a look into singlestore - it seems like a nice fit for this use case.

Reply on news.ycombinator.com