Indexing and Fuzzy Matching (Dehijacking #1929)

> Should be noted though that Lucene indexes are "only" used for fuzzy matching.
Sorry, yes, I should know better, non-fuzzy matching would be nice but seems impossible when building OS or Container SBOMs

> Index writes / updates / deletes, as well as commits, are executed in a single thread. Are we talking full-blown corruption (e.g. exception on modification / read), or inconsistencies (e.g. index documents missing)?
Corruption may be an inaccurate portrayal. Incomplete and unrepairable without shutting down the system, manually deleting an orphaned lock file and restarting. I what I've regularly observed.

> [Keeping search indexes consistent with the system of record](https://www.confluent.io/blog/using-logs-to-build-a-solid-data-infrastructure-or-why-dual-writes-are-a-bad-idea/) is hard, and TBH, DT currently does not do a good job of handling this today. I don't want to deviate too much from the original discussion here, but I think mid-term we may be able to resolve all these indexing issues by simply leveraging the DB's capabilities (e.g. [PostgreSQL's GIN index](https://about.gitlab.com/blog/2016/03/18/fast-search-using-postgresql-trigram-indexes/) or [FTS](https://www.crunchydata.com/blog/postgres-full-text-search-a-search-engine-in-a-database)).

This looks promising. If there is not already active work being done to implement this, I could have my.arm twisted to do so.

_Originally posted by @nscuro in https://github.com/DependencyTrack/dependency-track/issues/1929#issuecomment-1703789226_