imagem de Perfil de Andrew Vogel

byAndrew VogelonApril 2, 2019

na semana Passada, fui chamado para saltar em um projeto Rails que precisava de algumas otimizações de desempenho. Nossa abordagem escolhida foi tirar um processo de pesquisa e atualização em massa da Terra ActiveRecord e mover tudo para Postgres. A melhoria de desempenho foi enorme para grandes conjuntos de recordes. Na parte alta, vimos que os tempos de solicitação vão de > 5 minutos a sub 2 segundos.

eu já havia usado SQL e Postgres antes, mas não estava totalmente familiarizado com seu conjunto completo de recursos. No início, fiz parceria com um colega de trabalho que tem um conhecimento mais profundo dos Postgres. Depois de alguns dias escrevendo consultas e refatorando o pacote de testes, tudo estava verde e eu estava pronto para implantar no teste para teste.Aqui estão algumas coisas interessantes de SQL e PostgreSQL que aprendi ao longo do caminho.

CTE

embora não seja exclusivo do PostgreSQL, CTE ou expressões de tabela comuns, são uma maneira de escrever consultas reutilizáveis em um sistema de banco de dados.

digamos que temos 2 tabelas – books e authors.

Tabela de Livros
id título author_id gênero
1 Digital em Fortaleza 1 thriller
2 O Código Da Vinci 1 thriller
3 Harry Potter e A Câmara dos Segredos 2 fantasia
Tabela Autores
id nome > idade
1 Dan Brown 54
2 J. K. Rowling 53

podemos escrever uma verdade simples CTE para obter os livros com os autores:

with authors_and_books as ( SELECT b.id as book_id, b.title, a.name as author_name FROM books b JOIN authors a on b.author_id = a.id; ); 

em Seguida, selecionando a partir de nossa CTE, temos a seguinte:

SELECT * FROM authors_and_books; 
book_id título author_name
1 Digital de Fortaleza Dan Brown
2 O Código Da Vinci Dan Brown
3 Harry Potter e A Câmara dos Segredos J. K. Rowling

este exemplo é simples, mas você pode ver como isso pode realmente ser útil quando você precisa reutilizar consultas complexas.

Temp Tables (PostgreSQL)

outro recurso de banco de dados realmente útil é a capacidade de criar tabelas temporárias. No meu caso, isso foi realmente útil para encenar dados “não processados” que foram postados no front-end. Para criar uma tabela temporária, você precisará definir seu esquema como sua instrução normal create table ...

CREATE TEMPORARY TABLE temp_isbns ( title varchar(255) NOT NULL, isbn varchar(255) NOT NULL, author_name varchar(255) NOT NULL ) ON COMMIT DROP; 

a parte importante aqui é o ON COMMIT no final. Você precisará informar ao Postgres como lidar com a tabela temporária no final do bloco de transações. DROP diz Postgres para soltar a tabela temporária no final do bloco de transação. Os documentos Postgres descrevem mais das opções de confirmação ON.

COALESCE

a função COALESCE retorna o primeiro valor não nulo passado para ele. Esta função aceita um número ilimitado de argumentos e retorna o primeiro argumento não nulo, avaliado da esquerda para a direita.

Aqui estão alguns exemplos de como isso pode ser:

Exemplo de Adesão 1

select coalesce(null, 1); 

adesão

a Adesão Exemplo 2

select coalesce(null, null, 1, null); 

adesão

a Adesão Exemplo 3

select coalesce(2, null, 1); 

adesão

Upserts

Upserting, ou atualizando e inserindo, é um superful recurso no Postgres. Ele permite que você manipule inserções com resolução de conflitos se um registro já existir no banco de dados.

lembre-se da nossa tabela de livros?

Tabela de Livros
id título author_id gênero
1 Digital em Fortaleza 1 thriller
2 O Código Da Vinci 1 thriller
3 Harry Potter e A Câmara dos Segredos 2 fantasia

Upserts Exemplo 1

vamos inserir alguns registros em livros, mas se o livro não existe, nós vamos escolher não fazer nada:

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT DO NOTHING; 

Agora vejamos a nossa tabela de:

SELECT * FROM books; 
id título author_id gênero
1 Digital em Fortaleza 1 thriller
2 O Código Da Vinci 1 thriller
3 Harry Potter e A Câmara dos Segredos 2 fantasia
4 Harry Potter and The Half Blood Prince 2 fantasia

Upserts Exemplo 2

Outra mão, vamos inserir registros em livros e atualizar o grave com o ID correspondente. De acordo com os documentos Postgres, você precisará usar a tabela EXCLUDED para fazer referência aos valores propostos para inserção.

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title; 

olhando para a nossa mesa uma última vez:

SELECT * FROM books; 
id título author_id gênero
1 Digital em Fortaleza 1 thriller
2 O Código Da Vinci 1 thriller
3 Harry Potter e O Cálice de Fogo 2 fantasia
4 Harry Potter and The Half Blood Prince 2 fantasia

Obrigado por seguir junto com este post de blog. Se houver um recurso Postgres específico sobre o qual você gostaria de ouvir, sinta-se à vontade para entrar em contato com [email protected]

este post foi útil? Compartilhe com os outros.

  • Twitter logoTweet
  • Facebook logoCompartilhe
  • Linkedin logoPost

Deixe uma resposta

O seu endereço de email não será publicado.