MIME-Version: 1.0
References: <CAEzWdqfqSt6J-ja--fj5FvO2rG2wvETzJv4oYoPREJ3DbTijBA@mail.gmail.com>
In-Reply-To: <CAEzWdqfqSt6J-ja--fj5FvO2rG2wvETzJv4oYoPREJ3DbTijBA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 6 Aug 2024 17:43:13 -0400
Message-ID: <CANzqJaCjTv7ZzUnx+UE7urbPYcx-BxdOUSeoQNqjk61ZJFvMtw@mail.gmail.com>
Subject: Re: Standard of data storage and transformation
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e945ec061f0aae1b"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCjTv7ZzUnx%2BUE7urbPYcx-BxdOUSeoQNqjk61ZJFvMtw%40mail.gmail.com>
Precedence: bulk

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

On Tue, Aug 6, 2024 at 5:07=E2=80=AFPM yudhi s <learnerdatabase99@gmail.com=
> 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 event
> 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 case=
s
> of running online transaction search reports which will be mostly real ti=
me
> reporting and also some daily transaction batch reports based on customer=
s
> and also month end reports for customers. In target state it will hold
> Approx. ~400 million transactions/day which can be billions of rows acros=
s
> multiple related parent/child tables.
>
> There is another requirement to send these customer transaction data to a=
n
> olap system which is in a snowflake database and there it will be persist=
ed
> for many years. The lag between the data in postgres/oltp and in snowflak=
e
> 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 worth of
> transaction data, it will point to the snowflake database.
>
> Now the question which we are wondering is , should we send the data as i=
s
> in normalized table form to snowflake and then there we transform/flatten
> 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( f=
or
> example creating materialized views on top of base 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 over
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 partition
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 hour
partition would be smaller.
9) Testing.  Nothing beats full-scale testing.

--=20
Death to America, and butter sauce!
Iraq lobster...

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Aug 6, 2024 at 5:07=E2=80=AFPM yu=
dhi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerdatabase99@=
gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote cl=
ass=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">Hi All,<div>We are hav=
ing a use case in which we are having transaction data for multiple custome=
rs in one of the Postgres databases(version 15.4) and we are consuming it f=
rom multiple sources(batch file processing, kafka event processing etc). It=
&#39;s currently stored in normalized form=C2=A0postgres with=C2=A0constrai=
nts,=C2=A0indexes, partitions defined. This postgres database is holding th=
e transaction data for around a month or so. There are use cases of running=
 online transaction search reports which will be mostly real time reporting=
 and also some daily transaction batch reports based on customers and also =
month end reports for customers. In target state it will hold Approx. ~400 =
million transactions/day which can be billions of rows across multiple rela=
ted parent/child tables.<br></div><div><br>There is another requirement to =
send these customer transaction data to an olap system which is in a snowfl=
ake 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 &lt;1 month worth of transaction data and if it=
 needs to scan for &gt;1month worth of transaction data, it will point to t=
he snowflake database.<br><br>Now the question which we are wondering is , =
should we send the data as is in normalized table form to snowflake and the=
n there we transform/flatten the data to support the reporting use case or =
should we first flatten or transform the data in postgres itself and make i=
t as another structure( for example creating materialized views on top of b=
ase table) and only then move that data to the snowflake? What is the appro=
priate standard and downside if we do anything different.<br></div></div></=
blockquote><div><br></div><div>Some thoughts:</div><div>0) How big are the =
records?</div><div>1) Materialized views add disk space overhead.</div><div=
>2) Materialized views are for when you query the same static over and over=
 again.</div><div>3) IIUC, you&#39;ll be moving the data from PG to Snowfla=
ke just once.</div><div>4) Writing an MV to disk and then reading it only o=
nce doubles the IO requirements.</div><div>5) Thus, my first thought would =
be to extract the data from PG using a denormalizing &quot;plain&quot;=C2=
=A0view.=C2=A0</div><div>5a) If you can&#39;t make that Fast Enough, then o=
bviously you must pull the normalized data from PG and denorm it elsewhere.=
=C2=A0 You know your situation better than us.</div><div>6) Indices will be=
 critical: not too many, but not too few.</div><div>7) Obviously consider p=
artitioning, 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 T=
hat&#39;s a LOT.</div><div>7b) Partitioning=C2=A0every THREE hours means on=
ly 248 child tables.=C2=A0 A lot, but much more manageable.</div><div>7c) T=
hat might well kill reporting performance, though, if it&#39;s for example =
one customer across many partitions.</div><div>8) You (hopefully) know what=
 kind of queries will be run.=C2=A0 Maybe partition by customer (or whateve=
r) range and THEN by an hour range.</div><div>8a) You&#39;d have to simulta=
neously run multiple extract 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) Testing.=C2=A0 Nothing beats full-scale testing.</di=
v><div><br></div></div><span class=3D"gmail_signature_prefix">-- </span><br=
><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to Ameri=
ca, and butter sauce!<div>Iraq lobster...</div></div></div></div>

--000000000000e945ec061f0aae1b--