All perguntas(dba)

Estou executando o postgres 16.1 com um único usuário e nenhuma outra atividade no servidor. Eu executo este teste várias vezes e os resultados são muito consistentes.

O problema

A consulta abaixo será executada com tempos muito consistentes e o mesmo plano de execução (independentemente de o servidor ter sido iniciado há 3 segundos ou executado por semanas). Mas o tempo total de execução depende se a consulta será executada a partir de um cliente SQL ou de psql no servidor.

O tempo de qualquer SQL-Client é cerca de 3 vezes maior em comparação ao psql. Mas se eu executar a mesma consulta em um cliente SQL, mas com

explicar analisar tempos detalhados wal buffers

o tempo é muito comparável à execução do psql.

A observação que fiz é que a execução no psql usa 5 núcleos correspondentes à configuração ax_parallel_workers_per_gather=5. Por outro lado, a execução do SQL-Client utiliza apenas 1 núcleo e o tempo de execução é de cerca de 90 segundos em vez de 30.

Você pode me ajudar a elaborar isso?

2024-02-01 09:56:25.828 CET [650041] postgres@E_LOG:  duration: 29491.907 ms  plan:
    Query Text: WITH
    "x0" AS
        (
        SELECT
            "x1"."cal_dateid" AS "cal_dateid",
            "x1"."cal_dateint" AS "cal_dateint",
            "x1"."cal_date" AS "cal_date",
            "x1"."cal_year" AS "cal_year",
            "x1"."cal_month" AS "cal_month",
            "x1"."cal_day" AS "cal_day",
            "x1"."cal_yearmonth" AS "cal_yearmonth",
            "x1"."cal_datetext" AS "cal_datetext",
            "x1"."cal_yearmonthtext" AS "cal_yearmonthtext",
            "x1"."cal_monthtext" AS "cal_monthtext",
            extract(week from "x1"."cal_date") AS "c_date",
            "x1"."QWE" AS "QWE"
        FROM
            "public"."ABC" "x1"
        ),
    "DEF" AS
        (
        SELECT
            "x0"."cal_date" AS "cal_date",
            "x0"."cal_year" AS "cal_year"
        FROM
            "x0"
        )
    SELECT
        "DEF"."cal_year" AS "intr_year",
        SUM("ABC_ALL"."ID") AS "ID",
        COUNT(DISTINCT "ABC_ALL"."id") AS "id_sing",
        SUM("CDC"."SUM") AS "SUM_1"
    FROM
        "public"."ABC_2" "ABC_ALL"
            LEFT OUTER JOIN "DEF"
            ON "DEF"."cal_date" = "ABC_ALL"."date_occ"
                LEFT OUTER JOIN "public"."case" "CDC"
                ON "CDC"."id_fk" = "ABC_ALL"."occ_id"
    GROUP BY
        "DEF"."cal_year"
    ORDER BY
        "intr_year" DESC NULLS last;
    GroupAggregate  (cost=1753092.95..5239798.05 rows=151 width=28)
      Group Key: x1.cal_year
      ->  Gather Merge  (cost=1753092.95..4973483.11 rows=26631343 width=26)
            Workers Planned: 5
            ->  Sort  (cost=1752092.87..1765408.54 rows=5326269 width=26)
                  Sort Key: x1.cal_year DESC NULLS LAST, ABC_ALL.id
                  ->  Hash Left Join  (cost=164169.92..1084201.62 rows=5326269 width=26)
                        Hash Cond: (ABC_ALL.date_occ = x1.cal_date)
                        ->  Parallel Hash Right Join  (cost=162083.00..1068132.16 rows=5326269 width=26)
                              Hash Cond: (CDC.id_fk = ABC_ALL.occ_id)
                              ->  Parallel Seq Scan on case CDC  (cost=0.00..892067.69 rows=5326269 width=12)
                              ->  Parallel Hash  (cost=155333.00..155333.00 rows=540000 width=22)
                                    ->  Parallel Seq Scan on ABC_2 ABC_ALL  (cost=0.00..155333.00 rows=540000 width=22)
                        ->  Hash  (cost=1397.52..1397.52 rows=55152 width=8)
                              ->  Seq Scan on ABC x1  (cost=0.00..1397.52 rows=55152 width=8)
    JIT:
      Functions: 22
      Options: Inlining true, Optimization true, Expressions true, Deforming true

