Re: PostgreSQL as a local in-memory cache - Mailing list pgsql-performance

From Chris Browne
Subject Re: PostgreSQL as a local in-memory cache
Whole thread Raw
Responses Re: PostgreSQL as a local in-memory cache  (Steve Wampler)
List pgsql-performance
"" <> writes:
> My question is how can I configure the database to run as quickly as
> possible if I don't care about data consistency or durability? That
> is, the data is updated so often and it can be reproduced fairly
> rapidly so that if there is a server crash or random particles from
> space mess up memory we'd just restart the machine and move on.

For such a scenario, I'd suggest you:

- Set up a filesystem that is memory-backed.  On Linux, RamFS or TmpFS
  are reasonable options for this.

- The complication would be that your "restart the machine and move
  on" needs to consist of quite a few steps:

  - recreating the filesystem
  - fixing permissions as needed
  - running initdb to set up new PG instance
  - automating any needful fiddling with postgresql.conf, pg_hba.conf
  - starting up that PG instance
  - creating users, databases, schemas, ...

When my desktop machine's not dead [as it is now :-(], I frequently
use this very kind of configuration to host databases where I'm doing
functionality testing on continually-freshly-created DBs and therefore
don't actually care if they get thrown away.

I have set up an "init.d"-style script which has an extra target to do
the database "init" in order to make the last few steps mentioned as
quick as possible.

  ~/dbs/ init

goes an extra mile, using sed to rewrite postgresql.conf to change

I expect that, if running on a ramdisk, you'd want to fiddle some of
the disk performance parameters in postgresql.conf.

It's certainly worth trying out the ramdisk to see if it helps with
this case.  Note that all you'll lose is durability under conditions
of hardware outage - PostgreSQL will still care as much as always
about data consistency.

[Thinking about wilder possibilities...]

I wonder if this kind of installation "comes into its own" for more
realistic scenarios in the presence of streaming replication.  If you
know the WAL files have gotten to disk on another server, that's a
pretty good guarantee :-).
select 'cbbrowne' || '@' || '';
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow <>

pgsql-performance by date:

From: Matthew Wakeling
Subject: Re: B-Heaps
From: Steve Wampler
Subject: Re: PostgreSQL as a local in-memory cache