Re: Batch process

[Rama Krishnan <raghuldrag@xxxxxxxxx>]

Hi Adrian, 

Thanks for the update. 

Is it possible to achieve 10000 records deletion on every iteration because my original table contains 5M records during the deletion process it consumes more cpu and resources. 

On Wed, 20 Jul, 2022, 20:37 Adrian Klaver, <adrian.klaver@xxxxxxxxxxx> wrote:

On 7/19/22 22:08, Rama Krishnan wrote:
> Hi All,
>
> I am doing purge activity my sales table contains 5M records I am going
> to delete more than 1 year data (which was 3M) records so it was running
> more so I want to do batch wise deletion through plsql
>
>
>
>
>   created or replace function data_purge() returns void as$$
> Declare
> Begin
> Drop table test_old;
> Create table test_old as select * from sales where bill_date<now()
> -interval '1 year';
>
> Delete table sales where sales_id in (select sales_id from test_old;
>
> End;
> $$ language plpgsql;
>
>
> Kindly guide me
>

This looks like a case, going forward for partitioning:

https://www.postgresql.org/docs/14/ddl-partitioning.html


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx