There's event data with start timestamp and end timestamp. The data is huge so I'm planning to create cluster keys to increase query performance. I guess date column would be a good candidate for cluster key.
However, I'm confused with which timestamp column should I convert to date and use for clustering; because choosing the timestamp can depend on the question asked. How would I create cluster this table?
All clustering does is re-order your files (it also actually combines partitions if you have many small partitions). Just like dis-defragmentation tools used to do for hard drives. And just like for harddrives, files/data that is together, is faster. But here what you are really trying to do is not load data. Thus if you have data across years, and you have a WHERE start_time > '2020-08-12'::date you want to not touch most of those partition, thus save a lot of IO
You should thus "order it"/"cluster on" keys that you use the most in your queries. If you events end roughly within the same day as they start, start or end makes sense, but I would guess you would pick start, and truncate that to date in the cluster key definition.
The Strategies for Selecting Clustering Keys are quite to the point.
As for how to do it, if it exists already, you want the ALTER commands
