Thread: 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!
Attachment
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