Estratégia de Indexação

Sport Tech Club - Otimização de Performance PostgreSQL

Visão Geral

Este documento define a estratégia de indexação para otimizar queries do Sport Tech Club, considerando os padrões de acesso multi-tenant e requisitos de performance.

---

1. Princípios de Indexação

1.1 Diretrizes Gerais

principles:
  # Índices compostos com tenant_id primeiro
  multi_tenant_first: true

  # Índices parciais para dados ativos
  partial_indexes: true

  # BRIN para dados temporais
  brin_for_time_series: true

  # Covering indexes para queries frequentes
  covering_indexes: true

  # Índices de expressão quando necessário
  expression_indexes: true

1.2 Tipos de Índices Utilizados

Tipo	Uso	Exemplo
B-tree	Igualdade, range, ordenação	WHERE tenant_id = X AND status = 'ACTIVE'
BRIN	Dados sequenciais/temporais	WHERE created_at BETWEEN X AND Y
GIN	Arrays, JSONB, full-text	WHERE tags @> ARRAY['beach_tennis']
GiST	Geoespacial, range types	WHERE location <-> point(X,Y) < 1000
Hash	Apenas igualdade (raro)	WHERE id = X

---

2. Índices por Schema

2.1 Schema: Identity

Tabela: tenants

-- Índice principal por slug (único)
-- Já criado pelo Prisma: @unique

-- Índice para busca por status
CREATE INDEX idx_tenants_status ON identity.tenants(status)
WHERE status = 'ACTIVE';

-- Índice BRIN para queries temporais
CREATE INDEX idx_tenants_created_at_brin ON identity.tenants
USING BRIN(created_at);

Tabela: users

-- Índice composto para busca por tenant + email
CREATE INDEX idx_users_tenant_email ON identity.users(tenant_id, email)
WHERE deleted_at IS NULL;

-- Índice para busca por keycloak_id (autenticação)
CREATE UNIQUE INDEX idx_users_keycloak_id ON identity.users(keycloak_id);

-- Índice para busca por CPF (validação de duplicidade)
CREATE INDEX idx_users_tenant_cpf ON identity.users(tenant_id, cpf)
WHERE cpf IS NOT NULL AND deleted_at IS NULL;

-- Índice para busca por status (listagens)
CREATE INDEX idx_users_tenant_status ON identity.users(tenant_id, status)
WHERE deleted_at IS NULL;

-- Índice para último login (análises)
CREATE INDEX idx_users_last_login ON identity.users(last_login_at DESC NULLS LAST)
WHERE status = 'ACTIVE';

-- Covering index para listagem básica
CREATE INDEX idx_users_list_covering ON identity.users(
  tenant_id,
  status,
  first_name,
  last_name,
  email
) WHERE deleted_at IS NULL;

Tabela: player_profiles

-- Índice para busca por esportes preferidos (GIN)
CREATE INDEX idx_player_profiles_sports ON identity.player_profiles
USING GIN(preferred_sports);

-- Índice para jogadores públicos
CREATE INDEX idx_player_profiles_public ON identity.player_profiles(user_id)
WHERE is_public = true;

Tabela: skill_ratings

-- Índice composto para ranking por esporte
CREATE INDEX idx_skill_ratings_sport_rating ON identity.skill_ratings(
  sport,
  rating DESC
);

-- Índice para busca de jogadores por nível
CREATE INDEX idx_skill_ratings_sport_level ON identity.skill_ratings(
  sport,
  level,
  rating DESC
);

-- Covering index para estatísticas
CREATE INDEX idx_skill_ratings_stats ON identity.skill_ratings(
  player_profile_id,
  sport
) INCLUDE (rating, games_played, wins, losses, win_streak);

2.2 Schema: Arena

Tabela: arenas

-- Índice composto para busca por tenant + status
CREATE INDEX idx_arenas_tenant_status ON arena.arenas(tenant_id, status)
WHERE deleted_at IS NULL;

-- Índice para busca geográfica
CREATE INDEX idx_arenas_location ON arena.arenas
USING GIST(point(longitude, latitude))
WHERE latitude IS NOT NULL
  AND longitude IS NOT NULL
  AND status = 'ACTIVE';

-- Índice para busca por cidade/estado
CREATE INDEX idx_arenas_location_text ON arena.arenas(
  address_state,
  address_city
) WHERE status = 'ACTIVE' AND deleted_at IS NULL;

-- Full-text search para nome e descrição
CREATE INDEX idx_arenas_fts ON arena.arenas
USING GIN(to_tsvector('portuguese', coalesce(name, '') || ' ' || coalesce(description, '')));

Tabela: courts

