Timescaledb for edge devices

[archerian]

General question, I've seen performance metrics for (dedicated) server grade data, how suited is Timescaledb for managing data on lower powered HW, like single/dual core CPUs with 4-8GB RAM on a containerized system?

[mfreed]

We test timescaleDB on low resource ARM32-bit devices as part of build process, and we have production deployments on edge IOT devices akin to Raspberry Pi. So certainly your config should be fine!

A good testing platform for you would actually be the dev instances you can use on Timescale Cloud, which have 2vCPU (one physical core) and 4GB RAM.

[wrobell]

I am in the process of migrating my edge processing framework and projects from HDF5 to TimescaleDB.

When looking into TimescaleDB, I was considering

• chunking and compression implementation (i.e. HDF5 does not allow last chunk to be uncompressed, which causes implementation or data loss issues)
• probability of data loss of improperly shutdown devices (quite an issue with HDF5 in my experience)
• autorecovery of improperly shutdown device (hardly possible with HDF5)
• single writer multiple readers scenario (some documentation suggests HDF5 is quite capable here, but HDF5 requires writer to start first)

I have three Raspberry Pi devices

• rpi4 with 4gb ram

  • collects data from 16 sensors every second plus 6 event driven sensors; this project is not migrated yet, but I have added one more sensor to this rpi, which data is saved into TimescaleDB; I have three weeks of data in this database
  • I synchronize databases of two other devices (see below) with TimescaleDB on rpi4 via custom script (I use different architectures, so streaming replication is not an option; logical replication does not work with TimescaleDB)
  • I have archive data from two former projects migrated to TimescaleDB - one has 2.5 months of data and other project has nearly two years of data; for the latter, I got reduction of original dataset from about 3GB to 0.5GB compressed (or nearly 10GB uncompressed)
  • Grafana is used to visualize data from multiple databases on this device

• rpi zero as GPS tracker with data stored in TimescaleDB

  • the TimescaleDB contains 110 hours of GPS data
  • the device survived about 80 improper shutdowns, no data loss so far

• rpi zero with two projects deployed

  • first project reads data from solar panel inverter every second, it has about 10 months of data; the data is migrated to TimescaleDB and for last 3 weeks the project is saving data to TimescaleDB directly; the uncompressed table is about 1.2GB, which gets to about 70MB after compression; this is as twice as much comparing to HDF5, but considering benefits of TimescaleDB, I find this good compromise
  • second project fetches ADB/Mode-S messages; my initial tests show the TimescaleDB compressed database will have about 13GB for about year of data that I gathered so far, about the same size as HDF5 files
  • the device survived about 5 improper shutdowns while running TimescaleDB database, which resulted in no data loss so far
  • I am using scripts written in R to generate various reports, which pull data from this device

As you can see, TimescaleDB has certain limitations and it needs more work to use all PostgreSQL features, but despite these limitations, after about 3 weeks of using it, I find its current version as very good storage solution for edge processing projects.

[mfreed]

Hi @wrobell thanks for the detailed report.

Would you mind summarizing what you think TimescaleDB's limitations are given the above? Overall it seemed like a pretty strong argument for TimescaleDB, and the only thing I could notice is one deployment where TimescaleDB compressed from 1.2GB to 70MB versus ~35NB for HSF5.

[wrobell]

The compression example I provided is probably like comparing apples to oranges - different chunks, not sure about the compression parameters or even algorithms itself. The difference in this particular example did not matter, so I decided not to investigate it more and I accepted it as it is. I should be more clear about this.

The limitations of TimescaleDB

• no logical replication (and I cannot use streaming one due to different architectures of my machines)
• cannot dump data of a single table; even worse, making a backup of single table creates file without error and without data
• restore of database backup requires bit more than simply running pg_restore
• large data import will create uncompressed database first and, as discussed in other ticket, it takes time to start compressing it
• it seem it is not possible to configure the compression algorithms

[wrobell]

I have finally migrated my project, which fetches ADB/Mode-S messages on Raspberry Pi Zero, to use TimescaleDB. It has over 520 million entries and takes 6.5 GB of storage (45 GB uncompressed, my initial assessment was more pessimistic).

This project also exposes certain limitation of current TimescaleDB chunking approach. It is time based (i.e. hour, day). I prefer to have chunks as equally as possible to be filled with data, so I had to choose 1 day chunk size. 1 hour chunks would contain from zero to over 200 thousands of records. The attached screenshot should illustrate the problem - it shows number of ADB/Mode-S messages received per time. Maybe count based chunking will be possible in the future as well?