MIME-Version: 1.0
References: <CAEzWdqfqSt6J-ja--fj5FvO2rG2wvETzJv4oYoPREJ3DbTijBA@mail.gmail.com>
 <CANzqJaCjTv7ZzUnx+UE7urbPYcx-BxdOUSeoQNqjk61ZJFvMtw@mail.gmail.com>
In-Reply-To: <CANzqJaCjTv7ZzUnx+UE7urbPYcx-BxdOUSeoQNqjk61ZJFvMtw@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Thu, 8 Aug 2024 01:34:05 +0530
Message-ID: <CAEzWdqcphs605OCn2YXZoa_ayQAycaXzymZ9oC3sQFMWxOfHMQ@mail.gmail.com>
Subject: Re: Standard of data storage and transformation
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005b3a74061f1d6a0e"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqcphs605OCn2YXZoa_ayQAycaXzymZ9oC3sQFMWxOfHMQ%40mail.gmail.com>
Precedence: bulk

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

On Wed, Aug 7, 2024 at 3:13=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com=
> wrote:

> On Tue, Aug 6, 2024 at 5:07=E2=80=AFPM yudhi s <learnerdatabase99@gmail.c=
om>
> wrote:
>
>> Hi All,
>> We are having a use case in which we are having transaction data for
>> multiple customers in one of the Postgres databases(version 15.4) and we
>> are consuming it from multiple sources(batch file processing, kafka even=
t
>> processing etc). It's currently stored in normalized form postgres
>> with constraints, indexes, partitions defined. This postgres database is
>> holding the transaction data for around a month or so. There are use cas=
es
>> of running online transaction search reports which will be mostly real t=
ime
>> reporting and also some daily transaction batch reports based on custome=
rs
>> and also month end reports for customers. In target state it will hold
>> Approx. ~400 million transactions/day which can be billions of rows acro=
ss
>> multiple related parent/child tables.
>>
>> There is another requirement to send these customer transaction data to
>> an olap system which is in a snowflake database and there it will be
>> persisted for many years. The lag between the data in postgres/oltp and =
in
>> snowflake will be ~1hr. And any reporting api can query postgres for <1
>> month worth of transaction data and if it needs to scan for >1month wort=
h
>> of transaction data, it will point to the snowflake database.
>>
>> Now the question which we are wondering is , should we send the data as
>> is in normalized table form to snowflake and then there we
>> transform/flatten the data to support the reporting use case or should w=
e
>> first flatten or transform the data in postgres itself and make it as
>> another structure( for example creating materialized views on top of bas=
e
>> table) and only then move that data to the snowflake? What is the
>> appropriate standard and downside if we do anything different.
>>
>
> Some thoughts:
> 0) How big are the records?
> 1) Materialized views add disk space overhead.
> 2) Materialized views are for when you query the same static over and ove=
r
> again.
> 3) IIUC, you'll be moving the data from PG to Snowflake just once.
> 4) Writing an MV to disk and then reading it only once doubles the IO
> requirements.
> 5) Thus, my first thought would be to extract the data from PG using a
> denormalizing "plain" view.
> 5a) If you can't make that Fast Enough, then obviously you must pull the
> normalized data from PG and denorm it elsewhere.  You know your situation
> better than us.
> 6) Indices will be critical: not too many, but not too few.
> 7) Obviously consider partitioning, but note that too many partitions can
> make query planning MUCH slower.
> 7a) 31 days cut into hours means 744 partitions.  That's a LOT.
> 7b) Partitioning every THREE hours means only 248 child tables.  A lot,
> but much more manageable.
> 7c) That might well kill reporting performance, though, if it's for
> example one customer across many partitions.
> 8) You (hopefully) know what kind of queries will be run.  Maybe partitio=
n
> by customer (or whatever) range and THEN by an hour range.
> 8a) You'd have to simultaneously run multiple extract jobs (on for each
> "customer" range), but that might not be too onerous, since then each hou=
r
> partition would be smaller.
> 9) Testing.  Nothing beats full-scale testing.
>
>
>
The table has ~100+ columns but I think the denormalized or the flatten
table which we are planning to create will mostly have a large number of
columns in it as that will be based on the columns from multiple tables
joined together. However, the plan was to have the cooked data ready so as
not to do the additional work in target or downstream. So I was thinking of
a physical model for persisting the transformed data(using MV) rather than
using a simple view which will have performance overhead.

