Seemingly completely opposite to my usual focus on large scale
enterprise issues and requirements, I'm looking into PostgreSQL on small
handheld devices. Clearly these have limited memory and little "disk"
capability...

Are there some ports available to various devices?

What is the lowest memory footprint PostgreSQL has achieved?
[8Mb was last quote]

How little disk space has anyone achieved?
Is that an available port, or just a set of configure options?

Is there a definitive list of supported ports? (for anything)

Does PostgreSQL run on? 
- Windows 2003 (is that part of Win32 port as a defined target?)
- PalmOS
- Zaurus

Related info is copied below:
Q: Does PostgreSQL write repeatedly even when there is a no overt SQL
write activity? With a write-capable, but very low write rate,
PostgreSQL seems a good candidate, given current memory capacities?

Dredging up last year's discussion on "postgres on a PDA" we have..

"Al Bean" <albean84 ( at ) hotmail ( dot ) com> writes:
> My PDA has 32MB of RAM (well more like 16 with the way Sharp allocates it) 
> and I use a 256MB FLASH memory card which sort of looks like a hard drive on 
> the PDA. I know postgresql will run in about 8MB of RAM so I think I should 
> be ok on this front but I'm wondering about the data residing in
> FLASH.  

Postgres has kind of gotten away from the notion of a small disk
footprint :-(.  Some things to look at:

* Definitely reduce the size of WAL segments (see XLogSegSize).  You
can't afford the default 16MB.

* You probably don't want to have multiple databases.  I'd suggest doing
all your work in template1.  Consider also dropping template0 --- its
only value would be to reinit template1, and if you need to do that you
might as well re-initdb.

But I fear the real problem is going to be that FLASH memory has a
limited lifetime (measured in write cycles).  Postgres is going to spend
that lifetime with abandon, because it has absolutely no motivation to
avoid disk writes.  Are you prepared to replace the FLASH every so
often?

..reply...

In article <1883 ( dot ) 1042606899 ( at ) sss ( dot ) pgh ( dot ) pa (
dot ) us>,
Tom Lane  <tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us> wrote:
>attention I think it was on the order of magnitude of 10000 write
>cycles --- which Postgres could blow through in no time.  I hope
>it's better now, but I dunno by how much.  Anyone have more
>up-to-date info? 

Only about 1 order of magnitude better.  From the Linux Embedded FAQ:
Q:



3.3 Flash Limited write cycles

   Flash  have limited write cycles capabilities from 200 000 to 400 000.
   Using  swap  on such device is dangerous. 300 000 writes gives you 200
   days  at  1  write / minute and 83 hours at 1 write / second. More, If
   you interrupt power at arbitrary times while the device is writing,
   you can lose the integrity of the file system being modified. The loss
   is not limited to the 512 byte sector being modified, as it generally
   is  with rotating disks; you can lose an entire erase block, maybe 64K
   at once. The risk goes up as the "disk" approaches full, because erase
   rewrite  cycles happen more often in this condition. Un*x file systems
   spread their super block tables around the "disk", in the hope that at
   least  one will survive a head crash. Create one file, then /bin/sync:
   what percentage of the device's erase blocks get hit?


Thanks,

Simon Riggs
2nd Quadrant



Thu, 29 Apr 2004 23:14:15 +0100
Simon Riggs <simon@2ndquadrant.com>
Small OS ports & Handheld devices
Simon Riggs <simon@2ndquadrant.com> writes:
> ... I'm looking into PostgreSQL on small
> handheld devices. Clearly these have limited memory and little "disk"
> capability...

> Are there some ports available to various devices?

I don't know of any supported ports.

> What is the lowest memory footprint PostgreSQL has achieved?

This depends entirely on your postgresql.conf settings and how complex
the queries you want to process are.  I would think you could get it
down to maybe 4 or so meg if you have bottom-of-the-barrel requirements.
Performance in this configuration not guaranteed ;-)

> How little disk space has anyone achieved?
> Is that an available port, or just a set of configure options?

IIRC, configuring the WAL segment size is a pg_config_manual.h setting
in CVS tip, but in extant releases you'd have to dig into the xlog code
to adjust it.

> Q: Does PostgreSQL write repeatedly even when there is a no overt SQL
> write activity?

Given a SELECT-only query load, I'd expect PG to reach a state of zero
new writes fairly quickly (at the latest, after a vacuum and checkpoint
have occurred).  The real problem is that any single update operation
will generate quite a number of disk writes, the more so the smaller
your shared_buffers setting :-(.  It's not at all optimized to minimize
writes in a low-but-not-zero-update-traffic situation.  So you'd likely
be facing some issues with FLASH lifetime.

			regards, tom lane

Thu, 29 Apr 2004 18:56:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Small OS ports & Handheld devices 
Simon Riggs wrote:
> Is there a definitive list of supported ports? (for anything)

http://www.postgresql.org/docs/7.4/static/supported-platforms.html

For example the Linux/arm port can run on many PDA's but because of the 
write intensity and the limited write cycles of flash memory this 
continues to be a toy setup.


Fri, 30 Apr 2004 01:06:28 +0200
Peter Eisentraut <peter_e@gmx.net>
Re: Small OS ports & Handheld devices
> Simon Riggs wrote:
> > Is there a definitive list of supported ports? (for anything)
>
> http://www.postgresql.org/docs/7.4/static/supported-platforms.html
>
> For example the Linux/arm port can run on many PDA's but because of the
> write intensity and the limited write cycles of flash memory this
> continues to be a toy setup.

Why?  If you really need PostgreSQL on your PDA don't use Flash.  Use an IDE
drive.
http://www.flash-memory-store.com/ibmmicrodrives.html
http://www.memorysuppliers.com/ibmmic1gbwtr.html

$369 for 4GB of storage in a compact flash card is not all that bad.  $189
for 2.2GB is very reasonable when you consider the 512MB CF is going for
$149!

These are basically IDE drives in a compact flash form factor, as such they
should have lifetimes approaching a regular IDE drive, right?  Just don't
bounce them around too much.

Later
Rob


Thu, 29 Apr 2004 19:43:20 -0400
"Rob Butler" <robert.butler5@verizon.net>
Re: Small OS ports & Handheld devices
Rob Butler wrote:
> $369 for 4GB of storage in a compact flash card is not all that bad.
> $189 for 2.2GB is very reasonable when you consider the 512MB CF is
> going for $149!

The current "cheap" workaround to get a Hitachi (ne IBM) 4GB microdrive is
to buy a Creative Muvo2 4GB and open it up to extract the intact (OEM) 4GB
drive. $200 in the US typically.

Being a digital camera nerd, I pick up these things... now back to the usual
traffic.

Peter


Fri, 30 Apr 2004 08:35:22 +0100
"Peter Galbavy" <peter.galbavy@knowtion.net>
Re: Small OS ports & Handheld devices