François Beausoleil's questions

Estou escrevendo um gatilho de validação. O gatilho deve validar se a soma de uma matriz é igual a outro campo. Como tenho muitas instâncias dessa validação, quero escrever um único procedimento e criar vários gatilhos, cada um com um conjunto diferente de campos a serem verificados.

Por exemplo, tenho o seguinte esquema:

CREATE TABLE daily_reports(
     start_on date
   , show_id uuid
   , primary key(start_on, show_id)

     -- _graph are hourly values, while _count is total for the report
   , impressions_count bigint not null
   , impressions_graph bigint[] not null

   -- interactions_count, interactions_graph
   -- twitter_interactions_count, twitter_interactions_graph
);

A validação deve confirmar isso impressions_count = sum(impressions_graph).

Estou preso porque não sei como acessar dinamicamente um campo NEWde dentro do plpgsql:

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
  total bigint;
  array_sum bigint;
BEGIN
  -- TG_NARGS = 2
  -- TG_ARGV[0] = 'impressions_count'
  -- TG_ARGV[1] = 'impressions_graph'

  -- How to access impressions_count and impressions_graph from NEW?
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
  validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

Tentei Executar Comandos Dinâmicos fazendo EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0], mas PL/PGsql reclama que NEW é uma relação desconhecida.

Estou visando especificamente o PostgreSQL 9.1.

Existe alguma vantagem em usar um em detrimento do outro? Estou apenas procurando opções para ocultar o endereço IP real dos servidores mestre e escravo de meus processos e não ter que reiniciá-los se o mestre falhar.

Se o Nagios detectar um mestre com falha, ainda quero me envolver para fazer o failover, inclusive informando ao HAproxy / PgBouncer sobre o novo mestre.

Estou usando o PostgreSQL 9.1 no Ubuntu. O agendamento VACUUM ANALYZEainda é recomendado, ou o autovacuum é suficiente para atender todas as necessidades?

Se a resposta for "depende", então:

• Eu tenho um banco de dados grande (tamanho de despejo compactado de 30 GiB, diretório de dados de 200 GiB)
• Faço ETL no banco de dados, importando cerca de 3 milhões de linhas por semana
• As tabelas com as alterações mais frequentes são todas herdadas de uma tabela mestre, sem dados na tabela mestre (os dados são particionados por semana)
• Eu crio rollups por hora e, a partir daí, relatórios diários, semanais e mensais

Estou perguntando porque a programação VACUUM ANALYZEestá impactando na minha reportagem. Ele é executado por mais de 5 horas e tive que matá-lo duas vezes esta semana, porque estava afetando as importações regulares do banco de dados. check_postgresnão relata nenhum inchaço significativo no banco de dados, então isso não é realmente um problema.

A partir dos documentos, o autovacuum também deve cuidar do envolvimento do ID da transação. A questão permanece: eu ainda preciso de um VACUUM ANALYZE?

Eu tenho um banco de dados grande (PostgreSQL 9.1) que estou preparando para replicar usando a replicação de streaming. O segundo servidor será usado para relatórios e propósitos de backup. Ao criar relatórios, geralmente é útil criar tabelas "temporárias". Ao usar "temporário", quero dizer tabelas que são consideradas zero, mas que não estão usando CREATE TEMPORARY TABLE.

Posso forçar um usuário a criar novas tabelas apenas com seu esquema separado? Essas alterações de esquema terão algum impacto na replicação de streaming? O servidor mestre só tem alterações no esquema público.

Tenho uma consulta no seguinte formulário:

SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000)

A consulta IN tem de 10 a 25.000 valores. A consulta é executada por minutos de cada vez. Tenho um acúmulo de quase 500.000 consultas como essa para executar.

A coluna twitter_user_id é indexada. Alguma ideia de como eu poderia acelerar isso?

# \d twitter_personas
                                    Table "public.twitter_personas"
      Column      |          Type          |                         Modifiers                          
------------------+------------------------+------------------------------------------------------------
 persona_id       | uuid                   | not null
 twitter_user_id  | bigint                 | 
 screen_name      | character varying(40)  | not null
 avatar_url       | text                   | 
 hashval          | integer                | not null default nextval('personas_hashval_seq'::regclass)
Indexes:
    "twitter_personas_pkey" PRIMARY KEY, btree (persona_id)
    "index_twitter_personas_on_screen_name" UNIQUE, btree (screen_name)
    "index_twitter_personas_on_screen_name_persona_id" btree (screen_name, persona_id)
    "index_twitter_personas_twitter_user_id" btree (twitter_user_id) WHERE twitter_user_id IS NOT NULL

Estou usando o particionamento baseado em tempo "clássico" usando gatilhos. Encontrei a necessidade de um gatilho separado, que é executado na tabela original.

CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE FUNCTION insert_twitter_interactions ...;
CREATE TRIGGER insert_twitter_interactions_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

