Milovan Zogovic's questions

Estou lutando para encontrar uma solução (de preferência DBaaS) na qual possa confiar para armazenar e consultar cerca de 300 milhões de linhas de dados (aproximadamente 100 GB).

Os dados em questão são praticamente numéricos. Há também uma coluna de "descrição" na qual eu gostaria de realizar uma pesquisa de texto completo. Existem algumas colunas de "categoria" usadas para filtragem também. Também quero filtrar/ordenar os resultados da pesquisa de várias maneiras (mais de 10 índices diferentes).

Não há necessidade de fazer junções complexas, pois os dados são praticamente desnormalizados. Os dados são fortemente atualizados: cerca de 50 milhões de registros são substituídos todos os dias.

Eu tentei pela primeira vez com o DynamoDB, mas ele pode suportar apenas até 5 índices e não é capaz de fazer pesquisa de texto completo a uma velocidade razoável. Também considerei o BigQuery do Google, mas ele foi projetado para dados "somente anexados". Agora estou considerando o Redshift, mas não tenho certeza de como ele será capaz de lidar com um número tão grande de atualizações diárias.

Qualquer conselho seria apreciado!

Eu tenho uma enorme tabela postgres (10 GB de dados - 160 milhões de registros). A tabela é estática e não há operações de gravação nela executadas. Eu quero duplicá-lo, executar gravações, reindexá-lo e, em seguida, com uma única transação rápida, excluir o antigo e renomear o novo para o nome original.

Qual é a maneira mais rápida de duplicar uma tabela tão grande?

Eu tenho duas tabelas de banco de dados. Um contém centenas de milhões de registros. Vamos chamar aquele history. O outro é calculado diariamente e quero copiar todos os seus registros para historyum.

O que eu fiz foi rodar:

INSERT INTO history SELECT * FROM daily

E funcionou por um tempo, mas começou a ficar cada vez mais lento à medida que o número de discos continuava crescendo. Agora eu tenho cerca de 2 milhões de registros que precisam ser copiados de dailypara historyem uma única operação e demora muito para ser concluído.

Existe outra maneira mais eficiente de copiar dados de uma tabela para outra?

Tenho uma tabela com dois índices:

• last_channel_snapshots_views_idx (view_count DESC NULLS LAST)
• last_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST)

O que eu quero é poder classificar por view_count geral e dentro de uma rede específica. O Postgres usa o índice correto para ambos os casos. No entanto, se eu quiser encontrar o registro com a maioria das visualizações em que network_id é NULL, ele usa o primeiro índice e filtra o , executando assim muito lento:

explain analyze SELECT *
FROM latest_channel_snapshots
WHERE network_id IS NULL
ORDER BY view_count DESC NULLS LAST
LIMIT 5 OFFSET 500000;

                                                                                     QUERY PLAN                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=39599.04..39599.44 rows=5 width=74) (actual time=1926.271..1926.287 rows=5 loops=1)
   ->  Index Scan using latest_channel_snapshots_views_idx on latest_channel_snapshots  (cost=0.00..42001.59 rows=530336 width=74) (actual time=0.060..1899.224 rows=500005 loops=1)
         Filter: (network_id IS NULL)
         Rows Removed by Filter: 305022
 Total runtime: 1926.309 ms
(5 rows)

Eu tenho uma tabela de vídeos com 18 milhões de linhas. Quando procuro um vídeo específico por ID, leva até 6 segundos para ser concluído. Às vezes leva alguns milissegundos, às vezes até 6 segundos, mas em média é de cerca de 2 segundos.

O aplicativo está hospedado no heroku e estou usando o banco de dados Crane ( https://addons.heroku.com/heroku-postgresql ) com 410 MB de RAM.

Existe alguma maneira de acelerar isso? Estou consultando vídeos 50 vezes por segundo em média, e novos vídeos são inseridos/atualizados a uma taxa de 50 por segundo.

explain analyze SELECT * FROM videos WHERE id = 17841464 LIMIT 1;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.892..2337.894 rows=1 loops=1)
   ->  Index Scan using videos_pkey on videos  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.888..2337.888 rows=1 loops=1)
         Index Cond: (id = 17841464)
 Total runtime: 2337.943 ms

Veja como fica a tabela:

 \d+ videos;
                                                           Table "public.videos"
     Column     |            Type             |                      Modifiers                      | Storage  | Stats target | Description 
----------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('videos_id_seq'::regclass) | plain    |              | 
 uuid           | character(11)               | not null                                            | extended |              | 
 channel_id     | integer                     | not null                                            | plain    |              | 
 category_id    | integer                     |                                                     | plain    |              | 
 title          | character varying(255)      |                                                     | extended |              | 
 published_at   | timestamp without time zone |                                                     | plain    |              | 
 view_count     | bigint                      |                                                     | plain    |              | 
 like_count     | integer                     |                                                     | plain    |              | 
 dislike_count  | integer                     |                                                     | plain    |              | 
 favorite_count | integer                     |                                                     | plain    |              | 
 comment_count  | integer                     |                                                     | plain    |              | 
 disabled       | boolean                     | default false                                       | plain    |              | 
 created_at     | timestamp without time zone |                                                     | plain    |              | 
 updated_at     | timestamp without time zone |                                                     | plain    |              | 
Indexes:
    "videos_pkey" PRIMARY KEY, btree (id)
    "videos_uuid_idx" UNIQUE, btree (uuid)
    "videos_latest_by_channel_idx" btree (channel_id, published_at DESC)
    "videos_top_by_channel_idx" btree (channel_id, view_count DESC)
Has OIDs: no

Estou tentando SOMAR 8 milhões de REALvalores de ponto flutuante ( ) com uma consulta simples como esta:

SELECT SUM(metric) FROM metrics;

No entanto, ele retorna um resultado muito impreciso. Deve retornar 137.586,77, mas retorna 137.303 (diferença de 283,77)

Existe uma maneira de forçar uma consulta a ser mais precisa?

Eu tenho várias instruções SQL em execução em alguns dos meus plpgsqlgatilhos. Eu habilitei o log com o log_statement = 'all'. Ele registra a 'instrução de acionamento', mas não as instruções dentro da própria consulta.

Existe uma maneira de exibir as instruções que foram executadas no gatilho no log padrão do postgres?

Eu implementei a estratégia de desnormalização de dados usando RULEs do postgresql. Escolhi regras em vez de gatilhos por motivos de desempenho.

O esquema é estruturado assim:

• O aplicativo tem muitos clientes
• Cliente tem muitos projetos
• O projeto tem muitos usuários

Uma parte do sistema está armazenando hitspara cada usuário na statstabela. Hit é uma métrica imaginária, não é realmente relevante. O sistema pode coletar muitas dessas métricas. Existem muitos registros na tabela de estatísticas (> 1.000.000 por dia).

Quero saber quantos acessos são por usuário, por projeto, por cliente e por aplicativo em determinado dia.

Para fazê-lo funcionar rapidamente, agrupei as estatísticas por dia e armazenei a saída na tabela user_hits. Durante este processo, também o application_id, client_id e project_id foram adicionados (como colunas) e os índices apropriados criados.

Quero otimizar ainda mais o processo agrupando as coisas por project_id, client_id e finalmente application_id. O pipeline de dados é assim:

stats -> user_hits -> project_hits -> client_hits -> application_hits

Quero ter certeza de que, ao excluir os dados de user_hitsum determinado dia, os dados project_hitsdessa mesma data também serão excluídos. Este processo deve se propagar até a última tabela da cadeia.

Eu defini estas regras simples:

CREATE RULE delete_children AS ON DELETE TO user_hits
  DO ALSO
  DELETE FROM project_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO project_hits
  DO ALSO
  DELETE FROM client_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO client_hits
  DO ALSO
  DELETE FROM application_hits WHERE day = OLD.day;

No entanto, quando emito uma declaração como esta:

DELETE FROM user_hits WHERE day = current_date;

Espero que ele execute essas 3 consultas em retorno:

DELETE FROM project_hits WHERE day = current_date;
DELETE FROM client_hits WHERE day = current_date;
DELETE FROM application_hits WHERE day = current_date;

No entanto, isso não acontece.

Ele conclui a operação, mas leva alguns minutos para fazer isso (com dados de teste). Com dados reais, leva horas, enquanto executar essas 3 consultas manualmente leva alguns milissegundos. O tempo gasto parece proporcional ao número de combinações (usuários x projetos x clientes x aplicativos).

Qual é o problema aqui? Estou esquecendo de algo? Isso pode ser implementado com gatilhos de maneira otimizada?

Script de exemplo incluído que reproduz o problema:

https://gist.github.com/assembler/5151102

ATUALIZAÇÃO: A transição de user_hitspara project_hits(e assim por diante) é feita pelo processo de trabalho em segundo plano (uma vez que envolve entrar em contato com serviços de terceiros para obter informações adicionais). É inteligente o suficiente para recalcular tudo para datas perdidas. Portanto, a única coisa que preciso é uma maneira de EXCLUIR registros em cascata de maneira otimizada.

ATUALIZAÇÃO: statsa tabela é preenchida diariamente. O único cenário possível é excluir incondicionalmente os dados do dia inteiro e substituí-los por novos valores.

ATUALIZAÇÃO: notei que o número de linhas afetadas (extraídas da explaininstrução) é exatamente igual ao produto das linhas afetadas em user_hits, project_hits, client_hitse application_hitstabelas (centenas de milhões de linhas).

Acontece que funciona assim:

1. eu corroDELETE FROM user_hits WHERE day = current_date;
2. Para cada linha na user_hitstabela, RULE é acionado, o que exclui TODAS as linhas deproject_hits
3. Para cada linha de project_hits, RULE é acionado, o que exclui TODAS as linhas declient_hits
4. Para cada linha de client_hits, RULE é acionado, o que exclui TODAS as linhas deapplication_hits

Portanto, o número de operações é igual ao produto da contagem das linhas afetadas nessas tabelas.