4 ms·

At some point, isn't it better to build your own database? And choose the trade offs that fit your project. Or pay/hire someone to do it.

wiredfool 4y ago
And that point is when you can’t find someone to host it for you. Though, I’d be sorely tempted to spin up a bfc of clickhouse on bare metal.
- dravita 4y ago
  That one was tried, among many others covered in the article.
  - JackWritesCode 4y ago
    Clickhouse isn’t really relevant for a lot of us. SingleStore outperforms clickhouse and the latter isn’t MySQL wire compatible. Far more features come with SingleStore too. And if you speak to people running Clickhouse, they’re also maintaining a Postgres set-up. With SingleStore, you get your OLTP and OLAP in one database. So our users & sites table sit in memory (backed up by disk), meaning ultra fast read/write speeds (comparable to clustered/high availability Redis). And then we put our pageviews/events in columnstore (disk) which offers rapid performance for analytical queries.
Yoric 4y ago
That sounds like a really bad solution, actually. Building a reliable DBMS with any kind of decent performance can easily take years. And by then, it's still not sure that you're doing any better than any of the existing solutions.
JackWritesCode 4y ago
Definitely not. No regrets after making the move. March next year will be the two year mark.
- amirulmenjeni 4y ago
  I'm curious how you (and your team I presume) came to decide you need to build your own database?
  - JackWritesCode 4y ago
    We didn’t decide that, we used SingleStore
aarondf 4y ago
I honestly can't think of any time where "build your own database" is a good solution, unless you're a database company or maybe Google.
nnopepe 4y ago
These people don't have the inhouse skills to maintain a simple SQL server, let alone optimize their schema (MySQL being too slow to sum/group 11m records in a timely manner? Please.) Attempting to make your own db while being ignorant of competing products would be an absolute disaster
- JackWritesCode 4y ago
  I completely agree! For the people following back home, who don’t want to move away from MySQL, can you let us know how to achieve a <10ms GROUP BY aggregation with a high cardinality column (11 million distinct values for “pathname”). The query was: SELECT SUM(pageviews) as total, pathname FROM pageviews GROUP BY pathname ORDER BY total DESC LIMIT 10. If we don’t hear an answer from you, I’ll be really upset. Otherwise, we may have to add your ideas to the article!

wiredfool 4y ago

And that point is when you can’t find someone to host it for you. Though, I’d be sorely tempted to spin up a bfc of clickhouse on bare metal.

dravita 4y ago
That one was tried, among many others covered in the article.
- JackWritesCode 4y ago
  Clickhouse isn’t really relevant for a lot of us. SingleStore outperforms clickhouse and the latter isn’t MySQL wire compatible. Far more features come with SingleStore too. And if you speak to people running Clickhouse, they’re also maintaining a Postgres set-up. With SingleStore, you get your OLTP and OLAP in one database. So our users & sites table sit in memory (backed up by disk), meaning ultra fast read/write speeds (comparable to clustered/high availability Redis). And then we put our pageviews/events in columnstore (disk) which offers rapid performance for analytical queries.

Yoric 4y ago

That sounds like a really bad solution, actually. Building a reliable DBMS with any kind of decent performance can easily take years. And by then, it's still not sure that you're doing any better than any of the existing solutions.

JackWritesCode 4y ago

Definitely not. No regrets after making the move. March next year will be the two year mark.

amirulmenjeni 4y ago
I'm curious how you (and your team I presume) came to decide you need to build your own database?
- JackWritesCode 4y ago
  We didn’t decide that, we used SingleStore

aarondf 4y ago

I honestly can't think of any time where "build your own database" is a good solution, unless you're a database company or maybe Google.

nnopepe 4y ago

These people don't have the inhouse skills to maintain a simple SQL server, let alone optimize their schema (MySQL being too slow to sum/group 11m records in a timely manner? Please.) Attempting to make your own db while being ignorant of competing products would be an absolute disaster

JackWritesCode 4y ago
I completely agree! For the people following back home, who don’t want to move away from MySQL, can you let us know how to achieve a <10ms GROUP BY aggregation with a high cardinality column (11 million distinct values for “pathname”). The query was: SELECT SUM(pageviews) as total, pathname FROM pageviews GROUP BY pathname ORDER BY total DESC LIMIT 10. If we don’t hear an answer from you, I’ll be really upset. Otherwise, we may have to add your ideas to the article!