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

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!