5 ms·

Recently i've been writing a sort of archival focused SQL database. Partly out of want, partly out of learning desire. FTS would be of great benefit if i added

Recently i've been writing a sort of archival focused SQL database. Partly out of want, partly out of learning desire. FTS would be of great benefit if i added it. So i ask, where do FTS and SQL constraints or implementations differ greatly? Why does Lucene exist rather than having the same features in MySQL or Postgres? I'm asking in the context of road bumps, things to watch out for - should i try to merge the ideas and implementation of a SQL DB and FTS. (Note: I am aware that some SQL has FTS, but i imagine it still differs or has weaknesses when compared to a strictly focused FTS engine like Lucene)

boberoni 6y ago
The first thing that I think of is that SQL is designed for relational schemas, which would involve JOINs operations as well as indexes that optimize for WHERE, GROUP BY, etc. Overall, you have a reasonable expectation of structure over your data. On the other hand, FTS is working on less structured data a.k.a. variable length text. Therefore, an FTS system would need to include token filtering methods (which the article demonstrates) and optimize for the relevancy of search results. Another challenge for FTS is to create a ranking for your results. I would imagine that Lucene makes it easier to tune and administer your own definition of "relevancy" and "ranking", based on your application's use case.
- adsjhdashkj 6y ago
  Gotcha. So it sounds like no immediate blockers for FTS with SQL, but that focused FTS solutions (Lucene/etc) typically offer greater flexibility than you would traditionally see with a SQL DB's FTS. Appreciate the thoughts, thanks :)
mamcx 6y ago
One major "weakness" of rdbms is that all internals are black blocks. You can do "SELECT * FROM table" but, weirdly, can't "SELECT * FROM A_Index". NOTHING in the relational model requiere this limitation. Is purely accidental. BTW, I'm building a relational lang and also, very long time ago, try to port lucene to Delphi. So, what you need to support FTS and other kind of structures is... support them. For interfacing, you can consider ANY key-value as a relation of exactly 2 columns. Any scalar, a relation of 1 column, 1 row. You can put relations in relations, so you can do recursive data. Maybe the hardest is to have not only storage of btrees but also other stuff. I think you can fit FTS data in btrees anyway, but probably tweak it. When building the relational lang I stumble upon how support a n-column data inside a btree. You can split: pk=key values=col1 col2 col3 or as I later "get", just store the full row and change the functions that do the comparing. In rust start with: pub struct Tree { index: usize, <--signal which column as key data: BTreeSet<Vec<...>>, <-- Store all row }
- adsjhdashkj 6y ago
  How funny - i don't know SQL that well, and at work recently i was lamenting that i couldn't find a way to just query an index directly. Funny how self learning can sometimes mirror what you end up asking in work - even though they're not related at all.
- branko_d 6y ago
  > but, weirdly, can't "SELECT * FROM A_Index". In all honesty, most DBMSes have some sort of "hinting" mechanism that accomplishes roughly the same. It's a cludge, though, usually in the form of specially formatted comments or some sort of syntactic "piggy-back" on top of the regular SQL, or even outside SQL (e.g. SQL Server plan guides). I'd love to be able to specify the physical access path with more "first-class" syntax, and mix-and-match with logical access more easily.
fabian2k 6y ago
I don't really know enough about this for a proper answer, but some parts necessary for FTS are features in Postgres. The inverted index is the base building block, and Postgres does have this as GIN (generalized inverted index). The Postgres version is useful for more than just FTS, but as far as I understand it's pretty much the same thing under the hood as what dedicated FTS engines use. From what I've read, the biggest weaknesses in Postgres FTS are in the language-specific stuff and not the core indexing. And especially in ranking results, where there are much more advanced methods than what Postgres uses.
- JaggedNZ 6y ago
  I've had great success with Postgres FTS for some complex (i.e large, non-standard, highly specialised corpus) and basic search (very small corpus) features. Prior to switching to Postgres FTS had to maintain a seperate FTS applications index. I would say make sure that the gain of using a seperate FTS engine outweighs the maintenance cost, given that maintaining a FTS index in Postgres can be very minimal if configured correctly.

boberoni 6y ago

The first thing that I think of is that SQL is designed for relational schemas, which would involve JOINs operations as well as indexes that optimize for WHERE, GROUP BY, etc. Overall, you have a reasonable expectation of structure over your data. On the other hand, FTS is working on less structured data a.k.a. variable length text. Therefore, an FTS system would need to include token filtering methods (which the article demonstrates) and optimize for the relevancy of search results. Another challenge for FTS is to create a ranking for your results. I would imagine that Lucene makes it easier to tune and administer your own definition of "relevancy" and "ranking", based on your application's use case.

adsjhdashkj 6y ago
Gotcha. So it sounds like no immediate blockers for FTS with SQL, but that focused FTS solutions (Lucene/etc) typically offer greater flexibility than you would traditionally see with a SQL DB's FTS. Appreciate the thoughts, thanks :)

mamcx 6y ago

One major "weakness" of rdbms is that all internals are black blocks. You can do "SELECT * FROM table" but, weirdly, can't "SELECT * FROM A_Index". NOTHING in the relational model requiere this limitation. Is purely accidental. BTW, I'm building a relational lang and also, very long time ago, try to port lucene to Delphi. So, what you need to support FTS and other kind of structures is... support them. For interfacing, you can consider ANY key-value as a relation of exactly 2 columns. Any scalar, a relation of 1 column, 1 row. You can put relations in relations, so you can do recursive data. Maybe the hardest is to have not only storage of btrees but also other stuff. I think you can fit FTS data in btrees anyway, but probably tweak it. When building the relational lang I stumble upon how support a n-column data inside a btree. You can split: pk=key values=col1 col2 col3 or as I later "get", just store the full row and change the functions that do the comparing. In rust start with: pub struct Tree { index: usize, <--signal which column as key data: BTreeSet<Vec<...>>, <-- Store all row }

adsjhdashkj 6y ago
How funny - i don't know SQL that well, and at work recently i was lamenting that i couldn't find a way to just query an index directly. Funny how self learning can sometimes mirror what you end up asking in work - even though they're not related at all.
branko_d 6y ago
> but, weirdly, can't "SELECT * FROM A_Index". In all honesty, most DBMSes have some sort of "hinting" mechanism that accomplishes roughly the same. It's a cludge, though, usually in the form of specially formatted comments or some sort of syntactic "piggy-back" on top of the regular SQL, or even outside SQL (e.g. SQL Server plan guides). I'd love to be able to specify the physical access path with more "first-class" syntax, and mix-and-match with logical access more easily.

fabian2k 6y ago

I don't really know enough about this for a proper answer, but some parts necessary for FTS are features in Postgres. The inverted index is the base building block, and Postgres does have this as GIN (generalized inverted index). The Postgres version is useful for more than just FTS, but as far as I understand it's pretty much the same thing under the hood as what dedicated FTS engines use. From what I've read, the biggest weaknesses in Postgres FTS are in the language-specific stuff and not the core indexing. And especially in ranking results, where there are much more advanced methods than what Postgres uses.

JaggedNZ 6y ago
I've had great success with Postgres FTS for some complex (i.e large, non-standard, highly specialised corpus) and basic search (very small corpus) features. Prior to switching to Postgres FTS had to maintain a seperate FTS applications index. I would say make sure that the gain of using a seperate FTS engine outweighs the maintenance cost, given that maintaining a FTS index in Postgres can be very minimal if configured correctly.