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)
Large 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.
You'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.
- 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)