4 ms·
We migrated FTS to OpenSearch from PostgreSQL a couple of months ago. Here the main issues we had with PostgreSQL FTS: - cross-tables FTS requires at least mat
by izietto 4y ago
We migrated FTS to OpenSearch from PostgreSQL a couple of months ago. Here the main issues we had with PostgreSQL FTS:
- cross-tables FTS requires at least materialized views, which lock data for writing at refresh. This was too much worrying for us.
- sorting by rank is not indexable, so we can't sort our dataset and have acceptable timings at the same time. Our dataset isn't enormous, but neither small (~1.5m records)
- mdavidn 4y agoLarge materialized views in general are really slow in PostgreSQL. If you go down the path of indexing joined data, consider database triggers or application-level hooks to update individual rows as-needed in a standalone search table. These are generally the same hooks necessary to update a document in OpenSearch.
- izietto 4y agoYou're right, that's actually what we implemented, application-level hooks, but they needed development and maintenance effort that come for free with the adapter we're using for OpenSearch integration, which also comes with welcome features: synonyms, partial matches, and many others. Spoiler, the adapter is Searchkick: https://github.com/ankane/searchkick https://github.com/ankane/searchkick
- ngrilly 4y agoExactly that. Had the same issue with sorting. It is indexable though, and very fast, when using the RUM extension.
- izietto 4y agoNever heard about RUM extension, I'll give it a look. Thanks!