Postgres – Criar estatísticas

2025-12-24 by Leka

O Postgres é “tão evoluído” e blá blá blá, mas não consegue ter uma rotina de criação automática de estatísticas. Parece que voltamos para antes dos anos 2000.

Bom, não vou explicar a importância da criação de estatísticas, isso você já deveria saber, se não sabe, pergunta para sua IA favorita.

A código abaixo vai ler a tabela pg_stat_statements e, a partir dela, eu tento fazer um monte de regex para separar das querys a parte do SARG para criar as estatísticas.

É preciso adicionar a extensão pg_stat_statements:

create extension pg_stat_statements;

Dependendo do seu workload logo em seguida você já vai ter acesso a alguma coisa, mas para ter dados melhores, é melhor deixar o tempo passar para acumular mais informações.

Depois de algum tempo, quando você rodar o script abaixo, deve ter um resultado mais interessante para criar as estatísticas:

WITH normalized AS (SELECT queryid
                         , query
                         -- Remove DO $ ... $ e normaliza espaços
                         , lower(regexp_replace(
            regexp_replace(query, '^do \$\$|\\$\$;$', '', 'gi'),
            '\s+', ' ', 'g'
                                 )) AS norm_query
                         , total_exec_time
                         , calls
                    FROM pg_stat_statements
                    WHERE query ILIKE 'select%'
                      AND calls > 10
                      AND query ~* ' where ')
   , tables AS (SELECT n.queryid
                     , n.query
                     , n.norm_query
                     , n.total_exec_time
                     , n.calls
                     -- Captura tabela e alias
                     , (regexp_match(n.norm_query,
                                     '(?:from|join)\s+([a-z0-9_\.]+)(?:\s+as\s+|\s+)?([a-z0-9_]+)?'))[1] AS table_obj_full
                     , (regexp_match(n.norm_query,
                                     '(?:from|join)\s+([a-z0-9_\.]+)(?:\s+as\s+|\s+)?([a-z0-9_]+)?'))[2] AS table_alias
                     , COALESCE(
            (regexp_match(n.norm_query, '(?:from|join)\s+([a-z0-9_\.]+)(?:\s+as\s+|\s+)?([a-z0-9_]+)'))[2],
            split_part((regexp_match(n.norm_query, '(?:from|join)\s+([a-z0-9_\.]+)'))[1], '.', 2)
                       )                                                                                 AS main_identifier_raw
                     , split_part(
            lower(
                    regexp_replace(
                            (regexp_match(n.norm_query, '(?:from|join)\s+([a-z0-9_\.]+)'))[1],
                            '[^a-z0-9_\.]',
                            '',
                            'g'
                    )
            ),
            '.',
            CASE WHEN (regexp_match(n.norm_query, '(?:from|join)\s+([a-z0-9_\.]+)'))[1] LIKE '%.%' THEN 2 ELSE 1 END
                       )                                                                                 AS table_name
                FROM normalized n
                WHERE (regexp_match(n.norm_query, '(?:from|join)\s+([a-z0-9_\.]+)'))[1] IS NOT NULL)
   , where_clauses AS (SELECT t.queryid
                            , t.table_name
                            , lower(t.main_identifier_raw) AS main_identifier
                            , (regexp_match(
            t.norm_query,
            'where\s+(.*?)(?:\sgroup by|\sorder by|\slimit|;|$)'
                               ))[1]                       AS where_block
                            , t.total_exec_time
                            , t.calls
                       FROM tables t)
   , where_columns AS (SELECT wc.table_name
                            , wc.main_identifier
                            , CASE
                                  WHEN rm.m[1] LIKE '%.%' THEN lower(split_part(rm.m[1], '.', 1))
                                  ELSE NULL END                                 AS column_prefix
                            , rm.m[1]                                           AS full_column_name
                            , regexp_replace(rm.m[1], '^[a-z0-9_]+\.', '', 'i') AS column_name
                            , wc.total_exec_time
                       FROM where_clauses wc
                                CROSS JOIN LATERAL regexp_matches(
                               wc.where_block,
                               '([a-z_][a-z0-9_\.]*)\s*(=|>|<|>=|<=|<>|in\b|like\b)',
                               'gi'
                                                   ) AS rm(m))
   , distinct_columns AS (SELECT table_name
                               , column_name
                               , SUM(total_exec_time) AS total_cost
                          FROM where_columns
                          WHERE column_name IS NOT NULL
                            AND (
                              column_prefix IS NULL
                                  OR column_prefix = main_identifier
                                  OR column_prefix = table_name
                              )
                          GROUP BY table_name, column_name)
   , validated_columns AS (SELECT dc.table_name
                                , dc.column_name
                                , dc.total_cost
                           FROM distinct_columns dc
                                    JOIN information_schema.columns isc
                                         ON lower(isc.table_name) = lower(dc.table_name)
                                             AND lower(isc.column_name) = lower(dc.column_name)
                           WHERE dc.table_name NOT LIKE 'pg_%')
   , family AS (SELECT vc.table_name
                     , (SELECT array_agg(c)
                        FROM (SELECT column_name AS c
                              FROM validated_columns sub
                              WHERE sub.table_name = vc.table_name
                              ORDER BY total_cost DESC
                              LIMIT 8) sub2)       AS columns
                     , COUNT(DISTINCT column_name) AS occurrences
                     , SUM(total_cost)             AS total_table_cost
                FROM validated_columns vc
                GROUP BY vc.table_name
                HAVING COUNT(DISTINCT column_name) > 1)
   , correlation_estimate AS (SELECT f.*
                                   , (array_length(f.columns, 1) ^ 1.3) * ln(total_table_cost + 5) AS score
                              FROM family f)
   , existing_ext_stats AS (SELECT cls.relname                                         AS table_name
                                 , st.stxname                                          AS stat_name
                                 , array_agg(att.attname ORDER BY att.attname)::text[] AS stat_columns
                            FROM pg_statistic_ext st
                                     JOIN pg_class cls ON cls.oid = st.stxrelid
                                     JOIN pg_attribute att
                                          ON att.attrelid = cls.oid
                                              AND att.attnum = ANY (st.stxkeys)
                            GROUP BY cls.relname, st.stxname)
   , dedup AS (SELECT ce.*
                    , 's_' || regexp_replace(ce.table_name, '[^a-z0-9_]', '', 'g') ||
                      '_' || substr(md5(array_to_string(ce.columns, ',')), 1, 8) AS stat_name
                    , EXISTS (SELECT 1
                              FROM existing_ext_stats es
                              WHERE es.table_name = ce.table_name
                                AND es.stat_columns = ce.columns::text[])        AS already_exists
               FROM correlation_estimate ce)