Atualmente estou investigando as possíveis aplicações e vantagens da utilização do intervalo negativo dos tipos de dados int ou bigint no SQL Server, com foco particular no intervalo que se estende de -2.147.483.648 a 0. Você poderia, por favor, esclarecer os usos potenciais e benefícios de empregar uma faixa tão negativa? Além disso, estou intrigado com a possibilidade de otimizar este intervalo negativo como uma alternativa ao intervalo convencional de 0 a positivo, que normalmente começa com uma semente de 1. Estou ansioso para descobrir estratégias inovadoras e eficientes para capitalizar este intervalo negativo em todo o mundo. uma variedade de cenários.

Atualmente estamos usando MySQL. Idealmente, gostaríamos de replicação baseada em instrução ou linha para outro cluster completamente diferente que seja somente leitura, exceto para o fluxo de replicação que chega. As únicas instruções que não queremos replicadas são aquelas no formato DELETE.......WHERE arquivo = verdadeiro. Dessa forma, podemos manter nosso banco de dados transacional com 3 meses de dados para maior velocidade e nosso banco de dados de réplica pode ter 2,5 anos de dados.

Ainda não encontrei uma maneira de fazer isso, então apreciaria qualquer opção de fazer isso neste momento. Naturalmente, existem exclusões transacionais e queremos que elas sejam replicadas.

Existe uma maneira de configurar isso?

Estive pesquisando e conversando no Google para obter informações.

Ah, minha única outra opção que pensei é replicar primeiro e depois na réplica, adicionar gatilhos a todas as tabelas para depois copiar os dados para outra tabela, mas no gatilho filtrar todas as linhas para quando uma exclusão acontecer, então se a exclusão acontecer, acionamos e verificamos se archive=true|false. Se for falso, replicamos e se for verdadeiro, ignoramos e os dados são agora arquivados. Infelizmente, isso resulta em um tamanho de conjunto de dados bastante grande em nossa réplica, mas é uma opção que tenho em mente. Eu esperava filtrar antes que atingisse a réplica.

O problema aberto no gatilho é se eu realmente recebo os dados da linha para o valor.

Aqui está o SQL Fiddle para minha pergunta: https://sqlfiddle.com/sql-server/online-compiler?id=ab1634d7-fec7-4918-ac1c-3f4fcac8dc92

Eu tenho os seguintes dados de amostra:

DROP TABLE IF EXISTS #Price
CREATE TABLE #Price (DataId INT IDENTITY(1,1), NameOfWidget VARCHAR(50), Price MONEY, PriceScheduleId INT, 
                     StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO #Price (NameOfWidget, Price, PriceScheduleId, StartEffectiveWhen, EndEffectiveWhen)
VALUES
    ('CoolWidget', 3.51, 1, '2015-1-1', '2021-12-31'),
    ('CoolWidget', 2.00, 2, '2017-1-1', '2022-12-31'),
    ('CoolWidget', 4.23, 1, '2021-1-1', '2100-12-31'),
    ('CoolWidget', 2.00, 2, '2021-1-1', '2100-12-31'),
    ('OtherWidget', 13.24, 1, '2014-1-1', '2100-12-31')

Agora preciso colocar esses dados no seguinte formato:

NameOfWidget    StartEffectiveWhen  EndEffectiveWhen
CoolWidget      2015-01-01          2016-12-31
CoolWidget      2017-01-01          2021-12-31
CoolWidget      2021-01-01          2022-12-31
CoolWidget      2023-01-01          2100-12-31
OtherWidget     2015-01-01          2100-12-31

Isso segue a seguinte lógica, agrupado por NameOfWidget:

