High Latency and DB Locks on Bulk Deletion Endpoint #381
Description
Hi,
Description:
We are using a custom deletion endpoint based on this code
to perform bulk deletion of items.
The code performs a simple query as shown below.
class CustomBulkTransactionsClient(BulkTransactionsClient):
"""BulkTransactionsClient with bulk delete capabilities."""
async def bulk_item_delete(self, items: ItemsPayload, request: Request) -> str:
"""Bulk delete items."""
if not items.ids:
raise HTTPException(
status_code=status.HTTP_400_BAD_REQUEST,
detail="No IDs provided for bulk deletion.",
)
query, params = render(
"""
DELETE FROM items
WHERE collection = :collection_id
AND id = ANY(:ids)
""",
collection_id=request.path_params["collection_id"],
ids=items.ids,
)
async with request.app.state.get_connection(request, "w") as conn:
result = await conn.execute(query, *params)
_, deleted_count_str = result.split()
deleted_count = int(deleted_count_str)
return f"Successfully deleted {deleted_count} items."Issue:
What we’ve noticed is that the latency is quite high, and we are running into database locks when executing the bulk deletion. We believe that the trigger function after deletion might be contributing to this. Do you agree with this assessment?
Additionally, would you have any recommendations for optimizations or changes? Our partitions are relatively constant, and we could potentially accept delayed re-calculations for efficiency.
Screenshot (for reference):
Looking forward to your input.
Erik