17 ms·
I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potenti
by remus 6y ago
I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potential for scaling issues at a very reasonable price.
- refenestrator 6y agoIn that specific case, you probably want to roll up that time-series data as it gets older, while keeping the full dataset in a flat file system for data science etc if you need it. You probably never need a millisecond-granularity data point from 6 months ago in your database.
- sitkack 6y agoThey probably shouldn't be rows at all. They are effectively low frequency sound files. I'd probably store them in parquet and use a FDW wrapper in Postgres.
- nomel 6y agoI have time series of 2d 64x64 sensor data, resulting in a few billion values that I'm trying to cram into some custom parquet format. I'm often surprised that it's 2021 and we're still stuck in tabular data, with n-dimensional arrays often not even considered.
- sitkack 6y agoThermal or depth map camera? Really depends on how you want to process and query it. At 16k per frame, I'd store each one sequentially. Do you need to only look at a single pixel across a million frames? Or do you process groups of 20-100 frames at a time?
- dls2016 6y agoI'm starting a project in this realm right now, though only three sensors to begin with. Generally I'm leaning towards "everything in Postgres", but I think I'm going to store the raw sensor data in the filesystem.
- ants_a 6y agoI did a set of benchmarks recently for multi-dimensional scientific sensor data. You most definitely don't want row per measurement in PostgreSQL, but you can get surprisingly good results where you store a block of results per row in an array. For even better results TimescaleDB and ClickHouse achieved approximately 2-6 bytes per float32 timestamped measurement, depending on the dataset and shape.
- dls2016 6y agoI intend to use TimescaleDB (+PostGIS) for all other data and last year researched this approach for the raw sensor data. I think they have improved compression since then. For my use, the raw sensor data should only be retrieved 1) to perform a windowed analysis (the results of which will be stored in PostgreSQL) or 2) to display for the user. I'm planning to archive the raw sensor data in files for archival, so I think it's easier to just jam the data in 15-minute netCDF files and call it a day. Will definitely keep an open mind.
- beckingz 6y agoThis is what I'm doing right now for my home sensor network. 3 ESP8266's with temperature, humidity, and light sensors sending a reading every second to a python app that writes a row to postgres on a raspberry pi 3. So far the hardest bit has been getting all the services to restart on pi restart. Postgres works just fine.
- dls2016 6y agoI'm going to be using an RPI as well and have been messing with systemd. I know people have opinions, but I don't and it was straightforward to configure a Python script to run as a service.
- neolog 6y agoHow frequently do they sample?
- 6y ago
- nitwit005 6y agoMost of the time, people just decide on the aggregates they want from those sensors in advance, and discard the raw data. I worked at a company that had some IOT devices logging "still alive and working fine" every couple of minutes. There was no point to holding onto that data. You only needed to know when the status changed or it stopped reporting in, as that's all anyone cared about.
- citrin_ru 6y ago> 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows PosgreSQL is a great OLTP DB, but this looks like a good fit for ClickHouse or some time series DB.