I have a simple use case wherein I update the parent table when a row is inserted/deleted in the child table.
Suppose we have a Products table and Items table and ProductId is the foreign key in the Items table.
Whenever an item is inserted or deleted in the Items table, an update is made in the Products table corresponding to the item. An update is simply a query like:
UPDATE "Products" SET "PropertyX" = $1, "UpdatedAt" = $2 WHERE "Id" = $3
If the items inserted/deleted in the items table correspond to the same product in the products table then the database load increases considerably causing timeouts even for 3K reqs/min due to Lock:transactionid waits.
I tested this on Aurora Postgres (with 4 CPUs, 16G Ram) and this happens even if the number of rows in the Items table is in the thousands and the number of rows in the Products table is in the hundreds. I used .net core with entity framework.