I have a background in relational databases and new to Prometheus. I wonder why Prometheus is not a good choice for high cardinality data? Why do I need to use low cardinality data? It's exact opposite from SQL DBs.
What are the technical reason for that?
As far as I know, Prometheus doesn't mind high-cardinality data. What Prometheus doesn't like is high-cardinality labels.
Let's start with Prometheus official documentation, it gives a good high-level explanation why:
CAUTION: Remember that every unique combination of key-value label pairs represents a new time series, which can dramatically increase the amount of data stored. Do not use labels to store dimensions with high cardinality (many different label values), such as user IDs, email addresses, or other unbounded sets of values.
The important part is really that unique combinations of key-value creates new time series in Prometheus.
For instance, if you store a gauge-type metric registration_complete that records the time a user took to complete his/her registration form, Prometheus won't have any issue with that. You'll have one time series that have hundred of thousand of values: one value for each user that registered (how long it took). You'll be able to graph that metric over time, get the p95, etc.
If you want to add some cardinality to it, you could add a label like region: US-east, Asia-Pacific, etc. You'll be able to graph all regions and compare them or maybe group them all. The number of regions is probably low (<10) and it is certainly bound. If on AWS, there's a fixed number of regions and they don't change much over time. Sure, AWS might add, remove or rename a region, but that's not changing by the minute and there's not thousands of them.
So to come back to what Prometheus advise against: you should not create high-cardinality labels. You should not add a user_id label to your registration_complete metric. If you do so, you'll have hundred of thousands of different time series, one for each user!, and they will all have only one data point. That's really the worst case scenario.
In this case, in order to graph the registration_complete metric over all labels, Prometheus will have to query all the individual time series (the X thousands of them) and aggregate all of them.
You said you come from a SQL background, so I'll attempt an analogy. Unique combination of key-value label pairs creating new time series in Prometheus amounts to individual tables in SQL. Having a label user_id equals to having one table per user_id.
Note: not all TSDB work the same way and I can't speak for them all.
