MIME-Version: 1.0
References: <CAD=mzVUQUyM6H2O4WqVF=1tgrEMuxPq0QU+DbpG6vOBxzA8LbQ@mail.gmail.com>
In-Reply-To: <CAD=mzVUQUyM6H2O4WqVF=1tgrEMuxPq0QU+DbpG6vOBxzA8LbQ@mail.gmail.com>
From: Sushrut Shivaswamy <sushrut.shivaswamy@gmail.com>
Date: Sat, 15 Jun 2024 20:44:24 +0530
Message-ID: <CAH5mb99Q7+VxroUeHcxQxtti8KjbzFR64jt7B_x28+XyXh-TcQ@mail.gmail.com>
Subject: Re: Design for dashboard query
To: sud <suds1434@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b1cbd0061aef304b"
Archived-At: <https://www.postgresql.org/message-id/CAH5mb99Q7%2BVxroUeHcxQxtti8KjbzFR64jt7B_x28%2BXyXh-TcQ%40mail.gmail.com>
Precedence: bulk

--000000000000b1cbd0061aef304b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=99m 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 <suds1434@gmail.com> wrote:

> Hello All,
>
> Its postgres version 15.4. We are having a requirement in which aggregate=
d
> 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 displ=
ay
> 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.
>
>
>

--000000000000b1cbd0061aef304b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Have you tried creating an index on the user ID column?</=
div><div dir=3D"auto">Scanning the entire table to apply granular filters o=
n a few attributes seems unnecessary.</div><div dir=3D"auto"><br></div><div=
 dir=3D"auto">Materialised views make sense if you want to aggregate some c=
olumns and query a subset of the data but would recommend trying indexes fi=
rst.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Finally, shameless =
plug but consider using the pg_analytica extension that enables fast analyt=
ic queries on the tables which is ideal for analytics use cases like dashbo=
ards.</div><div dir=3D"auto"><div><a href=3D"https://github.com/sushrut141/=
pg_analytica">https://github.com/sushrut141/pg_analytica</a></div>I=E2=80=
=99m the author of the extension and am looking for initial users to try it=
 out.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks,</div><div =
dir=3D"auto">Sushrut</div><div dir=3D"auto"><br></div><div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 15 Jun 2024 =
at 6:54 PM, sud &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.co=
m</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;padding-le=
ft:1ex;border-left-color:rgb(204,204,204)"><div dir=3D"ltr"><p dir=3D"ltr">=
<span>Hello All,</span></p><p dir=3D"ltr"><span>Its postgres version 15.4. =
We are having a requirement in which aggregated information for all the use=
rs 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 i=
s billions of rows across many months and then join with other master table=
s and aggregate those and then display the results based on the input &quot=
;user id&quot; filter.</span></p><p dir=3D"ltr"><span>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 suppor=
ting incremental refresh of materialized view and full refresh can take lon=
ger. So , do we have any other option available? </span><span>Additionally =
, It should not impact or block the online users querying the same material=
ized view when the refresh is happening.</span></p><p dir=3D"ltr"><br></p><=
/div>
</blockquote></div></div>

--000000000000b1cbd0061aef304b--