All perguntas(dba)

Vejo que o PostgreSQL 17 introduziu um backup incremental que parece realmente ótimo! Eu estava pensando se ele poderia ser usado para restaurar um backup incremental.

Nosso caso de uso é que temos um único servidor de banco de dados. Atualmente, quando precisamos de tempo de inatividade, paramos nosso aplicativo, fazemos pg_dump no banco de dados primário, restauramos em um servidor secundário e, em seguida, reiniciamos nosso aplicativo apontado para o banco de dados secundário. Quando o tempo de inatividade é feito no servidor de banco de dados primário, fazemos o inverso. Isso agora normalmente leva cerca de 20 minutos entre o dump e a restauração e eu gostaria de minimizar isso ainda mais.

O que eu adoraria poder fazer é deixar o banco de dados rodando, despejar o banco de dados existente e restaurá-lo para o secundário. Então, parar o banco de dados, fazer um backup incremental e restaurá-lo, então trazer o secundário para cima.

Imagino que isso nos pouparia uma quantidade significativa de tempo de inatividade e manteria nossa configuração simples. Poderíamos considerar uma solução mais sofisticada usando replicação, mas isso parece mais complexo do que realmente precisamos.

Tenho uma tabela Postgres com 350M registros. Tenho 3x índices nela:

historical_offers(recovery_date, uprn)
historical_offers(recovery_date, account_id)
historical_offers(recovery_date, individual_id)

Se eu executar uma consulta para uma data mais antiga que 24 horas, ela é rápida. Mas se eu executar para hoje (e às vezes ontem), ela é muito lenta (0,05 ms vs 300 ms).

Minha consulta está em todos os campos 3x e usa os índices 3x, então mistura os resultados de forma agradável e rápida para datas > 24 horas~. Então, não acho que seja um problema com a condição OR nos campos 3x que precisam usar índices 3x. Além disso: se eu modificar a consulta para executar APENAS em 1 campo, terei o mesmo problema.

Teorias atuais:

• há atraso na gravação dos índices (mas pensei que os índices fossem atualizados ao mesmo tempo que a tabela é atualizada)
• o planejamento de consulta está estragando e usando o menor índice (eu li algo que essa é uma prática conhecida do Postgres). Talvez eu precise adicionar dicas para "forçá-lo" a usar os índices corretos?

Resposta lenta (hoje):

EXPLAIN ANALYZE SELECT * FROM historical_offers.historical_offers WHERE (historical_offers.uprn = '1001005' OR historical_offers.account_id = 'SW1006' OR historical_offers.individual_id = '6752da6') AND (historical_offers.recovery_date = '2025-01-02');
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                       |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Index Scan using historical_offers_date_individual_id_idx on historical_offers  (cost=0.57..8.56 rows=1 width=174) (actual time=346.467..346.467 rows=0 loops=1) |
|   Index Cond: (recovery_date = '2025-01-02'::date)                                                                                                               |
|   Filter: (((uprn)::text = '1001005'::text) OR ((account_id)::text = 'SW1006'::text) OR ((individual_id)::text = '6752da6'::text))     |
|   Rows Removed by Filter: 1470748                                                                                                                                |
| Planning Time: 0.099 ms                                                                                                                                          |
| Execution Time: 346.488 ms                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN 6
Time: 0.383s

Consulta rápida (data 2 dias atrás):

