What's your experience with using Postgres in IoT-contexts?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

What's your experience with using Postgres in IoT-contexts?

Thorsten Schöning
Hi all,

TL;DR:

Does anyone actually use Postgres with ARM based low performance
hardware and only 256 MiB of RAM? What are your experiences in other
stripped down environments? Is there some lower RAM limit with which
using Postgres doesn't make any sense anymore? Is Postgres able to
compete with SQLite regaridng lower performance boundaries or is there
some point at which SQLite will be the better choice, because Postgres
needs some more resources to work properly?

Thanks!

Background:

I have some server side backend exposing web services and storing data
in Postgres. The stored data are telegrams from smart metering
devices, lots of them, mostly small. The provided web services take
care of generating reports etc. based on those telegrams.

It's now considered to put large parts of that backend onto some ARM
based IoT device, simply because that device would do a lot of the
same things. The available backend is Java-based, so it would simply
be possible as well and I already ran things on some Raspi-like
hardware and things worked pretty well. OTOH, the targetted hardware
is less capable than some Raspi, we talk about only 256 MiB of RAM
currently. The exact details of the hardware are not clear yet.

In the easiest case, the newly created device would only need to store
some hundreds of telegrams per day in some known time window and
forward those telegrams afterwards. In most cases it would be those of
the same day only, but might be those of the last X few days as well
in case of problems. In the long term, though, that device should be
able to store telegrams for years and might generate reports on it's
own as well.

I've already found some interesting infos like the following, but
would be interested in other expereinces as well.

https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Tony Shelver
Not sure about PG in that environment. Have you thought about something like H2 java database?  https://www.h2database.com/html/main.html

