pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutomate to rename table
11+ messages / 6 participants
[nested] [flat]
* Automate to rename table
@ 2023-08-09 20:38 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Rajesh Kumar @ 2023-08-09 20:38 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi friends,
What is the best way to rename the table name automatically every 30
minutes?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Automate to rename table
@ 2023-08-09 20:49 Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-08-09 20:49 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table
Hi friends,
What is the best way to rename the table name automatically every 30 minutes?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 09:04 Wells Oliver <wells.oliver@gmail.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 1 reply; 11+ messages in thread
From: Wells Oliver @ 2023-08-10 09:04 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Wild. What's the use-case here?
On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
> *Sent:* Wednesday, August 9, 2023 1:39 PM
> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 09:52 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Rajesh Kumar @ 2023-08-10 09:52 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; pgsql-admin@lists.postgresql.org
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@gmail.com> wrote:
> Wild. What's the use-case here?
>
> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
> Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
>> *Sent:* Wednesday, August 9, 2023 1:39 PM
>> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <wellsoliver@gmail.com>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 10:11 Holger Jakobs <holger@jakobs.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 2 replies; 11+ messages in thread
From: Holger Jakobs @ 2023-08-10 10:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@gmail.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.oliver@gmail.com> wrote:
>
>> Wild. What's the use-case here?
>>
>> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
>> Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
>>> *Sent:* Wednesday, August 9, 2023 1:39 PM
>>> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <wellsoliver@gmail.com>
>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 15:06 Ron <ronljohnsonjr@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 11+ messages in thread
From: Ron @ 2023-08-10 15:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
That requires adding the date field to the PK.
On 8/10/23 05:11, Holger Jakobs wrote:
> 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@gmail.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.oliver@gmail.com>
> wrote:
>
> Wild. What's the use-case here?
>
> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR)
> <Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
> *Sent:* Wednesday, August 9, 2023 1:39 PM
> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 15:07 Ron <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Ron @ 2023-08-10 15:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Old school trigger+function based partitioning is exactly what you want.
On 8/10/23 04:52, Rajesh Kumar wrote:
> 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@gmail.com> wrote:
>
> Wild. What's the use-case here?
>
> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR)
> <Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
> *Sent:* Wednesday, August 9, 2023 1:39 PM
> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Automate to rename table
@ 2023-08-10 15:27 Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 1 reply; 11+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-08-10 15:27 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
A partitioned table with less than 500M rows will suffer from performance issues. Just my $0.02
From: Holger Jakobs <holger@jakobs.com>
Sent: Thursday, August 10, 2023 3:12 AM
To: pgsql-admin@lists.postgresql.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.dba09@gmail.com<mailto:rajeshkumar.dba09@gmail.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.oliver@gmail.com<mailto:wells.oliver@gmail.com>> wrote:
Wild. What's the use-case here?
On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com<mailto:Matthew.Wetmore@express-scripts.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.dba09@gmail.com<mailto:rajeshkumar.dba09@gmail.com>>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.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.oliver@gmail.com<mailto:wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 15:49 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 1 reply; 11+ messages in thread
From: Rajesh Kumar @ 2023-08-10 15:49 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
@Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.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.Wetmore@express-scripts.com> wrote:
> A partitioned table with less than 500M rows will suffer from performance
> issues. Just my $0.02
>
>
>
> *From:* Holger Jakobs <holger@jakobs.com>
> *Sent:* Thursday, August 10, 2023 3:12 AM
> *To:* pgsql-admin@lists.postgresql.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.dba09@gmail.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.oliver@gmail.com>
> wrote:
>
> Wild. What's the use-case here?
>
>
>
> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
> Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
> *Sent:* Wednesday, August 9, 2023 1:39 PM
> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <wellsoliver@gmail.com>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 16:21 Keith Fiske <keith.fiske@crunchydata.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Keith Fiske @ 2023-08-10 16:21 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Aug 10, 2023 at 11:49 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> @Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.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.Wetmore@express-scripts.com> wrote:
>
>> A partitioned table with less than 500M rows will suffer from performance
>> issues. Just my $0.02
>>
>>
>>
>> *From:* Holger Jakobs <holger@jakobs.com>
>> *Sent:* Thursday, August 10, 2023 3:12 AM
>> *To:* pgsql-admin@lists.postgresql.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.dba09@gmail.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.oliver@gmail.com>
>> wrote:
>>
>> Wild. What's the use-case here?
>>
>>
>>
>> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
>> Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
>> *Sent:* Wednesday, August 9, 2023 1:39 PM
>> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <wellsoliver@gmail.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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automate to rename table
@ 2023-08-10 16:26 Keith Fiske <keith.fiske@crunchydata.com>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 0 replies; 11+ messages in thread
From: Keith Fiske @ 2023-08-10 16:26 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@crunchydata.com>
wrote:
>
>
> On Thu, Aug 10, 2023 at 11:49 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
>
>> @Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.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.Wetmore@express-scripts.com> wrote:
>>
>>> A partitioned table with less than 500M rows will suffer from
>>> performance issues. Just my $0.02
>>>
>>>
>>>
>>> *From:* Holger Jakobs <holger@jakobs.com>
>>> *Sent:* Thursday, August 10, 2023 3:12 AM
>>> *To:* pgsql-admin@lists.postgresql.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.dba09@gmail.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.oliver@gmail.com>
>>> wrote:
>>>
>>> Wild. What's the use-case here?
>>>
>>>
>>>
>>> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
>>> Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
>>> *Sent:* Wednesday, August 9, 2023 1:39 PM
>>> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <wellsoliver@gmail.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
>
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-08-10 16:26 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-09 20:38 Automate to rename table Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-08-09 20:49 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-08-10 09:04 ` Wells Oliver <wells.oliver@gmail.com>
2023-08-10 09:52 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-08-10 10:11 ` Holger Jakobs <holger@jakobs.com>
2023-08-10 15:06 ` Ron <ronljohnsonjr@gmail.com>
2023-08-10 15:27 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-08-10 15:49 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-08-10 16:21 ` Keith Fiske <keith.fiske@crunchydata.com>
2023-08-10 16:26 ` Keith Fiske <keith.fiske@crunchydata.com>
2023-08-10 15:07 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox