Database & Dados

-- PostgreSQL
CREATE TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id  UUID NOT NULL DEFAULT gen_random_uuid(),
    -- ...
    CONSTRAINT uq_orders_public_id UNIQUE (public_id)
);

-- SQL Server
CREATE TABLE orders (
    id        BIGINT IDENTITY(1,1) PRIMARY KEY,
    public_id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    -- ...
    CONSTRAINT uq_orders_public_id UNIQUE (public_id)
);

-- Exemplo de identificadores aleatórios
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
    -- Meça padrão de inserção, page splits e tamanho do índice
);

-- Alternativa: chave interna sequencial e UUID público
CREATE TABLE customers (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id UUID NOT NULL DEFAULT gen_random_uuid(),
    name      VARCHAR(200) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_customers_public_id UNIQUE (public_id)
);

-- Exemplo de estrutura
CREATE TABLE products (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id    UUID NOT NULL DEFAULT gen_random_uuid(),
    name         VARCHAR(300) NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at   TIMESTAMPTZ NULL, -- NULL = ativo, NOT NULL = deletado
    CONSTRAINT uq_products_public_id UNIQUE (public_id)
);

-- Índice filtrado para buscar só ativos (PostgreSQL)
-- Exclui registros deletados deste índice
CREATE INDEX ix_products_name_active
    ON products (name)
    WHERE deleted_at IS NULL;

-- Query padrão: sempre com o filtro
SELECT * FROM products WHERE deleted_at IS NULL AND name ILIKE '%notebook%';

-- Índice simples pode exigir lookups para outras colunas
CREATE INDEX ix_orders_customer_id ON orders (customer_id);

-- Covering index: inclui as colunas desta query
CREATE INDEX ix_orders_customer_id_cover
    ON orders (customer_id)
    INCLUDE (status, total_amount, created_at);

-- Confira se o plano de execução usa o covering index:
SELECT status, total_amount, created_at
FROM orders
WHERE customer_id = 42 AND deleted_at IS NULL;

-- ✅ Índice composto: ordem importa
-- Escolha a ordem pelos predicados e pelo plano de execução
CREATE INDEX ix_orders_status_created
    ON orders (status, created_at DESC)
    WHERE deleted_at IS NULL;

-- Exemplos de manutenção manual: avalie antes de executar
-- Agende conforme a carga e a necessidade de manutenção
VACUUM (VERBOSE, ANALYZE) orders;

-- Ver dead tuples por tabela (monitorar proativamente)
SELECT
    schemaname,
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

-- Ver uso dos índices antes de considerar remoção
SELECT
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
ORDER BY relname;

-- Verificar fragmentação por índice
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent AS fragmentation_pct,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Reorganizar: avalie carga, densidade de páginas e benefícios
ALTER INDEX ix_orders_customer_id ON orders REORGANIZE;

-- Reconstruir: avalie custos e bloqueios nesta edição
ALTER INDEX ix_orders_customer_id ON orders REBUILD WITH (ONLINE = ON);

-- Atualizar estatísticas de uma tabela
UPDATE STATISTICS orders WITH FULLSCAN;

-- Ver queries mais lentas (Top 10 por CPU)
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_ms DESC;