Re: unlogged tables

On Wed, Nov 17, 2010 at 2:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 11/17/2010 02:22 PM, Kenneth Marshall wrote:
>>> I would be fine with only having a safe shutdown with unlogged tables
>>> and skip the checkpoint I/O all other times.
>
>> Yeah, I was just thinking something like that would be good, and should
>> overcome Robert's objection to the whole idea.
>
> I don't think you can fsync only in the shutdown checkpoint and assume
> your data is safe, if you didn't fsync a write a few moments earlier.
>
> Now, a few minutes ago Robert was muttering about supporting more than
> one kind of degraded-reliability table.  I could see inventing
> "unlogged" tables, which means exactly that (no xlog support, but we
> still checkpoint/fsync as usual), and "unsynced" tables which
> also/instead suppress fsync activity.  The former type could be assumed
> to survive a clean shutdown/restart, while the latter wouldn't.  This
> would let people pick their poison.

OK, so we're proposing a hierarchy like this.

1. PERMANENT (already exists). Permanent tables are WAL-logged,
participate in checkpoints, and are fsync'd. They survive crashes and
clean restarts, and are replicated.

2. UNLOGGED (what this patch currently implements). Unlogged tables
are not WAL-logged, but they do participate in checkpoints and they
are fsync'd on request. They survive clean restarts, but on a crash
they are truncated. They are not replicated.

3. UNSYNCED (future work). Unsynced tables are not WAL-logged, do not
participate in checkpoints, and are never fsync'd. After any sort of
crash or shutdown, clean or otherwise, they are truncated. They are
not replicated.

4. GLOBAL TEMPORARY (future work). Global temporary tables are not
WAL-logged, do not participate in checkpoints, and are never fsync'd.
The contents of each global temporary table are private to that
session, so that they can use the local buffer manager rather than
shared buffers. Multiple sessions can use a global temporary table at
the same time, and each sees separate contents. At session exit, any
contents inserted by the owning backend are lost; since all sessions
exit on crash or shutdown, all contents are also lost at that time.

5. LOCAL TEMPORARY (our current temp tables). Local temporary tables
are not WAL-logged, do not participate in checkpoints, and are never
fsync'd. The table definition and all of its contents are private to
the session, so that they are dropped at session exit (or at
transaction end if ON COMMIT DROP is used). Since all sessions exit
on crash or shutdown, all table definitions and all table contents are
lost at that time.

It's possible to imagine a few more stops on this hierarchy. For
example, you could have an ASYNCHRONOUS table between (1) and (2) that
always acts as if synchronous_commit=off, but is otherwise replicated
and durable over crashes; or a MINIMALLY LOGGED table that is XLOG'd
as if wal_level=minimal even when the actual value of wal_level is
otherwise, and is therefore crash-safe but not replication-safe; or a
level that is similar to unlogged but we XLOG the first event that
dirties a page after each checkpoint, and therefore even on a crash we
need only remove the tables for which such an XLOG record has been
written. All of those are a bit speculative perhaps but we could jam
them in there if there's demand, I suppose.

I don't particularly care for the name UNSYNCED, and I'm starting not
to like UNLOGGED much either, although at least that one is an actual
word. PERMANENT and the flavors of TEMPORARY are a reasonably
comprehensible as a description of user-visible behavior, but UNLOGGED
and UNSYNCED sounds a lot like they're discussing internal details
that the user might not actually understand or care about. I don't
have a better idea right off the top of my head, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company