Re: Unlogged tables, persistent kind

On Tue, Apr 26, 2011 at 8:49 AM, Leonardo Francalanci <m_lists(at)yahoo(dot)it> wrote:
>> > If that 1%  is random (not time/transaction related), usually you'd rather
>>have an empty  table.
>>
>> Why do you think it would be random?
>
> "Heap blocks would be zeroed if they were found to be damaged, following a
> crash."
>
> If you erase full blocks, you have no idea what data you erased; it could be
> something changed 1 hour ago, 1 month ago, 1 year ago. This is very different
> from,
> say, synchronous_commit=off: in that case, "the most recent transactions may be
> lost if the database should crash". In your case, "some (who knows which???)
> data
> is lost". So, to me that sounds like random loss. I don't think that that is
> different
> from a corrupted table. You're not deleting rows recently changed; you're
> deleting
> everything that is "physically close" to it.
>
>> > In other  words: is a table that is not consistant with anything else in the
>>db  useful?
>>
>> That's too big a leap. Why would it suddenly be inconsistent with  the
>> rest of the database?
>
>
> If you delete some data, and you have no idea what data you lost, I don't think
> you have a
> consistent db. Unless, of course, your table has no relation with any other
> table in the db.
>
> Of course, all these thoughts are based on the assumption that I know what
> happens when a
> block is erased; but my knowledge of postgresql internals is not so good, so I
> might be
> *very* wrong

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.

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.

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.
If you had 10 TB of sensor data and the database crashes, then you
want to lose a few blocks, not the whole lot. Low value => rare, minor
loss is acceptable, but it doesn;t mean total data loss is acceptable.
For that use case, total loss is catastrophic, not just mildly
irritating. If you are a Telco, losing a few minutes billing data
costs much less than having every server have better hardware so it
can cope with high WAL traffic as well. They don't want to lose the
data, but its a cost based trade off. Consistency is not an issue, you
are just missing some data. That is normal anyway, since sensor data
generators (mobile devices etc) frequently fail, are offline, turned
off etc, so there isn't even a definition of what complete data is
supposed to look like. The missing data looks exactly like lots of
people turned their phones off for a few minutes.

So my suggestion makes UNLOGGED tables more useful for the use case
they were designed to address - cached data (AIUI), plus they allow
another use case that doesn't seem to be well understood, low value
data in massive data volumes.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services