great points @garrison.
I do need to check for uniqueness on the contents of the JSONB column as well, maybe indeed unusual.
But very good point that even looking for presence of an existing record means that having an index will be beneficial in terms of performance, so there’s more of a case for adding the index.
Interesting idea with storing sha256 of jsonb column to improve index performance!
Thank you for your thoughts 
Notably one challenge with sha256ing JSON is that JSON objects can be equivalent in a JSON sense but not in a byte wise sense because the order of keys does not matter for object equivalence, but will produce different hashes.
Is the whole JSON object relevant for uniqueness or just some value inside of it?
Excellent catch. In my case the whole JSON object is relevant (I store varied chunks of external API responses).
I didn’t end up going with this solution for now anyways, still waiting to get my ass bitten by a race condition before bothering adding uniqueness constraint (for my use case it’s not a biggie if I get a duplicate once in a blue moon anyway).
Aha, this did cross my mind but I really didn’t think he was actually checking the JSON blobs themselves for uniqueness so I didn’t elaborate 
Indeed, you can be bitten not only by key order but also by whitespace, and probably other weird stuff with encoding. In crypto(graphy) in particular this gets messy because deriving an HMAC from computed state (like re-encoded JSON) is a big no-no (it tends to lead to vulns), which is why you get all that weird cursed json-in-json stuff with JWTs for example.
Ideally you would be using the hash just to check whether a value has changed, meaning that even if you get a “false positive” the worst case is just overwriting the JSON with itself. This assumes there is some other unique identifier at play, though, or you will just start accumulating similar-but-not-the-same blobs. Of course, this might also be true without the hash (I don’t know how postgres implements equality on JSON columns).
If it came to it, nested sorting and then re-encoding the JSON and hashing it as a string should be fine (unless you’re doing cryptography stuff, in which case hopefully you know better).
Honestly for your use case I’d be more worried about the nasty performance you’re going to get if you ever accumulate enough rows in that table to start actually stressing the database. Obviously if the table is small none of this matters anyway (and you could just index the column in full without worry, too).
A full explanation is of course rather involved, but I’ll try my best to answer this.
You are correct that locking the entire table would technically protect against this, but that’s not what was being discussed. As the post you cited detailed, the discussion was about row-level locks, and full table locks are really only relevant in the case of huge operations that touch the entire table.
Databases like postgres have “multi-granularity” locks, meaning (tautologically) that they can lock at different granularities. There are full-database locks (not sure if postgres has these), table locks, page locks (you’re not allowed to use these in postgres but they exist internally), and of course row locks.
When you run SELECT ... FOR UPDATE you get a row lock for each row returned by the select - in Ecto this is lock(query, "FOR UPDATE"). These are pessimistic, and they will block any other transaction which tries to modify the row until after the transaction with the locks commits or aborts (I will note for posterity that this can lead to deadlock). These row locks would, ostensibly allow one to execute an atomic get_or_insert. Except they don’t, because the row doesn’t exist yet. So there’s nothing to lock. That’s it - that’s all you have to understand about why it doesn’t work.
I brought up isolation levels because I was proposing a working solution to the problem, namely that this get_or_insert trick would happen to work under SERIALIZABLE isolation. That comment doesn’t exactly “distinguish isolation level from locking” (though they are of course distinguished). Jeremy was just correcting another comment which incorrectly stated that serializable isolation locks the entire table (it doesn’t).
The purpose of serializable isolation is to execute concurrent transactions with an outcome that is as if they were run in some serial order, one after another. This is important for ridding ones code of nasty race conditions, as this thread has so helpfully demonstrated
One of the things (but not the only thing) needed to achieve serializable isolation is something called a “predicate lock”. It is not sufficient to lock individual rows for exactly the reason we have discovered: they don’t always exist when you try to read them! So instead you need to lock on the “idea” that there could be a row there. In practice you lock on a range, like “all employees with salary between 100 and 200”, which may in turn lock on pages containing those ranges in an index, or something like that (it’s obviously rather implementation-specific).
The predicate locking that you get from serializable isolation just happens to be enough to solve this problem. There are other, weirder, anomalies that require the full SSI algorithm (or strict two-phase locking, another algorithm), and if you want to understand them, that paper I linked really is gold.
Interesting, are you saying that a table lock will work here but is not the most optimal solution?
“Not optimal” sort of understates it honestly, it is a textbook bottleneck. You’re only allowing a single reader / writer to the table at a time (at least, for any reader / writer that has to perform that sort of operation).
The inherent tension with all of these solutions is that at 0 scale they’re all fine. At any scale they’re all sorta bad because we are working with an arbitrary JSON blob, with the unique index being the best option because you need it anyway to make the get_by part scale, so may as well make it unique and upsert.
Ultimately I sort of what to revisit the concept of “Find or create by JSON blob”. Is there really nothing more unique? What is it?
Yes, well there’s always a bottle neck, no? I guess my “real” question is more like, assuming you need this, what the best balance of simplicity and performance? I’ve used a table lock for this before and never ran into any issues with it. Probably because it was a very specific operation that was rarely used. But if there is an approach that’s comparably simple but a lot more performant then of course I want to be aware. I love postgres, but I find that fairly often its API/docs don’t make it easy to spot the right place to start for something like this. So then people avoid involving it at all.
But also yes, my guess is for this particular problem (and most similar use cases) there is probably a not-too-hard way to implement a proper unique index. In my case, I had a client demand an upsert but refuse to enforce uniqueness. 
I think we’re mostly in agreement here.
As I said, at 0 scale basically anything works. Heck, even just “hope the race condition doesn’t happen” might be just fine.
If you’re not doing JSONB specifically I don’t super duper understand what would be even vaguely hard about a unique index. create unique index on my_table (my_unique_column) and just call it a day. You knock out two birds with one stone:
get_by will stay fastThis will take you all the way from “Hope the race condition doesn’t happen” up through tens of millions of rows without even really thinking about it and while yes, there is always a bottleneck, 8 orders of magnitude worth of scale is pretty dang good as a default approach.
We might not agree here. I’ll take a performance penalty over a race condition any day. But I’ll also take a performance penalty just to avoid complexity, until it’s no longer practical.
It’s not hard in almost any case, as I said above, that’s mostly all I have ever used. If you’re concerned I’m advocating people jump straight to table locks, that’s not it at all. I’m more interested in the case I described above, where I was expressly forbidden from enforcing uniqueness. Silly and rare, but it’s actually happened to me. To the best of my current knowledge, a table lock is the best approach there, but I’d be happy to learn something better for next time.