Re: "Unlogged indexes"

On Mon, May 6, 2013 at 4:43 PM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>wrote:

> On Sat, May 4, 2013 at 5:53 AM, Yang Zhang <yanghatespam(at)gmail(dot)com> wrote:
>
>> Yeah, I know that indexes for unlogged tables are unlogged. I was
>> just wondering if you could do this for logged tables. (Safely, such
>> that on crash recovery WAL replay won't throw up, these can be omitted
>> from base backups, etc.)
>>
> No, you cannot create unlogged indexes on logged tables. An unlogged
> tables is
> truncated when a server starts after a crash, and so are its indexes that
> become
> empty by default. But having an unlogged index on a logged table would
> mean that
> you would need to truncate and regenerate the index after a crash as the
> data of
> the normal table is still here,
>

The other option would be to mark the index as invalid, rather than
rebuilding it. But both of those options are hard to implement, as
recovery cannot change the system catalogs, which I think would be needed
to implement either one.

> what would impact the performance boot of the server.
> Do you have a particular use-case in mind? I cannot see advantages directly
> advantages in having an unlogged index on a logged table...
>

If your index is small but intensely updated, then not WAL during normal
use could save a lot of time; while rebuilding after an instance crash
could take negligible time.

But from some of Yang's other recent email, I think he is more interested
in not backing up his very large indexes, and rebuilding them if media
recovery is needed. That is obviously more of a trade off, but it seems
like a choice people should be able to make, if it were easy to implement.

Cheers,

Jeff