Projeto final: modelagem e otimização de um banco de dados de e-commerce

1. Levantamento de requisitos e modelagem conceitual

O primeiro passo para construir um banco de dados robusto de e-commerce é identificar as entidades fundamentais e seus relacionamentos. As principais entidades são: cliente, produto, pedido, estoque e pagamento. Cada uma possui atributos específicos e se relaciona com as demais através de cardinalidades bem definidas.

Os relacionamentos típicos incluem:
- Cliente 1:N Pedido — um cliente pode fazer vários pedidos
- Pedido N:M Produto — muitos produtos em muitos pedidos (tabela associativa item_pedido)
- Produto 1:N Estoque — cada produto tem um registro de estoque
- Pedido 1:1 Pagamento — cada pedido possui um pagamento associado

O diagrama entidade-relacionamento (DER) resultante deve representar claramente essas conexões, servindo como base para a modelagem lógica.

2. Modelagem lógica e normalização

Transformamos o DER em tabelas relacionais aplicando as formas normais (1FN, 2FN, 3FN). A tabela pedido, por exemplo, não deve armazenar o valor total calculado (violaria a 3FN), mas sim os itens individuais. Contudo, para consultas frequentes de total do pedido, podemos realizar uma desnormalização controlada adicionando uma coluna valor_total calculada via trigger ou aplicação.

-- Exemplo de tabela normalizada: item_pedido
CREATE TABLE item_pedido (
    id SERIAL PRIMARY KEY,
    pedido_id INTEGER NOT NULL REFERENCES pedido(id),
    produto_id INTEGER NOT NULL REFERENCES produto(id),
    quantidade INTEGER NOT NULL CHECK (quantidade > 0),
    preco_unitario DECIMAL(10,2) NOT NULL,
    UNIQUE (pedido_id, produto_id)
);

3. Criação do esquema físico e tipos de dados

A definição cuidadosa de colunas, tipos e restrições é crucial. Usamos VARCHAR(100) para nomes, INTEGER para IDs, DECIMAL(10,2) para valores monetários e TIMESTAMP para datas. Índices B-tree são criados em colunas frequentemente usadas em cláusulas WHERE e JOIN.

CREATE TABLE cliente (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_cliente_email ON cliente(email);

Para tabelas grandes como pedido, o particionamento por data (range partitioning) melhora a performance de consultas históricas:

CREATE TABLE pedido (
    id SERIAL,
    cliente_id INTEGER NOT NULL,
    data_pedido DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pendente',
    PRIMARY KEY (id, data_pedido)
) PARTITION BY RANGE (data_pedido);

CREATE TABLE pedido_2024 PARTITION OF pedido
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

4. Inserção de dados de exemplo e cenários reais

Para testar o esquema, geramos massa de dados simulada utilizando transações controladas:

BEGIN;

INSERT INTO cliente (nome, email) VALUES
    ('Maria Silva', 'maria@email.com'),
    ('João Souza', 'joao@email.com');

INSERT INTO produto (nome, preco, categoria) VALUES
    ('Notebook', 3500.00, 'Eletrônicos'),
    ('Mouse', 120.00, 'Acessórios');

INSERT INTO pedido (cliente_id, data_pedido, status) VALUES
    (1, '2024-06-15', 'confirmado'),
    (2, '2024-06-16', 'pendente');

INSERT INTO item_pedido (pedido_id, produto_id, quantidade, preco_unitario) VALUES
    (1, 1, 1, 3500.00),
    (1, 2, 2, 120.00),
    (2, 1, 1, 3500.00);

COMMIT;

5. Otimização de consultas comuns

Consultas típicas de e-commerce incluem junções e agregações. Utilizamos EXPLAIN ANALYZE para identificar gargalos e ajustamos índices compostos:

-- Consulta: total gasto por cliente no último mês
EXPLAIN ANALYZE
SELECT c.nome, SUM(ip.quantidade * ip.preco_unitario) AS total_gasto
FROM cliente c
JOIN pedido p ON c.id = p.cliente_id
JOIN item_pedido ip ON p.id = ip.pedido_id
WHERE p.data_pedido >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.id, c.nome;

Índice composto recomendado:

CREATE INDEX idx_pedido_cliente_data ON pedido(cliente_id, data_pedido);

Para relatórios de vendas, window functions são poderosas:

SELECT 
    p.data_pedido,
    p.id AS pedido_id,
    SUM(ip.quantidade * ip.preco_unitario) OVER (PARTITION BY p.data_pedido) AS total_diario
FROM pedido p
JOIN item_pedido ip ON p.id = ip.pedido_id
ORDER BY p.data_pedido;

6. Estratégias de disaster recovery e backup

Definimos RTO (tempo de recuperação) de 4 horas e RPO (perda máxima) de 15 minutos para o e-commerce. Backups lógicos com pg_dump são executados diariamente, enquanto backups físicos via WAL archiving garantem recuperação pontual:

-- Backup lógico
pg_dump -U admin -Fc ecommerce > /backup/ecommerce_$(date +%Y%m%d).dump

-- Configuração de WAL archiving (postgresql.conf)
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

Para contingência, configuramos replicação síncrona com failover automático usando pg_rewind e um monitor de alta disponibilidade como Patroni.

7. Testes de performance e ajustes finais

Simulamos 1000 usuários concorrentes com pgbench para identificar gargalos:

pgbench -h localhost -U admin -d ecommerce -c 1000 -j 8 -T 300 -f consultas_ecommerce.sql

Após análise, aplicamos tuning:

-- Ajustes no postgresql.conf
shared_buffers = 4GB
work_mem = 64MB
effective_cache_size = 12GB
maintenance_work_mem = 1GB

Manutenção preventiva com VACUUM e ANALYZE programados:

VACUUM ANALYZE pedido;
REINDEX TABLE pedido;

8. Documentação e boas práticas finais

Criamos um dicionário de dados documentando cada tabela e coluna, além de scripts de migração versionados com Flyway ou Liquibase. O checklist de manutenção inclui:

  • Reindexação semanal de tabelas com alta taxa de atualização
  • Monitoramento de dead tuples com consultas ao pg_stat_user_tables
  • Verificação de locks pendentes

Para escalabilidade futura, recomendamos:
- Sharding horizontal por região geográfica
- Read replicas para consultas de relatórios
- Redis para cache de sessão e catálogo de produtos

Referências