What does HackerNews think of rum?
RUM access method - inverted index with additional information in posting lists
Yes query plan reuse like every other db, this still blows me away PG replans every time unless you explicitly prepare and that's still per connection.
Better full-text scoring is one for me that's missing in that list, TF/IDF or BM25 please see: https://github.com/postgrespro/rum
https://github.com/postgrespro/rum
If you have a large-ish data set with lots of similar data (4M addresses and location names was the test case), Postgres FTS just doesn't perform.
There is no index that helps scoring results. You would have to install an extension like RUM index (https://github.com/postgrespro/rum) to improve this, which may or may not be an option (often not if you use managed databases).
If you want a best of both worlds, one could investigate this extension (again, often not an option for managed databases): https://github.com/matthewfranglen/postgres-elasticsearch-fd...
Either way, writing something that indexes your postgres database into elastic/opensearch is a one time investment that usually pays off in the long run.
The GIN indexes for FTS don't really work in conjunction with other indices, which is why https://github.com/postgrespro/rum exists. Luckily, it sounds like you can use your existing indices to filter and let postgres scan for matches on the tsvector. The GIN tsvector indices are quite expensive to build, so don't add one if postgres can't make use of it!
Edit: if any of the Crunchy Data people are reading this: support for RUM indexes would be super cool to have in your managed service.
What you need to know is that the pending list will be merged with the main b-tree during several operations. Only one of them is so extremely critical for your insert performance - that is during actual insert. Both vacuum and autovacuum (including autovacuum analyze but not direct analyze) will merge the pending list. So frequent autovacuums are the first thing you should tune. Merging on insert happens when you exceed the gin_pending_list_limit. In all cases it is also interesting to know which memory parameter is used to rebuild the index as that inpacts how long it will take: work_mem (when triggered on insert), autovacuum_work_mem (when triggered during autovauum) and maintainance_work_mem (triggered by a call to gin_clean_pending_list()) define how much memory can be used for the rebuild.
What you can do is:
- tune the size of the pending list (like you did)
- make sure vacuum runs frequently
- if you have a bulk insert heavy workload (eg. nightly imports), drop the index and create it after inserting rows (not always makes sense business wise, depends on your app)
- disable fastupdate, you pay a higher cost per insert but remove the fluctuation when the merge needs to happen
The first thing was done in the article. However I believe the author still relies on the list being merged on insert. If vacuums were tuned aggressively along with the limit (vacuums can be tuned per table). Then the list would be merged out of bound of ongoing inserts.
I also had the pleasure of speaking with one main authors of GIN indexes (Oleg Bartunov) during the mentioned PGCon. He gave probably the best solution and informed me to "just use RUM indexes". RUM[3] indexes are like GIN indexes, without the pending list and with faster ranking, faster phrase searches and faster timestamp based ordering. It is however out of the main PostgreSQL release so it might be hard to get it running if you don't control the extensions that are loaded to your Postgres instance.
[1] - video https://www.youtube.com/watch?v=Brt41xnMZqo&t=1s
[2] - slides https://www.pgcon.org/2019/schedule/attachments/541_Let's%20...
https://github.com/postgrespro/rum
Not that its going to be as good as Algolia but it could be a whole lot better for many use cases specifically TF/IDF and BM25 scoring:
There has been some work but not sure when it will be stable, needs a new kind of index:
EDIT: This seems to help with the ranking problem: https://github.com/postgrespro/rum
Anyone who is familiar with PG internals - is there something in the internal data structure that prevents a BM25 or TF-IDF style rank generation ?
The work on RUM seems to have stagnated (and TF-IDF was a todo anyhow here) https://github.com/postgrespro/rum
I have a theory that if they incorporate these algorithms, it makes postgres potent enough that a lot of people may choose not to use elasticsearch/lucene.
I was asking because ranking can be slow in PostgreSQL. PostgreSQL can use a GIN or GiST index for filtering, but not for ranking, because the index doesn't contain the positional information needed for ranking.
This is not an issue when your query is highly selective and returns a low number of matching rows. But when the query returns a large number of matching rows, PostgreSQL has to fetch the ts_vector from heap for each matching row, and this can be really slow.
People are working on this but it's not in PostgreSQL core yet: https://github.com/postgrespro/rum.
This is why I'm a bit surprised by the numbers you shared: fulltext search on 270 million rows in below 65ms on commodity hardware (sub 8€/mo).
A few questions, if I may:
- What is the average number of rows returned by your queries? Is there a LIMIT?
- Is the ts_vector stored in the table?
- Do you use a GIN or GiST index on the ts_vector?
Cheers.
I've also used Elasticsearch, and I reckon that's pretty damn amazing.
Anyone wanting more in-depth information should read or watch this FTS presentation from last year. It's by some of the people who has done a lot of work on the implementation, and talks about 9.6 improvements, current problems, and things we might expect to see in version 10. https://www.pgcon.org/2016/schedule/events/926.en.html
There's also some previous presentations on the same topic which are interesting. You can see the RUM index (which has faster ranking here): https://github.com/postgrespro/rum
https://www.pgcon.org/2016/schedule/attachments/436_pgcon-20...
https://news.ycombinator.com/item?id=12605156
And this new index type for PostgreSQL: