Bom, eu não sei como é exatamente a implementação no postgres, mas de uma forma geral, esse tipo de situação, em que você tem uma aplicação multithread e em que essas multiplas threads precisam de uma informação que é compartilhada entre elas, costuma ser usado semáforos para fazer essas atualizações, dessa forma, mesmo que ocorra de duas requisições acontecerem exatamente no mesmo momento, apenas uma delas terá permissão para ler e alterar a variável em questão, e a outra thread esperará a vez dela de realizar essa operação.

Então, muito provavelmente, o postgres, bem como os outros bancos, possuem uma variável do tipo semáforo, que armazena o valor do proximo id, que permita apenas um acesso simultâneo, e incrementa essa valor antes de liberar o acesso para a próxima conexão.

Para melhorar o paralelismo disso, e diminuir essa espera, os bancos podem fazer uma espécie de cache (ou buffer) também, onde ele deixaria, por exemplo, 10 ids prontos para serem usados, e as conexões vão pegando esses ids desse buffer na ordem em que foram criados, assim uma thread não precisaria esperar pela outra...