On Thu, Aug 10, 2023 at 11:49 AM Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx> wrote:@Wetmore, Matthew (CTR) How exactly?, In this case, we are not gonna need the old table. That is just kind of backup onlyOn Thu, 10 Aug 2023 at 08:27, Wetmore, Matthew (CTR) <Matthew.Wetmore@xxxxxxxxxxxxxxxxxxx> wrote:A partitioned table with less than 500M rows will suffer from performance issues. Just my $0.02
From: Holger Jakobs <holger@xxxxxxxxxx>
Sent: Thursday, August 10, 2023 3:12 AM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: Automate to rename table
Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.
Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx>:
I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.
So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.
How do we automate it?
I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.
Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days?
What's the best way?
On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@xxxxxxxxx> wrote:
Wild. What's the use-case here?
On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@xxxxxxxxxxxxxxxxxxx> wrote:
Simple CRON entry with a script that runs a psql command
You can:
alter table rename
Or
create table x as select * from original table.
Depending on you DDL needs or permissions/setup
From: Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Automate to rename table
Hi friends,
What is the best way to rename the table name automatically every 30 minutes?
--
Wells Oliver
wells.oliver@xxxxxxxxxPartitioned tables have overhead for routing the rows to the proper underlying table. As a table grows in size, that ovehead becomes less and less relevant vs the performance of dealing with a larger and larger table.Partitioning would seem to be an ideal solution here if that overhead is not relevant for your situation. I would recommend at least trying it out. Trigger-based partitioning would be significantly more overhead than built-in declarative partitioning, so I wouldn't recommend going down the trigger route unless you discover it's necessary.pg_partman can help with automating both the new child table creation and retention of the old table either being detached or dropped from the partition set. It also has some work-arounds for the primary key issue that may or may not work for you--
Just noticed you said you don't have a date column in this table. That would be needed with pg_partman. I would recommend adding some sort of date column into this table either way just so it's easier to keep track of the age of the data itself you're dealing with. Could just be a simple "created_at" column that is just set as a default of CURRENT_TIMESTAMP and no user has to worry about setting it.
On Thu, Aug 10, 2023 at 12:21 PM Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx> wrote:
