Re: [GENERAL] Unlogged indexes
On Mon, May 6, 2013 at 4:43 PM, Michael Paquier michael.paqu...@gmail.comwrote: On Sat, May 4, 2013 at 5:53 AM, Yang Zhang yanghates...@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
Re: [GENERAL] Unlogged indexes
On Sat, May 4, 2013 at 5:53 AM, Yang Zhang yanghates...@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
[GENERAL] Unlogged indexes
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.) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unlogged indexes
On 3 May 2013 21:06, Yang Zhang yanghates...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 t...@linux.com wrote: On 3 May 2013 21:06, Yang Zhang yanghates...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
