Time series data on Edge, TimescaleDB vs SQLEdge analysis
As part of the edge development we had recently received a chance to analyze TimescaleDB and SQLEdge. Will give some thoughts on the same.
Background
In edge development, there will be chances that we have to design databases on the edge itself, because of the limited bandwidth or disconnected scenarios of our edge devices.
As we have to store edge data from sensors, we are highly slanted to timeseries databases. So we considered the same as a primary qualifier for the databases.
Thus we wanted to compare some of the databases in the market, we skipped some cloud databases and well known databases due to multiple reasons listed below
SQL server / Cosmos DB / All cloud Databases
Skipping those databases due to the fact that we need an offline database without connectivity within the edge. So that we can deploy the same as dockerInfluxDB / other Timeseries Databases
Skipping those databases due to the fact that we need more time for our team to develop the same due to the learning curve within the team.
First Round
On our first round analysis we found that we have to exclude Postgres and SQL Lite also from our consideration due to the fact that they are not considered for timeseries datastore.
Final Round
On our final round we considered both TimescaleDB and SQLEdge. Now lets find some of its capabilities and some constraints or points to note while using the same.
SQLEdge
SQLEdge was nice database to compare between the both as its having a lot of capabilities and features offered out of the box, let find some of the key things below
Pros
- Time Series Capability
- Stream analytics Query Support
- Machine learning capabilities
- Docker support
- Sync with cloud SQL server
- Inherited features from conventional SQL database
- Minimal Learning Curve as SQL is more commonly used database language
Cons / Things to note
- Cost, all these features comes with cost per device. Its costing 10$/device /year while am writing the document and you can find the latest one on the link
TimescaleDB
TimescaleDB is an extended version of Postgres Database with some additional extension on it to provide timeseries data capabilities. you can find my post on enabling TimescaleDB on edge on the link
Pros
- Time Series Capability
- Opensource
- Docker support
- Inherited features from conventional SQL database
- Minimal Learning Curve as SQL is more commonly used database language
Cons / Things to note
- Manual Sync processing necessary if you have to sync the data with cloud or central server.
- TimescaleDB will create more tables and indexes for all the time series databases within the DB, this will lead to more physical size consumption on your disk space. This will be a constraint incase you are working with the limited disk space edge device.
Now you can choose between the two based on your priority.
Happy coding.
