Race conditions suck! They can be extremely difficult to debug, and often only occur naturally in production at the most critical moments (times of...
For further actions, you may consider blocking this person and/or reporting abuse
No matter what you do, you will still hit the race condition even after using NOT EXISTS, best way is to retry after failure to check if record already exists in next attempt. There should be at least two attempts. First check if record exists, if not try to insert, if insert fails, check if record exists or not.
I have used this method in entity access ORM I have written for typescript and there are two methods such as selectOrInsert, upsert, both of which retries operation at least 3 times.
Absolutely, or you can choose to just let the error bubble up to the user and they can give it another shot, especially if you expect the error to be very rare under normal conditions. In these cases the goal is to avoid violation of the constraints you want on your data, rather than avoiding errors necessarily.
Is it ok to use serializable isolation, or is it kind of antipattern? Does it come with performnace drawbacks?
I would say it's great for certain use cases and is not an anti-pattern at all, you just have to be aware of the drawbacks. It does indeed have a performance cost, although that would generally only be an issue for very high traffic services where the database could become a bottleneck even without the additional overhead of SERIALIZABLE. So I wouldn't worry about the performance cost too much, but it does exist.
Probably the biggest drawback is how often serialization failure errors occur even for transactions that shouldn't logically conflict. Sometimes they seem to error due to concurrent access to lower level resources (like pages) rather than rows, although I'm not an expert on those inner workings. The PostgreSQL documentation officially suggests that "applications using this level must be prepared to retry transactions due to serialization failures." Implementing that retry mechanism can add additional complexity to your code. It also adds additional performance overhead since you have to execute failed transactions again.
Personally if I am working on something where data integrity is paramount (say, a banking system) then I would use SERIALIZABLE isolation level. Otherwise I would look to alternatives, where maybe I have to think harder about how to protect my data's integrity, but the system produces fewer errors. SERIALIZABLE isolation level is a very effective tool but it could also be seen as a bit heavy-handed for many use cases.