Re: LSM tree for Postgres

On Tue, Aug 4, 2020 at 6:11 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> On Tue, Aug 04, 2020 at 11:22:13AM +0300, Konstantin Knizhnik wrote:
> >Hi hackers,
> >
> >I want to share results of my last research of implementing LSM index
> >in Postgres.
> >Most of modern databases (RocksDB, MongoDB, Tarantool,...) are using
> >LSM tree instead of classical B-Tree.
> >
>
> I was under the impression that LSM is more an alternative primary
> storage, not for indexes. Or am I wrong / confused?

As I understand, there are different use-cases. We can use LSM for
index, and this is good already. Such indexes would be faster for
insertions and probably even vacuum if we redesign it (see my previous
message), but slower for search. But for updates/deletes you still
have to do random access to the heap. And you also need to find a
heap record to update/delete, probably using the LSM index (and it's
slower for search than B-tree).

LSM as a primary storage can do more advanced tricks. For instance,
some updates/inserts_on_conflict could be also just pushed to the top
level of LSM without fetching the affected record before.

So, in my point of view LSM as an index AM is far not a full power LSM
for PostgreSQL, but it's still useful. Large insert-only tables can
benefit from LSM. Large tables with many indexes could also benefit,
because non-HOT updates will become cheaper.

------
Regards,
Alexander Korotkov