Início / user-44694

Tom's questions

Martin Hope
Tom
Asked: 2023-07-08 04:47:14 +0800 CST
  • 5

Estou usando o recurso LISTEN/NOTIFY do PostgreSQL para ouvir quando uma linha é alterada.

Para isso utilizo um script PHP para escutar um canal específico.

Como quero manter a alta disponibilidade dessa notificação, é possível ter mais de um cliente ouvindo o mesmo canal e o PostgreSQL distribuir uma notificação para cada cliente (semelhante ao Rabbit) ou os dois clientes sempre receberão a mesma notificação?

postgresql
Martin Hope
Tom
Asked: 2022-03-11 05:50:08 +0800 CST
  • 3

O uso de condições duplicadas no PostgreSQL pode causar algum problema?

Por exemplo, tenho uma tabela de compras com um ID de cliente e minha estrutura em alguns casos duplica uma condição de pesquisa, como no exemplo abaixo.

SELECT p.*
FROM purchases p
WHERE p.customer_id = 123 
AND p.customer_id = 123;

OU

SELECT p.*
FROM purchases p
WHERE p.customer_id = 123 
AND p.salesman = 456 
AND p.departament = 789 
AND p.customer_id = 123;
postgresql postgresql-performance
Martin Hope
Tom
Asked: 2022-02-02 09:11:02 +0800 CST
  • 1

Eu tenho um script PHP que escuta o NOTIFY acionado por um gatilho do PostgreSQL. Quando meu script "ouve" a notificação ele executa outros comandos dentro deste loop, como SELECT, UPDATE, DELETE, etc...

Eg.: $conn->exec('LISTEN "notify_hello"');

while (1) {
    $result = "";
    // wait for one Notify 10seconds instead of using sleep(10)
    $result = $conn->pgsqlGetNotify(PDO::FETCH_ASSOC, 10000);

    if ( $result ) {

        $stmt = $conn->prepare ( 'SELECT * FROM table' );

        ...
    }
    ...
}

Minha dúvida não está relacionada à programação ou como usar LISTEN, mas diretamente com o banco de dados.

Atualmente aproveito a conexão já aberta com o banco de dados para realizar as demais operações DQL/DML dentro do loop, minha dúvida é se posso usar essa conexão já aberta, pois teoricamente ela está ocupada monitorando as notificações do NOTIFYrecurso do PostgreSQL ou se Devo criar uma nova conexão sempre que executar um comando DQL/DML e encerrá-los após a operação que realizei?

postgresql
Martin Hope
Tom
Asked: 2022-01-28 12:00:27 +0800 CST
  • 2

Eu uso o PostgreSQL 12 para armazenar meus dados brutos e o ElasticSearch como datawarehouse para relatórios.

Acontece que existem situações com JOIN(por exemplo, dados que existem em uma tabela mas não existem em outra) o ElasticSearch não me atendendo, tendo que fazer consultas que retornam uma grande quantidade de resultados diretamente no PostgreSQL.

Muitos anos atrás eu trabalhava com um SELECT <column1, column2> FROM <table> WHERE <conditions>;totalmente sem LIMITou OFFSET, isso fazia com que meu banco de dados ficasse com uma carga alta, ocupando muita memória para retornar os resultados e meu servidor web não dava conta da quantidade de dados que era retornado, travando (memória estouro de limite, por exemplo).

Para resolver isso, comecei a trabalhar com um sistema de paginação, trazendo um número limitado de registros e paginando os resultados com LIMIT e uma condição para exibir os registros abaixo do último registro da consulta anterior.

Exemplo:

-- 1st Query
SELECT <column1, column2> FROM <table> WHERE <conditions> LIMIT 512;
-- 2nd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_1 LIMIT 512;
-- 3rd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_2 LIMIT 512;

Na minha opinião este método não parece ser muito bom. Lendo sobre CURSORSisso parece que ele faz algo parecido, mas usando apenas uma busca. E vários sites dizem que ele tem um desempenho muito melhor do que o LIMIT/OFFSET.

Esse tipo de afirmação é verdadeira ou a forma como trabalho atualmente também é boa?

Como não consigo realizar um EXPLAIN ANALYZEem cada um FETCHdos CURSOR, não tenho como realmente analisar se ele é mais rápido e tem melhor desempenho.

postgresql postgresql-performance
Martin Hope
Tom
Asked: 2021-11-24 18:07:47 +0800 CST
  • 1

Lendo a documentação do pg_cron vi que os exemplos só executam um comando ao agendar uma tarefa.

