codecool's questions

Estou lendo a seção de backup dos documentos do postgresql antes de criar uma estratégia de backup em minha instância do EC2.

Portanto, existem 2 maneiras de fazer isso.

1. Crie um despejo usando pg_dump.
2. Backup em nível de arquivo com WALarquivamento ativado. Estarei usando Snapshots, então meu caso de uso se enquadra nesta categoria.

Agora a questão é preciso congelar meu sistema de arquivos antes de fazer o backup? A documentação do Postgresql sugere que a recuperação do WAL não é afetada por um instantâneo inconsistente. Faz sentido congelar apenas para estar seguro?

Devo fazer backup através de ambas as formas acima?

Que tipo de período de tempo após o qual devo remover logs antigos do WAL?

Os documentos também mencionam que os logs e dados do WAL devem estar no mesmo sistema de arquivos para tornar o backup consistente. Nesse caso, estou pensando em fazer backup dos logs do WAL separadamente do instantâneo do EBS e salvá-lo no S3 e em algum local externo da Amazon. Pensamentos sobre esta estratégia?

Por favor, mencione se eu estiver perdendo alguma coisa.

Estou usando a AWS como meu ambiente de nuvem. Instalei o PostgreSQL na mesma unidade que meu volume de instância raiz. Anexei e montei a segunda unidade à minha instância. Agora quero mover todos os meus dados do PostgreSQL para a unidade diferente. Ainda estou no modo de desenvolvimento para poder excluir os dados antigos se facilitar a transferência neste momento. Qual é a melhor maneira de fazer isso?

O tablespace do PostgreSQL é algo que eu deveria olhar?

Um usuário está conectado a uma organização por alguma função. Digamos que haja 2 funções freelancer e funcionário.

Um funcionário só pode fazer parte de uma organização e, se for funcionário, não pode ser freelancer. Considerando que um freelancer pode estar conectado a várias organizações e também não pode ser um funcionário.

Resumindo, Freelancer e Funcionário são usuários. O Freelancer está conectado à Organização por meio de um relacionamento de muitos para muitos. O funcionário está conectado à organização por meio de um relacionamento de um para muitos.

O que eu pensei:

user{
   id,
   name,
   other_fields,
}

organization{
    id,
    other_fields
}

freelancer{
    user_id,
    organization_id,
    Primary_Key(user_id, organization_id)
}

employee{
    user_id,
    organization_id,
    Primary_key(user_id),
}

Há um problema aqui, no entanto. A restrição de ser freelancer ou funcionário, mas não ambos, deve ser implementada no nível do aplicativo.

1. Quais são os prós e contras do esquema acima?
2. Quais são as outras alternativas possíveis para o esquema de design?
3. E se houver mais categorias de usuários? O esquema atual requer a adição de uma nova tabela se uma nova função de usuário for criada. Está tudo bem ou ruim?
4. A manutenção do banco de dados também é uma preocupação de fazer um esquema que não deve tornar a manutenção um problema

Eu tenho um aplicativo onde tenho que armazenar endereços de vários tipos de localização.

Por exemplo, meu aplicativo requer endereços de pessoas, hotéis e locais de eventos.

Devo criar tabelas separadas para diferentes tipos de endereços com os mesmos campos? ou Devo adicionar um campo adicional informando qual é o tipo de endereço?

Agora a situação devido à qual esta pergunta veio a mim. esquema é

    Table User_Read_Book
       user_id | book_id 

Agora, quero obter usuários que leram determinados livros. Diga, dê-me usuários que leram o livro número 1 e 2. O número de livros para testar pode ir até 10.

Primeira consulta que escrevi:

Select user_id from User_Read_Book Where book_id In (1,2) Group by user_id Having count(book_id) = 2

Segunda consulta:

Select  user_id from User_Read_Book as U join User_Read_Book as U1 On
 U.user_id = U1.user_id And U1.book_id = 1 where U.book_id = 2

Como dito nesta resposta https://stackoverflow.com/a/621891 que preferem joins em caso de group by e tendo feito a segunda consulta.

Mas minha pergunta é qual é a melhor consulta quando o número a ser correspondido é grande. Diga quando você precisa encontrar usuários que leram 7 livros

   Having Count(book_id) = 7
   or
   6 joins to the same table.

