Gerrydb tables and the UDFs to calculate their summary stats change infrequently. As such, we should explore using views–esp. materialized views–to avoid doing a lot of rework for every client by storing these summaries in a table.
Proposed schema would either:
Separate materialized views for each summary stat, with SUM({demographic_col}) results stored in separate columns.
Single table, partitioned by stat type, with the schema of stat_type TEXT, totals JSONB or something.
If we go this route then we may want to explore lazy insert summary results, i.e. the summary endpoint first looks in the summary totals table for the totals and if it doesn't finds them, calculates them itself using the UDF then inserts the results for the next person.
Anyway this is definitely a perf thing so let's see if it's necessary to add this complexity or if it's fine w/o.
Gerrydb tables and the UDFs to calculate their summary stats change infrequently. As such, we should explore using views–esp. materialized views–to avoid doing a lot of rework for every client by storing these summaries in a table.
Proposed schema would either:
SUM({demographic_col})results stored in separate columns.stat_type TEXT, totals JSONBor something.If we go this route then we may want to explore lazy insert summary results, i.e. the summary endpoint first looks in the summary totals table for the totals and if it doesn't finds them, calculates them itself using the UDF then inserts the results for the next person.
Anyway this is definitely a perf thing so let's see if it's necessary to add this complexity or if it's fine w/o.