I noticed a funny thing happening in my most recent pet project. All of the sudden (auto-incremented) primary key was out of range for my smallint column.
But how come? I had like 15 records in that particular table. It turns out it was all by design and completely makes sense when you think about it, but I managed to miss that anyway.
The problem is when you do an upsert sequence on your primary key will be incremented anyway - it had to be obtained before actual insert so when you take a step back it’s like pretty obvious ;). You can read some discussion more about it here.
So just if you are doing a lot of inserts with on conflict do nothing maybe you can filter out duplicates before the actual insert? (Which is exactly what I did) Or maybe you can get rid of the autoincrement key in favor of different unique key / use composite key?