davetapley's questions

Ao executar:

TRUNCATE TABLE YYYYY RESTART IDENTITY

Estou vendo esta mensagem de erro:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "XXXXX" references "YYYYY".
HINT:  Truncate table "XXXXX" at the same time, or use TRUNCATE ... CASCADE.

Das duas sugestões em HINT:

1. Usar TRUNCATE ... CASCADEfaz sentido e funciona, mas é menos explícito porque é preciso inspecionar YYYYYpara ver onde vai a cascata. Isso me faz querer tentar a outra opção:

2. Truncate table "XXXXX" at the same time, mas minha pergunta:

   O que significa truncar uma tabela at the same time?

Eu tentei adicionar um TRUNCATE XXXXX...(e envolvê-los em um BEGIN/ COMMIT), mas isso gera o mesmo erro.

Eu tenho uma tabela com aproximadamente um milhão de linhas.

Ele está sendo usado em produção e executei um UPDATEque cobre aproximadamente 95% dessas linhas.

Depois de cinco horas , cancelei o pedido porque estava demorando muito.

A tabela tem uma coluna de ID de incremento automático, então tentei estender a WHEREcondição da minha consulta para incluir id BETWEEN 1 AND 500.

Esta atualização foi concluída em aproximadamente dois segundos. Em seguida, iterei manualmente idem lotes de 500, por exemplo BETWEEN 500 AND 1000, depois BETWEEN 1000 AND 1500.

Nesse ritmo, serão necessários 2.000 lotes de 500 para atualizar toda a tabela.

A atualização de 2.000 lotes de 2 segundos cada leva pouco mais de uma hora .

Minhas perguntas são:

1. Que razões existem para esta diferença?
2. Não me importo com o isolamento da transação, então existe uma maneira de simular essa 'atualização em lotes', sem precisar fazer o script das 2000 atualizações para serem executadas separadamente.

Tenho uma tabela t, com uma coluna chamada json, do tipo JSON. Dentro do JSON existe uma chave natural :

> SELECT json->'id' AS id FROM t LIMIT 1;
             id              
-----------------------------
 " 63631ff3809de7a17398602f"

Posso criar um UNIQUE INDEXon id, assim:

> CREATE UNIQUE INDEX t_id ON t((json->>'id'));                                                                                                                       

CREATE INDEX

Eu gostaria de adicionar isso como um table_constraint_using_index , mas falha para ambos PRIMARY KEY:

> ALTER TABLE t ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id;                                                                                   
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_pkey
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

e UNIQUE:

> ALTER TABLE t ADD CONSTRAINT t_unique_id UNIQUE USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_unique_id...
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

Devo ser capaz de adicionar tal restrição?

Podemos criar um índice parcial para um campo no Postgres.

Eu estou olhando para usar isso para parar as linhas que estão archivedem um índice em um created_at field:

1. Primeiro criamos o índice:

   test=# CREATE INDEX idx_my_table_created_at ON my_table(created_at) WHERE NOT(archived);
   

2. Inserimos duas linhas, uma que entra idx_my_table_created_ate outra que não:

    id |         created_at         | archived
   ----+----------------------------+----------
     2 | 2012-10-12 17:39:17.28511  | t
     1 | 2012-10-12 17:38:55.531278 | f
   

3. Agora temos UPDATEuma linha que está atualmente no índice, de forma que não deveria estar no índice:

   test=# UPDATE my_table SET archived = true WHERE id = 1;
   

Minha pergunta: id = 1Ainda está em idx_my_table_created_at?

Esta questão segue uma resposta a uma pergunta anterior que fiz sobre como lidar com linhas que não serão consultadas quando forem antigas, portanto, archived.

Estou tendo dificuldade em encontrar explicações 'lay' de como os índices são armazenados em cache no PostgreSQL, então gostaria de uma verificação da realidade em qualquer uma ou todas essas suposições:

1. Índices PostgreSQL, como linhas, vivem no disco, mas podem ser armazenados em cache.
2. Um índice pode estar inteiramente no cache ou não estar.
3. Se é armazenado em cache ou não, depende da frequência com que é usado (conforme definido pelo planejador de consulta).
4. Por esta razão, a maioria dos índices 'sensatos' estarão no cache o tempo todo.
5. Os índices residem no mesmo cache (o buffer cache?) que as linhas e, portanto, o espaço de cache usado por um índice não está disponível para as linhas.

Antes de fazer isso, gostaria de deixar claro que empregar um índice parcial traz duas vantagens:

1. Reduzimos o tamanho do índice no cache, liberando mais espaço para as próprias linhas no cache.
2. Reduzimos o tamanho do B-Tree, resultando em uma resposta de consulta mais rápida.

Temos uma tabela em um banco de dados PostgreSQL que cresce na ordem de milhões de linhas por dia.

Cada linha consiste em:

ID
Foreign user ID
Date and time
Other data

A data e a hora não são estritamente monotônicas com ID, mas são próximas.

Ao consultar esta tabela, estamos interessados ​​apenas em obter linhas para um determinado ID de usuário estrangeiro, com data e hora nas últimas duas semanas. Linhas com mais de duas semanas nunca serão consultadas, mas gostaríamos de mantê-las para fins de arquivamento.

Dado este caso de uso especial:

1. Devemos ter um índice na coluna de data e hora?
2. Existe alguma dica que podemos dar de que a data e a hora estão (quase) aumentando monotonicamente com o ID.
3. Devemos tentar remover linhas com mais de duas semanas da tabela, isso provavelmente melhorará o desempenho?