PgConf.Russia 2017 talks

RUM indexes and their applications

Alexander Korotkov
Postgres Pro, Chief developer

Alexander has a status of PostgreSQL Major Contributor. He graduated from the MEPhI with honours. Alexander defended PhD thesis based on his impact in PostgreSQL development, specifically enhancement in fuzzy search of strings. One of his greatest achievement in the open-source community is development of the infrastructure for index access method extensibility. Alexander also improved GiST and GIN indexes, optimized multicore architechture, statistics and selectivity assessment. Three times he was a mentor in GSoC program .

I want to present a new custom access method, which extends the current GIN capabilities using additional information stored in posting tree/list. For example, positional information as an additional information allows new AM returns results in relevance order, which could considerably improve execution time of full text queries. Also, positions could be used to speedup phrase search making it really efficient feature of FTS. Storing timestamps provides several interesting possibilities - results can be ordered by time (thinks about fresh articles, which match FTS query) using timestamps per se and store item pointers ordered by timestamp. Both techniques provides an order of magnitude speedup for such kind of queries. Yet another application of the new AM is support of inverted full text search - find queires which match the given document.

There are GIN and VODKA access methods, so there is now RUM.

The new AM is an extension developed on top of Custom AM/Custom WAL features available in 9.6 and supported by Postgres Professional company.