working on a system where there are 30k sensors, some sensors generate out at 1hz, 10hz up to 1khz. Storing a spare table is an option but very slow. Considering storing this in a skinny table with symbol being the sensor name. if there is only a single table with a symbol and metric column, how does it handle multiple writes given each column is represented with an underlying file. Another option we are considering is store each sensor in their own table but not sure if there is any limit to having 30k tables. any feedback is greatly appreciated.
Hi @vtan ,
You can certainly use a row-modelled table i.e timestamp, symbol, metric, value.
Then, you can use secondary tables (and the soon to be released materialised views) to sample them by their frequency. So if you have a group of 10 Hz sensors, you could sample by 100T and put them in their own table.
Likewise if you havea group of 1Hz sensors, you could SAMPLE BY 1s and put them in their own table.
That will allow you to build a dense table (or materialised view).
You can have 30k tables, but that is probably a bit excessive.
In the future, we will release subpartitioning a table by symbol, which will simplify this issue for you.
Also, with the upcoming release of PIVOT, you could just go from skinny table to dense output just by adding the PIVOT statement around your subquery
1 Like
thank you all for great suggestions. we are still in ideation stage. Am gravitating toward a multi table approach using the sensors first letter to map to a table. for example a sensor starting with letter A will be stored in table ASensor.
schema will look like this
ts, action, sensor, value
action and sensor are symbol columns.
at most will use 26 tables because only 26 letters in alphabet. understand that some tables are heavy then others.
question with WAL, is there a WAL per table?
thanks
Table ingestion is independent for each table.
Each connection sending data will use its own WAL. If a WAL has already been made, and is available, it will re-use it.
Therefore, each connection can ingest independently into its own WAL.
Then, the sequencer will group these into a single transaction stream and apply this to the table.