Re: Unlogged tables, persistent kind

On Tue, May 3, 2011 at 4:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> You're assuming that there are referential links *from* other tables
>> to the table with damage. In which case you would be correct. But of
>> course, if you needed that data for integrity you would never do that,
>> so the problem is a nonexistent use case. The suggested mode is for
>> Fact data, not reference tables.
>
> So I suppose your notion of "fact data" includes no fields that are wide
> enough to need toasting?  Because as soon as you have any out-of-line
> values, there's an equivalent of foreign keys behind the scenes, where
> the user can't see it (until he gets "missing chunk number" or some such
> error).
>
>> The current assessment is that UNLOGGED tables are useful only for
>> running a data cache. If the database crashes, then the table is
>> truncated and you must refill the cache. If that is the case, then it
>> must surely be better to have a cache that is already 99% full, than
>> one which starts at empty. There is no damage or loss because parts of
>> the cache were missing.
>
> A cache that starts at 99% full of untrustworthy data is NOT better.

That's a bit pessimistic.

The case that bugs me is that a cache that's 99% trustworthy, but
where I have no idea that:
a) 1% of it is crud, and
b) Which 1% of it is crud
is still a pretty unacceptable scenario.

I head back to our policy for handling caches:
If in doubt, TRUNCATE.

That policy would be nicely consistent with the way 9.1 deals with
unlogged tables.

>> Unlogged Tables are currently so volatile they are unusable for any
>> other purpose. I want to see a table that is useful for low value
>> data, such as sensor data.
>
> Basically, you're being hopelessly optimistic both about the extent to
> which a crash is likely to render data inconsistent, and our ability to
> detect that inconsistency.  It doesn't matter whether the data is "low
> value", the difficulty of cleaning up remains the same.  I don't want to
> deal with trying to detect that, and I definitely don't want to dump the
> problems onto users.

+1, on both grounds.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"