Thread: [pgsql-advocacy] Postgres replaces SQLite as well as it replaces Oracle?

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.

It'll be interesting to see how these stats change as I leave the
thing running for months on end.

Cheers!
-JK

Well there are a variety of definitions of "embedded".  You could probably run
SQLite on a device that has a total of 1MB of RAM or similar. -- Darren Duncan

On 2017-02-24 5: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.
>
> It'll be interesting to see how these stats change as I leave the
> thing running for months on end.
>
> Cheers!
> -JK

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!

> 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@berkus.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!

On 02/25/2017 07:53 PM, Joshua Kramer wrote:
>> 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.

A few tips:

1. Put the stats cache on a ramdisk.
2. No autovacuum, use scheduled vaccum instead (and if you have nightly
downtime, consider rebuilding the database instead of vacuum)
3. Put tmp/sorting space on a ramdisk

>
> 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.

I think Bruce did a bunch of work tracking all the places where we write
to disk, once upon a time.  Bruce?

--
Josh Berkus
Containers & Databases Oh My!

On Mon, Feb 27, 2017 at 09:05:50AM -0800, Josh Berkus wrote:
> On 02/25/2017 07:53 PM, Joshua Kramer wrote:
> > At this time it's running as-is.  I've enabled sysstat so I can keep
> > track of disk writes and tune as needed.
>
> I think Bruce did a bunch of work tracking all the places where we write
> to disk, once upon a time.  Bruce?

Uh, I did?  The only thing I can think of is a blog post comparing
performance on various types of storage:

    http://momjian.us/main/blogs/pgblog/2012.html#February_2_2012

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 02/28/2017 12:56 PM, Bruce Momjian wrote:
> On Mon, Feb 27, 2017 at 09:05:50AM -0800, Josh Berkus wrote:
>> On 02/25/2017 07:53 PM, Joshua Kramer wrote:
>>> At this time it's running as-is.  I've enabled sysstat so I can keep
>>> track of disk writes and tune as needed.
>>
>> I think Bruce did a bunch of work tracking all the places where we write
>> to disk, once upon a time.  Bruce?
>
> Uh, I did?  The only thing I can think of is a blog post comparing
> performance on various types of storage:

Ages ago, it was back when Whitepages.com was looking for help.  You
might still have the notes somewhere ...


--
Josh Berkus
Containers & Databases Oh My!