search enhancements

[18f]

search enhancements

[jessieay]

I've had luck with textacular in the past. Not sure what the "multisearch-like feature" limitation you are referring to is. More details?

I still have the textacular search code I wrote if you're interested in taking a look. Was for searching through a postgres table with a few hundred thousand records, matching against a few columns (which we weighted).

If you suggest Elasticsearch, then we should do that. I am not sure how difficult it will be to procure elasticsearch for our needs. Have other 18F projects used it in the past?

[jessieay]

Actually, my memory deceived me. I ended up using pg_search in that project with trigram, which I am guessing is what you referring to when you said "multi-search" https://github.com/Casecommons/pg_search#configuring-multi-search

[pkarman]

@jessieay By "multisearch" I meant (quotes from pg_search README):

records of many different Active Record classes can be mixed together into one global search index
across your entire application

which is I think what we would need to do in order to de-normalize our many tables into one. Otherwise, we have to use pg_search_scope, but since we need to search across tables:

It is possible to search columns on associated models. Note that if you do this, it will be impossible to
speed up searches with database indexes.
However, it is supported as a quick way to try out cross-model searching.

I.e. either we "flatten" our many tables into one (multisearch), or we suffer the same performance hit we have now.

Here's a good write-up of cross-model search. Note that the author wants to search one model, and then he adds on a couple of columns from a couple of associations. Our use case is more complex, ergo the performance hit (far more associations to join). http://codeloveandboards.com/blog/2014/06/20/easy-multi-table-full-text-search-whith-rails/

Since ES is essentially flattening our many tables into one external store, it's philosophically the same thing as pg_search multisearch.

[afeld]

@pkarman Great write-up. Worth noting that, with the current solution:

• speed could be dramatically improved by creating search indexes
• pagination should be very straightforward
• sorting by arbitrary columns should also be relatively easy, at least as far as the query is concerned

You can see more background in #355. That being said, I don't feel super strongly about one solution over the other.

[pkarman]

@afeld all those points are true if we go with Option 1. My worry is mostly maintenance. That much custom search code frightens me.

Our problem space is somewhat different than most of the use cases I see for using postgres full-text search (a couple of which are referenced as comments in the current code), which is when you have lots of data in a few tables, and only want to search a few, mostly text, columns. I call that a "deep" data scenario.

Ours is "wide" data: modest amounts of data (rows), normalized across many tables, with the added constraint of authorization, where we want to be able to search just about every column (could be dozens depending on the client).

[afeld]

👍

[pkarman]

As of https://github.com/18F/C2/releases/tag/2016-02-25T033955 this is now in production.