O que acontece quando adicionamos um índice a uma tabela existente com uma grande quantidade de dados?

Com plain CREATE INDEX, a tabela será bloqueada para gravações, mas não para leituras.

Use CREATE INDEX CONCURRENTLYpara evitar bloqueios de gravação também.

Dos documentos do PostgreSQL emCREATE INDEX :

Quando esta opção é usada, o PostgreSQL construirá o índice sem fazer nenhum bloqueio que impeça inserções, atualizações ou exclusões simultâneas na tabela; enquanto uma construção de índice padrão bloqueia as gravações (mas não as leituras) na tabela até que seja concluída. Há várias advertências a serem observadas ao usar essa opção — consulte Construindo índices simultaneamente .

E mais especificamente (como o @ypercube comentou ):

O PostgreSQL oferece suporte à criação de índices sem bloquear as gravações . Esse método é invocado especificando a CONCURRENTLYopção de CREATE INDEX. Quando esta opção é usada, o PostgreSQL deve executar duas varreduras da tabela e, além disso, deve aguardar o término de todas as transações existentes que poderiam usar o índice. Portanto, esse método requer mais trabalho total do que uma construção de índice padrão e leva muito mais tempo para ser concluído. No entanto, como permite que as operações normais continuem enquanto o índice é criado, esse método é útil para adicionar novos índices em um ambiente de produção.

Ênfase em negrito minha.