Re: Fast Deletion For Large Tables

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.