This is one of those complex situations where just using SERIALIZABLE can be a good solution.
To elaborate a bit about the table locks: by taking a lock on the entire table every time, you’re forcibly serializing all of the transactions taking that lock (plus anything touching that table, too). There are some databases (e.g. SQLite) which do, by design, serialize all transactions (and therefore have no write concurrency).
This is okay in SQLite because the database is literally in-process and there is effectively no latency. But with a postgres server, you probably have at least a few milliseconds of latency times at least a few round trips for an interactive transaction. During that time, all of the other transactions are blocked, so your throughput just gets obliterated.
(Also, for the record, SQLite’s transactional throughput is abysmal anyway. It’s just not meant for that.)
Depending on the task you could distribute the locks more evenly to recover some concurrency. For example, if the lat/long had to be unique per user, you could take a lock on the user (or on a row for that user in a table created for that purpose). Then at least multiple users could perform inserts simultaneously.
Also, I’d like to correct something I said earlier:
This is actually not true, and Jose’s post is wrong (I knew this but forgot). Postgres does not return the existing row in an upsert if no update takes place (see the bit about RETURNING under “Description”). You could get around this by performing a token update (e.g. SET value=EXCLUDED.value), but this is a very bad idea because postgres’s ancient MVCC implementation writes a new row every time you do this (even if you change nothing). If you run get_or_insert a lot, which is usually the case with such a function, this will degrade performance (postgres has to gc those rows, and also it stores said rows oldest to newest because, again, ancient MVCC implementation).
Unfortunately I think this means that it’s literally impossible to perform an atomic get_or_insert outside of SERIALIZABLE. Of course, in practice errors due to conflicts will be rare, so this hardly matters.