1. Encontra o mais baixo StartEffectiveWhen.
2. Encontra o próximo valor mais baixo StartEffectiveWhenou EndEffectiveWhen. Essa data se torna a próxima EndEffectiveWhen. Mas se fosse um EndEffectiveWhen, então subtraímos um dia dele.
3. Em seguida, repete as etapas acima, exceto que exclui os dados já utilizados.

O objetivo é ter uma linha para cada "janela" do período de tempo.

O código abaixo faz o que preciso, mas usa um loop para fazer isso.

Como sempre, meus dados reais são muito mais complexos. Ele também possui 56 milhões de linhas. (O código abaixo leva 3 horas para ser executado em meus dados reais)

Estou esperando uma maneira de fazer o que tenho abaixo sem precisar fazer um loop.

Meu código (lento, baseado em loop)

DROP TABLE IF EXISTS #EffectiveRange
CREATE TABLE #EffectiveRange (EffectiveDateId INT IDENTITY(1,1), StartEffectiveWhen DATE, EndEffectiveWhen DATE, EndWhenOfRowsThatMatchStartDate DATE, SecondStartWhen DATE, NameOfWidget VARCHAR(50), CalculationRound INT)

DECLARE @CalculationRound INT = 1

-- This is < 15 in my real code
WHILE (@CalculationRound < 5)
BEGIN

    -- Find the first/next range in source price table.
    INSERT INTO  #EffectiveRange(StartEffectiveWhen, EndWhenOfRowsThatMatchStartDate, SecondStartWhen, NameOfWidget, CalculationRound)
    SELECT  MIN(price.StartEffectiveWhen) StartWhen, NULL, NULL, price.NameOfWidget, @CalculationRound
    FROM    #Price price            
    WHERE   price.StartEffectiveWhen > 
            (SELECT MAX(maxValue.StartWhen) 
             FROM 
                (SELECT MAX(rangesSub.StartEffectiveWhen) AS StartWhen
                 FROM   #EffectiveRange AS rangesSub
                 WHERE  rangesSub.NameOfWidget = price.NameOfWidget
                 UNION ALL
                 SELECT CAST('1/1/1900' AS DATE) AS StartWhen) AS maxValue)
    GROUP BY price.NameOfWidget 

    -- Find the end date for the rows that match the start date we just found.
    UPDATE  #EffectiveRange SET
       EndWhenOfRowsThatMatchStartDate = calc.EndWhenOfRowsThatMatchStartDateCalc
    FROM 
        (
            SELECT  MIN(price.EndEffectiveWhen) AS EndWhenOfRowsThatMatchStartDateCalc, price.NameOfWidget
            FROM    #Price price
                    JOIN #EffectiveRange ranges
                        ON ranges.NameOfWidget = price.NameOfWidget
                        AND ranges.CalculationRound = @CalculationRound
            WHERE   price.StartEffectiveWhen = ranges.StartEffectiveWhen
            GROUP BY price.NameOfWidget
        ) AS calc
        JOIN #EffectiveRange ranges
            ON ranges.NameOfWidget = calc.NameOfWidget
            AND ranges.CalculationRound = @CalculationRound

    -- Find the next largest start date for the calculation round.        
    UPDATE  #EffectiveRange SET
        SecondStartWhen = calc.SecondStartWhen
    FROM 
        (
            SELECT  MIN(price.StartEffectiveWhen) SecondStartWhen, price.NameOfWidget
            FROM    #Price price
                    JOIN #EffectiveRange ranges
                        ON ranges.NameOfWidget = price.NameOfWidget
                        AND ranges.CalculationRound = @CalculationRound
            WHERE   price.StartEffectiveWhen > ranges.StartEffectiveWhen
            GROUP BY price.NameOfWidget
        ) AS calc
        JOIN #EffectiveRange ranges
            ON ranges.NameOfWidget = calc.NameOfWidget
            AND ranges.CalculationRound = @CalculationRound

    -- Send the EndWhen to be the lesser of EndWhenOfRowsThatMatchStartDate and secondStartDate.  
    -- This will define our window of effectiveness for this round of the test. (once we have all of the windows (aka each time a change was made),
    -- we will caclulate the price for each window.
    UPDATE #EffectiveRange SET
        EndEffectiveWhen = IIF((EndWhenOfRowsThatMatchStartDate < SecondStartWhen) OR SecondStartWhen IS NULL, EndWhenOfRowsThatMatchStartDate, DATEADD(DAY, -1, SecondStartWhen))
    WHERE   CalculationRound = @CalculationRound

    SET @CalculationRound = @CalculationRound + 1
END

-- Show the final result
SELECT  ranges.NameOfWidget, ranges.StartEffectiveWhen, ranges.EndEffectiveWhen
FROM    #EffectiveRange ranges
ORDER BY ranges.NameOfWidget, ranges.StartEffectiveWhen


DROP TABLE IF EXISTS #EffectiveRange
DROP TABLE IF EXISTS #Price

Atualizar

Este SQL Fiddle mostra o que acabei fazendo:

https://sqlfiddle.com/sql-server/online-compiler?id=b0d81632-b14d-4374-a80e-0835750f48bc

@Akina me fez pensar sobre meu problema na direção certa. (Obrigado @Akina!)

Por precaução, aqui está a consulta que acabei usando:

DROP TABLE IF EXISTS #Price
CREATE TABLE #Price (DataId INT IDENTITY(1,1), NameOfWidget VARCHAR(50), Price MONEY, PriceScheduleId INT, StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO #Price (NameOfWidget, Price, PriceScheduleId, StartEffectiveWhen, EndEffectiveWhen)
VALUES
    ('CoolWidget', 3.51, 1, '2015-1-1', '2021-12-31'),
    ('CoolWidget', 2.00, 2, '2017-1-1', '2022-12-31'),
    ('CoolWidget', 4.23, 1, '2021-1-1', '2100-12-31'),
    ('CoolWidget', 2.00, 2, '2021-1-1', '2100-12-31'),
    ('OtherWidget', 13.24, 1, '2014-1-1', '2018-5-4'),
    ('OtherWidget', 13.24, 1, '2018-5-6', '2019-12-31'),
    ('OtherWidget', 13.24, 1, '2020-1-1', '2100-12-31')

;WITH OrderedDates AS 
(
    SELECT  priceStart.NameOfWidget, priceStart.StartEffectiveWhen AS DateWhen, 1 AS IsStartDate, 0 AS IsEndDate
    FROM    #Price priceStart

    UNION 

    SELECT  priceStart.NameOfWidget, priceStart.EndEffectiveWhen AS DateWhen, 0 AS IsStartDate, 1 AS IsEndDate
    FROM    #Price priceStart
    
)
SELECT  OrderedDates.NameOfWidget,
        CASE 
            WHEN LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen) IS NULL THEN '1900-1-1'
            WHEN LAG(OrderedDates.IsStartDate ) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen) = 1 
                THEN  LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen)
            ELSE DATEADD(DAY, 1, LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen))
        END AS StartEffectiveWhen, 
        
        CASE
            WHEN OrderedDates.IsEndDate = 1 THEN OrderedDates.DateWhen
            ELSE DATEADD(DAY, -1, OrderedDates.DateWhen)
        END AS EndEffectiveWhen
