suportebahia/Equipe SBahia - DBA Specialist

DBA Specialist - Equipe SBahia

Responsabilidades

Modelagem Física

• Tabelas, índices, constraints
• Foreign keys e integridade
• Particionamento
• Views e materialized views
• Procedures e functions

Performance

• Query optimization
• Index strategy
• Execution plans (EXPLAIN)
• Tuning de configuração
• Connection pooling

Infraestrutura

• Alta disponibilidade (HA)
• Replication (master-slave, multi-master)
• Sharding (quando necessário)
• Backup e restore
• Disaster recovery

Competências Técnicas

SQL Avançado

-- Query otimizada com CTEs
WITH ranked_orders AS (
  SELECT 
    o.*,
    c.name as customer_name,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) as rn
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.status = 'completed'
)
SELECT * FROM ranked_orders WHERE rn <= 5;

-- Window Functions
SELECT 
  department,
  employee_name,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

-- Materialized View para analytics
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
  DATE_TRUNC('month', created_at) as month,
  SUM(total) as revenue,
  COUNT(*) as order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

Index Strategy

-- Composite index para queries específicas
CREATE INDEX idx_orders_customer_status 
ON orders(customer_id, status) 
WHERE status IN ('pending', 'completed');

-- Partial index
CREATE INDEX idx_active_users ON users(email) 
WHERE deleted_at IS NULL;

-- Covering index (include)
CREATE INDEX idx_orders_covering 
ON orders(customer_id, status) 
INCLUDE (total, created_at);

ORM Optimization (Prisma)

// PROBLEM: N+1 queries
const orders = await prisma.order.findMany();
for (const order of orders) {
  const customer = await prisma.customer.findUnique({
    where: { id: order.customerId }
  });
  order.customer = customer;
}

// SOLUTION: Include
const orders = await prisma.order.findMany({
  include: {
    customer: true,
    items: { include: { product: true } }
  }
});

// SOLUTION: Select only needed fields
const orders = await prisma.order.findMany({
  select: {
    id: true,
    status: true,
    customer: { select: { name: true, email: true } }
  }
});

High Availability

PostgreSQL Replication

# docker-compose.yml
services:
  primary:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: secret
    volumes:
      - primary_data:/var/lib/postgresql/data
  
  replica1:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: secret
    command: >
      postgres -c primary_conninfo='host=primary port=5432 user=replica password=secret'
    depends_on:
      - primary
    volumes:
      - replica1_data:/var/lib/postgresql/data

Backup Strategy

# Daily full backup
0 2 * * * pg_dump -Fc -f /backups/full_$(date +\%Y\%m\%d).dump

# WAL archival for point-in-time recovery
archive_command = 'wal-g wal-push %p'

# Restore command
pg_restore -d mydb /backups/full_20240115.dump

Monitoring

Queries Lentas

-- PostgreSQL
SELECT 
  query,
  calls,
  mean_exec_time,
  total_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- MySQL
SELECT * FROM performance_schema.events_statements_history
ORDER BY TIMER_WAIT DESC LIMIT 10;

Connection Pool

// Prisma with connection pool
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL
    }
  },
  log: ['query', 'error', 'warn'],
});

// Connection pool tuning (PostgreSQL)
-- postgresql.conf
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 16MB

Boas Práticas

1. Normalize até 3NF - Evite redundância
2. Indexes estratégicos - Query patterns > covering indexes
3. Migrations versionadas - Sempre backwards compatible
4. Soft deletes - deleted_at em vez de DELETE
5. Auditoria - Triggers para tabelas críticas
6. Connection pool - Configure adequadamente
7. Monitor queries lentas - pg_stat_statements
8. Teste em produção-like - Dados reais impactam performance
9. Particione tabelas grandes - Por data ou range
10. RTO/RPO definidos - Disaster recovery planejado