SELECT table_name
     , columns
     , occurrences
     , round(score::numeric, 2)                                AS score
     , stat_name
     , 'CREATE STATISTICS IF NOT EXISTS ' ||
       quote_ident(stat_name) ||
       ' ON ' ||
       array_to_string(
               ARRAY(SELECT quote_ident(c) FROM unnest(columns) c), ', '
       ) ||
       ' FROM ' ||
       quote_ident(table_name) ||
       ';'                                                     AS suggested_cmd
     , 'Filtros mais custosos sobre (' ||
       array_to_string(columns, ', ') ||
       ') em ' || table_name ||
       '. Ocorrências: ' || occurrences ||
       '. Custo total estimado: ' || total_table_cost || 'ms.' AS justification
FROM dedup
WHERE NOT already_exists
  AND table_name NOT LIKE 'pg_%'
  AND table_name NOT IN ('table_constraints', 'columns', 'tables')
  AND array_length(columns, 1) > 1
ORDER BY score DESC, total_table_cost DESC, occurrences DESC;

Postgres – vacuum e analyze verbose

2025-12-15 by Leka

Depois de passar algum tempo analisando a saída do vacuum e do analyze, para entre 800 tabelas descobrir quais estão com algum possível problema, montei esse script que, novamente, abre uma tela para fazer essa análise e indicar possíveis necessidades de atenção.

