public inbox for [email protected]  

help / color / mirror / Atom feed

From: Sushrut Shivaswamy <[email protected]>
To: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Design for dashboard query
Date: Sat, 15 Jun 2024 20:44:24 +0530
Message-ID: <CAH5mb99Q7+VxroUeHcxQxtti8KjbzFR64jt7B_x28+XyXh-TcQ@mail.gmail.com> (raw)
In-Reply-To: <CAD=mzVUQUyM6H2O4WqVF=1tgrEMuxPq0QU+DbpG6vOBxzA8LbQ@mail.gmail.com>
References: <CAD=mzVUQUyM6H2O4WqVF=1tgrEMuxPq0QU+DbpG6vOBxzA8LbQ@mail.gmail.com>

Have you tried creating an index on the user ID column?
Scanning the entire table to apply granular filters on a few attributes
seems unnecessary.

Materialised views make sense if you want to aggregate some columns and
query a subset of the data but would recommend trying indexes first.

Finally, shameless plug but consider using the pg_analytica extension that
enables fast analytic queries on the tables which is ideal for analytics
use cases like dashboards.
https://github.com/sushrut141/pg_analytica
I’m the author of the extension and am looking for initial users to try it
out.

Thanks,
Sushrut


On Sat, 15 Jun 2024 at 6:54 PM, sud <[email protected]> wrote:

> 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.
>
>
>

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Design for dashboard query
  In-Reply-To: <CAH5mb99Q7+VxroUeHcxQxtti8KjbzFR64jt7B_x28+XyXh-TcQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox