All perguntas(dba)

Sou novo no PostgreSQL e um pouco novo em bancos de dados em geral. Existe uma maneira estabelecida de como devemos indexar valores UUID no Postgres? Estou dividido entre usar hash e usar um trie, a menos que já haja algo embutido que ele use automaticamente. O que quer que eu use vai lidar com grandes quantidades de dados.

A família de operadores SP-GiST "text_ops" indexa usando um trie. Como os UUIDs são bastante longos e muito diferentes, eles parecem atraentes, embora eu só faça pesquisas de correspondência completa.

Há também uma opção de hash. Hashing é O(1), e não precisarei fazer nenhuma comparação além da igualdade, é claro, mas como os UUIDs são bastante longos, temo que gerar hashes a partir deles desperdiçaria muito tempo.

Ou isso é algo que depende muito das especificidades do sistema e do uso?

Eu prefiro usar bigserial na maioria dos casos, mas me disseram para usar uuid para isso. Precisamos de uuid porque podemos ter vários servidores usando bancos de dados diferentes, portanto, não há garantia de que teremos bigints exclusivos. Poderíamos usar uma sequência (e semente) diferente para cada servidor, mas ainda não é tão flexível quanto os UUIDs. Por exemplo, não poderíamos migrar entradas de banco de dados de um servidor para outro sem converter os IDs e suas referências em todos os lugares.

É possível atualizar uma visão materializada de forma incremental no PostgreSQL, ou seja, apenas para os dados que são novos ou alterados?

Considere esta tabela e visualização materializada:

CREATE TABLE graph (
   xaxis integer NOT NULL,
   value integer NOT NULL,
);

CREATE MATERIALIZED VIEW graph_avg AS 
SELECT xaxis, AVG(value)
FROM graph
GROUP BY xaxis

Periodicamente, novos valores são adicionados graphou um valor existente é atualizado. Desejo atualizar a exibição a graph_avgcada duas horas apenas para os valores que foram atualizados. No entanto, no PostgreSQL 9.3, toda a tabela é atualizada. Isso é bastante demorado. A próxima versão 9.4 permite CONCURRENTatualização, mas ainda atualiza toda a visualização. Com centenas de milhões de linhas, isso leva alguns minutos.

Qual é uma boa maneira de acompanhar os valores atualizados e novos e apenas atualizar a exibição parcialmente?

Usando PG 9.1 no Ubuntu 12.04.

Atualmente, leva até 24h para executarmos um grande conjunto de instruções UPDATE em um banco de dados, que são da forma:

UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid

(Estamos apenas sobrescrevendo campos de objetos identificados por ID.) Os valores vêm de uma fonte de dados externa (ainda não no banco de dados em uma tabela).

As tabelas têm um punhado de índices cada e nenhuma restrição de chave estrangeira. Nenhum COMMIT é feito até o final.

Demora 2h para importar um pg_dumpbanco de dados inteiro. Esta parece ser uma linha de base que devemos razoavelmente visar.

Além de produzir um programa personalizado que de alguma forma reconstrói um conjunto de dados para o PostgreSQL reimportar, há algo que possamos fazer para aproximar o desempenho do UPDATE em massa ao da importação? (Esta é uma área que acreditamos que as árvores de mesclagem estruturadas em log lidam bem, mas estamos nos perguntando se há algo que possamos fazer no PostgreSQL.)

Algumas ideias:

• descartando todos os índices não-ID e reconstruindo depois?
• aumentando checkpoint_segments, mas isso realmente ajuda a sustentar a taxa de transferência de longo prazo?
• usando as técnicas mencionadas aqui ? (Carregar novos dados como tabela e, em seguida, "mesclar" dados antigos onde o ID não é encontrado em novos dados)

Basicamente, há um monte de coisas para tentar e não temos certeza de quais são as mais eficazes ou se estamos negligenciando outras coisas. Passaremos os próximos dias experimentando, mas pensamos em perguntar aqui também.

Eu tenho carga simultânea na tabela, mas é somente leitura.

Eu tenho lido alguns ótimos artigos sobre o cache do plano do SQL Server por Kimberly Tripp, como este: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

