Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1tC5Ac-00Azhc-Lj
	for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2024 22:52:05 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1tC5Aa-00C99o-29
	for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2024 22:52:04 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <rui@crazybean.net>)
	id 1tC5AZ-00C99Z-N8
	for pgsql-admin@lists.postgresql.org; Fri, 15 Nov 2024 22:52:04 +0000
Received: from tunnel173844-pt.tunnel.tserv13.ash1.ipv6.he.net
 ([2001:470:7:2e6::2] helo=sloth.crazybean.net)
	by magus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <rui@crazybean.net>)
	id 1tC5AW-0029O9-Gy
	for pgsql-admin@postgresql.org; Fri, 15 Nov 2024 22:52:03 +0000
Received: from smtpclient.apple (<unknown>
 [2001:470:e117:4:506a:ca05:99aa:144f])
	by sloth.crazybean.net (OpenSMTPD) with ESMTPSA id 93471091
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Fri, 15 Nov 2024 17:51:59 -0500 (EST)
From: Rui DeSousa <rui@crazybean.net>
Message-Id: <F3BCD9E6-EB7F-475E-B20C-62D11950FE1D@crazybean.net>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_C1F1AEBB-9C9A-42EE-8B26-B0D295AA63D0"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.10\))
Subject: Re: PostgreSQL historical database
Date: Fri, 15 Nov 2024 17:51:58 -0500
In-Reply-To: 
 <CAODZiv6e4fBROiXjZ8iKEcEMrt=qUeP+vyoc5RmjKpqu8Z_THw@mail.gmail.com>
Cc: Erik Serrano <eserranos@gmail.com>,
 Samed YILDIRIM <samed@reddoc.net>,
 Pgsql-admin <pgsql-admin@lists.postgresql.org>,
 pgsql-admin <pgsql-admin@postgresql.org>
To: Keith Fiske <keith.fiske@crunchydata.com>
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>
X-Mailer: Apple Mail (2.3696.120.41.1.10)
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/F3BCD9E6-EB7F-475E-B20C-62D11950FE1D%40crazybean.net>
Precedence: bulk


--Apple-Mail=_C1F1AEBB-9C9A-42EE-8B26-B0D295AA63D0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8



> On Nov 5, 2024, at 1:15 PM, Keith Fiske <keith.fiske@crunchydata.com> =
wrote:
>=20
> 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 =
slice this problem. =20

You need some form of logical replication.  I=E2=80=99ve used both of =
these 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.=20
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 =
content 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 form of partitioned tables and had no performance =
issues.


--Apple-Mail=_C1F1AEBB-9C9A-42EE-8B26-B0D295AA63D0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br =
class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div =
class=3D"">On Nov 5, 2024, at 1:15 PM, Keith Fiske &lt;<a =
href=3D"mailto:keith.fiske@crunchydata.com" =
class=3D"">keith.fiske@crunchydata.com</a>&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div class=3D""><span =
style=3D"caret-color: rgb(0, 0, 0); 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; =
-webkit-text-stroke-width: 0px; text-decoration: none; float: none; =
display: inline !important;" class=3D"">I would first recommend looking =
into partitioning for managing data retention like =
this.</span></div></blockquote></div><br class=3D""><div class=3D"">I =
don=E2=80=99t think you need partitioning. &nbsp;The is so many ways to =
slice this problem. &nbsp;</div><div class=3D""><br class=3D""></div><div =
class=3D"">You need some form of logical replication. &nbsp;I=E2=80=99ve =
used both of these methods in the past to do exactly the issue at hand. =
&nbsp;These are not the only solutions.</div><div class=3D""><br =
class=3D""></div><div class=3D"">1, Queue tables via triggers and then =
asynchronous replicate those transaction to the archive database in real =
time.&nbsp;</div><div class=3D"">2. Messages queues; Modern application =
make use of massage queues; Just attach the archive database to the same =
message queue.</div><div class=3D""><br class=3D""></div><div class=3D"">I=
 don=E2=80=99t see a need for partitioning. &nbsp;I managed a =
Muti-terabyte content delivery platform where the data was only licensed =
between 30 to 90 days. &nbsp;Records where being purged more or less =
just as fast as new records were being created. &nbsp;In essence the =
database was completely reloaded with new data every 30-90 days =
depending on the data stream. &nbsp;We did no use any form of =
partitioned tables and had no performance issues.</div><div class=3D""><br=
 class=3D""></div></body></html>=

--Apple-Mail=_C1F1AEBB-9C9A-42EE-8B26-B0D295AA63D0--