Re: Fast insertion indexes: why no developments

On Tue, Oct 29, 2013 at 10:49 AM, Leonardo Francalanci <m_lists(at)yahoo(dot)it> wrote:
>> Another point to add: I don't really see btree as a barrier to
>> performance for most of the problems I face. The real barriers to
>> database performance are storage, contention, and query planning.
>
> Ehm that's true for regular OLTP stuff, which I understand is what most (95%?) of people use/need. But if you try to insert rows into a 50M table with a couple of indexes, btrees just can't keep up.
> Of course, you can't have it all: fast at big table insertion, good contention, good query times...
>
>> Postgres btreee indexes are pretty fast and for stuff like bulk
>> insertions there are some optimization techniques available (such as
>> sharding or create index concurrently).
>
>
> At the moment I'm relying on partitioning + creating indexes in bulk on "latest" table (the partitioning is based on time). But that means K*log(N) search times (where K is the number of partitions).
> That's why I gave a look at these different indexing mechanisms.

I bet you've mis-diagnosed the problem. Btrees don't have a problem
keeping up with 50m records; you're problem is that after a certain
point your page cache can't keep up with the pseudo-random i/o
patterns and you start seeing faults to storage. Disk storage is
several order of magnitude slower than memory and thus performance
collapses. This has nothing to do the btree algorithm except to the
extent it affects i/o patterns.

With the advances in storage over the last several years such that
commodity priced SSD is available I think that all lot of assumptions
under these trade-offs will change.

merlin