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