Fast Deletion For Large Tables

From: Raymond Chui <raymond(dot)chui(at)noaa(dot)gov>
To: pgsql-admin(at)postgresql(dot)org, pgadmin-support(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Fast Deletion For Large Tables
Date: 2002-10-02 18:20:39
Message-ID: 3D9B38F7.12A7AEA3@noaa.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support pgsql-admin pgsql-general


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!

Attachment Content-Type Size
raymond.chui.vcf text/x-vcard 312 bytes

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message G.LeeJohnson 2002-10-03 03:25:19 type mismatch
Previous Message Dave Page 2002-10-02 11:46:00 Re: Error Number: 3706 Provider cannot be found

Browse pgsql-admin by date

  From Date Subject
Next Message Jodi Kanter 2002-10-03 15:26:45 password encryption
Previous Message Hegyvari Krisztian 2002-10-02 16:33:40 Re: [GENERAL] PL/SQL Developer and TOAD

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2002-10-02 18:47:33 Re: Question about Cursors
Previous Message Charles H. Woloszynski 2002-10-02 18:01:00 Error with sequences, "sequence was re-created"