Csaba Nagy wrote: > First of all, thanks for all the suggestions. > >> put a SERIAL primary key on the table > Or: >> Maybe add OIDs to the table, and delete based on the OID number? > > No, this is not acceptable, it adds overhead to the insertions. Normally > the overhead will be small enough, but on occasions it is noticeable. How about using the following? delete from <table> where ctid in (select ctid from <table> limit <num>); Here's a live example: db=> select count(*) from sometable; count ------- 381 (1 row) db=> delete from sometable where ctid in (select ctid from sometable limit 5); DELETE 5 db=> select count(*) from sometable; count ------- 376 (1 row) Does anyone see problems with the above delete? --- Anyway, do you have figures of the overhead you mentioned? How fast is PG (with OIDs) and how does it compare with the alternatives you're using? In your original post you wrote: > On other databases, it is possible to limit the delete to a maximum > number of rows to be deleted. I don't know what "other databases" you're referring to, but are you sure they don't have anything similar to PG OIDs, without even you knowing it, and without any option to disable them? It's even possible that in the "other databases" you're already paying that overhead, and that makes it quite acceptable in PG, too. Or maybe there's some other kind of overhead, much bigger than the OIDs one? For example, you're using a high overhead mechanism to consume rows (triggers on delete, insering into another table), are you sure that in the "other databases" this doesn't slow all the inserts down much more than adding OIDs on PG would do? PG has MVCC, I guess that makes deletes and inserts on the same table play nice to each other, but how about the other databases? Do they need to acquire a lock on inserts/deletes? That would make your concurrent inserts/deletes much slower that just adding a column to the table. Maybe you could even add an index, and still be faster thanks to MVCC. Also, the trigger is fired once for each deleted row. Have you considered a single stored procedure that loops over the rows to be processed, instead of relaying on deletes and triggers? .TM.
