Início / user-275963

Perennialista's questions

Martin Hope
Perennialista
Asked: 2024-08-18 01:42:09 +0800 CST
  • 6

Estou usando o Postgres 12 e no meu aplicativo tenho uma tabela que estou usando para armazenar eventos específicos que contêm informações sobre coisas que aconteceram fora do sistema e relacionadas a alguns registros no meu BD. A tabela se parece com isso:

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    eventable_type VARCHAR(255) NOT NULL,
    eventable_id BIGINT NOT NULL,
    type VARCHAR(255) NOT NULL,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
);
CREATE INDEX index_events_on_eventable ON events (eventable_type, eventable_id);

Por exemplo: uma reunião foi agendada no Google Agenda. Um evento é criado nesta tabela com os detalhes do que aconteceu e o registro é associado à representação interna da reunião no BD. O dataatributo contém os detalhes do evento que também contêm um id exclusivo como:

INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "created", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "updated", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "deleted", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "created", "GoogleId": "dsfsdf2343"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "updated", "GoogleId": "dsfsdf2343"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "deleted", "GoogleId": "dsfsdf2343"}'::jsonb);

Eu consulto a tabela de eventos assim:

SELECT * FROM events WHERE events.type = 'GoogleCalendarEvent' AND (data->>'GoogleId' = 'abcdef1234') LIMIT 1;

Em termos de cardinalidade de operações, o número de gravações é aproximadamente 3 vezes maior que o número de leituras . Ou seja: escrevemos mais do que lemos. A tabela tem cerca de 3 milhões de linhas, crescendo rapidamente. Cerca de 300 mil linhas são adicionadas à tabela todos os dias.

No momento, armazenamos apenas um outro typeevento na tabela, vamos chamá-lo de GoogleEmailEvent. Filtrar por GoogleCalendarEventretornaria aproximadamente 50% dos registros na tabela. Filtrar por GoogleIdnormalmente retornaria menos de 10 registros, mas realmente precisamos de apenas 1 porque todos eles estão associados ao mesmo "Eventable", como você pode ver nas inserções de exemplo.

Quero melhorar o tempo de execução da consulta, pensei em:

  • adicionando um índice WHERE data->>'GoogleId' IS NOT NULL. Mas estou preocupado em deixar as gravações mais lentas
  • armazenando data->>'GoogleId'em uma tabela separada junto com o id do evento para permitir uma recuperação rápida. Quão eficaz isso seria? Isso também tornaria as gravações um pouco mais lentas.
  • indexando created_ate usando isso na consulta para restringir os registros na consulta de alguma forma

Detalhe importante: A grande maioria das vezes (99% das vezes ou mais) o evento correspondente é aquele que foi inserido na tabela recentemente (digamos, em 10 minutos). Posso aproveitar esses detalhes para acelerar a consulta? Adicionar resolveria ORDER BY Id DESC LIMIT 1o problema?

postgresql