MIME-Version: 1.0
References: <CAD=mzVUQUyM6H2O4WqVF=1tgrEMuxPq0QU+DbpG6vOBxzA8LbQ@mail.gmail.com>
 <CAH5mb99Q7+VxroUeHcxQxtti8KjbzFR64jt7B_x28+XyXh-TcQ@mail.gmail.com>
In-Reply-To: <CAH5mb99Q7+VxroUeHcxQxtti8KjbzFR64jt7B_x28+XyXh-TcQ@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sun, 16 Jun 2024 01:39:11 +0530
Message-ID: <CAEzWdqe94+n79xTgxBzsW-M4FDbcxRYbLck7EhUPbqLsrH_JOg@mail.gmail.com>
Subject: Re: Design for dashboard query
To: Sushrut Shivaswamy <sushrut.shivaswamy@gmail.com>
Cc: sud <suds1434@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000eab269061af34e25"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqe94%2Bn79xTgxBzsW-M4FDbcxRYbLck7EhUPbqLsrH_JOg%40mail.gmail.com>
Precedence: bulk

--000000000000eab269061af34e25
Content-Type: text/plain; charset="UTF-8"

>
>
> 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
>> 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.
>>
>>
>>
Yes incremental refresh is by default not available but you can do the
refresh using "concurrently" keyword which will be online i believe and
won't block your ongoing  queries if any.

And there are some other ways like create another view(say mv_new) with
exactly the same definition and refresh that with whatever interval you
want and then switch the views with the original one using the rename
command.


ALTER MATERIALIZED VIEW mv RENAME TO mv_old;
ALTER MATERIALIZED VIEW mv_new RENAME TO mv;
DROP MATERIALIZED VIEW mv_old;

Or

have a log table created (say transaction_log) and populate it using
triggers with each delta insert/update/delete on the transaction table. And
then schedule a cron job which will periodically flush the rows from the
transaction_log table to the materialized view. This will achieve your
incremental refresh.

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

<div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex"><div><br><div class=3D"gmail_quote"><div dir=3D"ltr" cla=
ss=3D"gmail_attr">On Sat, 15 Jun 2024 at 6:54 PM, sud &lt;<a href=3D"mailto=
:suds1434@gmail.com" target=3D"_blank">suds1434@gmail.com</a>&gt; wrote:<br=
></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;=
border-left:1px solid rgb(204,204,204);padding-left:1ex"><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 f=
or 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 tabl=
e data which is billions of rows across many months and then join with othe=
r master tables and aggregate those and then display the results based on t=
he 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 periodicall=
y to show the data based on the input user id.  However i am seeing , postg=
res not supporting incremental refresh of materialized view and full refres=
h can take longer. So , do we have any other option available? </span><span=
>Additionally , It should not impact or block the online users querying the=
 same materialized view when the refresh is happening.</span></p><p dir=3D"=
ltr"><br></p></div></blockquote></div></div></blockquote><div><br></div><di=
v>Yes incremental refresh is by default not available but you can do the re=
fresh using &quot;concurrently&quot; keyword which will be online i believe=
 and won&#39;t=C2=A0block your=C2=A0ongoing=C2=A0 queries if any.=C2=A0</di=
v><div><br></div><div>And there are some other ways like create another vie=
w(say mv_new) with exactly the same definition and refresh that with whatev=
er interval you want and then switch the views with the original one using =
the rename command.</div><div><br></div><div><br>ALTER MATERIALIZED VIEW mv=
 RENAME TO mv_old;<br>ALTER MATERIALIZED VIEW mv_new RENAME TO mv;<br>DROP =
MATERIALIZED VIEW mv_old;<br></div><div><br></div><div>Or</div><div><br></d=
iv><div>have a log table created (say transaction_log) and populate it usin=
g triggers with each delta insert/update/delete on the transaction table. A=
nd then schedule a cron job which will periodically flush the rows from the=
 transaction_log table to the materialized view. This will achieve your inc=
remental refresh.</div><div><br></div></div></div>

--000000000000eab269061af34e25--