Scenario 1: Minecraft Server
Let’s pretend we have 39,000 players on a massive Minecraft server.
Most primary keys are going to be a UUID.
exe. SELECT x, y, z FROM a WHERE UUID = ‘069a79f4-44e9-4726-a5be-fca90e38aaf5’;
A lot of player data. Multiple tables consisting of rows with values values like counts on player actions, join/leave times, money/coins/currencies, inserts of up to about 1KB in a specific column, but most only a couple of bytes and mostly integers for the rest. Many values could UPDATE every 90 seconds.
Let’s pretend the there are 1,000 UPDATES per second on table(s) of up to 25-75 million entries and maybe 3,000 SELECT queries per second.
We’ll call this 25% UPDATES and 75% SELECT
Maybe 250GB max for total database size
Would rowstore or columnstore be better for this solution? Analytics aren’t really important, the important factor is achieving the lowest latency for reads. It seems like rowstore makes more sense with tables that aren’t over hundreds of gigabytes big, and there would be ton of selects per second on some random data. I read through so much of the documentation, but it still seems like a really hard choice between rowstore or columnstore, though it does appear to be possible to easily change this in the future if needed.
Scenario 2: Twitter Scenario
What would their best option be for Tweets? 700 million tweets per day, a potential 1 trillion inserts every 3-4 years
So a constant 8k INSERTS/second is a must (most likely batched though) and needs to be able to support up to 150,000 INSERTS at peak times.
That’s about 50% INSERT and 50% SELECT queries since tweets are stored in a cache
Maybe 100TB-275TB database size
Would Twitter want to use Rowstore or Columnstore?
I see Columnstore as a better option because you won’t be using SELECT that often if stuff was cached in something like Aerospike. Columnstore seems to be the better option for things like logs or small inserts that exceed billions of rows. Chat logs, server logs, small bits of data that can
(No, I don’t work for Twitter or Hypixel, this is really mostly out of curiosity to get a better understanding of real world use cases for Rowstore and Columnstore lol)