https://github.com/bigleka/pg-scripts/blob/main/vacuum_analyzer.py

Ele consegue analisar:

Vacuum Analyze ou
Verbose Analyze ou
ou os dois ao mesmo tempo.

É bem simples, execute o vacuum verbose e o analyze verbose no seu aplicativo favorito e depois copie e cole o resultado, clique em “Analisar” e pronto, temos alguma coisa para analisar.

GCP – Clonar instâncias entre projetos

2025-11-26 by Leka

Imagine o seguinte cenário:

Você organizou a estrutura da sua conta GCP para trabalhar com projetos distintos;

Fez as TAGs para poder ter os valores corretos por projeto e saber exatamente quanto cada um custa;

Agora precisa copiar uns bancos que estão em um servidor CloudSQL de um projeto para outro projeto, fácil certo? Até descobrir que a GCP não faz de forma fácil.

Mas tem outras formas, da pra gerar um backup do banco, mandar para o bucket, copiar o arquivo de um bucket para outro e depois restaurar, ou usar um servidor intermediário, faz a mesma coisa mas manda o arquivo para esse servidor e restaurado no outro, claro que dá, além de tomar tempo e mesmo que você consiga fazer em paralelo, quantos bancos consegue fazer? o servidor aguenta? o GCP tem limite de IO e operações de Leitura/Gravação no CloudSQL, além de inevitavelmente ser lento.

Eles tem a opção de restaurar um backup da instância em cima de outra instância no mesmo projeto, então deveria ter a opção de fazer a mesma coisa em projetos distintos, no final das contas eles tem, mas tem que ser feito por meio de requisição de API.

No link abaixo montei um python que vai carregar suas credenciais, listar os projetos que você tem direito de acesso, lista as instâncias, backups das instâncias e ai te ajuda a restaurar esse backup em uma outra instância em qualquer outro CloudSQL de qualquer outro projeto.

https://github.com/bigleka/gcp/blob/main/gcp_restore_cloud_database_in_another_project.py

O app é bem simples:

Clique em “Carregar Credenciais GCP”;
localize o arquivo json gerado pelo comando.
gcloud auth application-default login
ele vai carregar a lista dos projetos que você tem acesso nas duas listas de “Origem” e “Destino”;
Clicando no projeto ele vai listar as instâncias na combobox abaixo da lista dos projetos.
Clicando na instância de banco ele vai listar os backups disponíveis, selecione o backup que você quer restaurar na outra instância.
Agora faça a mesma coisa no “Destino”, selecione o projeto e a instância.
ELE VAI SOBRESCREVER a instância de destino, apagando e restaurando o backup por cima da instância do destino.
e clique em “Restaurar”.

Como o processo é assíncrono, caso você queira acompanhar o status da restauração, vá para o console da GCP e acompanhe de lá.

GCP – Copiar flags entre instâncias

2025-11-23 by Leka

Para quem já trabalhou com o GCP CloudSQL sabe a dor de cabeça que é não ter uma forma fácil de gerenciar as flags das instâncias ou uma forma fácil de copiar as flags de uma instância para outra.

Parece que eles gostam muito de dificultar uma administração que deveria ser simples.

Bom, no link abaixo eu montei um outro python que carrega a lista de projetos que você tem acesso, lista as instâncias e as flags, ai você pode escolher quais vão ser aplicadas na outra instância e até editá-las antes de aplicar.

Trabalho em progresso:

Ainda estou tentando listar quais as flags que são obrigatórias de ter reboot, então, por enquanto, assuma que todas vão causar algum tipo de reboot no destino (mesmo que não causem).

https://github.com/bigleka/gcp/blob/main/gcp_copy_cloudsql_flags.py

Eu sou muito fã de linha de comando, mas como as pessoas gostam de interface gráfica, esse python usa o tkinter para montar uma tela zoada para você ser mais feliz.

Basicamente, deixei todas as dependências que precisam ser instaladas na parte comentada do código.

Ordem da chave primária

2024-05-20 by Leka

