The update job in meronense takes too long to finish

It seems the update script on meronense isn't finishing in the same amount of time it would need before. I am not sure if this is because of the complexity of the views that we have in the DB or the recent postgresql update.

One of the views that take several hours to load look like this:

ipv6servers=> \d+ ipv6servers 
                                         View "public.ipv6servers"
               Column               |    Type     | Collation | Nullable | Default | Storage | Description 
------------------------------------+-------------+-----------+----------+---------+---------+-------------
 valid_after_date                   | date        |           |          |         | plain   | 
 server                             | server_enum |           |          |         | plain   | 
 guard_relay                        | boolean     |           |          |         | plain   | 
 exit_relay                         | boolean     |           |          |         | plain   | 
 announced_ipv6                     | boolean     |           |          |         | plain   | 
 exiting_ipv6_relay                 | boolean     |           |          |         | plain   | 
 reachable_ipv6_relay               | boolean     |           |          |         | plain   | 
 server_count_sum_avg               | numeric     |           |          |         | main    | 
 advertised_bandwidth_bytes_sum_avg | numeric     |           |          |         | main    | 
View definition:
 WITH included_statuses AS (
         SELECT statuses.status_id,
            statuses.server,
            statuses.valid_after
           FROM statuses
             JOIN aggregated_ipv6 USING (status_id)
          GROUP BY statuses.status_id, statuses.server, statuses.valid_after
         HAVING statuses.running_count > 0 AND (1000 * sum(aggregated_ipv6.server_count_sum)) > (999 * statuses.running_count)
        ), included_dates AS (
         SELECT date(included_statuses.valid_after) AS valid_after_date,
            included_statuses.server
           FROM included_statuses
          GROUP BY (date(included_statuses.valid_after)), included_statuses.server
         HAVING count(included_statuses.status_id) >= 12 AND date(included_statuses.valid_after) < (( SELECT max(date(included_statuses_1.valid_after)) AS max
                   FROM included_statuses included_statuses_1))
        ), grouped_by_status AS (
         SELECT statuses.valid_after,
            statuses.server,
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.guard_relay
                    ELSE NULL::boolean
                END AS guard_relay_or_null,
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exit_relay
                    ELSE NULL::boolean
                END AS exit_relay_or_null,
            aggregated_ipv6.announced_ipv6,
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exiting_ipv6_relay
                    ELSE NULL::boolean
                END AS exiting_ipv6_relay_or_null,
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.reachable_ipv6_relay
                    ELSE NULL::boolean
                END AS reachable_ipv6_relay_or_null,
            sum(aggregated_ipv6.server_count_sum) AS server_count_sum,
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN sum(aggregated_ipv6.advertised_bandwidth_bytes_sum)
                    ELSE NULL::numeric
                END AS advertised_bandwidth_bytes_sum
           FROM statuses
             JOIN aggregated_ipv6 USING (status_id)
          WHERE (statuses.status_id IN ( SELECT included_statuses.status_id
                   FROM included_statuses)) AND (date(statuses.valid_after) IN ( SELECT included_dates.valid_after_date
                   FROM included_dates
                  WHERE included_dates.server = statuses.server))
          GROUP BY statuses.status_id, statuses.valid_after, statuses.server, (
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.guard_relay
                    ELSE NULL::boolean
                END), (
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exit_relay
                    ELSE NULL::boolean
                END), aggregated_ipv6.announced_ipv6, (
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exiting_ipv6_relay
                    ELSE NULL::boolean
                END), (
                CASE
                    WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.reachable_ipv6_relay
                    ELSE NULL::boolean
                END)
        )
 SELECT date(grouped_by_status.valid_after) AS valid_after_date,
    grouped_by_status.server,
    grouped_by_status.guard_relay_or_null AS guard_relay,
    grouped_by_status.exit_relay_or_null AS exit_relay,
    grouped_by_status.announced_ipv6,
    grouped_by_status.exiting_ipv6_relay_or_null AS exiting_ipv6_relay,
    grouped_by_status.reachable_ipv6_relay_or_null AS reachable_ipv6_relay,
    floor(avg(grouped_by_status.server_count_sum)) AS server_count_sum_avg,
    floor(avg(grouped_by_status.advertised_bandwidth_bytes_sum)) AS advertised_bandwidth_bytes_sum_avg
   FROM grouped_by_status
  GROUP BY (date(grouped_by_status.valid_after)), grouped_by_status.server, grouped_by_status.guard_relay_or_null, grouped_by_status.exit_relay_or_null, grouped_by_status.announced_ipv6, grouped_by_status.exiting_ipv6_relay_or_null, grouped_by_status.reachable_ipv6_relay_or_null
  ORDER BY (date(grouped_by_status.valid_after)), grouped_by_status.server, grouped_by_status.guard_relay_or_null, grouped_by_status.exit_relay_or_null, grouped_by_status.announced_ipv6, grouped_by_status.exiting_ipv6_relay_or_null, grouped_by_status.reachable_ipv6_relay_or_null;

The quick fix, is that we need to make this a materialized view and store the data instead of running it every time.

So the next step is going through all these view and materialize them, so they are stored into memory and the queries will be faster.

The next possible step could be using timescaledb on the website already. They have the continuous aggregates that can help us with updating these views https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/about-continuous-aggregates/#about-continuous-aggregates