MIME-Version: 1.0
References: 
 <CA+dvXXuiL4+SRCZ5fqTr4WZ+pHhRaC9e=D7APBcZ2JGJZMiy6w@mail.gmail.com>
 <CAAo1mbnMTFf9pAyoY7dGGR5xKmZcEd6-nRZ9xyNNa3d5DoJLyg@mail.gmail.com>
 <CA+dvXXvQAFm_NHKStG4P8WOxWhM6DGYLcMMM9_B02iE5gsw_bg@mail.gmail.com>
 <CAODZiv6e4fBROiXjZ8iKEcEMrt=qUeP+vyoc5RmjKpqu8Z_THw@mail.gmail.com>
 <F3BCD9E6-EB7F-475E-B20C-62D11950FE1D@crazybean.net>
In-Reply-To: <F3BCD9E6-EB7F-475E-B20C-62D11950FE1D@crazybean.net>
From: Erik Serrano <eserranos@gmail.com>
Date: Sat, 16 Nov 2024 10:41:29 -0300
Message-ID: 
 <CA+dvXXugr9a5xCi95hsi5_FKTpYvM3WDg6EdgAGacwOp-5YkqA@mail.gmail.com>
Subject: Re: PostgreSQL historical database
To: Rui DeSousa <rui@crazybean.net>
Cc: Keith Fiske <keith.fiske@crunchydata.com>,
 Samed YILDIRIM <samed@reddoc.net>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>,
 pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e35ebf062707d7bc"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BdvXXugr9a5xCi95hsi5_FKTpYvM3WDg6EdgAGacwOp-5YkqA%40mail.gmail.com>
Precedence: bulk

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

Muchas gracias, Michel .  Saludos

El vie, 15 nov 2024, 19:52, Rui DeSousa <rui@crazybean.net> escribi=C3=B3:

>
>
> On Nov 5, 2024, at 1:15 PM, Keith Fiske <keith.fiske@crunchydata.com>
> wrote:
>
> I would first recommend looking into partitioning for managing data
> retention like this.
>
>
> I don=E2=80=99t think you need partitioning.  The is so many ways to slic=
e this
> problem.
>
> You need some form of logical replication.  I=E2=80=99ve used both of the=
se
> methods in the past to do exactly the issue at hand.  These are not the
> only solutions.
>
> 1, Queue tables via triggers and then asynchronous replicate those
> transaction to the archive database in real time.
> 2. Messages queues; Modern application make use of massage queues; Just
> attach the archive database to the same message queue.
>
> I don=E2=80=99t see a need for partitioning.  I managed a Muti-terabyte c=
ontent
> delivery platform where the data was only licensed between 30 to 90 days.
> Records where being purged more or less just as fast as new records were
> being created.  In essence the database was completely reloaded with new
> data every 30-90 days depending on the data stream.  We did no use any fo=
rm
> of partitioned tables and had no performance issues.
>
>

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

<p dir=3D"ltr">Muchas gracias, Michel .=C2=A0 Saludos </p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">El vie=
, 15 nov 2024, 19:52, Rui DeSousa &lt;<a href=3D"mailto:rui@crazybean.net">=
rui@crazybean.net</a>&gt; escribi=C3=B3:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex"><div style=3D"word-wrap:break-word;line-break:after-white-space"><br>=
<div><br><blockquote type=3D"cite"><div>On Nov 5, 2024, at 1:15 PM, Keith F=
iske &lt;<a href=3D"mailto:keith.fiske@crunchydata.com" target=3D"_blank" r=
el=3D"noreferrer">keith.fiske@crunchydata.com</a>&gt; wrote:</div><br><div>=
<span style=3D"font-family:Helvetica;font-size:12px;font-style:normal;font-=
variant-caps:normal;font-weight:400;letter-spacing:normal;text-align:start;=
text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;tex=
t-decoration:none;float:none;display:inline!important">I would first recomm=
end looking into partitioning for managing data retention like this.</span>=
</div></blockquote></div><br><div>I don=E2=80=99t think you need partitioni=
ng.=C2=A0 The is so many ways to slice this problem. =C2=A0</div><div><br><=
/div><div>You need some form of logical replication.=C2=A0 I=E2=80=99ve use=
d both of these methods in the past to do exactly the issue at hand.=C2=A0 =
These are not the only solutions.</div><div><br></div><div>1, Queue tables =
via triggers and then asynchronous replicate those transaction to the archi=
ve database in real time.=C2=A0</div><div>2. Messages queues; Modern applic=
ation make use of massage queues; Just attach the archive database to the s=
ame message queue.</div><div><br></div><div>I don=E2=80=99t see a need for =
partitioning.=C2=A0 I managed a Muti-terabyte content delivery platform whe=
re the data was only licensed between 30 to 90 days.=C2=A0 Records where be=
ing purged more or less just as fast as new records were being created.=C2=
=A0 In essence the database was completely reloaded with new data every 30-=
90 days depending on the data stream.=C2=A0 We did no use any form of parti=
tioned tables and had no performance issues.</div><div><br></div></div></bl=
ockquote></div>

--000000000000e35ebf062707d7bc--