Re: "Unlogged indexes"

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, 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...

Regards,
--
Michael