Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fast Deletion For Large Tables

Fast Deletion For Large Tables

From
Raymond Chui
Date:
I have some tables with huge data.
The tables have column timestamp and float.
I am try to keep up to 6 day of data values.
What I do is execute SQL below from crontab (UNIX to
schedule commands).

BEGIN;
DELETE FROM table_1 WHERE column_time < ('now'::timestamp - '6
days'::interval);
.....
DELETE FROM table_n WHERE column_time < ('now'::timestamp - '6
days'::interval);
COMMIT;


Everything is running fine, except take long time to finish.
Because some tables stored values from 50,000 to 100,000 rows
Some deletion need to deleted up to 45,000 rows.

So I am thinking just delete the rows by their row number or row ID,
like

DELETE FROM a_table WHERE row_id < 45000;

I know there is row_id in Oracle.
Is there row_id for a table in Postgres?
Thank Q!

Re: [ADMIN] Fast Deletion For Large Tables

From
Stephan Szabo
Date:
On Wed, 2 Oct 2002, Raymond Chui wrote:

> I have some tables with huge data.
> The tables have column timestamp and float.
> I am try to keep up to 6 day of data values.
> What I do is execute SQL below from crontab (UNIX to
> schedule commands).
>
> BEGIN;
> DELETE FROM table_1 WHERE column_time < ('now'::timestamp - '6
> days'::interval);
> .....
> DELETE FROM table_n WHERE column_time < ('now'::timestamp - '6
> days'::interval);
> COMMIT;
>
>
> Everything is running fine, except take long time to finish.
> Because some tables stored values from 50,000 to 100,000 rows
> Some deletion need to deleted up to 45,000 rows.
>
> So I am thinking just delete the rows by their row number or row ID,
> like
>
> DELETE FROM a_table WHERE row_id < 45000;
>
> I know there is row_id in Oracle.
> Is there row_id for a table in Postgres?

Not really of that sort IIRC Oracle's row_id definition, although you
could probably fake something with a sequence.

Re: [GENERAL] Fast Deletion For Large Tables

From
Roland Roberts
Date:
>>>>> "rc" == Raymond Chui <raymond.chui@noaa.gov> writes:

    rc> Everything is running fine, except take long time to finish.
    rc> Because some tables stored values from 50,000 to 100,000 rows
    rc> Some deletion need to deleted up to 45,000 rows.

Is column_time indexed?

roland
--
               PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375

Re: [GENERAL] Fast Deletion For Large Tables

From
Roland Roberts
Date:
>>>>> "rc" == Raymond Chui <raymond.chui@noaa.gov> writes:

roland
--
               PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375