FROM    OrderedDates
ORDER BY OrderedDates.NameOfWidget

Quero poder obter dados da tabela que possui a chave estrangeira como chave primária, usando apenas os dados da WHEREcláusula da outra tabela.

Eu tentei uma junção interna e estou obtendo resultados estranhos. Estou usando https://sqliteonline.com para testar isso.

Eu tenho duas tabelas, uma chamada carsque tem nome e ID. Aquele que é chamado logged_datae possui um ID, faixa e ID do carro que é uma chave estrangeira usando car.id.

Quero obter todos os nomes de carros (distintos) que possuem dados registrados em uma pista específica.

Estas são as declarações que tentei:

CREATE TABLE cars (id INTEGER PRIMARY KEY NOT NULL, name TEXT UNIQUE NOT NULL DEFAULT 'Car1');
INSERT INTO cars DEFAULT VALUES;
INSERT OR IGNORE INTO cars (name) VALUES ("BMW");
INSERT OR IGNORE INTO cars (name) VALUES ("test");
CREATE TABLE logged_data (id INTEGER PRIMARY KEY NOT NULL, track TEXT NOT NULL, car_id INTEGER NOT NULL, FOREIGN KEY(car_id) REFERENCES cars(id) ON DELETE CASCADE);
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "Car1"));
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "Car1"));
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "BMW"));
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "BMW"));
INSERT INTO logged_data (track, car_id) VALUES ("track1", (SELECT id FROM cars WHERE name = "test"));
INSERT INTO logged_data (track, car_id) VALUES ("track1", (SELECT id FROM cars WHERE name = "test"));
SELECT DISTINCT name FROM cars INNER JOIN logged_data ON logged_data.car_id = cars.id WHERE track = "track";

Isso retorna:

Car1
BMW
test

testnão deve ser retornado porque não possui dados registrados em track.

Tentei selecionar tudo para ver como são as tabelas unidas:

SELECT * FROM cars INNER JOIN logged_data ON logged_data.car_id = cars.id WHERE track = "track";

Isso apenas retorna todos os dados de todas as faixas, mesmo que eu tenha solicitado apenas arquivos track.

O que é estranho, porém, é que se eu obtiver nomes de carros distintos com dados registrados em track1. Ele retorna corretamente apenas test. Esta é a abordagem correta para o que eu quero? Por que estou vendo esse comportamento diferente?

SELECT DISTINCT name FROM cars INNER JOIN logged_data ON logged_data.car_id = cars.id WHERE track = "track1";

Isso retorna:

test

Digamos que eu tenha uma tabela chamada Customercom uma chave primária e colunas de dados como Namee DateOfBirth. Quero escrever um SP chamado que UpdCustomerByKeyreceba parâmetros CustomerKeye Name.DateOfBirth

O CustomerKeyparâmetro seria obrigatório, mas os parâmetros de dados seriam opcionais. Se não forem fornecidos, a coluna deverá permanecer inalterada.

Geralmente, isso é feito com algo como

CREATE PROCEDURE [UpdCustomer]
(
    @CustomerKey BIGINT,
    @Name NVARCHAR(100) = NULL,
    @DateOfBirth DATETIME = NULL
) AS
BEGIN
    UPDATE Customer SET
        Name = ISNULL(@Name, Name),
        DateOfBirth = ISNULL(@DateOfBirth, DateOfBirth)
    WHERE CustomerKey = @CustomerKey
END

Por aqui

UpdCustomer @CustomerKey=123, @DateOfBirth='2000-05-12'

será atualizado DateOfBirth, deixando Nameinalterado.

O problema com esse design é que ele não permite que o chamador defina colunas de dados como NULL. Se eu tentasse definir DateOfBirthassim NULL:

UpdCustomer @CustomerKey=123, @DateOfBirth=NULL

em vez de definir DateOfBirthcomo NULL, deixaria essa coluna inalterada.

Como mantenho esse design básico, mas permito definir colunas NULL?

Estou lutando para normalizar um banco de dados onde há vários endereços pr. do utilizador.

A situação é que um usuário pode ter um endereço normal e um endereço de entrega. Quando um usuário faz um pedido, um dos endereços deve estar conectado ao pedido.

Estou tentando obter uma normalização 3NF. Então, armazenarei os endereços em uma tabela de junção. Devo adicionar uma chave primária à tabela user_addresses e usá-la na coluna fk_address_id na tabela de pedidos? ou isso seria quebrar o 3NF?

O que estou basicamente tentando fazer é possibilitar a realização de um pedido com um endereço diferente.

Quero ter dois bancos de dados em servidores separados. Vamos chamar os bancos de dados de Fonte e Destino. Target é a cópia do servidor de relatórios de Source. Exceto pelas alterações causadas por um procedimento armazenado que preciso executar regularmente no Target, quero que Source e Target sejam idênticos e sincronizados aproximadamente uma vez por dia. Todos os dados necessários para criar o Source vêm do mesmo servidor do Source.

