Como evitar consultas pesadas em um banco de dados, afetando o desempenho de outros bancos de dados no mesmo servidor?

Vou citar algumas coisas do wiki do Postgres, Priorities :

Priorizando usuários, consultas ou bancos de dados

O PostgreSQL não possui recursos para limitar quais recursos um determinado usuário, consulta ou banco de dados consome ou, de forma correspondente, para definir prioridades de modo que um usuário/consulta/banco de dados obtenha mais recursos do que outros. É necessário usar os recursos do sistema operacional para alcançar a priorização limitada possível.

Existem três recursos principais pelos quais os usuários, consultas e bancos de dados do PostgreSQL irão competir:

Memória
CPU
Disco E/S

Destes, a E/S de disco geralmente é um gargalo para aplicativos de banco de dados , mas nem sempre é esse o caso. Alguns designs e consultas de esquema são particularmente pesados ​​para a CPU. Outros realmente se beneficiam de ter muita memória para trabalhar, normalmente para classificação.

Considere esta parte em particular:

As prioridades são realmente o problema?

Antes de se esforçar muito para priorizar suas consultas/usuários/bancos de dados, vale a pena otimizar suas consultas e ajustar seu banco de dados . Você pode descobrir que pode obter um desempenho perfeitamente aceitável sem brincar com prioridades ou tomar medidas extremas, usando técnicas como:

• Melhorando suas consultas
• Sintonize o autovacuum para reduzir o inchaço
• Geralmente polindo o desempenho do seu cluster
• Evitando o uso de VACUUM FULL. Isso consome muita memória e demora uma eternidade para escanear, desperdiçando largura de banda de E/S de disco. Consulte a página wiki em VACUUM FULLpara obter mais informações.

E especialmente este:

A CPU é realmente o gargalo?

As pessoas geralmente reclamam de CPU atrelada (100%) e assumem que essa é a causa da lentidão do banco de dados. Esse não é necessariamente o caso - um sistema pode mostrar um uso aparente de 100% da CPU , mas na verdade ser limitado principalmente pela largura de banda de E/S . Considere o teste a seguir, que inicia 20 processos `dd', cada um lendo um bloco diferente de 1 GB do disco rígido (aqui: /dev/md0) em deslocamentos de 1 GB (execute o teste como root).

... teste segue ...

... o que pode ser confundido com uma CPU ocupada , mas na verdade é uma carga causada por E/S de disco . O principal sinal de alerta aqui é a presença de uma alta porcentagem de CPU iowait ("%wa"), indicando que grande parte da carga aparente é realmente causada por atrasos no subsistema de E/S. A maioria dos processos `dd' estão no estado 'D' - ou seja, suspensão ininterrupta em uma chamada de sistema - e se você marcar "wchan" com "ps" verá que eles estão dormindo esperando por E/S.

Em vez de presumir que a contenção da CPU é o problema , é uma boa ideia usar as Ferramentas de Análise de Desempenho disponíveis para ter uma ideia melhor de onde realmente estão os gargalos do sistema.

Então, eu diria que você deve:

• primeiro, siga os conselhos acima, execute mais alguns testes de análise e certifique-se de que o gargalo seja do banco de dados que você suspeita e que seja causado por contenção de CPU (o que pode ser o caso se suas consultas forem tão caras computacionalmente). Ou pode ser parcialmente verdadeiro e há contenção de CPU, mas também espaço para melhorias de consulta.

• siga o conselho do 2º parágrafo acima (melhorar as consultas, geralmente polir o cluster, verificar e otimizar as configurações de vácuo e autovacuidade.)
  Você também deve otimizar as várias configurações de memória (principalmente work_mem) com valores diferentes para os clientes que se conectam a esse banco de dados que tem carga de trabalho diferente.

• Se depois de tudo isso, o problema persistir e ainda houver contenção de E/S de CPU ou disco ou ambos, existem algumas opções descritas na página de Prioridades vinculadas que você pode tentar ("renice", priorizar extensão etc.).

• Uma das sugestões lá (que certamente vale a pena considerar) é separar seus bancos de dados em 2 clusters (instâncias), um para o db computacionalmente caro e outro para todo o resto, com a carga de trabalho usual. Consulte Priorizando bancos de dados separando em vários clusters , onde menciona a provável opção de limpeza:

• Máquinas separadas

  Sua melhor opção é separar instâncias do PostgreSQL em diferentes máquinas físicas. Eles não vão competir por recursos.