Every time I've used Lucene I have combined it with a SQL provider. It's not necessarily about one or the other. The FTS facilities within the various SQL providers are convenient, but not as capable by comparison. I don't think mixing these into the same thing makes sense. They are two very different animals that are better joined by way of the document ids.
- saves infra costs
- saves infra headaches
- devs only need to be experts in one system (or well I guess one and a half, probably there's something to learn about ParadeDB too, but probably less than in learning Lucine)
- no need to worry about keeping data up to date in the separate seach system
- all data is available when you want to do new queries that you handn't thought of when implementing the data transfer/indexingSELECT * FROM benchmark_logs WHERE severity < 3 ORDER BY timestamp DESC LIMIT 10;
this index
CREATE INDEX ON benchmark_logs (severity, timestamp);
cannot be used as proposed: "Postgres can jump directly to the portion of the tree matching severity < 3 and then walk the timestamps in descending order to get the top K rows."
Postgres with this index can walk to a part of the tree with severity < 3, but timestamps are sorted only for the same severity.