This message was deleted.

Here are a few things I can think of off the top of my head: Long • useful when you are ingesting records across large ranges of time in one job ... opens up fewer time chunks for processing • less fragmentation based on the above • for lower data volumes, allows better sharding for range partitioning Short • less contention with auto-compaction, i.e. can compact much more recent data • better query pruning for higher data volumes Others, please add ... 🙂 Thanks. John

What query patterns do you want to support? Are you just thinking about time partitioning here or also about rollup?

User behaviour analytics. Major pattern is DAU of ~3months, but sometimes 1year. Rollup is last resort because it brings low resolution.

Primary partitioning is about arranging the time line such that it serves good segments sizes and better pruning at query time. • Year partitioning means that you will have less overall segments, but for any query within a given year, even for just a few days or months would need to read all the segments for the year and then filter them individually on the time column within the segments. So, not very effective at pruning on time with smaller timeframes but probably best for the 1 year queries. • Doing it by hour would likely have many more segments, at least one per hour over the year (that's 24*365 = 8760 minimum for a year). Hour means that you could prune more effectively for very narrow queries, but this may also means that you need to read a lot more segments for the 3 month queries ( 3*30*24 = 2160 segments). • So without more info on data density over time, it sounds like month partitioning might be a better choice. I don't think you should think of rollup as a last resort. You can have detailed and rollup tables that address different needs which will help reduce overall resource requirements at query time, particularly in high concurrency, low latency situations. Also consider approximation functions that can be applied at ingestion (rollup) and/or query time to significantly accelerate distinct count and quantile functions. Secondary partitioning can also help significantly on pruning when using a column or set of columns frequently as filter criteria. It will split up segment files within a time chunk along the selected column(s), it stores a shard-spec which holds information about which values of the partitioning column(s) are in each segment and therefore enables pruning when filtering on those columns at query time.

Hi @Takaaki Nakama It is great to hear you are exploring range partitioning. The first consideration I would look at while deciding the segmentGranularity is to make sure that we end up with optimally sized segments. We want it to be in the 5M rows / 300-700 MB range. This allows for optimal scan times for the segments while balancing parallelism with the cost of context switching. A way to check scan times would be look at the right metric (specifically

query/segment/time

) being emitted and making sure that segment scan times are not elevated. We want it to be sub 500ms ideally. More info on the metric mentioned above can be found here: https://druid.apache.org/docs/latest/operations/metrics Enable metrics. The second consideration would be the typical intervals users issue queries against. We want them to be able to prune to only the segments that are required. Setting this to a year would yield the kinds of issues that Sergio mentioned in his post above. That being said I would shy away from a

YEAR

granularity unless there is a specific reason you would need that. For most cases

HOUR

or

DAY

and maybe

MONTH

should work fine.