Fwd: PostGreSQL Replication and question on maintenance

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

> Hello postGreSQL Community ,
>
>
>
> Hope everyone is doing great !!.
>
>
> *Background*
>
> We use PostgreSQL Version 10.6 version and heavily use PostgreSQL for our
> day to day activities to write and read data. We have 2 clusters running
> PostgreSQL engine , one cluster
>
> keeps data up to 60 days and another cluster retains data beyond 1 year.
> The data is partitioned close to a week( ~evry 5 days a partition) and we
> have around 5 partitions per month per each table and we have 2 tables
> primarily so that will be 10 tables a week. So in the cluster-1 we have
> around  20 partitions and in cluster-2 we have around 160 partitions ( da=
ta
> from 2018). We also want to keep the data for up to 2 years in the
> cluster-2 to serve the data needs of the customer and so far we reached
> upto 1 year of maintaining this data.
>
>
>
> *Current activity*
>
> We have a custom weekly migration DB script job that moves data from 1
> cluster to another cluster what it does is the below things.
>
> 1) COPY command to copy the data from cluster-1 and split that data into
> binary files
>
> 2) Writing the binary data into the cluster-2 table
>
> 3) Creating indexes after the data is copied.
>
>
>
> *Problem what we have right now. *
>
> When the migration activity runs(weekly) from past 2 times , we saw the
> cluster read replica instance has restarted as it fallen behind the
> master(writer instance). Everything
>
> after that worked seamlessly but we want to avoid the replica getting
> restarted. To avoid from restart we started doing smaller binary files an=
d
> copy those files to the cluster-2
>
> instead of writing 1 big file of 450 million records. We were successful
> in the recent migration as the reader instance didn=E2=80=99t restart aft=
er we
> split 1 big file into multiple files to copy the data over but did restar=
t
> after the indexes are created on the new table as it could be write
> intensive.
>
>
>
> *DB parameters set on migration job*
>
> work_mem set to 8 GB  and maintenace_work_mem=3D32 GB.
>
> Indexes per table =3D 3
>
> total indexes for 2 tables =3D 5
>
>
>
> *DB size*
>
> Cluster-2 =3D 8.6 TB
>
> Cluster-1 =3D 3.6 TB
>
> Peak Table relational rows =3D 400 - 480 million rows
>
> Average table relational rows =3D 300 - 350 million rows.
>
> Per table size =3D 90 -95 GB , per table index size is about 45 GB
>
>
>
> *Questions*
>
> 1) Can we decrease the maintenace_work_mem to 16 GB and will it slow down
> the writes to the cluster , with that the reader instance can sync the da=
ta
> slowly ?.
>
> 2) Based on the above use case what are your recommendations to keep the
> data longer up to 2 years ?
>
> 3) What other recommendations you recommend ?.
>
>
>
>
>
> Appreciate your replies.
>
> THanks
> githubkran
>
>>

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

<div dir=3D"ltr"><br><div class=3D"gmail_quote"><div dir=3D"ltr"><div class=
=3D"gmail_quote"><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"><div dir=3D"ltr"><p class=3D"MsoNormal" style=3D"margin:4.8pt 0in =
2.4pt;line-height:110%;font-size:10pt;font-family:Verdana,sans-serif"><span=
 style=3D"font-family:Calibri,sans-serif">Hello postGreSQL Community ,</spa=
n></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p><p class=3D"MsoNormal" style=3D"margin:4.8=
pt 0in 2.4pt;line-height:110%;font-size:10pt;font-family:Verdana,sans-serif=
"><span style=3D"font-family:Calibri,sans-serif">Hope everyone is doing gre=
at !!.</span></p><p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;lin=
e-height:110%;font-size:10pt;font-family:Verdana,sans-serif"><span style=3D=
"font-family:Calibri,sans-serif"><br></span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Background</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">We use PostgreSQL Version
10.6 version and heavily use PostgreSQL for our day to day activities to wr=
ite
and read data. We have 2 clusters running PostgreSQL engine , one cluster</=
span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">keeps data up to 60 days and
another cluster retains data beyond 1 year. The data is partitioned close t=
o a
week( ~evry 5 days a partition) and we have around 5 partitions per month p=
er
each table and we have 2 tables primarily so that will be 10 tables a week.=
 So
in the cluster-1 we have around=C2=A0 20
partitions and in cluster-2 we have around 160 partitions ( data from 2018)=
. We
also want to keep the data for up to 2 years in the cluster-2 to serve the =
data
needs of the customer and so far we reached upto 1 year of maintaining this
data.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Current activity</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">We have a custom weekly
migration DB script job that moves data from 1 cluster to another cluster w=
hat
it does is the below things. </span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">1) COPY command to copy the
data from cluster-1 and split that data into binary files</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">2) Writing the binary data
into the cluster-2 table</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">3) Creating indexes after the
data is copied. </span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif"><span style=3D"text-decoration-line:none">=C2=A0</spa=
n></span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Problem what we have right now. </span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">When the migration activity
runs(weekly) from past 2 times , we saw the cluster read replica instance h=
as
restarted as it fallen behind the master(writer instance). Everything</span=
></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">after that worked seamlessly
but we want to avoid the replica getting restarted. To avoid from restart w=
e
started doing smaller binary files and copy those files to the cluster-2 </=
span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">instead of writing 1 big file
of 450 million records. We were successful in the recent migration as the
reader instance didn=E2=80=99t restart after we split 1 big file into multi=
ple files to
copy the data over but did restart after the indexes are created on the new
table as it could be write intensive. </span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">DB parameters set on migration job</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">work_mem set to 8 GB=C2=A0 and maintenace_work_mem=3D32 GB.=
</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Indexes per table =3D 3</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">total indexes for 2 tables =3D
5</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">DB size</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Cluster-2 =3D 8.6 TB</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Cluster-1 =3D 3.6 TB</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Peak Table relational rows =3D
400 - 480 million rows</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Average table relational rows
=3D 300 - 350 million rows.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Per table size =3D 90 -95 GB ,
per table index size is about 45 GB</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Questions</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">1) Can we decrease the
maintenace_work_mem to 16 GB and will it slow down the writes to the cluste=
r ,
with that the reader instance can sync the data slowly ?.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">2) Based on the above use
case what are your recommendations to keep the data longer up to 2 years ? =
</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">3) What other recommendations
you recommend ?.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Appreciate your replies.</span></p></div><br><div class=3D"=
gmail_quote"><div class=3D"gmail_attr">THanks</div><div class=3D"gmail_attr=
">githubkran</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 cl=
ass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_quote=
"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"gmail_quo=
te"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div=
 dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div dir=3D"ltr"><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"><div class=3D"gmail_qu=
ote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"gmail_=
quote"><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><div class=3D=
"gmail_quote"><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><div c=
lass=3D"gmail_quote"><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"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div>
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div>
</blockquote></div></div></div>
</blockquote></div>
</blockquote></div></div></div>
</blockquote></div>
</blockquote></div></div>
</blockquote></div>
</blockquote></div></div>
</blockquote></div></div>
</div></div>

--00000000000030bea405975bd7d5--