EXPLAIN ANALYZE SELECT * FROM historical_offers.historical_offers WHERE (historical_offers.uprn = '1001005' OR historical_offers.account_id = 'SW1006' OR historical_offers.individual_id = '6752da6') AND (historical_offers.recovery_date = '2025-01-01');
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                          |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Bitmap Heap Scan on historical_offers  (cost=13.88..78.14 rows=16 width=174) (actual time=0.031..0.032 rows=0 loops=1)                                                                                                                                                                              |
|   Recheck Cond: (((recovery_date = '2025-01-01'::date) AND ((uprn)::text = '1001005'::text)) OR ((recovery_date = '2025-01-01'::date) AND ((account_id)::text = 'SW1006'::text)) OR ((recovery_date = '2025-01-01'::date) AND ((individual_id)::text = '6752da6'::text))) |
|   ->  BitmapOr  (cost=13.88..13.88 rows=16 width=0) (actual time=0.030..0.030 rows=0 loops=1)                                                                                                                                                                                                       |
|         ->  Bitmap Index Scan on historical_offers_date_uprn_idx  (cost=0.00..4.62 rows=5 width=0) (actual time=0.013..0.013 rows=0 loops=1)                                                                                                                                                        |
|               Index Cond: ((recovery_date = '2025-01-01'::date) AND ((uprn)::text = '1001005'::text))                                                                                                                                                                                          |
|         ->  Bitmap Index Scan on historical_offers_date_account_id_idx  (cost=0.00..4.62 rows=5 width=0) (actual time=0.008..0.008 rows=0 loops=1)                                                                                                                                                  |
|               Index Cond: ((recovery_date = '2025-01-01'::date) AND ((account_id)::text = 'SW1006'::text))                                                                                                                                                                                      |
|         ->  Bitmap Index Scan on historical_offers_date_individual_id_idx  (cost=0.00..4.62 rows=5 width=0) (actual time=0.008..0.008 rows=0 loops=1)                                                                                                                                               |
|               Index Cond: ((recovery_date = '2025-01-01'::date) AND ((individual_id)::text = '6752da6'::text))                                                                                                                                                                     |
| Planning Time: 0.113 ms                                                                                                                                                                                                                                                                             |
| Execution Time: 0.054 ms                                                                                                                                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN 11
Time: 0.026s

Estou trabalhando com uma tabela que contém aproximadamente 70 milhões de registros. Preciso criar uma chave primária e vários índices nesta tabela. As consultas SQL que estou usando são as seguintes:

BEGIN;
ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (uniqueid);
CREATE INDEX IF NOT EXISTS table_name_column1_idx ON table_name (column1);
CREATE INDEX IF NOT EXISTS table_name_column2_idx ON table_name (column2);
CREATE INDEX IF NOT EXISTS table_name_column3_idx ON table_name (column3);
CREATE INDEX IF NOT EXISTS table_name_column4_idx ON table_name (column4);
CREATE INDEX IF NOT EXISTS table_name_column5_idx ON table_name (column5);
COMMIT;

No entanto, executar essas consultas leva um tempo considerável. Não tenho nenhuma restrição de recursos, então estou procurando maneiras de otimizar esse processo. Especificamente, estou considerando executar essas consultas SQL em paralelo ou simultaneamente a partir de um aplicativo Python Django.

Minhas perguntas são:

1. Quais estratégias posso usar para minimizar bloqueios e tornar o processo de indexação mais rápido?
2. Existem práticas recomendadas ou ferramentas que podem ajudar a gerenciar a criação de vários índices em uma tabela grande de forma eficiente?
3. Usar BEGIN; COMMIT; durante a criação do índice traz algum benefício de desempenho?

Qualquer conselho ou recomendação será muito apreciado.

Suponha que uma tabela contenha apenas números inteiros e tenha um índice columnstore agrupado.

CREATE TABLE cci_account
(  
    AccountKey int NOT NULL,  
    UnitSold int,
    INDEX ColStore CLUSTERED COLUMNSTORE
);

então parece que somar uma coluna agrupada por outra é sempre rápido em tal tabela, por exemplo

SELECT
    AccountKey,
    SUM(UnitSold)
FROM
    cci_account
GROUP BY
    AccountKey;

Por que isso é tão rápido? Meu entendimento da arquitetura columnstore é que os segmentos que contêm AccountKeynão têm ideia de qual UnitSoldvalor está em suas linhas. Como, então, o SQL Server consegue agrupar tão rapidamente por AccountKey? Não consigo imaginar nenhum algoritmo que consiga isso.

Contexto

Tenho experimentado particionamento de tabelas e encontrei muitas surpresas. Atualmente, estou lendo o máximo de documentação que posso, mas há uma quantidade chocante dela . Acho que estou no segundo de seis white papers. Não vi essa pergunta mencionada em nenhum lugar da documentação.

O Problema

Usando DROP_EXISTING = ON, parece que algumas tabelas podem ser particionadas no local. Por exemplo, posso fazê-lo funcionar com um índice clusterizado exclusivo. No entanto, não posso fazer o mesmo com uma chave primária. Este guia concorda com minha conclusão e diz que particionar uma tabela com uma chave primária no local requer a remoção da chave primária primeiro. É como se houvesse uma restrição de sintaxe em vez de uma de funcionalidade.