Por que existe até mesmo uma opção para "otimizar para cargas de trabalho ad hoc"? Isso não deveria estar sempre ligado? Independentemente de os desenvolvedores estarem usando SQL ad-hoc ou não, por que você não teria essa opção habilitada em todas as instâncias que a suportam (SQL 2008+), reduzindo assim o inchaço do cache?

Esta pode ser uma pergunta boba, e minha suspeita é que não posso fazer isso, mas existe uma construção no SQL que me permitiria fazer algo como o seguinte:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

Eu quero selecionar dados onde duas colunas estão em um conjunto de pares.

Eu gostaria de evitar o uso de uma subconsulta, se possível.

Eu quero ter uma exportação do meu diagrama de banco de dados para PDF ou tipos de imagem. Como posso fazer isso?
Trabalhei com SQL Server 2008 R2.

De acordo com o MSDN, Getdate(), GetUtcDate() e CURRENT_TIMESTAMP todos retornam DATETIME. Fiz um pequeno teste, que confirma que:

CREATE TABLE #t(T DATETIME2(7));
GO

DECLARE @i INT ;
SET @i=1;

WHILE @i<10000 BEGIN ;
INSERT #t VALUES(CURRENT_TIMESTAMP) ;
SET @i=@i+1;
END ;

SELECT DISTINCT t 
FROM #t 
ORDER BY t ;

---

2013-01-28 13:23:19.4930000
2013-01-28 13:23:19.4970000
2013-01-28 13:23:19.5000000
2013-01-28 13:23:19.5030000
2013-01-28 13:23:19.5070000
2013-01-28 13:23:19.5100000
2013-01-28 13:23:19.5130000

(recorte)

Existe uma função semelhante que retorna DATETIME2(7)?

Precisamos gravar os resultados da consulta SELECT em um arquivo csv. Como isso pode ser feito usando o T-SQL no SQL Server 2008 r2? Eu sei que isso pode ser feito no SSIS, mas por alguns motivos, não temos essa opção.

Tentei usar o proc sugerido no artigo abaixo, mas quando executo o proc, o SQL reclama que não consegue executar sys.sp_OACreate e sys.sp_OADestroy que são chamados neste proc.

Você sabe como podemos ativar esses componentes ou conhece alguma maneira melhor de gravar em um arquivo usando T-SQL?

Desde já, obrigado.

Ao comparar o tempo de execução de duas consultas diferentes, é importante limpar o cache para garantir que a execução da primeira consulta não altere o desempenho da segunda.

Em uma pesquisa do Google, encontrei estes comandos:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Na verdade, minhas consultas estão demorando mais para serem concluídas após várias execuções do que antes. No entanto, não tenho certeza se esta é a técnica recomendada.

Qual é a melhor prática?

Tenho uma coluna "criada" com tipo timestamp without time zone default now()em um banco de dados PostgreSQL.

Se eu selecionar colums, ele terá um formato agradável e legível por padrão:

SELECT created FROM mytable;

         created
---------------------------
2011-05-17 10:40:28.876944

Mas eu gostaria de obter o timestamp em apenas milissegundos (como um Long). Algo assim:

SELECT meuformato(criado) FROM minhatabela;

     created
-----------------
2432432343876944

Como posso obter a coluna timestamp em apenas milissegundos do PostgreSQL?

Resposta a Jaque:

Eu recebo a mesma diferença que você (-3600), mas se eu usar timestamp with time zoneposso ver que o "erro" ou diferença é porque '1970-01-01' obtém fuso horário +01.

create table my_table_2(created timestamp with time zone);
CREATE TABLE
insert into my_table_2 (created) values (now()), ('1970-01-01');
INSERT 0 2
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
(2 rows)

A diferença é um bug? Eu posso ser por causa do "horário de verão" no momento?

Também interessante ao usar to_timestamp()para inserir timestamp 0 e 1.

insert into my_table_2 (created) values (to_timestamp(0));
INSERT 0 1

insert into my_table_2 (created) values (to_timestamp(1));
INSERT 0 1
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
 1970-01-01 01:00:00+01        |                0
 1970-01-01 01:00:01+01        |                1