How to deal with concurrency when inserting polygons in a SQL table in a way that they don't intersect with each other

Question

Problem:

I have a table X, in which I am inserting polygons (ex: geometry data for a city). Before inserting them, I check if the input polygon intersects with any existing polygon in the table and only when there is no intersection, the insertion is performed.

While this works normally, the process fails when two different requests from different machines/pods try to insert polygon that doesn't intersect with anything in the database but intersects with each other.

Additional info

Database system is PostgreSQL. Intersection is checked with ST_Intersects and the geometry data is indexed with a gist index.

Desirable outcome:

First request succeeds and the second request should fail while keeping the insertions as fast as possible.

Extra

1. The obvious thing to do is to lock table X, however this would slow down the insertion in table X by a major factor.
2. The other way I think of is to have an additional table with bounding boxes of nations(or state). This way, I first find which nations completely contains the polygon and then I can have a row level lock on these nations instead of the entire table. This might be somewhat better than option 1.

I would like ask if anyone knows a better way of solving this?

Answer 1

In case anyone faces an issue like this, what I did at the end was:

1. First checked all the xyz tiles at a sufficient zoom level that overlapped the input polygon. (Used mercantile library for this)
2. Obtained quadkeys of those tiles and then obtained advisory lock on these keys.
3. Once the lock is obtained, performed the checks and executed the job.
4. Released the lock after the job.