Em uma postagem do StackOverflow , vi que um usuário tentou executar vários VACUUM ao agendar a tarefa, mas ocorreu um erro.

Existe uma maneira de executar VACUUM em várias tabelas em sequência usando pg_cron? Existem cerca de 112 tabelas selecionadas que devem ser aspiradas, de um total de 155, então agendar uma tarefa para cada uma não é muito prático.

Ou por exemplo, excluir registros antigos de uma tabela e imediatamente ao final do processo executar o cron nas tabelas selecionadas?

postgresql postgresql-12
Martin Hope
Tom
Asked: 2021-11-12 19:55:56 +0800 CST
  • 1

Tenho tabelas que mudam muito durante o dia, muitos dados são deletados, modificados e inseridos.

Suspeito que as tabelas e os índices dessas tabelas possam estar inchados.

Vi que existem opções de extensões para o PostgreSQL que podem verificar isso, mas gostaria de evitar criar extensões no meu banco de dados.

Como posso obter essas informações (tabela/índice está inchado) sem ter que usar extensões do PostgreSQL (ex.:pgstattuple), usando apenas recursos nativos do PostgreSQL 12.?

postgresql postgresql-performance
Martin Hope
Tom
Asked: 2021-10-29 12:46:22 +0800 CST
  • 1

Tenho uma tabela que possui mais de 10.000.000 registros, e estou criando uma consulta que retorna cerca de 4436 registros.

Acontece que me dá a impressão de que o custo da consulta para chegar ao último registro é muito alto.

Index Scan using idx_name on task  (cost=0.28..142102.57 rows=3470 width=34) (actual time=14.690..22.894 rows=4436 loops=1)
"  Index Cond: ((situation = ANY ('{0,1,2,3,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[])) AND (deadline < CURRENT_TIMESTAMP))"
Planning Time: 1.335 ms
JIT:
  Functions: 5
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.654 ms, Inlining 0.000 ms, Optimization 1.214 ms, Emission 13.163 ms, Total 16.030 ms
Execution Time: 24.758 ms

Esse nível de custo é aceitável ou esse índice precisa ser melhorado?

Índice:

CREATE INDEX idx_name ON task (situation, deadline, approved)
WHERE
deadline IS NOT NULL AND
situation <> ALL ('{4,5}'::integer[]) AND
approved = 'N';

Minha consulta:

SELECT
        task.deadline,
        task.id
    FROM
        task
    WHERE
        task.deadline IS NOT NULL
        AND task.situation IN ('0', '1', '2', '3', '6' ,'7' ,'8','9','10','11','12','13','14','15','16','17','18','19','20')
        AND task.situation NOT IN ('4', '5')
        AND task.deadline < CURRENT_TIMESTAMP
        AND task.approved = 'N';
postgresql postgresql-performance
Martin Hope
Tom
Asked: 2021-03-10 12:22:23 +0800 CST
  • 0

Eu tenho uma tabela com prazos de entrega de tarefas, que é baseada no departamento de tarefas (apenas), categoria de tarefas (apenas) ou tarefa e categoria do produto. Essas regras podem ser aplicadas diretamente a um cliente, um grupo de clientes ou a todos os clientes.

Exemplo:

  • Cliente 01 + Departamento: Design + Categoria: Criar Logo = 24 horas
  • Cliente 01 + Departamento: Design + Qualquer Categoria = 48 horas Todos
  • Clientes + Departamento: Design + Qualquer Categoria = 72 horas Todos
  • Clientes + Qualquer Categoria + Categoria: Criar Logo = 48 horas

Esta tabela tem a estrutura abaixo:

|rule_id|customer_id|departament_id|category_id|deadline|

SQL para criar tabelas:

CREATE TABLE public.test_customer (
    id INTEGER NOT NULL,
    name varchar(50) NOT NULL
);
CREATE TABLE public.test_departament (
    id INTEGER NOT NULL,
    name varchar(50) NOT NULL
);
CREATE TABLE public.test_category (
    id INTEGER NOT NULL,
    name varchar(50) NOT NULL
);
CREATE TABLE public.test_task (
    id INTEGER NOT NULL,
    customer_id INTEGER,
    departament_id INTEGER,
    category_id INTEGER,
    description varchar(50) NOT NULL
);
CREATE TABLE public.test_time_rule (
    id INTEGER NOT NULL,
    customer_id INTEGER,
    departament_id INTEGER,
    category_id INTEGER,
    time_deadline INTEGER
);

