We’re hitting a big performance issues when trying to delete old entries while inserting newer entries.
In some cases we’ve experienced absurd slowdowns, with a batch of 100 new entries taking up to 35s to complete the insert, an increase of about 200-500x what it is in regular.
This issue is more pronounced with larger tables with more indices, but it also impacts small tables.
Does anyone have any advice to run deletes + inserts concurrently? we only delete old entries, so there shouldn’t be conflicting queries.
Our workflow requires inserting a lot of (potentially useless) data, running jobs to mark the useful ones and delete the rest.
Is there a way to do this without affecting insert speed?
Can anyone share some of your DELETE performances?
I can delete about 100 rows in 300-600ms which feels a lot longer than what I can achieve with inserts. The main issue is I can insert in parallel without worrying about contention, but multiple
DELETE ... WHERE ... LIMIT ... will hit the same rows and deadlock very easily.
I’ll try first selecting the primary keys of the rows I want to delete and then deleting them with a
DELETE ... WHERE pk IN (...) and I’ll be able to run those in parallel.