On Tue, Aug 4, 2020 at 6:11 PM Tomas Vondra <tomas.von...@2ndquadrant.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