Re: unlogged tables

On Wed, Nov 17, 2010 at 2:42 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> 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.
>
> We're assuming here that the checkpoint activity for the unlogged table
> causes significant load on a production system.  Maybe we should do some
> testing before we try to make this overly complex?  I wouldn't be
> surprised to find that on most filesystems the extra checkpointing of
> the unlogged tables adds only small minority overhead.
>
> Shouldn't be hard to build out pgbench into something which will test
> this ... if only I had a suitable test machine available.

I guess the point I'd make here is that checkpoint I/O will be a
problem for unlogged tables in exactly the same situations in which it
is a problem for regular tables. There is some amount of I/O that
your system can handle before the additional I/O caused by checkpoints
starts to become a problem. If unlogged tables (or one particular
variant of unlogged tables) don't need to participate in checkpoints,
then you will be able to use unlogged tables, in situations where they
are appropriate to the workload, to control your I/O load and
hopefully keep it below the level where it causes a problem. Of
course, there will also be workloads where your system has plenty of
spare capacity (in which case it won't matter) or where your system is
going to be overwhelmed anyway (in which case it doesn't really matter
either). But if you are somewhere between those two extremes, this
has to matter.

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