Re: Postgres replaces SQLite as well as it replaces Oracle?

> Poll the size of the various system catalogs as you go; you may discover
> that keeping it small is mostly a matter of aggressively vacuuming them.

Here's what's interesting in that regard: I actually want to tune PG
to have as few disk writes as possible, so as to not burn through SD
cards.

Given that even a modest Raspberry Pi has 1GB of RAM, I thought I
would store all of the Postgres database files (and other stuff under
/var) in a RAM disk, then archive the ram disk on shutdown (and
restore the archive on startup). I'm not quite there yet. I wrote a
couple of systemd Units. One unit archives /var upon system shutdown
and one un-archives upon startup (but before the multi-user target
starts other services). The problem is, I have not been able to get
systemd to do things in a sane manner. It always insists upon
starting multi-user.target at the same time the un-archive is running,
so services that depend on /var being complete do not start cleanly.
Same with shutdown- it always kills power while the archive script is
running.

At this time it's running as-is. I've enabled sysstat so I can keep
track of disk writes and tune as needed.

On Sat, Feb 25, 2017 at 1:40 PM, Josh Berkus <josh(at)berkus(dot)org> wrote:
> On 02/24/2017 05:12 PM, Joshua Kramer wrote:
>> Hello,
>>
>> Has anyone done articles or advocacy for Postgres in embedded applications?
>>
>> I am fooling around with Home Assistant on a Raspberry Pi. This is a
>> Python 3 app that is normally backed by SQLite. But it has issues
>> because often times more than one process wants to write to the
>> database, and SQLite doesn't do that well. I found a handy guide on
>> replacing SQLite with PG in this application:
>>
>> https://community.home-assistant.io/t/howto-postgresql-on-raspberry-pi-all-in-one-installer/5527/21
>>
>> Two things I noticed right off the bat. The UI of HomeAssistant is
>> MUCH faster using PG than it is using SQLite. We're talking 5 seconds
>> for a page refresh vs half a second. Second, Postgres is tiny! The
>> whole stack- OS, Postgres, HASS server- takes up a whopping 67 MB of
>> RAM.
>
> You can probably get PostgreSQL even smaller if you're willing to
> jettison some functionality. Doesn't sound like you need to, though.
> Presumably you've deleted all the databases but one?
>
> We haven't really targeted the "embedded" use-case because, well,
> SQLite. However, when the smallest ARM card you can buy has 64MB of
> RAM, the profile of "embedded" has changed considerably. Back in the
> early days of PG, some of our earliest use cases were "embedded": TiVO,
> vehicle onboard systems, voicemail systems, etc. It's probably still
> being used for this, but we don't hear about it much.
>
>> It'll be interesting to see how these stats change as I leave the
>> thing running for months on end.
>
> Poll the size of the various system catalogs as you go; you may discover
> that keeping it small is mostly a matter of aggressively vacuuming them.
>
> Thanks so much for the story!
>
> --
> Josh Berkus
> Containers & Databases Oh My!