This is a multi-part message in MIME format.
------=_NextPart_000_0004_01CA9EBF.302650C0
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Hello,
We have here a postgres DB receiving daily few giga of text data, from a
production system, which has to be merged into tables (insert new rows &
update existing) on a Postgres database. Then we run aggregation queries to
update reports. The database size will grow regularly over time as we have
to keep at least 2 years of history
The thing is that we don't need to keep old versions of rows (to save disk
space, get the highest level of performances, and this is not for a
transactional application).
So currently I am doing full vacuums + recollect stats daily at the end of
the loading process
Is there another way (in particular avoiding the full vacuum & save loading
time) to not keep or clean these old versions of the updated rows. There is
a full backup done regularly + procedures for recovery. But I am not very
familiar with auto vacuum settings so I don't know what to choose.
For your information, biggest tables are partitioned by month so they
contains not more than few hundred mega, but bulk updates impacts old
partitions.
Best regards,
Julien Theulier
------=_NextPart_000_0004_01CA9EBF.302650C0
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
[at] font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
[at] font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Texte de bulles Car";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.TextedebullesCar
{mso-style-name:"Texte de bulles Car";
mso-style-priority:99;
mso-style-link:"Texte de bulles";
font-family:"Tahoma","sans-serif";}
..MsoChpDefault
{mso-style-type:export-only;}
[at] page Section1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DFR link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><span lang=3DEN-US>Hello,<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US>We have here a postgres DB =
receiving daily few
giga of text data, from a production system, which has to be merged into =
tables
(insert new rows & update existing) on a Postgres database. Then we =
run aggregation
queries to update reports. The database size will grow regularly over =
time as
we have to keep at least 2 years of history<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US>The thing is that we don’t =
need to
keep old versions of rows (to save disk space, get the highest level of =
performances,
and this is not for a transactional application).<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US>So currently I am doing full =
vacuums +
recollect stats daily at the end of the loading =
process<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US>Is there another way (in =
particular
avoiding the full vacuum & save loading time) to not keep or clean =
these
old versions of the updated rows. There is a full backup done regularly =
+
procedures for recovery. But I am not very familiar with auto vacuum =
settings
so I don’t know what to choose.<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US>For your information, biggest =
tables are partitioned
by month so they contains not more than few hundred mega, but bulk =
updates impacts
old partitions.<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US>Best =
regards,<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US>Julien =
Theulier<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p>
</div>
</body>
</html>
------=_NextPart_000_0004_01CA9EBF.302650C0--