Minha pergunta é esta: Quando uma tabela pode ser particionada sem descartar nada antes do particionamento? Em outras palavras, quando o particionamento pode ser feito no local?

Não estou perguntando especificamente sobre o que pode ser feito com DROP_EXISTINGem particular. Esse foi apenas um método que encontrei.

Suponha o SQL Server 2022. Não me importa se a operação é online ou não. Tenho uma pergunta diferente para algo bem parecido.

Código de demonstração

Em grande parte roubado de Paul White

CREATE PARTITION FUNCTION PF (integer) 
AS RANGE RIGHT
FOR VALUES 
(
    10000, 20000, 30000, 40000, 50000
);
GO
CREATE PARTITION SCHEME PS 
AS PARTITION PF 
ALL TO ([PRIMARY]);
GO
CREATE TABLE dbo.T1
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
    
    CONSTRAINT PK_T1
    PRIMARY KEY CLUSTERED (c1, c2, c3)
);

CREATE TABLE dbo.T2
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
    
    INDEX UIX_T2 UNIQUE CLUSTERED (c1, c2, c3)
);
GO

CREATE TABLE dbo.T3
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
    
    INDEX UIX_T3 UNIQUE NONCLUSTERED (c1, c2, c3)
);
GO


/* These work */
CREATE CLUSTERED INDEX UIX_T2 ON dbo.T2 (c1, c2, c3)
WITH (DROP_EXISTING = ON) ON PS(C1) 
GO

CREATE NONCLUSTERED INDEX UIX_T3 ON dbo.T3 (c1, c2, c3)
WITH (DROP_EXISTING = ON) ON PS(C1) 
GO

/* But I could never get the primary key to! */

Acima, você pode particionar índices exclusivos no lugar. No entanto, nada que eu tenha pensado em tentar funciona para chaves primárias. Tenho quase certeza de que isso REBUILDtambém pode fazer isso.

Tenho duas tabelas, para simplificar vamos chamá-las employeesde e employee_comms:

employee:

employee_comms:

Atualmente, comms_typesó pode ser phoneou email.

Escrevi uma consulta para filtrar funcionários por departamento e mostrar e-mail e telefone de cada um:

SELECT
  d.id,
  d.name,
  d.dept,
  p.comms_value as phone,
  e.comms_value as email
FROM
  (
    SELECT *
    FROM employee
    WHERE dept = 'Design'
  ) AS d
  LEFT JOIN (
    SELECT contact_id, comms_value
    FROM employee_comms
    WHERE comms_type = 'phone'
  ) AS p ON (d.id = p.contact_id)
  LEFT JOIN (
    SELECT contact_id, comms_value
    FROM employee_comms
    WHERE comms_type = 'email'
  ) AS e ON (d.id = e.contact_id);

O resultado é uma tabela onde a segunda tabela é transposta com linhas únicas e exclusivas onde nenhum employeevalor é duplicado ( id, name):

Atualmente, ele resolve minhas necessidades, mas continuo pensando que deveria haver uma solução mais eficiente, já que estou consultando a employee_commstabela duas vezes e isso pode se tornar um problema de desempenho quando a employee_commstabela ficar grande (e isso vai acontecer).

P: Existe uma abordagem mais eficiente?

Recentemente encontrei online este artigo incrível sobre backups parciais de Alexandr Omelchenko

-- this is how he would do a backup:

BACKUP DATABASE your_database TO DISK = 'full.bak'
BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_full.bak'
BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_diff.bak' WITH DIFFERENTIAL

-- this is how he would do a restore:

RESTORE DATABASE your_database FROM DISK = 'full.bak' WITH NORECOVERY 
GO 
RESTORE DATABASE your_dataabse FROM DISK = 'partial_backup_full.bak' WITH NORECOVERY  
GO 
RESTORE DATABASE your_database FROM DISK = 'partial_backup_diff.bak' WITH RECOVERY  
GO

Acontece que tenho alguns bancos de dados que têm 4 grupos de arquivos. Um desses grupos de arquivos é carregado com documentos que são salvos dentro de uma tabela naquele grupo de arquivos.