O envio de logs é um método adequado para resolver esse problema? Ou minha restrição "Preciso executar um procedimento armazenado que altere o destino do banco de dados" exclui isso completamente?

Preciso desenvolver um sistema de produção de servidor SQL do Azure capaz de armazenar recomendações de itens do usuário com base no histórico de compras do usuário, perfil de saúde do usuário e recomendações de itens (cerca de 415 MB). Conheço a produção aproximada em 13 meses, o tempo que os dados devem ser armazenados e calculei uma necessidade de cerca de 30 TB de armazenamento.

Tenho alguma experiência em trabalhar com bancos de dados, mas lidar com essa quantidade de dados é uma novidade para mim.

Minha abordagem inicial seria armazenar esses dados em vários bancos de dados usando sharding, mas não tenho certeza sobre como lidar com a parte de design do aplicativo, onde o aplicativo precisa estar ciente da estratégia de sharding e saber a qual banco de dados (shard) se conectar, por uma determinada operação (em nosso aplicativo, cada usuário é um guia). Também não tenho certeza da complexidade do gerenciamento de vários bancos de dados, do gerenciamento de transações que abrangem vários bancos de dados. Como será a estrutura de custos considerando que no Azure eu pago por banco de dados.

A comunidade teria alguma opinião sobre o meu problema?

Eu tenho uma tabela como abaixo no postgres:

create table posts (
    id bigserial,
    tags text[],
    content text,
    content_embedding vector(512)
);

create index on posts using GIN(tags);
-- from pgvector
create index ON posts USING hnsw(content_embedding vector_cosine_ops) WITH (m = 24, ef_construction = 100);

Cada linha é basicamente uma postagem em um blog com contentarmazenamento de seu texto, tags é uma matriz de tags (por exemplo '{"database","coding"}'), content_embeddingé onde armazeno uma representação vetorial contentgerada com algum modelo de IA que espero usar para pesquisa semântica.

Quero executar consultas como abaixo para obter postagens que tagscontenham databaseor hobbye ordená-las de acordo com o quão "semelhantes" elas são a um determinado vetor ( '[...]'abaixo por uma questão de brevidade):

select id, (content_embedding <=> '[...]') as cosine_similarity from posts where tags && '{"database","hobby"}' ORDER BY cosine_distance ASC

No entanto, parece que o plano de consulta explain analyzenão faz uso do índice vetorial, como espero

 Sort  (cost=8081.77..8089.15 rows=2952 width=16) (actual time=10.444..10.445 rows=20 loops=1)
   Sort Key: ((content_embedding <=> '[...]'::vector))
   Sort Method: quicksort  Memory: 26kB
   ->  Bitmap Heap Scan on posts (cost=1698.88..7911.62 rows=2952 width=16) (actual time=9.966..10.424 rows=20 loops=1)
         Recheck Cond: (tags && '{database,hobby}'::text[])
         Heap Blocks: exact=19
         ->  Bitmap Index Scan on posts_tags_idx  (cost=0.00..1698.14 rows=2952 width=0) (actual time=9.842..9.842 rows=20 loops=1)
               Index Cond: (tags && '{database,hobby}'::text[])
 Planning Time: 0.536 ms
 Execution Time: 10.496 ms

Quando removo a wherecláusula, vejo uma varredura de índice sendo usada para classificação

 Index Scan using posts_content_embedding_idx on posts  (cost=164.90..41510.78 rows=301590 width=16)
   Order By: (content_embedding <=> '[...]'::vector)

Eu tenho cerca de 300.000 linhas em posts. Isso é um fator? Existe uma maneira do postgres usar os índices gin e hnsw? Se não for possível, quantas linhas é o limite antes que minha consulta demore muito (> 100 ms)?

Estou ciente de que existem soluções criadas para esse caso de uso de pesquisa, como Elasticsearch ou talvez bancos de dados vetoriais, mas já tenho um banco de dados postgres e espero poder estendê-lo o máximo que puder.