Re: unlogged tables

On Sat, Dec 11, 2010 at 3:18 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Fri, Dec 10, 2010 at 8:16 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> As I was working on the hash index support, it occurred to me that at
>> some point in the future, we might want to allow an unlogged index on
>> a permanent table.
>
> That is the feature I would be most excited about.
>
>> With the current patch, an index is unlogged if
>> and only if the corresponding table is unlogged, and both the table
>> and the index are reset to empty on restart.  But we could have a
>> slightly different flavor of index that, instead of being reset to
>> empty, just gets marked invalid, perhaps by truncating the file to
>> zero-length (and adding some code to treat that as something other
>> than a hard error).  Perhaps you could even arrange for autovacuum to
>> kick off an automatic rebuild,
>
> Or just have rebuilding the index as part of crash recovery.  I
> wouldn't use the feature anyway on indexes that would take more than a
> few seconds to rebuild, And wouldn't want to advertise the database as
> being available when it is essentially crippled from missing indexes.
> I'd rather bite the bullet up front.

I don't think you can rebuild the indexes during crash recovery; I
believe you need to be bound to the database that contains the index,
and, as we've been over before, binding to a database is irrevocable,
so the startup process can't bind to the database, rebuild the index,
and then unbind. It would need to signal the postmaster to fire up
other backends to do this work, and at that point I think you may as
well piggyback on autovacuum rather than designing a similar mechanism
from scratch.

Also, while YOU might use such a feature only for indexes that can be
rebuilt in a few seconds, I strongly suspect that other people might
use it in other ways. In particular, it seems that it would be
possibly sensible to use a feature like this for an index that's only
used for reporting queries. If the database crashes, we'll still have
our primary key so we can continue operating, but we'll need to
reindex before running the nightly reports.

> I would think of it is as declaring that, instead of making the index
> recoverable via WAL logging and replay, instead make it recoverable by
> rebuilding.  So in that way it is quit unlike unlogged tables, in that
> we are not risking any data, just giving the database a hint about
> what the most expeditious way to maintain the index might be.  Well,
> more of an order than a hint, I guess.

I think it's six of one, half a dozen of the other. An index by its
nature only contains data that is duplicated in a table, so by
definition loss of an index isn't risking any data.

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