Re: Is it possible to have a "fast-write" Index?

On 5 June 2015 at 18:07, deavid <deavidsedice(at)gmail(dot)com> wrote:

> There are several use cases where I see useful an index, but adding it
> will slow too much inserts and updates.
> For example, when we have 10 million rows on a table, and it's a table
> which has frequent updates, we need several index to speed up selects, but
> then we'll slow down updates a lot, specially when we have 10 or more
> indexes.
> Other cases involve indexes for text search, which are used only for user
> search and aren't that important, so we want to have them, but we don't
> want the overload they put whenever we write on the table.
> I know different approaches that already solve some of those problems in
> some ways (table partitioning, partial indexes, etc), but i don't feel they
> are the solution to every problem of this kind.
>
> Some people already asked for "delayed write" indexes, but the idea gets
> discarded because the index could get out of sync, so it can omit results
> and this is unacceptable. But i think maybe that could be fixed in several
> ways and we can have a fast and reliable index (but maybe not so fast on
> selects).
>

This is exactly the use case and mechanism for BRIN indexes.

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