Hi,
I am currently in the initial phases of analyzing a multi-tenant SAAS product in the IOT space and planning to use Timescale DB.
As I am new to time series database modeling, I want to understand how to approach the database design for the following use case.
Our system will allow tenants to set up custom “meters.” For example, an Water Supply tenant can set up a meter for tracking the water usage of their customers, and a network router tenant can set up a meter to keep track of the bytes transferred via their router.
These tenants, in turn, ingest the respective instrument’s data of their customer’s usage and bill them based on their usages and optionally based on other attributes. For example, Water consumption can be billed at a standard rate based on consumption, and it can also be offered at different rates based on the attributes like source.
To address this requirement, I am thinking of using “Timescaledb” in the following way.
We are creating a table and its corresponding hyper table dynamically based on the inputs from the tenant when they set up the meter in our application UI.
CREATE TABLE meters (
id SERIAL PRIMARY KEY
tenant_id INTEGER NOT NULL REFERENCES tenants(id),
customer_id INTEGER NOT NULL REFERENCES customer(id),
name VARCHAR(32) NOT NULL
);
CREATE TABLE tenant1_water_meter_usage (
reported_at TIMESTAMPZ NOT NULL
meter_id INTEGER NOT NULL,
units_consumed DOUBLE PRECISION NOT NULL,
source VARCHAR(10), -- tenant defined enum values only supported
PRIMARY KEY (reported_at, meter_id)
);
SELECT create_hypertable('tenant1_water_meter_usage', 'reported_at');
SELECT add_dimension('tenant1_water_meter_usage', 'meter_id');
CREATE TABLE tenant2_driver_byter_recording (
reported_at TIMESTAMPZ NOT NULL
meter_id INTEGER NOT NULL,
bytes_transferred DOUBLE PRECISION NOT NULL,
PRIMARY KEY (reported_at, meter_id)
);
SELECT create_hypertable('tenant2_driver_byter_recording', 'reported_at');
SELECT add_dimension('tenant2_driver_byter_recording', 'meter_id');
We foresee each tenant will set up 30 to 50 meters to track the usage of their different products and services.
In the worst case, if we have 100 tenants with 50 meters each, we will have 5000 hypertables definition if we take this route.
Am I doing it right here?
Is there any other way of solving this problem?
