Re: Fast insertion indexes: why no developments

On 4 November 2013 19:55, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:

> How about having a 'TRANSIENT INDEX' that only exists in memory, so there is
> no requirement to write it to disk or to replicate directly? This type of
> index would be very fast and easier to implement. Recovery would involve
> rebuilding the index, and sharing would involve recreating on a slave.
> Probably not appropriate for a primary index, but may be okay for secondary
> indexes used to speed specific queries.
>
> This could be useful in some situations now, and allow time to get
> experience in how best to implement the basic concept. Then a more robust
> solution using WAL etc can be developed later.
>
> I suspect that such a TRANSIENT INDEX would still be useful even when a more
> robust in memory index method was available. As I expect it would be faster
> to set up than a robust memory index - which might be good when you need to
> have one or more indexes for a short period of time, or the size of the
> index is so small that recreating it requires very little time (total
> elapsed time might even be less than a disk backed one?).

UNLOGGED indexes have been discussed over many years and there is
pretty good acceptance of the idea for some use cases.

The main tasks are
* marking the index so they are unavailable on a hot standby
* rebuilding the index in full at the end of recovery - requires an
additional process to rebuild them possibly in priority order
* make sure the above doesn't violate security
* marking the index so it can't be used in the planner until rebuild
is complete - subtle stuff

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services