Обсуждение: Fast Deletion For Large Tables

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!

On 2 Oct 2002 at 14:20, Raymond Chui wrote:
> 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.

That's not much. How much time it exactly takes? Does it have index on
timestamp field?

>
> 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 don't think that's the problem. Do you have big enough WAL's for deleting
45000 records in one transaction? Try deleting them in batches or increase WAL
files and buffers..Should help you..


> I know there is row_id in Oracle.
> Is there row_id for a table in Postgres?

That's called as OIDs in postgresql. But as I said, it's likely that you might
not have sufficient;y big WAL. Try setting some higher values.. Or don't delete
in transactions if possible..


Bye
 Shridhar

--
Consent decree:    A document in which a hapless company consents never to commit
in the future whatever heinous violations of Federal law it    never admitted to
in the first place.

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.

>>>>> "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

>>>>> "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