All perguntas(dba)

As colunas do sistema Postgres estão documentadas no Capítulo 5. Definição de Dados > 5.4. Colunas do sistema .

Essa página menciona que oidos valores “são quantidades de 32 bits”. E essa página diz o mesmo sobre identificadores de transação. Então, vou assumir que significa oid, tableoid, xmin, cmin, xmax, e cmaxsão todos inteiros de 32 bits.

Mas isso deixa a ctidcoluna do sistema.

A localização física da versão da linha em sua tabela. Observe que, embora o ctid possa ser usado para localizar a versão da linha muito rapidamente, o ctid de uma linha mudará se for atualizado ou movido por VACUUM FULL. Portanto ctid é inútil como um identificador de linha de longo prazo. O OID, ou melhor ainda, um número de série definido pelo usuário, deve ser usado para identificar as linhas lógicas.

➡ Qual é o tipo de dados da ctidcoluna?

Especificamente, estou interessado na versão Postgres 10.3, mas se ela mudou em relação às versões anteriores, seria bom saber.

Eu tenho um banco de dados existente em uma instância do RDS AWS PostgreSQL. Eu preciso criar mais um banco de dados nesta instância, pois girar um cluster separado será caro. Não parece haver nenhuma opção para criar mais um banco de dados dentro desta instância. Eu entendo que posso criar um esquema separado para essa finalidade (como no MySQL), mas preciso manter os recursos para eles separados e, portanto, a necessidade de um banco de dados separado.

Por favor, deixe-me saber se é possível. PostgreSQL 9.6.5

Eu li sobre objetos JSON e o tipo de objeto JSON. Eu só quero fazer um select e ele retornar JSON. Não quero necessariamente armazenar um objeto JSON. Serialização em si, não é minha pergunta. As colunas são colunas regulares Varchar, Int, etc., sem objetos JSON, linhas de banco de dados "normais".

Posso fazer um SELECT antigo regular e retornar JSON para MySQL?

Não é isso que FOR JSON no SQL SERVER e rows_for_json fazem no PostgreSQL?

Eles pareciam à frente neste são, mas eu não queria me enganar.

Encontrei esta pergunta de 2016: https://stackoverflow.com/questions/35324795/mysql-5-7-return-row-as-json-using-new-json-features

Dada esta tabela:

CREATE TABLE test (
    id INT NOT NULL,
    description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');

Percebi que não consigo corrigir um problema tipográfico:

SELECT * FROM test WHERE id = 1;
UPDATE test SET description = 'CO₂' WHERE id = 1;
SELECT * FROM test WHERE id = 1;

porque a atualização corresponde, mas não tem efeito:

id          description
----------- -----------
1           CO2

(1 affected rows)

(1 affected rows)

id          description
----------- -----------
1           CO2

(1 affected rows)

É como se o SQL Server determinasse que, como ₂ obviamente é apenas um minúsculo 2 , o valor final não será alterado, portanto não vale a pena alterá-lo.

Alguém poderia lançar alguma luz sobre isso e talvez sugerir uma solução alternativa (além de atualizar para um valor intermediário)?

Eu tenho uma visão, complicated_view-- existem algumas junções e cláusulas where. Agora,

select * from complicated_view (9000 records)

é mais rápido, muito mais rápido , do que

select top 500 * from complicated_view

Estamos falando de 19 segundos contra mais de 5 minutos.

A primeira consulta retorna todos os 9.000 registros. Como só pegar o top 500 é ridiculamente mais longo?

Obviamente, vou examinar os planos de execução aqui ---- mas assim que descobrir por que o SQL Server está executando o "top 500" de maneira abaixo do ideal, como posso dizer a ele para executar o plano de maneira rápida, como tomar a mesa cheia?

Claro, eu posso ter que reescrever a visão inteiramente --- mas muito estranho.

Basicamente, estou conectando esta tabela de dados a um software de terceiros que pré-verifica tabelas com uma select top 500 *consulta padrão que não pode ser modificada. Então, além de despejar essa visão em uma tabela real (bastante desleixada) - também não consigo contornar o adendo "top 500".

Este é o SQL Server 2012.

EDIT: Discordo do sinalizador duplicado. A outra pergunta, o top foi mais rápido que todos. Este seria o comportamento ESPERADO, retornando menos linhas. Meu caso é o contrário. Além disso, meu entendimento é que o Top 100 é um algoritmo diferente do Top 100+. Eu nem acho que a pergunta duplicada tem a resposta correta. Ou seja, a consulta TOP X ordenará tabelas potencialmente massivas muito cedo, não DEPOIS de serem agregadas/filtradas/etc. O porquê é um mistério, mas o como está claramente lá.

Estou tentando aprimorar meu vocabulário para me comunicar melhor com meus colegas desenvolvedores. Temos vários lugares no site onde estamos debatendo se devemos procurar uma string desde o início ou 'running%'em qualquer lugar da string '%running%.

Eu tenho chamado a pesquisa do meio de "fuzzy" , o que percebo que está incorreto, pois fuzzy significa mudar a forma da palavra "run", "runing" [sic], "runed" [sic].

Qual é a terminologia correta para pesquisar no início de uma string e pesquisar no meio de uma string?

Eu tenho uma tabela station_logsem um banco de dados PostgreSQL 9.6:

    Column     |            Type             |    
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 submitted_at  | timestamp without time zone | 
 level_sensor  | double precision            | 
Indexes:
    "station_logs_pkey" PRIMARY KEY, btree (id)
    "uniq_sid_sat" UNIQUE CONSTRAINT, btree (station_id, submitted_at)

Estou tentando obter o último level_sensorvalor com base em submitted_at, para cada station_id. Existem cerca de 400 station_idvalores exclusivos e cerca de 20 mil linhas por dia por station_id.

Antes de criar o índice:

EXPLAIN ANALYZE
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM station_logs ORDER BY station_id, submitted_at DESC;

Exclusivo (custo=4347852.14..4450301.72 linhas=89 largura=20) (tempo real=22202.080..27619.167 linhas=98 loops=1)
   -> Classificar (custo=4347852.14..4399076.93 linhas=20489916 largura=20) (tempo real=22202.077..26540.827 linhas=20489812 loops=1)
         Chave de classificação: station_id, submit_at DESC
         Método de classificação: mesclagem externa Disco: 681040kB
         -> Seq Scan em station_logs (custo=0.00..598895.16 linhas=20489916 largura=20) (tempo real=0.023..3443.587 linhas=20489812 loops=$
 Tempo de planejamento: 0,072 ms
 Tempo de execução: 27690,644 ms

Criando índice:

CREATE INDEX station_id__submitted_at ON station_logs(station_id, submitted_at DESC);

Após criar o índice, para a mesma consulta:

Exclusivo (custo=0,56..2156367,51 linhas=89 largura=20) (tempo real=0,184..16263.413 linhas=98 loops=1)
   -> Varredura de índice usando station_id__submitted_at em station_logs (custo=0.56..2105142.98 linhas=20489812 largura=20) (tempo real=0.181..1$
 Tempo de planejamento: 0,206 ms
 Tempo de execução: 16263,490 ms

Existe uma maneira de tornar essa consulta mais rápida? Como 1 segundo, por exemplo, 16 segundos ainda é muito.

Eu estava trabalhando em uma demonstração envolvendo CCIs quando notei que algumas das minhas inserções estavam demorando mais do que o esperado. Definições de tabela para reproduzir:

DROP TABLE IF EXISTS dbo.STG_1048576;
CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
INSERT INTO dbo.STG_1048576
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

Para os testes estou inserindo todas as 1048576 linhas da tabela de teste. Isso é suficiente para preencher exatamente um rowgroup compactado, desde que ele não seja cortado por algum motivo.

Se eu inserir todos os inteiros mod 17000, leva menos de um segundo:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 17000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

Tempos de execução do SQL Server: tempo de CPU = 359 ms, tempo decorrido = 364 ms.

No entanto, se eu inserir os mesmos inteiros mod 16000, às vezes leva mais de 30 segundos:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

Tempos de execução do SQL Server: tempo de CPU = 32.062 ms, tempo decorrido = 32.511 ms.

Este é um teste repetível que foi feito em várias máquinas. Parece haver um padrão claro no tempo decorrido à medida que o valor do mod muda:

MOD_NUM TIME_IN_MS
1000    2036
2000    3857
3000    5463
4000    6930
5000    8414
6000    10270
7000    12350
8000    13936
9000    17470
10000   19946
11000   21373
12000   24950
13000   28677
14000   31030
15000   34040
16000   37000
17000   563
18000   583
19000   576
20000   584

Se você quiser executar testes, sinta-se à vontade para modificar o código de teste que escrevi aqui .

Não consegui encontrar nada interessante em sys.dm_os_wait_stats para a inserção do mod 16000:

╔════════════════════════════════════╦══════════════╗
║             wait_type              ║ diff_wait_ms ║
╠════════════════════════════════════╬══════════════╣
║ XE_DISPATCHER_WAIT                 ║       164406 ║
║ QDS_PERSIST_TASK_MAIN_LOOP_SLEEP   ║       120002 ║
║ LAZYWRITER_SLEEP                   ║        97718 ║
║ LOGMGR_QUEUE                       ║        97298 ║
║ DIRTY_PAGE_POLL                    ║        97254 ║
║ HADR_FILESTREAM_IOMGR_IOCOMPLETION ║        97111 ║
║ SQLTRACE_INCREMENTAL_FLUSH_SLEEP   ║        96008 ║
║ REQUEST_FOR_DEADLOCK_SEARCH        ║        95001 ║
║ XE_TIMER_EVENT                     ║        94689 ║
║ SLEEP_TASK                         ║        48308 ║
║ BROKER_TO_FLUSH                    ║        48264 ║
║ CHECKPOINT_QUEUE                   ║        35589 ║
║ SOS_SCHEDULER_YIELD                ║           13 ║
╚════════════════════════════════════╩══════════════╝

Por que a inserção de ID % 16000demora muito mais do que a inserção de ID % 17000?

Eu costumo projetar meus bancos de dados seguindo as próximas regras:

• Ninguém além de db_owner e sysadmin tem acesso às tabelas do banco de dados.
• As funções do usuário são controladas na camada do aplicativo. Eu costumo usar uma função de banco de dados para conceder acesso às visualizações, procedimentos armazenados e funções, mas em alguns casos, adiciono uma segunda regra para proteger alguns procedimentos armazenados.
• Eu uso TRIGGERS para validar inicialmente informações críticas.

CREATE TRIGGER <TriggerName>
ON <MyTable>
[BEFORE | AFTER] INSERT
AS
    IF EXISTS (SELECT 1 
               FROM   inserted
               WHERE  Field1 <> <some_initial_value>
               OR     Field2 <> <other_initial_value>)
    BEGIN
        UPDATE MyTable
        SET    Field1 = <some_initial_value>,  
               Field2 = <other_initial_value>  
        ...  
    END

• DML é executado usando procedimentos armazenados:

sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
sp_MyTable_Delete(@Key1, @Key2, ...);
sp_MyTable_Update(@Key1, @Key2, @Field3, ...);

Você acha que, nesse cenário, vale a pena usar DEFAULT CONSTRAINTs, ou estou adicionando um trabalho extra e desnecessário ao servidor DB?

Atualizar

Eu entendo que usando a restrição DEFAULT estou dando mais informações para outra pessoa que deve administrar o banco de dados. Mas estou mais interessado no desempenho.

Presumo que o banco de dados esteja sempre verificando os valores padrão, mesmo que eu forneça o valor correto, portanto, estou fazendo o mesmo trabalho duas vezes.

Por exemplo, existe uma maneira de evitar a restrição DEFAULT em uma execução de gatilho?

Eu tenho um aplicativo da web Django com backend postgresql 9.3.10 (sentado em um sistema operacional Linux). Eu encontrei um erro de disco cheio, de modo que, mesmo se eu tentar truncar uma tabela, recebo erros do tipo:

ERROR:  could not extend file "base/30137/33186048": No space left on device
HINT:  Check free disk space.

Não consigo adicionar facilmente mais espaço em disco ao servidor, nem posso excluir coisas nesta VM. No entanto, existem várias tabelas que são candidatas ao truncamento, mas parece que também não posso truncá-las agora.

Alguém pode me dar uma dica do que posso fazer aqui? Isso está afetando muito meu servidor de produção, e eu sou um DBA acidental aqui, totalmente perplexo.