How do I stop PG from wasting ids when doing ON CONFLICT DO NOTHING?
This is Working As Designed™.
Firstly, serial data types are not &quot;real&quot; types, they are just syntactic sugar for integer columns populated from sequences.
Then about sequences the manual says so:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused “holes” in the sequence of assigned values.

Subsequently (no pun intended), you cannot &quot;stop PG from wasting ids&quot; (even if you stop using Postgres altogether, because all other DBMSes behave in the same manner).