ACID, Partial Indexes e Patterns Avançados no PostgreSQL

Propriedades ACID

ACID é o conjunto de propriedades que garantem a confiabilidade das transações em bancos de dados relacionais. O PostgreSQL implementa todas elas de forma rigorosa, e entender cada uma é fundamental para tomar boas decisões de modelagem e arquitetura.

Atomicidade

Uma transação é indivisível: ou todas as operações dentro dela são executadas com sucesso, ou nenhuma delas é aplicada. Se qualquer etapa falhar, o banco faz rollback automaticamente de tudo que foi feito até aquele ponto.

BEGIN;
  UPDATE contas SET saldo = saldo - 500 WHERE id = 1;
  UPDATE contas SET saldo = saldo + 500 WHERE id = 2;
COMMIT;

Se a segunda UPDATE falhar, a primeira também é revertida. O saldo nunca fica em estado inconsistente.

Consistência

Após cada transação, o banco de dados deve permanecer em um estado válido. Isso significa que constraints, foreign keys, check constraints e triggers são respeitados. Uma transação que violaria a integridade referencial é rejeitada.

INSERT INTO orders (user_id, total) VALUES (9999, 150.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint

Isolamento

Transações concorrentes não interferem umas nas outras. O PostgreSQL usa MVCC (Multi-Version Concurrency Control) para permitir que múltiplas transações leiam e escrevam simultaneamente sem bloqueios desnecessários. O nível de isolamento padrão é READ COMMITTED, mas você pode configurar níveis mais restritivos.

Nível	Dirty Read	Non-Repeatable Read	Phantom Read
Read Committed	Não	Possível	Possível
Repeatable Read	Não	Não	Possível
Serializable	Não	Não	Não

BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT saldo FROM contas WHERE id = 1 FOR UPDATE;
  UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

Durabilidade

Uma vez que a transação recebe COMMIT, os dados estão persistidos de forma permanente — mesmo que o servidor caia imediatamente depois. O PostgreSQL garante isso através do WAL (Write-Ahead Log), que registra as alterações em disco antes de confirmar a transação.

---

Partial Indexes

Um partial index cobre apenas um subconjunto das linhas de uma tabela, definido por uma cláusula WHERE. Isso resulta em um índice menor, mais rápido e mais eficiente em disco.

Unique constraint com soft delete

Em aplicações que usam soft delete (ex: gem Discard no Rails), registros “descartados” recebem um timestamp em discarded_at. Um índice único tradicional incluiria esses registros, impedindo a reutilização de valores como CPF ou email.

CREATE UNIQUE INDEX index_employees_on_cpf
  ON employees USING btree (cpf)
  WHERE (discarded_at IS NULL);

Com isso, dois registros podem ter o mesmo CPF desde que apenas um deles esteja ativo. Os registros descartados não ocupam espaço no índice e não interferem na constraint de unicidade.

No Rails:

class AddUniqueIndexOnCpfToEmployees < ActiveRecord::Migration[7.1]
  def change
    add_index :employees, :cpf,
      unique: true,
      where: "discarded_at IS NULL",
      name: "index_employees_on_cpf"
  end
end

Quando usar partial indexes

• Tabelas com soft delete onde a maioria dos registros está ativa
• Colunas com distribuição desigual de valores (ex: apenas 5% dos pedidos estão com status pending)
• Queries que sempre filtram pelo mesmo predicado

CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

Se a maioria das queries busca pedidos pendentes e eles representam uma fração pequena da tabela, esse índice é muito mais eficiente que um índice completo.

---

Check Constraints

Check constraints validam dados no nível do banco, independente da aplicação. Diferente de validações no código, elas não podem ser contornadas — qualquer operação que viole a regra é rejeitada.

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

ALTER TABLE orders
  ADD CONSTRAINT chk_status_valid
  CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'));

Tentativas de inserir dados inválidos falham imediatamente:

INSERT INTO products (name, price) VALUES ('Widget', -10);
-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"

Check constraints são especialmente úteis para:

• Garantir que valores numéricos estão dentro de faixas válidas
• Restringir colunas de status a valores permitidos
• Validar relações entre colunas da mesma linha (ex: end_date > start_date)

ALTER TABLE events
  ADD CONSTRAINT chk_dates_valid CHECK (end_date > start_date);

---

Exclusion Constraints

Exclusion constraints são uma generalização das unique constraints. Enquanto um índice único impede valores duplicados exatos, uma exclusion constraint impede que linhas conflitem segundo um operador arbitrário.

O caso de uso clássico é impedir sobreposição de intervalos — por exemplo, reservas de sala que não podem se sobrepor no tempo.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE reservations
  ADD CONSTRAINT no_overlapping_reservations
  EXCLUDE USING gist (
    room_id WITH =,
    tsrange(start_time, end_time) WITH &&
  );

O operador && verifica sobreposição de ranges. Se alguém tentar reservar a sala 3 das 14h às 16h quando já existe uma reserva das 15h às 17h, o banco rejeita:

INSERT INTO reservations (room_id, start_time, end_time)
  VALUES (3, '2026-03-24 14:00', '2026-03-24 16:00');
-- ERROR: conflicting key value violates exclusion constraint "no_overlapping_reservations"

Essa validação é impossível de implementar de forma confiável apenas no código da aplicação, pois condições de corrida entre transações concorrentes podem criar sobreposições.

---

Connection Pooling e Timeouts

Cada conexão no PostgreSQL é um processo do sistema operacional, consumindo tipicamente 5-10 MB de memória. Com centenas de workers, o número de conexões pode ultrapassar o limite do banco.

Connection Pooling

Um connection pooler como o PgBouncer atua como middleware entre a aplicação e o PostgreSQL, mantendo um pool menor de conexões do lado do servidor.

[Rails Worker 1] ---\
[Rails Worker 2] -----> [PgBouncer] ----> [PostgreSQL]
[Rails Worker 3] ---/     (20 server      (max_connections = 100)
  (100 client              connections)
   connections)

O modo transaction pooling geralmente oferece o melhor equilíbrio: a conexão do servidor é compartilhada entre clientes e atribuída apenas durante uma transação.

Timeouts para disponibilidade

Timeouts evitam que queries travadas consumam conexões indefinidamente. Configure no PostgreSQL ou na aplicação:

-- Timeout global para statements (em milissegundos)
SET statement_timeout = '5s';

-- Timeout para aquisição de locks
SET lock_timeout = '3s';

-- Timeout para conexões idle em transação
SET idle_in_transaction_session_timeout = '30s';

No Rails, configure via database.yml:

production:
  variables:
    statement_timeout: 5000
    lock_timeout: 3000

A combinação de connection pooling com timeouts agressivos é o que mantém aplicações estáveis sob alta concorrência.

---

Referências

• PostgreSQL - CREATE INDEX
• PostgreSQL - Transaction Isolation
• PostgreSQL - Check Constraints
• PostgreSQL - Exclusion Constraints
• PgBouncer - Lightweight Connection Pooler
• High Performance PostgreSQL for Rails - Andrew Atkinson
• Discard Gem - Soft Deletes for ActiveRecord