Because what is happening is , after the data moves to snowflake , people
try to create their own version of the transformed table on top of
these normalized tables(which we call as refiners) and then query those
from UI or for reporting. And some people say we should avoid those
downstream refiners and should do it here in source/postgres.Also the plan
was to move the data from postgres once every hour.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Aug 7, 2024 at 3:13=E2=80=AFAM Ro=
n Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@gmai=
l.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Tue, =
Aug 6, 2024 at 5:07=E2=80=AFPM yudhi s &lt;<a href=3D"mailto:learnerdatabas=
e99@gmail.com" target=3D"_blank">learnerdatabase99@gmail.com</a>&gt; wrote:=
<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex"><div dir=3D"ltr">Hi All,<div>We are having a use case in which =
we are having transaction data for multiple customers in one of the Postgre=
s databases(version 15.4) and we are consuming it from multiple sources(bat=
ch file processing, kafka event processing etc). It&#39;s currently stored =
in normalized form=C2=A0postgres with=C2=A0constraints,=C2=A0indexes, parti=
tions defined. This postgres database is holding the transaction data for a=
round a month or so. There are use cases of running online transaction sear=
ch reports which will be mostly real time reporting and also some daily tra=
nsaction batch reports based on customers and also month end reports for cu=
stomers. In target state it will hold Approx. ~400 million transactions/day=
 which can be billions of rows across multiple related parent/child tables.=
<br></div><div><br>There is another requirement to send these customer tran=
saction data to an olap system which is in a snowflake database and there i=
t will be persisted for many years. The lag between the data in postgres/ol=
tp and in snowflake will be ~1hr. And any reporting api can query postgres =
for &lt;1 month worth of transaction data and if it needs to scan for &gt;1=
month worth of transaction data, it will point to the snowflake database.<b=
r><br>Now the question which we are wondering is , should we send the data =
as is in normalized table form to snowflake and then there we transform/fla=
tten the data to support the reporting use case or should we first flatten =
or transform the data in postgres itself and make it as another structure( =
for example creating materialized views on top of base table) and only then=
 move that data to the snowflake? What is the appropriate standard and down=
side if we do anything different.<br></div></div></blockquote><div><br></di=
v><div>Some thoughts:</div><div>0) How big are the records?</div><div>1) Ma=
terialized views add disk space overhead.</div><div>2) Materialized views a=
re for when you query the same static over and over again.</div><div>3) IIU=
C, you&#39;ll be moving the data from PG to Snowflake just once.</div><div>=
4) Writing an MV to disk and then reading it only once doubles the IO requi=
rements.</div><div>5) Thus, my first thought would be to extract the data f=
rom PG using a denormalizing &quot;plain&quot;=C2=A0view.=C2=A0</div><div>5=
a) If you can&#39;t make that Fast Enough, then obviously you must pull the=
 normalized data from PG and denorm it elsewhere.=C2=A0 You know your situa=
tion better than us.</div><div>6) Indices will be critical: not too many, b=
ut not too few.</div><div>7) Obviously consider partitioning, but note that=
 too many partitions can make query planning MUCH slower.</div><div>7a) 31 =
days cut into hours means 744 partitions.=C2=A0 That&#39;s a LOT.</div><div=
>7b) Partitioning=C2=A0every THREE hours means only 248 child tables.=C2=A0=
 A lot, but much more manageable.</div><div>7c) That might well kill report=
ing performance, though, if it&#39;s for example one customer across many p=
artitions.</div><div>8) You (hopefully) know what kind of queries will be r=
un.=C2=A0 Maybe partition by customer (or whatever) range and THEN by an ho=
ur range.</div><div>8a) You&#39;d have to simultaneously run multiple extra=
ct jobs (on for each &quot;customer&quot; range), but that might not be too=
 onerous, since then each hour partition would be smaller.</div><div>9) Tes=
ting.=C2=A0 Nothing beats full-scale testing.</div><div><br></div></div><br=
></div></blockquote><div><br></div><div>The table has ~100+ columns but I t=
hink the denormalized or the flatten table which we are planning to create =
will mostly have a large number of columns in it as that will be based on t=
he columns from multiple tables joined together. However, the plan was to h=
ave the cooked data ready so as not to do the additional work in target or =
downstream. So I was thinking of a physical model for persisting the transf=
ormed data(using=C2=A0MV) rather than using a simple view which will have p=
erformance overhead.=C2=A0</div><div><br></div><div>Because what is happeni=
ng is , after the data moves to snowflake , people try to create their own =
version of the transformed table on top of these=C2=A0normalized tables(whi=
ch we call as refiners) and then query those from UI or for reporting. And =
some people say we should avoid those downstream refiners and should do it =
here in source/postgres.Also the plan was to move the data from postgres on=
ce every hour.</div></div></div>

--0000000000005b3a74061f1d6a0e--