search

Police-Data-Accessibility-Project / prod-to-dev-migration

Regularly migrates production database and schema to development database
1 stars 0 forks source link

Setup job for refreshing materialized view(s) #12

Open maxachis opened 1 month ago

maxachis commented 1 month ago

Now that we're setting up a materialized view as per https://github.com/Police-Data-Accessibility-Project/data-sources-app/issues/345, we need to make sure it's refreshed regularly. PostgreSQL does not automatically refresh materialized views, so we would need to set up a job for managing this -- fortunately, we already have a function in dev_scripts.sql which can refresh our currently-only materialized view, so it'd be just a matter of calling that in the requisite environments.

There are a few considerations to consider:

How often to refresh?

The typeahead_suggestions materialized view takes data from the agencies, counties, and state_names tables, which will likely get data updates in descending order of frequency (with states probably never getting updated unless greater Idaho forms).

Do we put the code in here and rename the repository?

It'd be overkill to create a repository for one itty-bitty script, so naturally it'd make sense to put it in this repository. If we do that, though, the scope of this repository starts to expand to include a multitude of regular interfacing scripts. Maybe we should have one repository for migrations and another for regular database automation jobs. Or maybe we keep both in here. I'm not sure which is the best option.

josh-chamberlain commented 1 month ago