Similar to Improving performance with a common low-cardinality field
We get a large dataset and we load it by source. Let's say team_id. We currently have our data partitioned by team_id and then by the timestamps of events. However, this does mean the team_id ends up being repeated over each monthly (for e.g.) partition for that team. Is there a way to save space both in the tables and in any indexes we use that would include team_id?
For a very small, mostly constant number of teams and large cardinalities of otherwise narrow rows, it can make sense to have a separate table per team - and no team_id column at all, which mainly benefits index size (and performance). Especially if each team table is, in turn, partitioned (by time) like in your case.
I would only consider the added overhead if the bulk of your queries targets a single team. Else you create a lot of overhead when addressing multiple or all teams.
You do save some overhead by removing one level of partitioning in your case.
No, you have to keep the team_id in every row of every partition, even if it is the same for all rows in a partition.
