High Latency and DB Locks on Bulk Deletion Endpoint

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

Start with stac_fastapi/pgstac/transactions.py around line 348 and review the DELETE query alongside the post-delete trigger mentioned in the report. Measure query latency and lock behavior, then determine whether deferred recalculation is feasible; done requires an agreed optimization backed by improved measurements.