A ideia desses 2 scripts é fazer uma simples análise e sugerir uma possível melhoria na ordem da chave primária de uma tabela específica.

Estes scripts não são para serem seguidos a ferro e fogo, existem outros fatores para serem considerados na ordem de uma PK, mas para quem não tem nada, e quer ter pelo menos uma ideia para onde ir montei eles como procedure para o SQL e como função para o Postgresql.

Versão SQL Server:

ALTER PROCEDURE usp_SugerirNovaOrdemChavePrimaria
    @SchemaName NVARCHAR(128),
    @TableName NVARCHAR(128)
AS
BEGIN
	SET ARITHABORT OFF 
	SET ANSI_WARNINGS OFF
    DECLARE @ColumnName NVARCHAR(128);
    DECLARE @Sql NVARCHAR(MAX) = '';
    DECLARE @OrderSuggestions NVARCHAR(MAX) = '';
    DECLARE @DynamicSQL NVARCHAR(MAX);
    DECLARE @Densidade FLOAT;
    DECLARE @OrderTable TABLE (ColumnName NVARCHAR(128), Densidade FLOAT);
    DECLARE @CurrentOrder NVARCHAR(MAX) = '';

    -- Cursor para iterar sobre as colunas da chave primária
    DECLARE ColumnCursor CURSOR FOR
        SELECT COL_NAME(ic.object_id, ic.column_id) 
        FROM sys.indexes AS i 
        INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id 
        WHERE i.is_primary_key = 1 
        AND OBJECT_NAME(ic.object_id) = @TableName;

    OPEN ColumnCursor;
    FETCH NEXT FROM ColumnCursor INTO @ColumnName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Constrói e executa a consulta SQL dinâmica para calcular a densidade para cada coluna
        SET @DynamicSQL = 'SELECT @DensidadeOUT = (COUNT(DISTINCT ' + QUOTENAME(@ColumnName) + ') * 1.0 / COUNT(*)) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
        EXEC sp_executesql @DynamicSQL, N'@DensidadeOUT FLOAT OUTPUT', @Densidade OUTPUT;

        -- Adiciona os resultados em uma tabela temporária
        INSERT INTO @OrderTable (ColumnName, Densidade) VALUES (@ColumnName, @Densidade);

        -- Constrói a ordem atual
        SET @CurrentOrder += @ColumnName + ', ';

        FETCH NEXT FROM ColumnCursor INTO @ColumnName;
    END

    CLOSE ColumnCursor;
    DEALLOCATE ColumnCursor;

    -- Remove a última vírgula e espaço da ordem atual
    IF LEN(@CurrentOrder) > 0
    BEGIN
        SET @CurrentOrder = LEFT(@CurrentOrder, LEN(@CurrentOrder) - 1);
    END

    -- Constrói a sugestão de ordem com base na densidade
    SELECT @OrderSuggestions += ColumnName + ', '
    FROM @OrderTable
    ORDER BY Densidade ASC, ColumnName;

    -- Remove a última vírgula e espaço
    IF LEN(@OrderSuggestions) > 0
    BEGIN
        SET @OrderSuggestions = LEFT(@OrderSuggestions, LEN(@OrderSuggestions) - 1);
    END

    -- Compara a ordem atual com a sugerida
    IF @CurrentOrder = @OrderSuggestions
    BEGIN
        SELECT @TableName as [Object], 'A ordem atual já é a melhor.' AS SuggestedOrder;
    END
    ELSE
    BEGIN
        -- Retorna a sugestão de ordem
        SELECT @TableName as [Object], @OrderSuggestions AS SuggestedOrder;
    END
END

Versão para Postgresql:

CREATE OR REPLACE FUNCTION mc1_sugerir_nova_ordem_chave_primaria(schema_name text, nome_tabela text)
RETURNS text AS $$
DECLARE
    coluna_atual record;
    ordem_sugerida text := '';
    ordem_atual text := '';
    query text;