-- Índice composto para listagem por arena
CREATE INDEX idx_courts_arena_sport ON arena.courts(arena_id, sport, status)
WHERE deleted_at IS NULL;

-- Índice para busca por esporte (cross-arena)
CREATE INDEX idx_courts_sport_status ON arena.courts(sport, status)
WHERE deleted_at IS NULL;

-- Covering index para listagem
CREATE INDEX idx_courts_list ON arena.courts(
  arena_id,
  display_order,
  name
) INCLUDE (sport, surface, covered, lighting, status)
WHERE deleted_at IS NULL;

Tabela: operating_hours

-- Índice para busca de horários por dia
CREATE INDEX idx_operating_hours_arena_day ON arena.operating_hours(
  arena_id,
  day_of_week
) WHERE is_active = true;

Tabela: pricing_rules

-- Índice composto para busca de regras
CREATE INDEX idx_pricing_rules_arena ON arena.pricing_rules(
  arena_id,
  priority DESC,
  type
) WHERE is_active = true;

-- Índice para regras por quadra específica
CREATE INDEX idx_pricing_rules_court ON arena.pricing_rules(
  court_id,
  priority DESC
) WHERE court_id IS NOT NULL AND is_active = true;

-- Índice para regras válidas por período
CREATE INDEX idx_pricing_rules_validity ON arena.pricing_rules(
  arena_id,
  valid_from,
  valid_until
) WHERE is_active = true;

Tabela: promotions

-- Índice para busca por código
CREATE UNIQUE INDEX idx_promotions_code ON arena.promotions(arena_id, code)
WHERE code IS NOT NULL;

-- Índice para promoções ativas
CREATE INDEX idx_promotions_active ON arena.promotions(
  arena_id,
  valid_from,
  valid_until
) WHERE is_active = true;

-- Índice para promoções automáticas
CREATE INDEX idx_promotions_automatic ON arena.promotions(arena_id)
WHERE type = 'AUTOMATIC' AND is_active = true;

2.3 Schema: Booking

Tabela: bookings

-- Índice principal para verificação de conflitos
-- CRÍTICO: Query mais frequente do sistema
CREATE INDEX idx_bookings_conflict_check ON booking.bookings(
  court_id,
  start_time,
  end_time
) WHERE status NOT IN ('CANCELLED', 'NO_SHOW');

-- Índice para listagem por arena
CREATE INDEX idx_bookings_arena_time ON booking.bookings(
  arena_id,
  start_time DESC
) WHERE status NOT IN ('CANCELLED');

-- Índice para listagem por usuário
CREATE INDEX idx_bookings_user ON booking.bookings(
  user_id,
  start_time DESC
);

-- Índice para reservas pendentes de confirmação
CREATE INDEX idx_bookings_pending ON booking.bookings(
  arena_id,
  created_at
) WHERE status = 'PENDING';

-- Índice para reservas do dia (recepção)
CREATE INDEX idx_bookings_today ON booking.bookings(
  arena_id,
  start_time
) WHERE status IN ('CONFIRMED', 'CHECKED_IN', 'IN_PROGRESS');

-- BRIN para queries históricas
CREATE INDEX idx_bookings_history_brin ON booking.bookings
USING BRIN(created_at);

-- Covering index para listagem completa
CREATE INDEX idx_bookings_list ON booking.bookings(
  arena_id,
  start_time
) INCLUDE (court_id, user_id, status, total_price)
WHERE status NOT IN ('CANCELLED');

Tabela: payments

-- Índice para busca por booking
CREATE INDEX idx_payments_booking ON booking.payments(booking_id);

-- Índice para pagamentos pendentes
CREATE INDEX idx_payments_pending ON booking.payments(created_at)
WHERE status = 'PENDING';

-- Índice para reconciliação com gateway
CREATE UNIQUE INDEX idx_payments_external ON booking.payments(external_id)
WHERE external_id IS NOT NULL;

-- BRIN para relatórios financeiros
CREATE INDEX idx_payments_date_brin ON booking.payments
USING BRIN(created_at);

-- Índice para relatórios por status
CREATE INDEX idx_payments_status_date ON booking.payments(
  status,
  created_at DESC
);

Tabela: check_ins

-- Índice para busca por booking
CREATE UNIQUE INDEX idx_check_ins_booking ON booking.check_ins(booking_id);

-- Índice para check-ins do dia
CREATE INDEX idx_check_ins_today ON booking.check_ins(checked_in_at DESC);

2.4 Schema: Public

Tabela: audit_logs

-- BRIN para queries temporais (audit é sempre sequencial)
CREATE INDEX idx_audit_logs_time_brin ON public.audit_logs
USING BRIN(created_at);

-- Índice para busca por entidade
CREATE INDEX idx_audit_logs_entity ON public.audit_logs(
  entity_type,
  entity_id
);

-- Índice para busca por usuário
CREATE INDEX idx_audit_logs_user ON public.audit_logs(
  user_id,
  created_at DESC
);

-- Índice para busca por tenant
CREATE INDEX idx_audit_logs_tenant ON public.audit_logs(
  tenant_id,
  created_at DESC
);

-- Particionamento recomendado para logs
-- Ver seção de particionamento

---

3. Índices Especializados

3.1 Índices para Busca Geográfica

-- Extensão PostGIS (se não instalada)
CREATE EXTENSION IF NOT EXISTS postgis;

-- Índice GiST para busca por proximidade
CREATE INDEX idx_arenas_geom ON arena.arenas
USING GIST(
  ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
)
WHERE latitude IS NOT NULL
  AND longitude IS NOT NULL
  AND status = 'ACTIVE';

-- Query de exemplo: arenas em 5km de um ponto
-- SELECT * FROM arena.arenas
-- WHERE ST_DWithin(
--   ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography,
--   ST_SetSRID(ST_MakePoint(-46.6333, -23.5505), 4326)::geography,
--   5000  -- metros
-- );

3.2 Índices para Full-Text Search

-- Configuração de dicionário português
CREATE TEXT SEARCH CONFIGURATION portuguese_unaccent (COPY = portuguese);
ALTER TEXT SEARCH CONFIGURATION portuguese_unaccent
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, portuguese_stem;

-- Índice para busca de arenas
CREATE INDEX idx_arenas_search ON arena.arenas
USING GIN(
  to_tsvector('portuguese_unaccent',
    coalesce(name, '') || ' ' ||
    coalesce(description, '') || ' ' ||
    coalesce(address_city, '') || ' ' ||
    coalesce(address_neighborhood, '')
  )
) WHERE status = 'ACTIVE' AND deleted_at IS NULL;

-- Índice para busca de produtos
CREATE INDEX idx_products_search ON arena.products
USING GIN(
  to_tsvector('portuguese_unaccent',
    coalesce(name, '') || ' ' ||
    coalesce(description, '')
  )
) WHERE is_active = true;

3.3 Índices para JSONB

-- Índice GIN para metadata de arenas
CREATE INDEX idx_arenas_metadata ON arena.arenas
USING GIN(metadata jsonb_path_ops);

-- Índice GIN para settings de usuários
CREATE INDEX idx_users_settings ON identity.users
USING GIN(settings jsonb_path_ops);

-- Índice para propriedades específicas
CREATE INDEX idx_arenas_amenities ON arena.arenas
USING GIN(amenities);

-- Query de exemplo:
-- SELECT * FROM arena.arenas
-- WHERE amenities @> ARRAY['parking', 'wifi'];

---

4. Estratégias de Particionamento

4.1 Particionamento de Bookings por Tempo

-- Tabela particionada por mês
CREATE TABLE booking.bookings_partitioned (
  LIKE booking.bookings INCLUDING ALL
) PARTITION BY RANGE (start_time);

-- Partições mensais
CREATE TABLE booking.bookings_2024_01
  PARTITION OF booking.bookings_partitioned
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE booking.bookings_2024_02
  PARTITION OF booking.bookings_partitioned
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- ... (continuar para outros meses)

-- Função para criar partições automaticamente
CREATE OR REPLACE FUNCTION create_booking_partition()
RETURNS void AS $$
DECLARE
  start_date DATE;
  end_date DATE;
  partition_name TEXT;
BEGIN
  start_date := date_trunc('month', CURRENT_DATE + interval '1 month');
  end_date := start_date + interval '1 month';
  partition_name := 'bookings_' || to_char(start_date, 'YYYY_MM');

  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS booking.%I
     PARTITION OF booking.bookings_partitioned
     FOR VALUES FROM (%L) TO (%L)',
    partition_name, start_date, end_date
  );
END;
$$ LANGUAGE plpgsql;

-- Agendar criação automática (pg_cron)
SELECT cron.schedule('create-booking-partition', '0 0 15 * *',
  'SELECT create_booking_partition()');

4.2 Particionamento de Audit Logs

