I noticed that the ranking of full-text search in ttrss is a bit strange. Unlike searching engines, it does not seem to rank the articles with a ranking algorithm.
Then I found a thread suggesting sphinx but it’s overcomplicated and the query doesn’t work nicely within SQL.
SQLite FTS5 has very good support on BM25 ranking algorithm, and postgresql supports SQLite using a Foreign Data Wrapper.
Is it possible to (optionally) use the SQLite FTS5 to make a fulltext index for postgresql and sync the data directly within each INSERT/DELETE/UPDATE.
-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a', tokenize='myowntokenizer');
-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
-- search with sqlite BM25 algorithm (by default)
SELECT rowid,b,c FROM fts_idx where b match ? ORDER BY rank;