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.
