Full-text search is slow

[harrislapiroff]

We observed a number of timeouts over this past weekend. Trawling through the logs, Cameron noticed around those timeouts some slow requests for pages that included a text search term. I am able to reproduce that performing a text search on the tracker website takes several seconds to respond: https://pressfreedomtracker.us/all-incidents/?search=antifa

Let's find ways to improve performance here. I'll set the bar for closing this ticket as consistently achieving <1s responses for filtering queries involving text searches on the database page.

Some ideas that came up in discussion:

• Can we rely on Wagtail's internal page search index?
• Can we get better performance by reordering how filters are applied (e.g., if we do text-search first can that search be run on an indexed dataset instead of dynamically generating the index)?
• Something something materialized view (I didn't fully understand this, feel free to add your actual idea @chigby)

[chigby]

Saw some more worker timeouts today, and here are the surrounding log entries:

Around 02:00

Around 05:50

Around 08:57

Looking at these logs, it is hard to immediately discern a pattern to the requests, they almost seem random (e.g. page 65 for a nonexistent target) . Some of these might be slower than others but the thing that strikes between all three of these times is that it's a lot of requests at once. But also it doesn't really seem like so far out of line with what we're getting, say, in the middle of the day?

[harrislapiroff]

Do you think it's a deliberate DDoS? Someone trying to inflate the usage metrics on our filters 😉?

[chigby]

As of right now, the code from #1626 is deployed on staging, making direct comparisons with production somewhat straightforward.

With https://staging.pressfreedomtracker.us/all-incidents/?search=arrested, I'm getting ~3-3.5 second times. With the same query in production, https://pressfreedomtracker.us/all-incidents/?search=arrested, I'm getting ~6 second times.

[harrislapiroff]

🔥

[sssoleileraaa]

As follow-up from our discussion around creating a clear performance goal, I started this new "Scalability & Performance" page in the wiki as a starting point: https://github.com/freedomofpress/fpf-www-projects/wiki