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

From: Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>
To: deavid <deavidsedice(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Is it possible to have a "fast-write" Index?
Date: 2015-06-18 21:43:11
Message-ID: CAP-rdTYJ+GzX4eScgrKBas1=OzAZCaawTpmkJWEHKBJc6anEsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

2015-06-05 deavid <deavidsedice(at)gmail(dot)com>:

> Mode 3: on "aminsert", put the new entry on a second btree; leaving the
> first one untouched. Because the second btree is new, will be small, and
> writes should be faster. When doing a index scan, read tuples from both at
> same time (like merge sort). On vacuum, merge the second btree onto the
> first. On this mode, the index is sorted and there's no need of recheck.

You might be interested in reading the thread “Fast insertion indexes:
why no developments” (2013), starting at
1383033222(dot)73186(dot)YahooMailNeo(at)web172602(dot)mail(dot)ir2(dot)yahoo(dot)com .

That thread talks mostly about reducing the (delayed) I/O caused by
inserting in a super-big index at a continuously high rate, while you
seem more interested in the delay problem caused by the CPU time used
when inserting in multiple indexes (which should be quite fast
already, as most of the writing is delayed).

Your problem (if it is really about delay and not about continuous
throughput) might be better served by a delay-reducing solution such
as writing a logical “row X is inserted, please make sure that all
indexes are up-to-date” to the WAL, instead of the physical “row X is
inserted into table A, part Y of index Z must be updated like this,
part Q of index S must be updated like so, etc” as is done now.
Updating the indexes (not only the writing itself) would then be
performed in a delayed fashion. Reading of an index must always
additionally scan the in-memory queue of logical WAL records that is
kept.

Of course, switching the WAL wholesale from a physical description of
the changes that must be performed to a logical description is
probably not feasible. Therefore, one could think about some new kind
of “logical WAL” that gets logged separately (or even mixed in with
the normal, physical WAL), where first the logical WAL is written
(“insert row X in table A”), after which other operations can continue
and the logical WAL is converted to physical WAL asynchronously (by
“performing” the changes as is currently done, but by a background
process). Recovery then would first need to replay the physical WAL,
and then replay the logical WAL.

Nicolas

--
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Eric Ridge 2015-06-18 21:45:37 Re: Weirdness using Executor Hooks
Previous Message Eric Ridge 2015-06-18 21:41:58 Re: Weirdness using Executor Hooks