BEGIN
    -- Prepara a query para obter a ordem atual das colunas da chave primária
    FOR coluna_atual IN
        SELECT kcu.column_name
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        WHERE tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_name = nome_tabela
            AND tc.table_schema = schema_name
        ORDER BY kcu.ordinal_position
    LOOP
        ordem_atual := ordem_atual || coluna_atual.column_name || ', ';
    END LOOP;

    -- Remove a última vírgula e espaço da ordem atual
    IF LENGTH(ordem_atual) > 0 THEN
        ordem_atual := substr(ordem_atual, 1, LENGTH(ordem_atual) - 2);
    END IF;

    -- Prepara a query para calcular a densidade das colunas da chave primária
    query := format($f$
        SELECT 
            kcu.column_name,
            (COUNT(DISTINCT %I) * 1.0 / COUNT(*)) AS densidade
        FROM 
            information_schema.table_constraints AS tc
        JOIN 
            information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        JOIN 
            %I.%I AS t
            ON true
        WHERE 
            tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_name = %L
            AND tc.table_schema = %L
        GROUP BY 
            kcu.column_name
        ORDER BY 
            densidade DESC, kcu.column_name
    $f$, 'column_name', schema_name, nome_tabela, nome_tabela, schema_name);

    -- Executa a consulta e processa os resultados
    FOR coluna_atual IN EXECUTE query
    LOOP
        ordem_sugerida := ordem_sugerida || coluna_atual.column_name || ', ';
    END LOOP;

    -- Remove a última vírgula e espaço
    IF LENGTH(ordem_sugerida) > 0 THEN
        ordem_sugerida := substr(ordem_sugerida, 1, LENGTH(ordem_sugerida) - 2);
    END IF;

    -- Compara a ordem atual com a sugerida
    IF ordem_atual = ordem_sugerida THEN
        RETURN 'A ordem atual já é a melhor.';
    ELSE
        RETURN ordem_sugerida;
    END IF;
END;
$$ LANGUAGE plpgsql;

SQL na Caixinha

2022-06-23 by Leka

Que o SQL pode rodar em container já não é novidade tem um tempinho.

A facilidade que isso nos trás para testar recursos, novidades, configurações, bugs, etc. ajudou demais.

Só o trabalho de subir um SO, configurar todo o SO, atualizações do SO, baixar a instalação do SQL, todo o processo de instalação, atualização, configuração já cansa só de lembrar.

Tá certo que com a vantagem na nuvem podemos subir qualquer configuração a qualquer momento, só dependendo do limite do cartão de crédito, mas com o Docker, da pra fazer basicamente a mesma coisa sem precisar de uma conta em alguma nuvem, sem ter que ficar instalando um monte de binário com um monte de biblioteca, não se preocupando se está no patch certo do SO, etc.

Basicamente com duas linhas de comando você consegue “rodar” qualquer SQL Server do 17 até o 22 em qualquer cumulative update que houve nesse meio tempo.