That is included as the standarDB in a vehicle tracking system we use, although we have re[placed with PG.

On Mon, 5 Oct 2020 at 11:20, Thorsten Schöning <[hidden email]> wrote:
Hi all,

TL;DR:

Does anyone actually use Postgres with ARM based low performance
hardware and only 256 MiB of RAM? What are your experiences in other
stripped down environments? Is there some lower RAM limit with which
using Postgres doesn't make any sense anymore? Is Postgres able to
compete with SQLite regaridng lower performance boundaries or is there
some point at which SQLite will be the better choice, because Postgres
needs some more resources to work properly?

Thanks!

Background:

I have some server side backend exposing web services and storing data
in Postgres. The stored data are telegrams from smart metering
devices, lots of them, mostly small. The provided web services take
care of generating reports etc. based on those telegrams.

It's now considered to put large parts of that backend onto some ARM
based IoT device, simply because that device would do a lot of the
same things. The available backend is Java-based, so it would simply
be possible as well and I already ran things on some Raspi-like
hardware and things worked pretty well. OTOH, the targetted hardware
is less capable than some Raspi, we talk about only 256 MiB of RAM
currently. The exact details of the hardware are not clear yet.

In the easiest case, the newly created device would only need to store
some hundreds of telegrams per day in some known time window and
forward those telegrams afterwards. In most cases it would be those of
the same day only, but might be those of the last X few days as well
in case of problems. In the long term, though, that device should be
able to store telegrams for years and might generate reports on it's
own as well.

I've already found some interesting infos like the following, but
would be interested in other expereinces as well.

https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Thorsten Schöning
Guten Tag Tony Shelver,
am Montag, 5. Oktober 2020 um 15:44 schrieben Sie:

> Not sure about PG in that environment. Have you thought about something
> like H2 java database?  https://www.h2database.com/html/main.html

Yes, like SQLite as well. The point is that I was really interested in
keeping as much as possible and we e.g. use some Postgres-specific
constructs currently. Of course things can be changed, but the
question is if it's necessary at all, especially after reading the
PDF:

https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf

> That is included as the standarDB in a vehicle tracking system we use,
> although we have re[placed with PG.

And your tracking system is more like the server that I already have
or an embedded system within the vehicles themself?

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Diego-2
In reply to this post by Thorsten Schöning

I use pg in my raspberrypy 3b, for a weather station and works fine, but to be honest, I have a sencond raspi with a pgbouncer and a pendrive of 256GBs as data storage

In other, I have a zabbix server with pg monitoring aboutn 100 devices, but with an external hdd.

but, at the end of the day, I think is not the best idea.


On 2020-10-05 06:20, Thorsten Schöning wrote:
Hi all,

TL;DR:

Does anyone actually use Postgres with ARM based low performance
hardware and only 256 MiB of RAM? What are your experiences in other
stripped down environments? Is there some lower RAM limit with which
using Postgres doesn't make any sense anymore? Is Postgres able to
compete with SQLite regaridng lower performance boundaries or is there
some point at which SQLite will be the better choice, because Postgres
needs some more resources to work properly?

Thanks!

Background:

I have some server side backend exposing web services and storing data
in Postgres. The stored data are telegrams from smart metering
devices, lots of them, mostly small. The provided web services take
care of generating reports etc. based on those telegrams.

It's now considered to put large parts of that backend onto some ARM
based IoT device, simply because that device would do a lot of the
same things. The available backend is Java-based, so it would simply
be possible as well and I already ran things on some Raspi-like
hardware and things worked pretty well. OTOH, the targetted hardware
is less capable than some Raspi, we talk about only 256 MiB of RAM
currently. The exact details of the hardware are not clear yet.

In the easiest case, the newly created device would only need to store
some hundreds of telegrams per day in some known time window and
forward those telegrams afterwards. In most cases it would be those of
the same day only, but might be those of the last X few days as well
in case of problems. In the long term, though, that device should be
able to store telegrams for years and might generate reports on it's
own as well.

I've already found some interesting infos like the following, but
would be interested in other expereinces as well.

https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf

Mit freundlichen Grüßen,

Thorsten Schöning

Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Tony Shelver
In reply to this post by Thorsten Schöning


On Mon, 5 Oct 2020 at 16:34, Thorsten Schöning <[hidden email]> wrote:
Guten Tag Tony Shelver,
am Montag, 5. Oktober 2020 um 15:44 schrieben Sie:

> Not sure about PG in that environment. Have you thought about something
> like H2 java database?  https://www.h2database.com/html/main.html

Yes, like SQLite as well. The point is that I was really interested in
keeping as much as possible and we e.g. use some Postgres-specific
constructs currently. Of course things can be changed, but the
question is if it's necessary at all, especially after reading the
PDF:

https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf

> That is included as the standarDB in a vehicle tracking system we use,
> although we have re[placed with PG.

And your tracking system is more like the server that I already have
or an embedded system within the vehicles themself?

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow


.

 My tracking system stores, analyzes and reports on multiple events occuring in the vehicle.  This can include speed, change in GPS location (tracking), vehicle data / events such as RPM, engine temperature, change in direction, data from the OBDII system (vehicle diagnostics from engine, transmission, cooling and electrical systems, chassis and so on), data from the tracker's built-in accelerometer, connect / disconnect, ignition on / off and so on

This data is used to trigger operator / admin messages and warnings, report historical vehicle routes on map, follow current vehicle position and route, geofencing, and more.

Each vehicle reports position to the database at least every 30 seconds when moving, along with any other data as it happens.  So it's quite a bit.

H2 seemed to handle it fine for a limited number of vehicles, but we moved it to PG once we moved to production.
Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Thorsten Schöning
Guten Tag Tony Shelver,
am Dienstag, 6. Oktober 2020 um 16:33 schrieben Sie:

> Each vehicle reports position to the database at least every 30 seconds
> when moving, along with any other data as it happens.  So it's quite a bit.

> H2 seemed to handle it fine for a limited number of vehicles, but we moved
> it to PG once we moved to production.

But where does Postgres run in your setup? :-) On the tracked vehicle
itself with limited resources most likely or do you simply send things
over network to some cloud/server/...? Your first paragraph reads like
the former, but the second like the latter. :-)

If it's on the vehicle, I would be interested to somewhat know which
hardware you use, to compare what I have in mind. Thanks!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Tony Shelver
Everything is sent tot he cloud from the vehicle.  The trackers we currently run don't have the power to be able to do much.
There are a few vehicle devices we have access to (hub systems with a central computer / cpu / storage) which could handle a database, but in terms of our business requirements, everything is needed at the cloud / server level for analysis.


On Wed, 7 Oct 2020 at 08:28, Thorsten Schöning <[hidden email]> wrote:
Guten Tag Tony Shelver,
am Dienstag, 6. Oktober 2020 um 16:33 schrieben Sie:

> Each vehicle reports position to the database at least every 30 seconds
> when moving, along with any other data as it happens.  So it's quite a bit.

> H2 seemed to handle it fine for a limited number of vehicles, but we moved
> it to PG once we moved to production.

But where does Postgres run in your setup? :-) On the tracked vehicle
itself with limited resources most likely or do you simply send things
over network to some cloud/server/...? Your first paragraph reads like
the former, but the second like the latter. :-)

If it's on the vehicle, I would be interested to somewhat know which
hardware you use, to compare what I have in mind. Thanks!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Peter J. Holzer
In reply to this post by Thorsten Schöning
On 2020-10-05 11:20:20 +0200, Thorsten Schöning wrote:
> Does anyone actually use Postgres with ARM based low performance
> hardware and only 256 MiB of RAM?
[...]
> Background:
>
> I have some server side backend exposing web services and storing data
> in Postgres. The stored data are telegrams from smart metering
> devices, lots of them, mostly small. The provided web services take
> care of generating reports etc. based on those telegrams.
[...]
> In the easiest case, the newly created device would only need to store
> some hundreds of telegrams per day in some known time window and
> forward those telegrams afterwards. In most cases it would be those of
> the same day only, but might be those of the last X few days as well
> in case of problems. In the long term, though, that device should be
> able to store telegrams for years and might generate reports on it's
> own as well.

Do you plan to move some of that reporting to the IoT devices? (Maybe
equip them with a display with a dashboard, or something like that)

Because for simply storing a few hundred records which are written and
read sequentially, an RDBMS of any kind seems overkill. I'd simply write
them to files.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Thorsten Schöning
Guten Tag Peter J. Holzer,
am Samstag, 10. Oktober 2020 um 10:56 schrieben Sie:

> Do you plan to move some of that reporting to the IoT devices? (Maybe
> equip them with a display with a dashboard, or something like that)

Not necessarily with a display, but something like a dashboard for a
web-UI on the device itself definitely needs to be kept in mind.
Though, some display has been considered as well.

> Because for simply storing a few hundred records which are written and
> read sequentially, an RDBMS of any kind seems overkill. I'd simply write
> them to files.

That's still considered as well and what's done right now. But at some
point we need to maintain individual logical devices where those
telegrams come from anyway. It as well has been considered to store
only metadata in the database and telegrams in files, but as telegrams
are small mostly, this doesn't seem worth it. And because telegrams
are small, we would need some kind of container format anyway to not
run out of inodes and stuff like that too easily.

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Peter J. Holzer
On 2020-10-10 11:22:42 +0200, Thorsten Schöning wrote:
> Guten Tag Peter J. Holzer,
> am Samstag, 10. Oktober 2020 um 10:56 schrieben Sie:
>
> > Do you plan to move some of that reporting to the IoT devices? (Maybe
> > equip them with a display with a dashboard, or something like that)
>
> Not necessarily with a display, but something like a dashboard for a
> web-UI on the device itself definitely needs to be kept in mind.
> Though, some display has been considered as well.

Yeah, then it makes sense to use a database.


> > Because for simply storing a few hundred records which are written and
> > read sequentially, an RDBMS of any kind seems overkill. I'd simply write
> > them to files.
>
> That's still considered as well and what's done right now. But at some
> point we need to maintain individual logical devices where those
> telegrams come from anyway. It as well has been considered to store
> only metadata in the database and telegrams in files,

