Optimize tables for search

[jadudm]

Our search is very non-performant.

This is because multiple reasons:

1. all searches of interest require joins across multiple (large) tables
2. the dissemination tables were never designed (for search).

Tasks

Beta Give feedback

1. Add one or more materialized views to support a performant search interface
2. Create Django command to manage the creation/deletion/regeneration of the materialized view
3. Add an unmanaged Django model(s) to match the materialized view(s)
4. Generate summary report from the materialized view instead of re-running the query against separate tables
5. Verify that search against the materialized view returns the correct data
6. Add a GH Action to call fac materialized_views --refresh on a periodic schedule
7. Update .profile

Proposed: MATERIALIZED VIEW

We had discussed various strategies to cache/re-format tables that would be more performant.

Postgres has a notion of materialized views. A materialized view is... a view, but it is populated with data, and not dynamic at query-time. Think of it as a table, expressed by a query, that gets populated. As a result, it can be used for searches without incurring additional overhead.

Materialized views have a cost to create (in time and space), but once created, behave like other tables. They do not stay up to date with their "linked" tables, however. As a result, they must be periodically refreshed.

Solution sketch

https://github.com/GSA-TTS/FAC/tree/jadudm/materialized-views

This adds

• run.sh changes to drop and create a materialized view for our searchable data of interest
• sql files in dissemination for the drop, creation, and refresh of materialized views
• A django command to manage the dropping, creation, and refreshing

This will drop (if exists) and create (if not exists) a materialized view called dissemination_combined that combines general, federalaward, and finding rows. It only combines data where there are findings. So, this table is only of use if someone is searching for audits that have findings.

use

fac materialized_views --drop

fac materialized_views --create

fac materialized_views --refresh

The third command could be triggered via GH Action periodically (daily? 2x daily? 4x daily?) to update the view.

[danswick]

For now, @sambodeme's goal will be a branch where we can run a particularly taxing query against an unmanaged materialized view. @sambodeme, could you drop the query in this thread?

[jadudm]

Also, please feel free to rewrite/modify/etc. this ticket as needed. It... I don't know how I should have documented the spike/thinking differently.

[sambodeme]

Here is the query params:
Date range: 10/1/2022-10/31/2022
ALN: 20
Findings: Any

[jadudm]

From a question to the dev channel, the idea that we can use LEFT JOIN to construct the MATERIALIZED VIEW is very productive.

[danswick]

Collecting some related issues and PRs here for future navigators:

• Temporarily limit searches while we address the underlying issues: Search performance: implement temporary filter limits and communicate to users #3526. PRs done as part of larger IA work.
  • PR for temp app changes: Home and Header Updates #3483
  • PR for temp static site changes: Resource Pages, Home Page, and Header Updates FAC-transition-site#91
• PR implementing materialized views: Jadudm/materialized views #3511
• [Performance improvement] Switch to async gunicorn workers to enable more concurrent requests: Use async gunicorn workers #3535
• Break search out into "basic" page (general table only; no joins. Updated in "realtime") and "advanced" page (materialized views only. updated ~nightly): Create a new search page for General-only queries #3548
• Automate MV rebuilds Create GH workflow for rebuilding MV nightly #3551
• Bugfix for pre-cert workbooks broken by MV work: Fixes inability to download pre-cert SF-SACs #3554

[danswick]

There are some outstanding follow-up tasks here, but this ticket is otherwise complete.