Gaps on id column occurs due to the usage of sequences on PostgreSQL as the default value of the table’s primary key. When the transaction rollback the sequence value will not rollback thus the id column will be not sequential. I have tried to eliminate the gaps but it seems to impact the performance. What I am thinking right now is to log those missing IDs along with error message and request payload. As a result, I am struggling getting the ID that was generated by sequence when DB transaction rollback.
Please suggest approach to get the missing sequence ID from DB when function is rollbacked so we can log it.