Re: What's your experience with using Postgres in IoT-contexts? - Mailing list pgsql-general

Indeed. IoT architecture also begs the question of "when" detailed historical data may be needed, and how Edge Computing can factor into the overall solution model. Detailed transactions may live "at the edge" while needed aggregate / extract info is communicated to a central server to support real time response. But those left-behind detailed transactions may (or may not) follow later on via a lower priority / non- real time path if relevant and eventually valuable. Some examples I've had to work with:

When calculating real-time Equity / Security Index values, you might capture tick by tick data for each security in an index valuation formula. Just one security in an index (e.g., MSFT) could easily generate more than 100,000 ticks per day. One of the Large Cap indices currently has about 3,500 stocks in it. They might not all trade as frequently as MSFT, but you might see anywhere from 10 million to 100 million data points in a day. While this differs from IoT in that data sources aren't physically separated and as numerous as individual IoT devices, the challenge is similar in that a good real time architecture makes use of needed data at various stages in the process flow (and data network flow) and defers functions that can wait, including perhaps committing full details of every transaction to centralized long term storage, as long as the computed Index value can be published in real time.

Years ago we developed an online gaming platform supporting hundreds of thousands of concurrent users who came in from numerous countries around the world. Real-time scoring and chat communications posed challenges similar to the Equity Index solution above. We needed to be able to accept play data from thousands of concurrent players and have a game (or chat room) respond in near real time, but full detailed data could be queued up and gradually transmitted, processed, assimilated and committed to long term storage.

In health care data collection we see similar challenges: real time IoT biosensors may capture blood oximetry, glucose, lactate info, heart rate, etc. Some of this may be critical for real time monitoring and processing. Some gets processed "at the Edge" - aggregated, filtered, interpreted, etc. before getting to central / long term storage.

Deciding the level of detail that actually has to reach centralized long term storage - and when - is typically a non-trivial exercise. When you look at examples like monitoring a jet engine, gas turbines, or an air conditioner manufacturer and service company (one of my past clients) monitoring hundreds of thousands of HVAC units distributed around the country, data samples go past terabytes to petabytes, exabytes and more.

While you need to figure out how to trim the raw data to amounts that can reasonably be stored and managed, I've seen too many cases of being overly aggressive in discarding data thought to be superfluous; thoughtful analysis is critical here.

- Jon