> The biggest mistake I made was that I kept the UPDATEs in our code (we update the previous pageview with the duration, remove the bounce, etc.). In a few weeks, I'll be moving to 100% append-only by utilizing negative numbers.
> For example, if you want to set bounce_rate to 0%, you would write a 1 for bounce_rate on the first pageview and then insert a duplicate with -1 for bounce_rate. And for the duplicate row, you'd have nothing set for pageviews, visits and uniques, so it would all group nicely.
This is nearly a copy-paste from ClickHouse documentation.
> We shard on UUID, and then we set SiteId as the sort key. We do this because we want to utilize something called "local joins" in SingleStore. Long story short, events can be joined with event_properties (allowing you to have thousands of dynamic properties per event you track), and it's fast.
This statement is slightly disappointing as well. No magic, no advantage to ClickHouse.
The outcome can be read as follows: "use SingleStore exactly as you'd use ClickHouse and maybe it will work alright".
SingleStore advantages:
- good compatibility with MySQL dialect;
- offers good UPDATE/DELETE for fresh data (invalidated by this article, as they dropped the usage of UPDATE for better performance);
- good support for JOINs (invalidated by this article, as they ended up using local JOINs);
SingleStore disadvantages:
- not open-source, worse brand recognition, the old brand (MemSQL) did not work well too;
- performance claims don't validate;
- older company and many initial developers no longer work there;
Fathom sounds like a toy use case as the data amount is too low. ClickHouse is almost universally selected for similar and larger use cases, with hundreds of billions of events each day.
PS. Nevertheless, the post is hilarious:
> I was suffering from low energy in the two weeks leading up to this migration, and I was feeling awful throughout migration week, especially on migration day. On Sunday 14th March 2021, two days after we had finished the migration, my wife showed me an already-half-used bag of coffee beans I had been drinking through most of this migration, and the bag said "DECAF." Divorce proceedings are underway.