public inbox for [email protected]  

help / color / mirror / Atom feed

From: Keith Fiske <[email protected]>
To: 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: Tue, 5 Nov 2024 13:15:12 -0500
Message-ID: <CAODZiv6e4fBROiXjZ8iKEcEMrt=qUeP+vyoc5RmjKpqu8Z_THw@mail.gmail.com> (raw)
In-Reply-To: <CA+dvXXvQAFm_NHKStG4P8WOxWhM6DGYLcMMM9_B02iE5gsw_bg@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>

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

On Tue, Nov 5, 2024 at 12:30=E2=80=AFPM Erik Serrano <[email protected]> =
wrote:

>
> Dear Sirs,
>
> I'll tell you a little about what I need. Normally, during the day,
> records are made or recorded in the main database, which at the end of th=
e
> day are consolidated (accounting closings) and are recorded in the
> database. In order not to make the main database grow without measure
> (which will only maintain the range between 3 months to 1 year). For this
> reason, this data must be transferred to another database so that it last=
s
> over time and can be consulted by other areas. (This action is done human=
ly
> every day of the year at the end of the day)
> Therefore, the project seeks to be able to carry out this extraction of
> the consolidated data to another database, but automatically.
>
> I was thinking of doing this with some triggers or with jobs that allow m=
e
> to carry out these actions. I also thought of creating a replication of
> only the consolidated tables to the new historical database server, but I
> have not yet defined the method.
>
> That's why I need to know if there is a tool that allows me to create thi=
s
> database.
>
> I hope this clarifies a little the scope of the new historical database.
>
> Thank you very much in advance
> Regards
>
>
> *Erik R. Serrano Saavedra*
> *      Data Base Administrator*
>
>

I would first recommend looking into partitioning for managing data
retention like this. As Ron says, you'll want to look into the performance
implications of this, but it allows for the most efficient method of
removing old data from PostgreSQL and is typically worth the overhead
costs. Otherwise you're dealing with potentially expensive deletion
operations and managing bloat vs just detaching/dropping a table.

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]
  Subject: Re: PostgreSQL historical database
  In-Reply-To: <CAODZiv6e4fBROiXjZ8iKEcEMrt=qUeP+vyoc5RmjKpqu8Z_THw@mail.gmail.com>

* 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