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

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: Thorsten.Schoening@AM-SoFT.de
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

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: Thorsten.Schoening@AM-SoFT.de
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

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.

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

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: Thorsten.Schoening@AM-SoFT.de
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

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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: Thorsten.Schoening@AM-SoFT.de
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

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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: Thorsten.Schoening@AM-SoFT.de
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

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Hi Peter,

> On 14. Oct, 2020, at 14:49, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> 
> "IOT" means "Internet of things".

IOT also means "index organized table"...

So much for using abbreviations. :-)

Cheers,
Paul