Para começar, instale o Docker Desktop (https://www.docker.com/get-started/);

Após alguns restarts e atualizações você deve ter ele pronto no seu PC.

Agora, no Terminal, PowerShell, CMD digite o comando abaixo:

docker pull mcr.microsoft.com/mssql/server

Espere ele carregar algumas configurações

Em seguida vem a mágica com esse segundo comando:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest

E pronto, basicamente só isso e você vai ter um SQL Server Developer Edition 2022 rodando no seu PC

Claro que tem seus detalhes, nessa configuração simples tudo o que acontece no docker fica dentro do docker, se você apagar o container todas as bases que você criou, registros, etc. serão apagadas, o backup também conta.

Por padrão ele não integra com autenticação windows.

Se você procurar nos serviços ele não aparece listado.

Basicamente para conectar é seu hostname e a porta 1433 com usuário SA e a senha digitada ali em cima.

Caso precise da lista de todas as releases que você pode subir com o Docker a lista encontra-se aqui (https://hub.docker.com/_/microsoft-mssql-server).

Data Secrets #38

2021-10-15 by Leka

Foi um grande prazer ter participado do Data Secrets #38.

Quem tiver a oportunidade de assistir segue o vídeo:

AWS – EC2 com SQL

2021-03-23 by Leka

Caso você contrate uma AMI com SQL e precise da mídia de instalação do SQL para qualquer atividade, na unidade C:\ existe um diretório chamado “SQLServerSetup” com os binários para a instalação do SQL Server.

Isso ajuda caso precise trocar o Collation da instância, adicionar feature, reinstalar usando uma instância, adicionar uma instância, etc..

A instalação padrão vem na instância default, collation SQL_Latin1_General_CP1_CI_AS, tempdb nas configurações NNF, sem IFI, basicamente uma instalação NNF.

Aí vem outra pergunta, por que pegar uma imagem da AWS com SQL? por que não usar um RDS?

Bom, a resposta disso é mais com você do que comigo, porque tudo vai depender da necessidade.

AMI – EC2 com SQL Instalado

As imagens da AWS com SQL instalado vem em diversos sabores, você escolhe o tamanho da máquina e o tipo de licenciamento STD ou ENT, eles tem developer mas se optar por esse developer você vai pagar um custo pela licença de uma aplicação que pode ser baixada gratuitamente, e ai o preço desse licenciamento do STD ou ENT vai depender do tamanho da máquina que você escolher, a vantagem fica justamente na questão de licenciamento, quem recolhe e paga para a Microsoft é a AWS, você é apenas uma empresa que está usando uma imagem já pré-instalada, então sem stress quando a licenciamento;
Toda a administração do ambiente e com você, eles só deixam o SQL instalado e o resto é o trabalho de casa, desde restaurar o banco até todas as rotinas de manutenção.

RDS

Basicamente o SQL como serviço
você não loga na máquina, não tem nenhum acesso a estrutura onde o SQL está instalado
você não é SA nem faz parte da role de Sysadmin
você é owner dos seus bancos
todas as rotinas de manutenção do SO e algumas do SQL são geridas pela AWS.
é uma administração meio a meio

Vou tratar da comparação entre uma AMI e um RDS em outro post.

AWS – Redshift – Tráfego de dados

2021-03-18 by Leka

Imagine o seguinte cenário:

Você usa o Redshift como DW ou DL para seus relatório e cargas de dados;
Vê uma possibilidade de facilitar sua vida e dar liberdade para o próprio cliente acessar esses dados e gerar relatórios da forma que ele achar mais legal com a ferramenta que ele quiser, etc.;
Mas lembra que a AWS cobra pela saída de dados;
Procura no portal da AWS e descobre que eles não tem uma monitoração específica de quem está saindo com dados, mas eles acertam a cobrança… incrível…
Mas você não quer abandonar a ideia e quer ganhar alguma grana com isso..

O que vou mostrar não é a solução perfeita, ela carece de algumas melhorias mas já é um norte para ajudar nessa ideia…

O Redshift é um PostgreSQL modificado, então muita query em tabelas de sistema do PG funciona direitinho no Redshift…

Para esse cenário, você pode criar um pacote de integration services e rodar a query abaixo contra o Redshift:

select
	TRIM(q.DATABASE) AS DB,
	TRIM(u.usename) as usename,
	sum(bytes)/1024 as kbytes,
	sum(packets) as packets,
	date(b.starttime) as data
from
	stl_bcast b
join stl_query q ON
	b.query = q.query
	AND b.userid = q.userid
join pg_user u
on u.usesysid = q.userid
where
	packets>0
	and b.starttime >= dateadd(day, -7, current_Date)
	and datediff(seconds, b.starttime, b.endtime)>0
	--and u.usename like 'usr%'
	--and querytxt not like 'fetch%'
group by TRIM(q.DATABASE)
,u.usename
,date(b.starttime)

Essa query vai trazer a informação do volume em kb trafegado pela query executada.

Com isso, você consegue montar um report incremental e ratear o custo da saída de dados da AWS.

É 100%?, não,,, mas pelo menos já é alguma coisa já que a AWS não provê dados granularizados de quem consome a saída de dados.

novos códigos serão criados também em outro repositório:

https://github.com/bigleka

Blog do Leka

let's make things better

Category Archives: SQL