Re: PostgreSQL as a local in-memory cache

Tom Lane wrote:
> Dimitri Fontaine <dfontaine@hi-media.com> writes:
> > Josh Berkus <josh@agliodbs.com> writes:
> >> a) Eliminate WAL logging entirely
> >> b) Eliminate checkpointing
> >> c) Turn off the background writer
> >> d) Have PostgreSQL refuse to restart after a crash and instead call an
> >> exteral script (for reprovisioning)
>
> > Well I guess I'd prefer a per-transaction setting, allowing to bypass
> > WAL logging and checkpointing.
>
> Not going to happen; this is all or nothing.
>
> > Forcing the backend to care itself for
> > writing the data I'm not sure is a good thing, but if you say so.
>
> Yeah, I think proposal (c) is likely to be a net loss.
>
> (a) and (d) are probably simple, if by "reprovisioning" you mean
> "rm -rf $PGDATA; initdb".  Point (b) will be a bit trickier because
> there are various housekeeping activities tied into checkpoints.
> I think you can't actually remove checkpoints altogether, just
> skip the flush-dirty-pages part.

Based on this thread, I have developed the following documentation patch
that outlines the performance enhancements possible if durability is not
required.  The patch also documents that synchronous_commit = false has
potential committed transaction loss from a database crash (as well as
an OS crash).

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

  + None of us is going to be here forever. +
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.282
diff -c -c -r1.282 config.sgml
*** doc/src/sgml/config.sgml    22 Jun 2010 02:57:49 -0000    1.282
--- doc/src/sgml/config.sgml    23 Jun 2010 18:53:26 -0000
***************
*** 1463,1469 ****
          really guaranteed to be safe against a server crash.  (The maximum
          delay is three times <xref linkend="guc-wal-writer-delay">.)  Unlike
          <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
!         does not create any risk of database inconsistency: a crash might
          result in some recent allegedly-committed transactions being lost, but
          the database state will be just the same as if those transactions had
          been aborted cleanly.  So, turning <varname>synchronous_commit</> off
--- 1463,1470 ----
          really guaranteed to be safe against a server crash.  (The maximum
          delay is three times <xref linkend="guc-wal-writer-delay">.)  Unlike
          <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
!         does not create any risk of database inconsistency: an operating
!         system or database crash crash might
          result in some recent allegedly-committed transactions being lost, but
          the database state will be just the same as if those transactions had
          been aborted cleanly.  So, turning <varname>synchronous_commit</> off
Index: doc/src/sgml/perform.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v
retrieving revision 1.80
diff -c -c -r1.80 perform.sgml
*** doc/src/sgml/perform.sgml    29 May 2010 21:08:04 -0000    1.80
--- doc/src/sgml/perform.sgml    23 Jun 2010 18:53:26 -0000
***************
*** 1104,1107 ****
--- 1104,1169 ----
    </sect2>
    </sect1>

+   <sect1 id="non-durability">
+    <title>Non-Durable Settings</title>
+
+    <indexterm zone="non-durability">
+     <primary>non-durable</primary>
+    </indexterm>
+
+    <para>
+     Durability is a database feature that guarantees the recording of
+     committed transactions even if if the server crashes or loses
+     power.  However, durability adds significant database overhead,
+     so if your site does not require such a guarantee,
+     <productname>PostgreSQL</productname> can be configured to run
+     much faster.  The following are configuration changes you can make
+     to improve performance in such cases;  they do not invalidate
+     commit guarantees related to database crashes, only abrupt operating
+     system stoppage, except as mentioned below:
+
+     <itemizedlist>
+      <listitem>
+       <para>
+        Place the database cluster's data directory in a memory-backed
+        file system (i.e. <acronym>RAM</> disk).  This eliminates all
+        database disk I/O, but limits data storage to the amount of
+        available memory (and perhaps swap).
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        Turn off <xref linkend="guc-fsync">;  there is no need to flush
+        data to disk.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        Turn off <xref linkend="guc-full-page-writes">;  there is no need
+        to guard against partial page writes.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        Increase <xref linkend="guc-checkpoint-segments"> and <xref
+        linkend="guc-checkpoint-timeout"> ; this reduces the frequency
+        of checkpoints, but increases the storage requirements of
+        <filename>/pg_xlog</>.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        Turn off <xref linkend="guc-synchronous-commit">;  there might be no
+        need to write the <acronym>WAL</acronym> to disk on every
+        commit.  This does affect database crash transaction durability.
+       </para>
+      </listitem>
+     </itemizedlist>
+    </para>
+   </sect1>
+
   </chapter>