Re: "Unlogged indexes"

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

On Fri, May 3, 2013 at 1:46 PM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 3 May 2013 21:06, Yang Zhang <yanghatespam(at)gmail(dot)com> wrote:
>> Guessing the answer's no, but is there any way to construct indexes
>> such that I can safely put them on (faster) volatile storage? (Just to
>> be clear, I'm asking about indexes for *logged* tables.)
>
> Yes:
>
> CREATE INDEX ... TABLESPACE tablespacename;
> ALTER INDEX ... SET TABLESPACE tablespacename;
>
> Although there's a disparity between your email subject and main text.
> Indexes for logged tables are always logged. If you want an unlogged
> index you can only create it for an unlogged table.
>
> And putting indexes on a separate tablespace is probably not as
> advantageous as you're thinking. Might be worth testing.
>
> --
> Thom