Re: Automate to rename table

On Thu, Aug 10, 2023 at 11:49 AM Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
wrote:

> @Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com> How
> exactly?, In this case, we are not gonna need the old table. That is just
> kind of backup only
>
> On Thu, 10 Aug 2023 at 08:27, Wetmore, Matthew (CTR) <
> Matthew(dot)Wetmore(at)express-scripts(dot)com> wrote:
>
>> A partitioned table with less than 500M rows will suffer from performance
>> issues. Just my $0.02
>>
>>
>>
>> *From:* Holger Jakobs <holger(at)jakobs(dot)com>
>> *Sent:* Thursday, August 10, 2023 3:12 AM
>> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
>> *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(dot)dba09(at)gmail(dot)com>:
>>
>> 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(dot)oliver(at)gmail(dot)com>
>> wrote:
>>
>> Wild. What's the use-case here?
>>
>>
>>
>> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
>> Matthew(dot)Wetmore(at)express-scripts(dot)com> 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(dot)dba09(at)gmail(dot)com>
>> *Sent:* Wednesday, August 9, 2023 1:39 PM
>> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
>> *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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>>
>>
Partitioned 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

https://github.com/pgpartman/pg_partman

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com