4 ms·

> Create a table with time as a primary or sort key and you'll get fast queries with full SQL and joins. Be careful with this. You can easily end up with patho

> Create a table with time as a primary or sort key and you'll get fast queries with full SQL and joins. Be careful with this. You can easily end up with pathologically bad performance, resource usage, contention etc. if most of your requests end up being directed to the same workers or shards in a distributed system. Partitioning by time can cause this because most queries tend to be for recent time windows. Time can be a decent primary key withing a shard, but is normally a really bad choice for any key that's used to partition data between shards. Make sure you understand how your system partitions data so you don't fall into this. Apologies if that came across as pedantic - you probably already know this, but hopefully some other readers can be saved from learning this through painful experience.

deleted 8y ago
[deleted]
georgewfraser 8y ago
Column stores typically don’t use the primary key for anything except metadata. I think you’re thinking of distributed row stores that use the primary key to shard. In a column store, you would apply the sort/cluster key to your time column, which would partition the table within nodes, not across nodes. This will speed up queries that specify a narrow time range, and will not cause any of the pathological behavior you describe. Some column stores allow you to chhose a distribution key, which specifies how data is distributed across nodes. It can be used to optimize joins but it’s dangerous—it can cause exactly the problem you describe.
- alexhutcheson 8y ago
  Good clarification, thanks. I'm not familiar with the exact partitioning schemes of the various systems.
manigandham 8y ago
Right, if the primary key is being used to distribute data as a table then make time a secondary key for better distribution across partitions and nodes. Usually there's a difference between table primary/sort keys and distribution/shard keys. I should've made that more clear.

deleted 8y ago

[deleted]

georgewfraser 8y ago

Column stores typically don’t use the primary key for anything except metadata. I think you’re thinking of distributed row stores that use the primary key to shard. In a column store, you would apply the sort/cluster key to your time column, which would partition the table within nodes, not across nodes. This will speed up queries that specify a narrow time range, and will not cause any of the pathological behavior you describe. Some column stores allow you to chhose a distribution key, which specifies how data is distributed across nodes. It can be used to optimize joins but it’s dangerous—it can cause exactly the problem you describe.

alexhutcheson 8y ago
Good clarification, thanks. I'm not familiar with the exact partitioning schemes of the various systems.

manigandham 8y ago

Right, if the primary key is being used to distribute data as a table then make time a secondary key for better distribution across partitions and nodes. Usually there's a difference between table primary/sort keys and distribution/shard keys. I should've made that more clear.