Eu sei que esta pergunta é melhor respondida quando testada em dados grandes e em tempo real. Ainda assim, qual é a opinião dos especialistas sobre isso?

Não consigo entender o conceito de funções de janela. Como exatamente eles funcionam e quais são os prós e contras dessa técnica?

Eu li que usar limite e deslocamento é lento, mas ainda é usado. Portanto, a decisão entre funções de limite/deslocamento e janela depende do tamanho dos dados a serem obtidos do banco de dados ou das funções de janela sempre devem ser usadas?

Qual é o tamanho ideal da janela para obter o máximo desempenho disso? Pode ser o caso em que, se o número de linhas a serem obtidas for menor, basta usar limite/deslocamento?

Meu caso de uso requer basicamente classificar os dados na data e, em seguida, trazer os dados mais recentes primeiro. Como as funções da janela se encaixam nesse cenário.

Enquanto escrevia uma consulta outro dia, um pensamento me ocorreu e ficou preso em minha mente.

O que é preferível, primeiro verificar se existe um valor para uma coluna exclusiva e, em seguida, inserir ou inserir e deixar o db aumentar o erro de restrição exclusiva? Será que isso importa?

Editar: conforme sugerido abaixo na resposta de que esse problema depende do banco de dados, estou adicionando a tag postgresql.

Pesquisei mas não encontrei nenhuma discussão sobre o assunto. Existem prós e contras na criação de muitas colunas extras na tabela unida? Isso afeta a velocidade de recuperação de dados porque estamos juntando 3 tabelas?

Tanto quanto eu penso, pode diminuir um pouco, mas é necessário, caso contrário, como podemos salvar dados adicionais sobre relacionamentos muitos-para-muitos. Ou existe alguma outra forma que desconheço?

Como é o suporte Unicode do PostgreSQL?

Se eu quiser salvar dados Unicode, ele precisa passar pelo processo de conversão durante o armazenamento e a recuperação? Se sim, qual é o desempenho dessa conversão?

Se houver tal conversão, ela entrará em jogo ao armazenar dados ASCII simples?

Não consigo entender o conceito e o uso do esquema no PostgreSQL. Não tenho ideia de como isso pode afetar o design do meu banco de dados. Por que devo usá-lo?

Isso pode me afetar no futuro se eu decidir não pensar neles agora e me preocupar com eles mais tarde?

Explicação com um exemplo será bom.

Minha dúvida é quanto ao uso de índices.

1. Devo começar a indexar desde o início ou quando surgir um problema de desempenho?

2. Também podemos criar um índice temporário durante a execução de uma consulta. Quais são os prós e os contras de tais técnicas?

Eu tenho algumas perguntas sobre o trabalho de índices no PostgreSQL. Tenho uma Friendstabela com o seguinte índice:

   Friends ( user_id1 ,user_id2) 

user_id1e user_id2são chaves estrangeiras para a usertabela

1. Estes são equivalentes? Se não, então por quê?

   Index(user_id1,user_id2) and Index(user_id2,user_id1)
   

2. Se eu criar a chave primária (user_id1,user_id2), ela cria automaticamente índices para ela e

   Se os índices na primeira pergunta não forem equivalentes, qual índice é criado no comando de chave primária acima?

Estou projetando um esquema de banco de dados para um aplicativo da web. É a primeira vez que estou projetando um banco de dados com um objetivo comercial. Comecei a desenhar diagramas ER depois de passar pela fase de requisitos.

Minha pergunta é sobre as condições no esquema.

Eu tenho uma categoria de tabela e subcategoria com um para muitos relacionamentos entre eles. Uma categoria tem muitas subcategorias.

Tabela de categorias

======================
| Nome da categoria |
======================
| categoria1 |
| categoria2 |
======================

Tabela de subcategorias

===========================================
| Categoria do Proprietário | Nome da subcategoria |
===========================================
| categoria1 | subc1 |
| categoria1 | subc2 |
| categoria2 | subc3 |
| categoria2 | subc4 |
===========================================

Agora um usuário tem acesso a subc1se ele tiver acesso a category1.

Existe alguma maneira de representar esse cenário com o diagrama ER para que, ao consultar o diagrama, seja possível conhecer essa restrição?

Basicamente, quero saber como essa restrição de acesso é propagada para a próxima fase de projetos.