Re: How many records to delete ?

On 7/01/2010 5:03 AM, shulkae wrote:
> I am writing a shell script which runs as a cron entry. The objective
> is to delete older records from postgresql DB.
>
> I have thousands of records. What is the optimum number of records to
> delete in one delete command
>
> ( my script will delete records in a loop and I want to ensure that
> the swap file created during delete will not cause delete failure
> during low disk space condition )

The only thing that'd use much memory for deletes would be queued
referential integrity trigger checks. Are you removing many, many
millions of records from a table which is the _target_ of a foreign key
relationship (ie the deleted-from table has the referenced primary key)?
If not, you shouldn't need to worry about memory.

If you *are* deleting from a big table that is the target of a foreign
key relationship, make sure you have an index on the foreign key in
referencing table(s) (the one(s) you're not deleting from). That will
help Pg apply the RI checks much more quickly, though it shouldn't
affect memory use.

If you're worried about the OS's swap file growing, lock it to a fixed
size (Windows), use a swap partition (most UNIXes) or give Pg its own
partition.

--
Craig Ringer

>
> thanks
> Shul
>