Postgres Pro
Downloads
Home   >   mailing lists

Thread: "Unlogged indexes"

"Unlogged indexes"

From
Yang Zhang
Date:
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.)

Re: "Unlogged indexes"

From
Thom Brown
Date:
On 3 May 2013 21:06, Yang Zhang <yanghatespam@gmail.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

Re: "Unlogged indexes"

From
Yang Zhang
Date:
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@linux.com> wrote:
> On 3 May 2013 21:06, Yang Zhang <yanghatespam@gmail.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

Re: "Unlogged indexes"

From
Michael Paquier
Date:
On Sat, May 4, 2013 at 5:53 AM, Yang Zhang <yanghatespam@gmail.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

Re: "Unlogged indexes"

From
Jeff Janes
Date:
On Mon, May 6, 2013 at 4:43 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Sat, May 4, 2013 at 5:53 AM, Yang Zhang <yanghatespam@gmail.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