I’m aware of Optimizing storage for historic time-series data, but in this specific question, I’m not interested data retention or moving old (cold) data to another table, etc.
I want to understand better what kind of basic optimizations/techniques should be used when creating a time series table with the following structure, stored on a 3-node CreateDB cluster, each node with 64 GB RAM , 16 CPU cores and 500 GB storage:
CREATE TABLE IF NOT EXISTS emre.test_table_001 ( time timestamptz NOT NULL, location_id text NOT NULL, device_id text NOT NULL, sensor_reading_01 double precision, sensor_reading_02 double precision, ... sensor_reading_50 double precision, );
(time, location_id, device_id) is unique .
Let’s assume that:
- this table stores sensor readings from various devices identified by
device_id, residing at a geographical location identified by a
- the sensor data is sent to a .NET application, and this .NET application INSERTs data into this table every few seconds.
- other .NET applications SELECT from this table to show various dashboards to users, whenever users request these via a web application.
- a user is generally interested in the time series data for 1 or more devices in a single location in a given web page request.
- currently there are about ~80 devices scattered to ~50 different locations, sending sensor readings every second.
- ‘ hot data ’ should be available for about 3 months, meaning, currently hot data is about 100 million rows , but as new locations and devices are added, the amount of ‘ hot data ’ is estimated to be on the order a few billion rows .
Based on these assumptions and the ‘naive’ table CREATE command above, what would CrateDB experts say, with respect to the following topics and table parameters:
- Indexing: as far as I understand CrateDB creates indexes automatically, but coming from a PostgreSQL background, I wonder if I should also create some other indexes manually based on the conditions specified in WHERE queries in .NET applications?
- CLUSTERED parameter as described in CREATE TABLE — CrateDB: Reference
- PARTITIONED BY parameter as described in CREATE TABLE — CrateDB: Reference
- number-of-routing-shards, as described in CREATE TABLE — CrateDB: Reference