determine "dataset" materialized view refresh rate

[rshewitt]

User Story

In order to ensure new catalog records are recent, datagov wants to determine how often (and where) the "dataset" refresh occurs

Acceptance Criteria

[ACs should be clearly demoable/verifiable whenever possible. Try specifying them using BDD.]

• GIVEN the "dataset" mv
  WHEN a refresh rate is determined and implemented
  THEN "dataset" is updated

Background

• "dataset" is a materialized view containing the latest set of records (the datasets) for all harvest sources in the h20 db.
• materialized views need to be refreshed to reflect updates from the underlying query. they do a complete table rewrite.
• refresh took 2 minutes (see text below) for 141k records. at that time only dcatus records were in the "dataset" mv. we have ~450k records so it could take ~6 minutes total.

[user]=> REFRESH MATERIALIZED VIEW dataset;
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
REFRESH MATERIALIZED VIEW
Time: 123202.888 ms (02:03.203)

Security Considerations (required)

[Any security concerns that might be implicated in the change. "None" is OK, just be explicit here!]

Sketch

• refresh at end of each harvest (job overlap)
• cron action
• run REFRESH MATERIALIZED VIEW [CONCURRENTLY] dataset; on the h20 (read-write) db view. unsure whether configuring the refresh to prevent read-blocking (i.e. concurrently) will have an impact on update pushes to the read-only but it may need to be considered. maybe the nature of the update on the origin db reflects the update on the destination db.

[rshewitt]

number of jobs in the past 90 days grouped by hour of day of date_finished. jobs are running around the clock. for data recency, creating a separate "dataset" table and syncing it per record (e.g. via trigger) would probably yield the best results. running a refresh 6 times a day (for example) on a cron means we're accepting a 4-hour lag. running a refresh at the end of a harvest would be better but there's a possibility of multiple refreshes occurring at a similar time. maybe that's not a problem and the first process to take the lock wins followed by the other ones waiting. refreshing at end of harvest would add several minutes to processing.

 hour_of_day | record_count 
-------------+--------------
           0 |          125
           1 |          230
           2 |           94
           3 |          111
           4 |          327
           5 |          145
           6 |          140
           7 |           78
           8 |          143
           9 |          169
          10 |          256
          11 |          255
          12 |          116
          13 |          233
          14 |          223
          15 |          161
          16 |          130
          17 |          317
          18 |          335
          19 |          508
          20 |          573
          21 |          524
          22 |          505
          23 |          416

[rshewitt]

• commit on dev refreshing at the end of each harvest job. the sql query checks to see whether the "dataset" materialized view exists before attempting to refresh it.
• commit action
• new relic logs checking for refresh. something weird is going on with the logs (35 instances of the expected log message but only 3 jobs have run today on dev). i don't think the refresh is occurring 35 times though.

do we want this merged to main or still on dev?

[FuhuXia]

We dont need to worry about refresh rate if:

• Create a GH action to allow develop to refresh on demand
• A GH cron to do once a day in the morning
• Make all harvesting activity at night, and terminate them in the morning. (make half-finished ones resume next day).

[rshewitt]

We dont need to worry about refresh rate if:

• Create a GH action to allow develop to refresh on demand
• A GH cron to do once a day in the morning
• Make all harvesting activity at night, and terminate them in the morning. (make half-finished ones resume next day).

• refresh on demand sounds good. i'll add that.
• currently, jobs run when they run and complete when they complete. it takes far less change to refresh at the end of each harvest (see commit) than implement what you're asking.

[rshewitt]

successful action. checking the logs cf assigns the task with id 5967. grepping that id...

5967   refresh_dataset_mv                                         SUCCEEDED   Fri, 10 Oct 2025 18:09:39 UTC   python scripts/refresh_dataset_mv.py