INSERT INTO public.test_customer (id, name) VALUES(1, 'Customer with Rule');
INSERT INTO public.test_customer (id, name) VALUES(2, 'Customer without Rule');

INSERT INTO public.test_departament (id, name) VALUES(1, 'Front-End');
INSERT INTO public.test_departament (id, name) VALUES(2, 'Back-End');
INSERT INTO public.test_departament (id, name) VALUES(3, 'DBA');

INSERT INTO public.test_departament (id, name) VALUES(1, 'Design');
INSERT INTO public.test_departament (id, name) VALUES(2, 'Create HTML Pages');
INSERT INTO public.test_departament (id, name) VALUES(3, 'Create Tables');
INSERT INTO public.test_departament (id, name) VALUES(4, 'Fix Bugs');

INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(1, 1, 1, 1, 'Create New Form Design');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(2, 1, 2, 4, 'Fix Bug on Customer Table');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(2, 1, 3, 3, 'Create City Table');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(3, 2, 1, 1, 'Create New Form Design');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(4, 2, 3, 3, 'Create City Table');

INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(1, NULL, NULL, NULL, 20); --All Customers
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(2, NULL, 1, NULL, 30); --All Customers + Departament
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(3, NULL, NULL, 1, 40); --All Customers + Category
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(4, NULL, 3, 3, 50); --All Customers + Departament + Category

INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(5, 1, NULL, NULL, 20); --Customers With Rule
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(6, 1, 1, NULL, 30); --Customers + Departament
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(7, 1, NULL, 1, 40); --Customers + Category
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(8, 1, 3, 3, 50); --Customers + Departament + Category

Estou tentando criar um SELECTque liste todas as tarefas e mostre qual regra se aplica a ele. Para isso estou utilizando os níveis de aplicação da regra, que são:

1 - Regra com cliente + departamento + categoria (ID de cliente definido)

2 - Regra com grupo de clientes + departamento + categoria (ID do grupo de clientes definido)

3 - Regra geral (todos os clientes) + departamento + categoria (sem cliente definido e ID do grupo de clientes)

4 - Regra com cliente + departamento

Eu resumi as regras, mas elas chegam a 16 regras, e criei uma view para cada regra com as tarefas que correspondem aos seus parâmetros.

Com base nessas regras criei um SELECTcom vários UNION ALL(motivos de performance), mas como a linha pode ser repetida devido a um select anterior, acabei adicionando NOT EXISTScondições para evitar esse problema.

Exemplo:

 SELECT
    1 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NOT NULL
    AND tt.customer_id = ttr.customer_id
    AND tt.departament_id = ttr.departament_id
    AND tt.category_id = ttr.category_id
UNION
SELECT
    2 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NOT NULL
    AND tt.departament_id = ttr.departament_id
    AND tt.category_id = ttr.category_id
UNION
SELECT
    3 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NULL
    AND tt.customer_id = ttr.customer_id
    AND tt.departament_id = ttr.departament_id
UNION
SELECT
    4 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NULL
    AND tt.departament_id = ttr.departament_id
UNION
SELECT
    5 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NOT NULL
    AND tt.customer_id = ttr.customer_id
    AND tt.category_id = ttr.category_id
UNION
SELECT
    6 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NOT NULL
    AND tt.category_id = ttr.category_id
UNION
SELECT
    7 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NULL
    AND tt.customer_id = ttr.customer_id
UNION
SELECT
    8 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NULL
ORDER BY
    level;

Como você deve ter notado a cada nível que ele entra, todas as visualizações anteriores são adicionadas, o que gera um SQL grande e lento.

O meu bom senso diz-me que esta não é a melhor forma de fazer esta consulta.

Considerei algo que ignora uma linha com base no ID da tarefa já exibido em uma união anterior. Existe uma maneira melhor de criar este SELECT?

Observação: não posso adicionar o ID da regra usada na tarefa, pois essa regra pode ser alterada, excluída ou até adicionada no futuro a uma regra que melhor corresponda à tarefa.

postgresql union
Martin Hope
Tom
Asked: 2018-03-13 15:16:24 +0800 CST
  • 2

Qual é a melhor configuração para o pgBouncer no meu caso?

Tenho apenas 1 banco de dados, 1 usuário e o max_connectionsno postgresql.conf é 1024.

A configuração atual do meu pgbouncer.config é:

max_client_conn = 1024
default_pool_size = 1024
min_pool_size = 32
;max_db_connections = 50
;max_user_connections = 50
postgresql pgbouncer