-- Tabela particionada por mês
CREATE TABLE public.audit_logs_partitioned (
  LIKE public.audit_logs INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- Partições com retenção automática
-- Manter 12 meses, arquivar mais antigos

CREATE OR REPLACE FUNCTION maintain_audit_partitions()
RETURNS void AS $$
DECLARE
  retention_months INT := 12;
  drop_date DATE;
  partition_name TEXT;
BEGIN
  -- Remove partições antigas
  drop_date := date_trunc('month', CURRENT_DATE - (retention_months * interval '1 month'));
  partition_name := 'audit_logs_' || to_char(drop_date, 'YYYY_MM');

  -- Arquiva antes de dropar
  EXECUTE format(
    'COPY (SELECT * FROM public.%I) TO ''/backup/audit/%s.csv'' CSV HEADER',
    partition_name, partition_name
  );

  EXECUTE format('DROP TABLE IF EXISTS public.%I', partition_name);
END;
$$ LANGUAGE plpgsql;

---

5. Monitoramento de Índices

5.1 Queries de Análise

-- Índices não utilizados
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname IN ('identity', 'arena', 'booking')
ORDER BY pg_relation_size(indexrelid) DESC;

-- Índices mais utilizados
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname IN ('identity', 'arena', 'booking')
ORDER BY idx_scan DESC
LIMIT 20;

-- Tabelas com índices faltando (sequential scans altos)
SELECT
  schemaname,
  relname,
  seq_scan,
  idx_scan,
  CASE WHEN seq_scan > 0
    THEN round(100.0 * idx_scan / (seq_scan + idx_scan), 2)
    ELSE 100
  END AS idx_usage_percent,
  n_live_tup
FROM pg_stat_user_tables
WHERE schemaname IN ('identity', 'arena', 'booking')
  AND n_live_tup > 1000
ORDER BY seq_scan DESC;

5.2 Alertas de Performance

-- Índices inchados (necessitam REINDEX)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  pg_size_pretty(pg_relation_size(indrelid)) as table_size,
  round(100.0 * pg_relation_size(indexrelid) / NULLIF(pg_relation_size(indrelid), 0), 2) as ratio
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE schemaname IN ('identity', 'arena', 'booking')
  AND pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- > 10MB
ORDER BY ratio DESC;

5.3 Manutenção Automática

-- Função de manutenção de índices
CREATE OR REPLACE FUNCTION maintain_indexes()
RETURNS void AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT schemaname, indexname
    FROM pg_stat_user_indexes
    WHERE schemaname IN ('identity', 'arena', 'booking')
      AND pg_relation_size(indexrelid) > 100 * 1024 * 1024  -- > 100MB
  LOOP
    EXECUTE format('REINDEX INDEX CONCURRENTLY %I.%I',
                   r.schemaname, r.indexname);
    RAISE NOTICE 'Reindexed: %.%', r.schemaname, r.indexname;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Agendar manutenção semanal (domingo 3:00)
SELECT cron.schedule('maintain-indexes', '0 3 * * 0',
  'SELECT maintain_indexes()');

---

6. Configurações de Performance

6.1 postgresql.conf

# Memória para índices
shared_buffers = 4GB              # 25% da RAM
effective_cache_size = 12GB       # 75% da RAM
work_mem = 256MB                  # Para sorts e hashes
maintenance_work_mem = 1GB        # Para REINDEX, VACUUM

# Parallel Query
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost = 0.01
parallel_setup_cost = 100

# Índices
random_page_cost = 1.1            # SSD
effective_io_concurrency = 200    # SSD
default_statistics_target = 100

# Autovacuum agressivo
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

6.2 Estatísticas Estendidas

-- Estatísticas para colunas correlacionadas
CREATE STATISTICS stats_bookings_court_time
ON court_id, start_time
FROM booking.bookings;

CREATE STATISTICS stats_users_tenant_status
ON tenant_id, status
FROM identity.users;

-- Atualizar estatísticas
ANALYZE identity.users;
ANALYZE booking.bookings;
ANALYZE arena.arenas;

---

7. Checklist de Implementação

7.1 Índices Críticos (P0)

• [ ] idx_bookings_conflict_check - Verificação de conflitos
• [ ] idx_users_keycloak_id - Autenticação
• [ ] idx_users_tenant_email - Login por email
• [ ] idx_arenas_tenant_status - Listagem de arenas
• [ ] idx_courts_arena_sport - Listagem de quadras
• [ ] idx_payments_external - Webhooks de pagamento

7.2 Índices Importantes (P1)

• [ ] idx_arenas_location - Busca geográfica
• [ ] idx_arenas_search - Full-text search
• [ ] idx_bookings_user - Histórico do usuário
• [ ] idx_skill_ratings_sport_rating - Rankings
• [ ] idx_audit_logs_time_brin - Queries de auditoria

7.3 Índices de Otimização (P2)

• [ ] Covering indexes para listagens
• [ ] Índices parciais para dados ativos
• [ ] Particionamento de tabelas grandes
• [ ] Estatísticas estendidas

---

8. Referências

• PostgreSQL Index Types
• BRIN Indexes
• GIN Indexes
• Table Partitioning