I wasn't thinking of storing files in addition to the database, but
ditching the database completely. Since what you were describing is a
simple queue of telegrams waiting to be uploaded to a server and that
queue was expected to be relatively short (a few hundred to a few
thousand entries) I would do it like this:

Each telegram is written to a unique file in a directory "upload-queue".
Periodically (or triggered by inotify) the uploader checks if there is
something to upload. After it has successfully uploaded a telegram, it
deletes the file.

Very simple and robust.

> And because telegrams are small, we would need some kind of container
> format anyway to not run out of inodes and stuff like that too easily.

Not necessarily. You would have to allocate the appropriate number of
inodes, of course. Using one file per telegram wastes some space, but
for a few thousand telegrams that may not matter[1]. Indeed, with ext4 you
could make the inodes large enough that all or most of your telegrams
fit inside the inode and don't need an extra data block at all.

But that's just if you *only* need a simple queue. If you want to keep
your data in a database for other reasons, you can use it for the queue
as well.

        hp

[1] I have no idea what the smallest flash chips are these days.

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Thorsten Schöning
Guten Tag Peter J. Holzer,
am Samstag, 10. Oktober 2020 um 12:00 schrieben Sie:

> Not necessarily. You would have to allocate the appropriate number of
> inodes, of course. Using one file per telegram wastes some space, but
> for a few thousand telegrams that may not matter[1]. Indeed, with ext4 you
> could make the inodes large enough that all or most of your telegrams
> fit inside the inode and don't need an extra data block at all.

Thanks for suggesting what I had noted at least worth discussing as
well already and what "other interested parties" said to be a really
stupid idea. Doesn't seem that stupid to us two obviously, especially
if ext4 is already used anyway. ;-)

But it's really not only about a simple queue: I want to have long
term storage and access to individual telegrams according different
criteria like individual devices, when received etc.

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Hans Schou-5
> I want to have long term storage and access to individual telegrams

An IOT is not designed for that. It is used for control or delivery of data to a server.
You could have a PostgreSQL-client in the IOT but an MQ might be better.

Long term storage also means backup and recovery and I don't think you have that planned for your IOT.

./hans
Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Peter J. Holzer
On 2020-10-13 06:55:52 +0200, chlor wrote:
> > I want to have long term storage and access to individual telegrams
>
> An IOT is not designed for that. It is used for control or delivery of
> data to a server.

That's a rather dogmatic and narrow-minded point of view. "IOT" means
"Internet of things". There are many things which which could benefit
from network connectivity and don't necessarily need a central server or
may even act as servers for other "things".

It all depends on the application and the "thing".

> Long term storage also means backup and recovery and I don't think you
> have that planned for your IOT.

That depends on how valuable those data are.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Paul Förster
Hi Peter,

> On 14. Oct, 2020, at 14:49, Peter J. Holzer <[hidden email]> wrote:
>
> "IOT" means "Internet of things".

IOT also means "index organized table"...

So much for using abbreviations. :-)

Cheers,
Paul



Reply | Threaded
Open this post in threaded view
|

Re: What's your experience with using Postgres in IoT-contexts?

Jonathan Strong
In reply to this post by Peter J. Holzer

On Wed, Oct 14, 2020 at 8:49 AM Peter J. Holzer <[hidden email]> wrote:
On 2020-10-13 06:55:52 +0200, chlor wrote:
> > I want to have long term storage and access to individual telegrams
>
> An IOT is not designed for that. It is used for control or delivery of
> data to a server.

That's a rather dogmatic and narrow-minded point of view. "IOT" means
"Internet of things". There are many things which which could benefit
from network connectivity and don't necessarily need a central server or
may even act as servers for other "things".

It all depends on the application and the "thing".

> Long term storage also means backup and recovery and I don't think you
> have that planned for your IOT.

That depends on how valuable those data are.

        hp

--
   _  | Peter J. Holzer    | 


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