Acho que seria benéfico fazer backup dos grupos de arquivos separadamente.

Quais seriam as possíveis desvantagens em fazer isso? Além da sobrecarga de alterar o restore verifyonly , incluindo dbaTools , para acomodar essa mudança e garantir fazer backups com checksums ?

Algo que eu precisaria verificar é a compressão . Outra coisa é a opção checksum para os backups - como sugerido pelo próprio homem - Ola - aqui e Paul Randal aqui .

Definitivamente precisamos verificar se nossos backups são confiáveis .

Sou muito novo no MongoDB e comecei a aprender e trabalhar com ele agora. Tenho uma pergunta sobre uma consulta. Esta é a consulta:

db.getCollection("user_plates").count(
    {
        "plates": {
            $elemMatch: {
                registerDate: {
                    $gt: new Date("2024-03-20T00:00:00.000Z"),
                    $lt: new Date("2024-03-21T00:00:00.000Z")
                }
            }
        }
    }
);

Como você sabe, se a consulta encontrar pelo menos um elemento no array que satisfaça ambas as condições, ela retornará todos os elementos daquele documento. Agora, preciso ter apenas os elementos do array daquele documento, que o campo "registerDate"satisfaça ambas as condições. Não quero ver outros elementos do array daquele documento. Como devo modificar a consulta acima para conseguir isso?

desde já, obrigado

Estou usando o AGI para gerar Postgres SQL (AWS RDS Postgres), mas quero evitar que os usuários façam perguntas sobre tabelas subjacentes, como "Mostrar todos os bancos de dados", "Mostrar todos os usuários", que resultam em consultas como SELECT * FROM pg_databasee SELECT * FROM pg_roles.

O script de trabalho que tenho para isso é:

Conectar postgrescomo postgres(superusuário):

CREATE ROLE client_creator WITH LOGIN PASSWORD '<generate>' CREATEDB CREATEROLE;

Conecte-se postgrescomo client_creator:

CREATE DATABASE "client_db";
CREATE ROLE "client_reader" WITH LOGIN PASSWORD '<generate>';

Conecte-se client_dbcomoclient_creator

GRANT CONNECT, TEMPORARY ON DATABASE "client_db" TO "client_reader";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "client_reader";
GRANT USAGE ON SCHEMA public TO "client_reader";

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "client_reader"; 

REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM client_reader;
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public;

O problema é que os dois últimos REVOKEcomandos parecem não ter efeito, pois ainda consigo executar SELECT * FROM pg_rolese receber todas as linhas.

Também tentei revogar SELECTtabelas pg_catalog.

A questão é:

• Posso fazer isso?
• Devo fazer isso? Pensamentos...
  • Vi comentários em outras postagens que sugerem que isso é uma má ideia, mas como entendi que cada banco de dados tem uma cópia do pg_catalog, pensei que não seria um problema.
  • Acredito que as consultas geradas não devem depender de objetos pg_catalog, mas posso estar errado

Estou tentando o meu melhor para elaborar minha pergunta da forma mais direta e clara possível. Então, vou começar citando este tópico de resposta dizendo que,

Se você quiser ler e gravar dados FILESTREAM do Windows, clique em Habilitar FILESTREAM para acesso de streaming de E/S de arquivo.

Na minha máquina, configurei com sucesso o FILESTREAM com o SQL SERVER e já processei dados da tabela para a API, mas estou me perguntando o que " Habilitar FILESTREAM para acesso de streaming de E/S de arquivo " pode fazer em termos de otimização de desempenho.

Dadas estas configurações:

A questão principal aqui é: isso introduz diferentes acessos e recuperação de dados entre o ponto de solicitação e o local do arquivo?

O que quero dizer é que, se eu consigo gerenciar os dados do FILESTREAM no SQL Server sem essas configurações mencionadas, essa configuração oferece o mesmo controle sobre os dados que serão responsáveis ​​por acessar quando houver uma solicitação dos dados do arquivo correspondente?

Ainda não vi se é útil ou não, mas acho que tem potencial utilidade no meu caso de uso... No entanto, meu principal objetivo aqui é otimizar o desempenho sem sacrificar o controle granular e a capacidade de gerenciamento entre os dados do arquivo e o destino do aplicativo (API ou algo assim).