Thread: PostgreSQL with PostGIS on embedded hardware

PostgreSQL with PostGIS on embedded hardware

From
Paolo Rizzi
Date:
Hi all,
recently I came across a question from a customer of mine, asking me if
it would feasible to run PostgreSQL along with PostGIS on embedded hardware.
They didn't give me complete information, but it should be some kind of
industrial PC with a 600MHz CPU. Memory should be not huge nor small,
maybe a couple of GBytes, hard disk should be some type of industrial
Compact Flash of maybe 16 GBytes.

They are thinking about using this setup on-board of public buses and
trams, along with a GPS receiver, for self-localization. So that when
the bus or tram enters defined zones or passes near defined points,
events are triggered.
The database could probably be used completely read-only or almost that.

What performances do you think would be possible for PostgreSQL+PostGIS
on such hardware???

Bye
Paolo

Re: PostgreSQL with PostGIS on embedded hardware

From
"Joshua D. Drake"
Date:
On Fri, 2009-05-08 at 18:06 +0200, Paolo Rizzi wrote:
> Hi all,
> recently I came across a question from a customer of mine, asking me if
> it would feasible to run PostgreSQL along with PostGIS on embedded hardware.
> They didn't give me complete information, but it should be some kind of
> industrial PC with a 600MHz CPU. Memory should be not huge nor small,
> maybe a couple of GBytes, hard disk should be some type of industrial
> Compact Flash of maybe 16 GBytes.
>

Well the CPU is slow the but rest isn't so bad.

> They are thinking about using this setup on-board of public buses and
> trams, along with a GPS receiver, for self-localization. So that when
> the bus or tram enters defined zones or passes near defined points,
> events are triggered.
> The database could probably be used completely read-only or almost that.
>
> What performances do you think would be possible for PostgreSQL+PostGIS
> on such hardware???
>

If you aren't doing a lot of writing I don't see a huge barrier to this.

Sincerely,

Joshua D. Drkae


--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


Re: PostgreSQL with PostGIS on embedded hardware

From
Paolo Rizzi
Date:
Joshua D. Drake ha scritto:
> On Fri, 2009-05-08 at 18:06 +0200, Paolo Rizzi wrote:
>> Hi all,
>> recently I came across a question from a customer of mine, asking me if
>> it would feasible to run PostgreSQL along with PostGIS on embedded hardware.
>> They didn't give me complete information, but it should be some kind of
>> industrial PC with a 600MHz CPU. Memory should be not huge nor small,
>> maybe a couple of GBytes, hard disk should be some type of industrial
>> Compact Flash of maybe 16 GBytes.
>>
>
> Well the CPU is slow the but rest isn't so bad.
>
>> They are thinking about using this setup on-board of public buses and
>> trams, along with a GPS receiver, for self-localization. So that when
>> the bus or tram enters defined zones or passes near defined points,
>> events are triggered.
>> The database could probably be used completely read-only or almost that.
>>
>> What performances do you think would be possible for PostgreSQL+PostGIS
>> on such hardware???
>>
>
> If you aren't doing a lot of writing I don't see a huge barrier to this.
>
> Sincerely,
>
> Joshua D. Drkae
Thank you!!!
Indeed I also think it could be done, but I searched the Web and found
no previous experience of the like, so maybe it's just too weird putting
a spatial-enabled RDBMS on-board buses...!?!

Anyway I found the TurnKey PostgreSQL appliance. It's a small
Ubuntu-based live-cd with PostgreSQL and PostGIS preconfigured.
I could suggest these people to try it out on hardware similar to what
they intend to use, to have a feel of how it behaves.

Bye
Paolo




Re: PostgreSQL with PostGIS on embedded hardware

From
"Fernando Hevia"
Date:

> -----Mensaje original-----
> De: Paolo Rizzi
>
> Hi all,
> recently I came across a question from a customer of mine,
> asking me if it would feasible to run PostgreSQL along with
> PostGIS on embedded hardware.
> They didn't give me complete information, but it should be
> some kind of industrial PC with a 600MHz CPU. Memory should
> be not huge nor small, maybe a couple of GBytes, hard disk
> should be some type of industrial Compact Flash of maybe 16 GBytes.
>
> They are thinking about using this setup on-board of public
> buses and trams, along with a GPS receiver, for
> self-localization. So that when the bus or tram enters
> defined zones or passes near defined points, events are triggered.
> The database could probably be used completely read-only or
> almost that.
>

Hi Paolo,

I'm not really responding to your question. It happens that I collaborated
on a postgres/postgis based solution for public transportation and the
motive why you are trying to put the database in the embedded hardware is
puzzling to me. In this solution we used a centralized PG database, the
devices in buses captured geographical position and other business related
data and fetched it by cellular network to the central server.
Calculations on position where made on the server and related events where
fetched back accordingly.

If possible, I would like to know what drives you to put a database on each
device? You dont have a wireless link on each unit?


> What performances do you think would be possible for
> PostgreSQL+PostGIS on such hardware???

We never considered that solution so I couldn´t say.

>
> Bye
> Paolo
>

Regards,
Fernando.


Re: PostgreSQL with PostGIS on embedded hardware

From
Paolo Rizzi
Date:
>
>
>> -----Mensaje original-----
>> De: Paolo Rizzi
>>
>> Hi all,
>> recently I came across a question from a customer of mine,
>> asking me if it would feasible to run PostgreSQL along with
>> PostGIS on embedded hardware.
>> They didn't give me complete information, but it should be
>> some kind of industrial PC with a 600MHz CPU. Memory should
>> be not huge nor small, maybe a couple of GBytes, hard disk
>> should be some type of industrial Compact Flash of maybe 16 GBytes.
>>
>> They are thinking about using this setup on-board of public
>> buses and trams, along with a GPS receiver, for
>> self-localization. So that when the bus or tram enters
>> defined zones or passes near defined points, events are triggered.
>> The database could probably be used completely read-only or
>> almost that.
>>
>
> Hi Paolo,
>
> I'm not really responding to your question. It happens that I collaborated
> on a postgres/postgis based solution for public transportation and the
> motive why you are trying to put the database in the embedded hardware is
> puzzling to me. In this solution we used a centralized PG database, the
> devices in buses captured geographical position and other business related
> data and fetched it by cellular network to the central server.
> Calculations on position where made on the server and related events where
> fetched back accordingly.
>
> If possible, I would like to know what drives you to put a database on each
> device? You dont have a wireless link on each unit?
Indeed I was as puzzled as you when they described me their idea, but I
think it makes sense. The buses and trams have to be independent of the
radio link  because there are certain operations that have to performed
at the right moment in the right place (like oiling wheels or letting
down sand or salt or some other action).
However they _are_ going to use a centralized server, and putting the
same technology (PostgreSQL/PostGIS) both on-board and on-center, would
let them simplify development, configuration and maintenance.
Now that hardware is continuously getting cheaper and more powerful,
moving "intelligence" on-board may be a smart move...

>
>
>> What performances do you think would be possible for
>> PostgreSQL+PostGIS on such hardware???
>
> We never considered that solution so I couldn´t say.
In fact I searched the Web and found nobody that did that before :-)

>
>> Bye
>> Paolo
>>
>
> Regards,
> Fernando.

Bye
Paolo




Re: PostgreSQL with PostGIS on embedded hardware

From
PFC
Date:
> They didn't give me complete information, but it should be some kind of
> industrial PC with a 600MHz CPU. Memory should be not huge nor small,
> maybe a couple of GBytes, hard disk should be some type of industrial
> Compact Flash of maybe 16 GBytes.

    It should work perfectly OK.

    Remember that you need a fast CPU if you have a database server that
processes many queries from many users simultaneously.
    Since your "server" will process very few queries (maybe one per second,
something like that) even a slow (by modern standards) 600 MHz CPU will be
more than enough...
    I'd say for such an application, your hardware is way overkill (it would
work on a smartphone...) but since hardware is so cheap...

Re: PostgreSQL with PostGIS on embedded hardware

From
Paolo Rizzi
Date:
>
>> They didn't give me complete information, but it should be some kind
>> of industrial PC with a 600MHz CPU. Memory should be not huge nor
>> small, maybe a couple of GBytes, hard disk should be some type of
>> industrial Compact Flash of maybe 16 GBytes.
>
>     It should work perfectly OK.
>
>     Remember that you need a fast CPU if you have a database server that
> processes many queries from many users simultaneously.
>     Since your "server" will process very few queries (maybe one per
> second, something like that) even a slow (by modern standards) 600 MHz
> CPU will be more than enough...
>     I'd say for such an application, your hardware is way overkill (it
> would work on a smartphone...) but since hardware is so cheap...
A smartphone... you're right, I didn't think of that, but the hardware I
described is very much like the one of a modern smartphone!!!
Are you saying that PostgreSQL+PostGIS can actually run on a
smartphone??? Intriguing...
Did anyone ever actually tried that???

Bye
Paolo

Re: PostgreSQL with PostGIS on embedded hardware

From
Tom Lane
Date:
Paolo Rizzi <gis@oicom.com> writes:
> Are you saying that PostgreSQL+PostGIS can actually run on a
> smartphone??? Intriguing...
> Did anyone ever actually tried that???

If it's a supported CPU type and you've got a suitable build toolchain,
sure.  Seven or eight years ago we were getting a good laugh out of the
fact that you could run PG on a PlayStation 2.

The real issue with the kind of hardware you're describing is going to
be the finite write lifetime of a flash device.  For a low-update
application it'll probably be okay, but PG could very easily destroy a
flash in no time if you aren't careful to minimize updates.

            regards, tom lane

Re: PostgreSQL with PostGIS on embedded hardware

