As far as I understand the context/your use case,

I would definitely go with the unique_contraint and index on the table on the device_id column to have the most simple and correct way of lookups for these devices. (first make it work and correct)

To go faster (after making it work and correct) you could deploy various techniques from very simple to more complex (giving more headaches because of higher risk of race conditions)

Simplest in my mind would be a cache with a TTL(time to live) for every record you lookup, so you’ll always get a database hit on the first try and then it ‘sticks’ for x seconds or minutes within your cache. This is only applicable if it’s allowed to let a record / device access? linger after a deletion for max TTL timeout. If this is totally now allowed then you need some mechanism to update it after a deletion (See postgres pub/sub listen/notify below)

Now if you want to make it really fast, even on a first lookup, you could create a simple map (fast lookup) on the elixir side if you don’t expect too much of them (else it would take up too much ram), now to keep this up to date you could refresh it completely every x minutes (again, depends on how big the dataset is), otherwise you could use notify/listen (if you’re using postgres) to do a very lightweight pub/sub on modifications of your table.
(see for examples here and here)

To avoid data races, best if you always do your writes directly to the database and ignore your cache. The cache should only be updated by your pub/sub mechanism or full snapshot per x seconds. And you could always treat your cache as the true positive case, as in; if you can’t find them in your cache you do the ‘slow path’ of looking them up in the database. In this case you should always be correct and fast if it’s a returning client.

I’ve used the above technique myself to cache access tokens on the elixir side (which can refresh themselves out of the http request response loop) making the API calls in the microseconds because they don’t have to do a roundtrip to the database to check if they are still allowed. In this case it doesn’t matter if somebody’s access get revoked that it takes a couple of seconds before that cache updates itself and if it’s not found in the cache I always make a database call to see if it exists. So it’s really speeding up the case where API clients return often with a call.

Conclusion, it always depends on the context and requirements but always go for working & correct first, time it and see if it’s fast enough because the database will also keep a lot of your data hot in memory (last fetched records and your index most of the time) so it should be fast enough, if not you could always put in a lot more effort