Thread: How many records to delete ?

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 )

thanks
Shul

On Wed, Jan 6, 2010 at 22:03, shulkae <shulkae@gmail.com> wrote:

[...]
> I have thousands of records. What is the optimum number of records to
> delete in one delete command

Optimum in which way?


--
- Rikard - http://bos.hack.org/cv/

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
>

as many as you need to,

    DELETE FROM yourtable AS t
        WHERE CURRENT_DATE - t.recorddate < INTERVAL '90 days';

or whatever.   pooof, all records over that given parameter are deleted,
about as efficiently as can be.   of course, as always, the space
doesn't get reclaimed until that table is vacuumed.

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
>