public inbox for [email protected]  

help / color / mirror / Atom feed

From: Rui DeSousa <[email protected]>
To: Keith Fiske <[email protected]>
Cc: Erik Serrano <[email protected]>
Cc: Samed YILDIRIM <[email protected]>
Cc: Pgsql-admin <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: PostgreSQL historical database
Date: Fri, 15 Nov 2024 17:51:58 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAODZiv6e4fBROiXjZ8iKEcEMrt=qUeP+vyoc5RmjKpqu8Z_THw@mail.gmail.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>



> On Nov 5, 2024, at 1:15 PM, Keith Fiske <[email protected]> wrote:
> 
> I would first recommend looking into partitioning for managing data retention like this.

I don’t think you need partitioning.  The is so many ways to slice this problem.  

You need some form of logical replication.  I’ve 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. 
2. Messages queues; Modern application make use of massage queues; Just attach the archive database to the same message queue.

I don’t 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.

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: PostgreSQL historical database
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox