I am using Influxdb in many projects, but I am searching for an alternative and found QuestDB. I want to give it a try, and would like to retain the schema I have at the moment, but converting to QuestDB best practices.
So here is my influxdb schema:
I have multiple measurements, but all of them follow this pattern:
To convert a schema like this to QuestDB, we need to be aware of data types and default configuration:
CREATE TABLE table_name (
timestamp TIMESTAMP, -- InfluxDB uses an implicit timestamp; QuestDB needs it explicitly
name SYMBOL CAPACITY 50000, -- Tag converted to SYMBOL, if your tag has a limited number of values (even high cardinality is fine). A SYMBOL looks like a string, but behind the scenes is an enumerated value converted to a number, so very efficient to store and query. Adjust capacity to the expected cardinality for better performance
description varchar, -- Tag converted to varchar, as description is probably not a good candidate for an enumerated value
unit SYMBOL CAPACITY 256, -- Tag converted to SYMBOL
id UUID, -- Tag converted to UUID, supposing your ID is a UUID, otherwise it could be a varchar, or depending on how you store and query, a SYMBOL might be appropriate, depending if this is a mostly unique ID or not
value DOUBLE -- Field stored as a numeric column
)
TIMESTAMP(timestamp) -- Which timestamp column will be the designated timestamp. QuestDB partitions and indexes data based on this column, which ideally should be used as a filter in most of your queries
PARTITION BY DAY -- You can chooose hour, day, week, month, or year. You want a balance between manageable partition size (a few Gigabytes at most) and how many partition you need to open in your typical queries (not thousands ideally).
WAL -- Unless you want legacy features
DEDUP(timestamp, name) --in case you want to have built-in deduplication supporting UPSERTs
;