Postgres: Update OR Delete exactly one row matching WHERE clause where many rows match-CodePudding

I have seen several solutions to this involving selecting with limit 1 and then updating using the primary key. This is the way I'm doing this now, but it is not efficient enough for my application.

Current query: UPDATE "MappingTable" SET "DeviceId" = device_id WHERE "MappingId" = (SELECT "MappingId" FROM "MappingTable" WHERE "DeviceId" is null limit 1) AND "DeviceId" IS NULL RETURNING "Code"

Perhaps it is better to state the problem I am attempting to solve, because I am free to redesign the schema in order to solve the problem. The use case is: We have millions of devices that need to receive a one-time-use code from a table of possible codes. When we return a code, we set a column indicating which device is assigned that particular code. This needs to be as efficient as possible.

What we are doing at present is to have a mapping table with an int primary key, "MappingId", a "Code" column, and a "DeviceId" column. I am Currently testing with one million codes and 10 dummy "clients" banging on the service to test performance. I'm seeing very high amounts of locking and poor performance with a load of several hundred requests/sec, which is lower than what we expect in production.

I can design the database in any manner that will meet the requirement most efficiently. Again, I need to store a list of codes, then update/return exactly one code while recording the device that was assigned that code. I would appreciate any advice on design/query to meet that goal. I'm new to Postgres, so even having read dozens of questions/answers on SO and consulting docs for over a week, I am at a loss.

CodePudding user response：

How are you determining which row to update/delete if your query returns many results? Can the solution be as simple as updating the row with where code = desired_code.

CodePudding user response：

Rather than create codes then assign them to devices, work the other way around: Generate codes using a hash of the device ID then write them to the codes table.

Collisions will be exceedingly rare, tending to zero and can be handled by including the current timestamp in the hash input and retrying.