CREATE OR REPLACE FUNCTION maintain_data_pointers ...;
CREATE TRIGGER maintain_data_pointers_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

Não verifiquei totalmente, mas suspeito que a lógica de particionamento seja executada antes do gatilho de manutenção e, como a linha não termina na tabela pai, o segundo gatilho nunca é acionado.

O que acontece se eu quiser executar um AFTER INSERT OR UPDATEtambém? Como a linha não entra na tabela original, não consigo implementar a lógica posterior.

Preciso calcular o alcance, agrupado por diferentes critérios, e preferiria manter tudo em meu banco de dados. Estou procurando uma implementação de bitset pré-criada para PostgreSQL ou dicas sobre como posso atingir meu objetivo.

O que estou fazendo atualmente é armazenar instâncias Java serializadas de EWAHCompressedBitSet em colunas bytea, mas isso significa que tenho que ir até um processo Java para continuar meus cálculos.

Idealmente, gostaria de poder fazer o seguinte:

-- Unique people reached during a time window
SELECT   service_name, last_updated_at, bitset_or(followers_bs)
FROM     followers
GROUP BY service_name, last_updated_at

Eu usei uma solução comercial (Truviso) que tinha esse recurso e era baseada em PostgreSQL também.

Observe que eu tenho uma tabela que possui os dados em um formato descompactado, mas como os dados são muito grandes (10 milhões de linhas e contando), o JOIN simplesmente leva muito tempo do que desejo dedicar a ele.

Eu tenho uma instrução de inserção SQL grande que atualiza uma tabela de resumo. O processo normalmente é executado a cada hora e leva cerca de 5 a 10 minutos para calcular os únicos durante esse período. Eu "tolamente" escolhi executá-lo por um período de 15 dias e estou me perguntando se há algo que eu possa fazer para obter visibilidade de onde ele está. O processo já está em execução há 9 horas. Gostaria apenas de saber se ainda é a consulta SELECT que está rodando ou se está inserindo dados: preciso saber se devo parar e fazer lotes menores, ou se devo apenas deixar terminar.

Eu sei que o servidor está nisso há 9 horas por causa dessa declaração:

select
    now() - xact_start
  , procpid
  , client_addr
  , client_port
  , current_query
from pg_stat_activity
where xact_start is not null
order by 1 desc

A consulta real é:

INSERT INTO summary_show_unique_personas(period, show_id, persona_id, interactions_count)
  SELECT
      date_trunc('hour', created_at) AS period
    , show_id
    , persona_id
    , COUNT(*)
  FROM
          twitter_interactions
    JOIN  show_bindings        USING(interaction_id)
    JOIN  twitter_personas     USING(screen_name)
  WHERE
        created_at >= '__PERIOD_START_AT__' AND created_at < '__PERIOD_END_AT__'
    AND interaction_created_at >= '__PERIOD_START_AT__' AND interaction_created_at < '__PERIOD_END_AT__'
  GROUP BY
    1, 2, 3;

Já tenho alguns milhões de linhas em meu banco de dados. Eu não sabia sobre o tipo de dados UUID do PostgreSQL quando projetei meu esquema.

Uma das tabelas tem 16 milhões de linhas (cerca de 3,5 milhões a 4 milhões de registros por estilhaço), crescendo cerca de 500 mil registros por dia. Ainda posso me dar ao luxo de desativar o sistema de produção por algumas horas, se necessário. Não terei esse luxo em uma ou duas semanas.

Minha pergunta é, será que vale a pena fazer isso? Estou me perguntando sobre o desempenho do JOIN, o uso do espaço em disco (o despejo gzip'd completo é de 1,25 GiB), coisas dessa natureza.

O esquema da tabela é:

# \d twitter_interactions
                Table "public.twitter_interactions"
         Column          |            Type             | Modifiers 
-------------------------+-----------------------------+-----------
 interaction_id          | character(36)               | not null
 status_text             | character varying(1024)     | not null
 screen_name             | character varying(40)       | not null
 twitter_user_id         | bigint                      | 
 replying_to_screen_name | character varying(40)       | 
 source                  | character varying(240)      | not null
 tweet_id                | bigint                      | not null
 created_at              | timestamp without time zone | not null
Indexes:
    "twitter_interactions_pkey" PRIMARY KEY, btree (interaction_id)
    "twitter_interactions_tweet_id_key" UNIQUE, btree (tweet_id)
    "index_twitter_interactions_on_created_at" btree (created_at)
    "index_twitter_interactions_on_screen_name" btree (screen_name)
Triggers:
    insert_twitter_interactions_trigger BEFORE INSERT ON twitter_interactions FOR EACH ROW EXECUTE PROCEDURE twitter_interactions_insert_trigger()
Number of child tables: 9 (Use \d+ to list them.)