Here's one that bit us a few years ago:

SEQUENCEs, used to implement SERIAL and BIGSERIAL primary keys, are not transacted. "BEGIN; {insert 1,000,000 rows}; ROLLBACK" always adds 1,000,000 to the table's primary key SEQUENCE, despite the ROLLBACK. Likewise for upsert (via INSERT ON CONFLICT).

The end result: A table's SERIAL (32-bit signed integer) primary key can overflow even when it contains far fewer than 2^31 rows.