Hi I’m new to TimescaleDB but I’m excited to migrate over to it. We’re currently planning out our table schemas and I’m wondering what the best practices are.
We have multiple types of devices/data sources that have some overlapping fields and a bunch of non-overlapping fields. We often need to query and visualize data from different devices together.
Is best practice to store them in separate hypertables and do joins using time buckets? Or is it recommended to use one big hypertable and let compression squash all of the unused columns (when segmented by device)? For reference, we’re coming from Influx where we just had a big bucket for everything.
Hi @beasteers welcome to the Timescale community forum. I’ve dropped you a DM about office hours today, as this is a great question for that setting If you can’t make it, though, I’m sure one of the team or community will be able to help you out here.
We covered this one in the office hours, which was great!
The general consensus was that for a small amount of data with simple joins you’re better off with a hypertable per measurement (I suppose unless those measurements are always taken and used together, like two bits of data from one sensor).
Having a wide format might eventually compress well (although still worse than single tables I think, as you would have a lot of null padding), but it certainly won’t be doing you any favours in an uncompressed format (ie: the most recent data) because it sounds like your query patterns often use multiple measurements but not usually (ever?) every row in the table.
If you can’t make it, though, I’m sure one of the team or community will be able to help you out here.