I am designing a table of metrics in QuestDB. There will be few million rows per day they spread evenly thought the day. The rows are around 200 bytes, all numbers and timestamp. The data reading will span across multiple days usually, up to a year on edge cases.
I cannot decide I should make it partitioned by DAY or MONTH (or even YEAR). I understand I have to make this decision upfront since there is no way to switch from one to another.
Writing benefits from smaller partitions, reading from larger.
Small and large are relative to the RAM of the machine. You want a partition to fully fit the RAM so as a rule of thumb to be up to 10Gb for effective writing.
If your record is 200 bytes than it will be up to 400Mb of data 2 Million rows per day. This give you a freedom to use Monthly partitions which will take around 12 Gb per month if you can allocate 32-64 Gb of RAM for QuestDB.
Monthly partitions will be effective on multi-day scanning queries for longs period of time like monthly or annual spans. Daily partitions will see latency connected with multiple files opening on "cold" runs.
