Design for dashboard query

Hello All,

Its postgres version 15.4. We are having a requirement in which aggregated
information for all the users has to be displayed on the UI screen. It
should show that information on the screen. So basically, it would be
scanning the full table data which is billions of rows across many months
and then join with other master tables and aggregate those and then display
the results based on the input "user id" filter.

In such a scenario we are thinking of using a materialized view on top of
the base tables which will store the base information and refresh those
periodically to show the data based on the input user id. However i am
seeing , postgres not supporting incremental refresh of materialized view
and full refresh can take longer. So , do we have any other option
available? Additionally , It should not impact or block the online users
querying the same materialized view when the refresh is happening.