John Little's questions

Temos cerca de 100 alterações simples para fazer em nosso esquema de banco de dados, como esta:

alter table transactions alter customer_sport_id type bigint;

Antes era int4. A maioria das colunas alteradas possui um ou mais índices.

Cada um leva cerca de 30 a 45 minutos em uma poderosa instância RDS dedicada (db.r6i.4xlarge) sem outra carga.

Temos que confirmar após cada linha para evitar o uso de todo o armazenamento.

O problema é que é lento, levará dias para fazer as alterações e não podemos ficar inativos por tanto tempo.

Há algo que possamos fazer para acelerar isso? Por exemplo

1. descartando índices e criando-os novamente depois? (isso aceleraria?)
2. desabilitando o WAL? Não tenho certeza se isso é viável ou arriscado (por exemplo, o banco de dados pode ser corrompido se a migração falhar no meio do caminho)
3. Criar uma nova tabela e, de alguma forma, copiar todos os dados antigos para a nova tabela (poderíamos fazer isso em SQL ou exigiria um procedimento armazenado?), eliminar a tabela antiga e criar as sequências e índices na nova tabela ?

Aparentemente, colocamos aspirador uma vez por semana.

Aqui estão as estatísticas de desempenho do banco de dados da última hora (você pode ver no lançamento do armazenamento que duas instruções foram concluídas):

Se eu executar isso no DBeaver:

alter table classes alter column reference set not null;

no meu banco de dados local, que só eu abri no DBeaver, ele foi concluído com êxito.

Mas não consigo abrir a tabela no explorer e a coluna ainda aparece como anulável.

Se eu tentar fechar o DBeaver, ele diz que há uma transação aberta, mas acho que não.

Porém, se eu executar commit, de repente tudo funciona, consigo visualizar a tabela e a coluna aparece como não nula.

Achei que as instruções DDL não tinham o conceito de confirmação e reversão: preciso confirmar após cada uma em meus scripts de atualização do Flyway?

Existem vários posts bons, por exemplo:

Devo usar um índice composto ou de coluna única?

mostrando que se você tiver um índice composto nas colunas (A,B) e um índice único em (A), o postgres usará o índice composto (para melhorar o cache), e o índice único é supérfluo e pode ser descartado.

Ao testar isso, percebo que o postgres às vezes usará o índice único quando o composto estiver disponível, mas não sei se isso significa que ainda precisamos (para desempenho da consulta) de ambos os índices.

Considere o seguinte:

CREATE TABLE public.events (
    id serial4 NOT NULL,
    reference int4 NOT NULL,
    "name" varchar(255) NULL,
    bet_source varchar NULL,
    CONSTRAINT events_pkey PRIMARY KEY (id),
    CONSTRAINT events_reference_betsource_key UNIQUE (reference, bet_source)
);
CREATE INDEX event_reference ON public.events USING btree (reference);

Aqui, a pequena diferença é que o índice composto é único e foi declarado como uma restrição (que presumo ser o mesmo que criar um índice único).

explain (analyze, buffers)
select * from events e where e.reference = 123;

Index Scan using event_reference on events e  (cost=0.28..8.30 rows=1 width=51) (actual time=0.006..0.007 rows=0 loops=1)
  Index Cond: (reference = 123)
  Buffers: shared hit=2
Planning:
  Buffers: shared hit=69 dirtied=1
Planning Time: 0.432 ms
Execution Time: 0.036 ms

Aqui podemos ver no plano de explicação que o postgres optou por usar o índice único. Normalmente, quando testei isso em outras tabelas, o postgres escolhe o índice composto neste caso.

A questão é,

1. estou seguro para remover event_reference sem afetar o desempenho da consulta? Como não tenho acesso aos dados de produção (ou a uma cópia deles), seria difícil testar isso com dados reais.

2. é uma restrição exclusiva igual a um índice exclusivo (para fins de consulta e otimização de inserção)

Meu SQL:

select count('1')
 from plays,
      promotion, 
      partner 
where 
  partner.id = 1
  and promotion.partner_id = partner.id
  and plays.promotion_id = promotion.id

SQL gerado pelo Eloquent (um PHP ORM laravel)

select count(*) as aggregate 
from `plays` 
inner join `promotion` on `promotion`.`id` = `plays`.`promotion_id` 
where `promotion`.`partner_id` = 1

1. Minha versão está usando a junção interna e externa?
2. a junção interna do exemplo gerado é melhor ou é a mesma da minha versão?

Existem 3 tabelas neste exemplo:

plays
  int id
  int promotion_id

promotion
  int id
  int partner_id

partner
  int id

O sql serve apenas para contar todas as jogadas que pertencem a um parceiro (através da tabela de promoção)

Quando eu habilito o log sql para a tabela assim:

set global log_output = 'table'
set global general_log = 'on'

Faça algumas consultas e veja a tabela de log assim:

select * from mysql.general_log 
order by event_time desc

Não vejo meu sql. Eu vejo números hexadecimais como este no campo de argumento ou em branco ('') e nenhum SQL:

0x53484F5720435245415445205441424C4520606D7973716C602E6067656E6572616C5F6C6F6760

O que está dando errado?

Para requisitos regulatórios e de fraude, precisamos registrar todas as alterações na maioria das tabelas no banco de dados. A maneira como fizemos isso com muito sucesso em um projeto anterior é ter uma cópia de cada tabela, idêntica, exceto que:

1. 4 colunas adicionais: DateChanged, UserId, Action (Create, Update, delete) e IP
2. O Id pk não é mais único.
3. todas as outras restrições (FK, índices exclusivos, etc.) foram removidas.
4. as tabelas de auditoria estão em um esquema de auditoria separado

Em um job anterior, os DBAs Oracle escreveram um script para gerar tudo isso automaticamente, fizeram o seguinte:

1. Criou um novo esquema chamado audit se não existir
2. Itere sobre cada tabela t no esquema normal:
   1. criou uma nova tabela no esquema de auditoria com o mesmo nome de tabela, exceto prefixado com a_, por exemplo, a_t
   2. Adicionadas todas as mesmas colunas da tabela original, mais as 4 colunas adicionais (DateChanged, UserId, IP Address e Action)
   3. gatilhos gerados e adicionados à tabela original (se ainda não estiverem lá) para:
   4. Se estiver atualizando, escreva uma nova linha na tabela a_ correspondente, com ação de "Atualizar" contendo os valores pré-atualizados (antigos) em todas as colunas (a tabela principal terá os novos valores)
   5. Se estiver excluindo, adicione uma linha que seja uma cópia da linha da tabela principal, mas ação = "Excluir".
   6. Se inserir, adicione a linha ao esquema de auditoria

NOTA:

1. Todas as tabelas possuem um ID PK.
2. Algumas tabelas precisam ser excluídas para desempenho ou porque não são necessárias (por exemplo, a tabela de saldo que é atualizada por um gatilho)

A beleza desse sistema é que você pode consultar o que mudou e quando, ou quem fez alterações no quê, e ver o registro antes e depois da alteração. as alterações estão no nível da linha do banco de dados, não no nível da coluna individual.

Alguém já se deparou com algo assim para MYSQL 5.6? Não temos um DBA em nossa equipe que poderia escrever algo assim do zero, mas sabemos o suficiente para modificar algo semelhante.