From
Paolo Rizzi
Date:
>> Are you saying that PostgreSQL+PostGIS can actually run on a
>> smartphone??? Intriguing...
>> Did anyone ever actually tried that???
>
> If it's a supported CPU type and you've got a suitable build toolchain,
> sure.  Seven or eight years ago we were getting a good laugh out of the
> fact that you could run PG on a PlayStation 2.
Good to know!!! I imagine that on a PS3 it would be _really_ fast... :-)

>
> The real issue with the kind of hardware you're describing is going to
> be the finite write lifetime of a flash device.  For a low-update
> application it'll probably be okay, but PG could very easily destroy a
> flash in no time if you aren't careful to minimize updates.
This is something I thought about too, but it's something that those
people (this client of mine) should be well aware of, anyway I'll point
it out for them.

Anyway it seems interesting the fact that newer Flashes use several
techniques, such as wear leveling, to spread writes across the least
used cells. But this leads to files physical fragmentation, and it may
be a case where sequential scans are actually slower than random ones!!!

>
>             regards, tom lane

Bye
Paolo


Re: PostgreSQL with PostGIS on embedded hardware

From
PFC
Date:
> A smartphone... you're right, I didn't think of that, but the hardware I
> described is very much like the one of a modern smartphone!!!
> Are you saying that PostgreSQL+PostGIS can actually run on a
> smartphone??? Intriguing...
> Did anyone ever actually tried that???

    While the performance of ARM cpus used in smartphones, PDAs, etc, is
pretty good, this hardware is optimized for small size and low power use,
thus you generally get quite low memory bandwidth, the problem of Flash
endurance, and lack of standard interfaces to hook up to the rest of your
system.
    Embedded PC-Compatible hardware in the 600 MHz range you mention would
probably get a DIMM memory module (maybe for the only reason that
mass-production makes them so cheap) so you'd get a much higher memory
bandwidth, and much larger RAM. Even if the CPU is only 2x faster than a
smartphone, if the memory bandwidth is 10x higher, you'll see the
difference. It would also have standard interfaces, very useful for you,
and you can hook it up to a real SSD (not a micro-SD card) with real flash
wear leveling algorithms.

    But yeah since today's smartphones are more powerful that the desktops of
10 years ago (which ran PG just fine) it would probably work, if you can
run Linux on it...

Re: PostgreSQL with PostGIS on embedded hardware

From
Stefan Kaltenbrunner
Date:
Paolo Rizzi wrote:
>>> Are you saying that PostgreSQL+PostGIS can actually run on a
>>> smartphone??? Intriguing...
>>> Did anyone ever actually tried that???
>>
>> If it's a supported CPU type and you've got a suitable build toolchain,
>> sure.  Seven or eight years ago we were getting a good laugh out of the
>> fact that you could run PG on a PlayStation 2.
> Good to know!!! I imagine that on a PS3 it would be _really_ fast... :-)

well not really - while it is fairly easy to get postgresql running on a
PS3 it is not a fast platform. While the main CPU there is a pretty fast
Power based core it only has 256MB of Ram and a single SATA disk
available(though you could add some USB disks).


Stefan

Re: PostgreSQL with PostGIS on embedded hardware

From
Stefan Kaltenbrunner
Date:
Greg Stark wrote:
> On Mon, May 11, 2009 at 5:05 PM, Stefan Kaltenbrunner
> <stefan@kaltenbrunner.cc> wrote:
>>> Good to know!!! I imagine that on a PS3 it would be _really_ fast... :-)
>> well not really - while it is fairly easy to get postgresql running on a PS3
>> it is not a fast platform. While the main CPU there is a pretty fast Power
>> based core it only has 256MB of Ram and a single SATA disk available(though
>> you could add some USB disks).
>
> The nice thing about it is that TPC-C and other benchmarks all specify
> their bottom-line number in some unit like Transaction per second PER
> DOLLAR. So using a PS3 should be able to get ridiculously good results
> compared to expensive server hardware...

I kinda doubt that - the PS3 is certainly not server grade hardware so
you can only compare it to a desktop and I would bet that the typical
desktop you get for the 400€(you can get >4GB RAM a quadcore CPU for
that) price of a PS3 is going to outperform it significantly for almost
every workload...


Stefan


Re: PostgreSQL with PostGIS on embedded hardware

From
Greg Stark
Date:
On Mon, May 11, 2009 at 5:05 PM, Stefan Kaltenbrunner
<stefan@kaltenbrunner.cc> wrote:
>> Good to know!!! I imagine that on a PS3 it would be _really_ fast... :-)
>
> well not really - while it is fairly easy to get postgresql running on a PS3
> it is not a fast platform. While the main CPU there is a pretty fast Power
> based core it only has 256MB of Ram and a single SATA disk available(though
> you could add some USB disks).

The nice thing about it is that TPC-C and other benchmarks all specify
their bottom-line number in some unit like Transaction per second PER
DOLLAR. So using a PS3 should be able to get ridiculously good results
compared to expensive server hardware...

--
greg