raphael's questions

Estamos tentando combinar eventos com linhas com base na distância geográfica mais próxima

--pseudo code
LATERAL JOIN (
    SELECT line_id, geom 
    FROM lines 
    ORDER by distance(lines.geom, events.geom) 
    LIMIT 1) nearest_line

Às vezes, surpreendentemente, há empates. Queremos garantir que, se nosso pipeline de ETL for executado novamente com os mesmos conjuntos de dados, as mesmas linhas sejam retornadas. Uma ideia foi incluir line_idno ORDER BY, mas isso poderia distorcer nossa correspondência para valores mais baixos line_id. Existe alguma maneira de desfazermos os empates aleatoriamente, produzindo a mesma saída se nosso ETL tivesse que ser refeito nos mesmos conjuntos de dados?

Eu estava pensando em algo como, random()mas definir a semente com base em uma combinação de event_ide line_id. Algo como usar mod()nos IDs poderia ser... OK?

Tenho uma tabela de observações de sensores com obs_ts timestamp, sensor_id text, sensor_val int, e para preencher lacunas com dados temos modelos por dia da semana e hora do dia: model_id int, hour_of_day int, model_val int.

Para preencher os valores ausentes, juntaríamos essas duas tabelas por meio de uma tabela de cruzamento que é:sensor_id text, day_of_week int, model_id int

Se nossa tabela de observações for enorme, qual seria uma maneira ideal de indexá-la para unir isodowe hour. A indexação de um carimbo de data/hora também indexa funções como EXTRACT(isodow FROM obs_ts)ou devo tornar esses índices funcionais explícitos, por exemplo CREATE INDEX ON observations (EXTRACT isodow FROM obs_tx). Para ingressar na hora, seria melhor converter o hour_of_daypara um timerange?

Eu tenho uma estrutura de tabela particionada como:

CREATE TABLE measurements (
    sensor_id bigint,
    tx timestamp,
    measurement int
);

CREATE TABLE measurements_201201(
    CHECK (tx >= '2012-01-01 00:00:00'::timestamp without time zone 
       AND tx < ('2012-01-01 00:00:00'::timestamp without time zone + '1 mon'::interval))    
)INHERITS (measurements);
CREATE INDEX ON measurements_201201(sensor_id);
CREATE INDEX ON measurements_201201(tx);
CREATE INDEX ON measurements_201201(sensor_id, tx);
....

E assim por diante. Cada tabela tem aproximadamente 20 milhões de linhas.

Se eu consultar uma amostra de sensores e uma amostra de timestamps na WHEREcláusula, o plano de consulta mostra as tabelas corretas sendo selecionadas e os índices sendo usados, por exemplo:

SELECT *
FROM measurements
INNER JOIN sensors TABLESAMPLE BERNOULLI (0.01) USING (sensor_id)
WHERE tx BETWEEN '2015-01-04 05:00' AND '2015-01-04 06:00' 
    OR tx BETWEEN '2015-02-04 05:00' AND '2015-02-04 06:00' 
    OR tx BETWEEN '2014-03-05 05:00' AND '2014-04-07 06:00' ;

No entanto, se eu usar um CTE, ou colocar os valores de timestamp em uma tabela (não mostrado, mesmo com índices na tabela temporária).

WITH sensor_sample AS(
    SELECT sensor_id, start_ts, end_ts
    FROM sensors TABLESAMPLE BERNOULLI (0.01)
    CROSS JOIN (VALUES (TIMESTAMP '2015-01-04 05:00', TIMESTAMP '2015-01-04 06:00'),
        (TIMESTAMP '2015-02-04 05:00', TIMESTAMP '2015-02-04 06:00'),
        (TIMESTAMP  '2014-03-05 05:00', '2014-04-07 06:00') ) tstamps(start_ts, end_ts)
)

Algo como o abaixo

SET constraint_exclusion = on;
SELECT * FROM measurements
INNER JOIN sensor_sample USING (sensor_id)
WHERE tx BETWEEN start_ts AND end_ts

Executa uma varredura de índice em cada tabela. O que ainda é relativamente rápido, mas com o aumento da complexidade das consultas, isso pode se transformar em varreduras seq que acabarão sendo muito lentas para recuperar cerca de 40 mil linhas de um subconjunto limitado de tabelas particionadas (4-5 de 50).

Estou preocupado que algo assim seja o problema.

Para expressões não triviais, você precisa repetir a condição mais ou menos literal nas consultas para fazer o planejador de consultas do Postgres entender que pode confiar na restrição CHECK. Mesmo que pareça redundante!

Como posso melhorar o particionamento e a estrutura de consulta para reduzir a probabilidade de executar verificações seq em todos os meus dados?

Recebi um .dmparquivo de um banco de dados corporativo, mas atualmente só tenho acesso a uma versão gratuita do Oracle. Em última análise, gostaria de importar os dados para o PostgreSQL. Mas atualmente estou me perguntando se há uma maneira de restaurar a maior tabela (10,91 G) em partes, se necessário, para que eu possa exportar seu conteúdo para csvs para importar para o PostgreSQL. Atualmente, se eu tentar restaurar seletivamente a tabela de 10,91 GB com

impdp system/pw tables=TEST.BIG_TABLE directory=test_data dumpfile=test_data.dmp log=log.log;

eu recebo

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-31694: master table "SYSTEM"."SYS_IMPORT_TABLE_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table "SYSTEM"."SYS_IMPORT_TABLE_
01"
ORA-12953: The request exceeds the maximum allowed database size of 11 GB

O maior Tablespace que consegui criar até agora foi do SIZE 10500M, o comando: CREATE TABLESPACE test DATAFILE 'test.dbf' SIZE 10600M ONLINE;produziu erroORA-12953: The request exceeds the maximum allowed database size of 11 GB

Os resultados de select tablespace_name, sum(bytes) from dba_data_files group by tablespace_namesão:

tablespace | sum(bytes)/1000000
-----------+-------------------
SYSAUX     | 692.06016
UNDOTBS1   | 398.45888
USERS      | 104.8576
SYSTEM     | 524.288
TEST       | 11010.048

Estou pensando em migrar um data warehouse PostgreSQL atual para um host de nuvem com armazenamento SSD e RAM como uma das principais variáveis ​​de dimensionamento. Os dados mais volumosos com os quais estamos lidando no momento residirão em tabelas particionadas mensais. Cada mês tem cerca de 70 GB com índices (40-ish sem). Os dados provavelmente são carregados em massa periodicamente e, em seguida, serão acessados ​​por uma pequena equipe de 5 pesquisadores.

Eu tenho tentado procurar recomendações para especificação de RAM neste site e tudo o que encontrei é:

• Ajustar todo o banco de dados (> 1 TB, irrealista)
• Mais é melhor

Deve haver RAM suficiente para carregar pelo menos um índice inteiro (16 GB) na RAM? Há algum outro detalhe que devo fornecer?

Estou importando um grande número de arquivos grandes em várias tabelas a serem particionadas usando loops em um bloco de código plpgsql anônimo $do$.

$do$
BEGIN
    FOR yyyy in 2012..2016 THEN 
        EXECUTE $$COPY table$$||yyyy||$$ FROM 'E:\data\file$$||yyyy||$$.csv DELIMITER ',' CSV;$$;
    END LOOP;
END;
$do$ LANGUAGE plpgsql

Todo esse processo deve levar cerca de 15 horas e espero que todas as importações não sejam revertidas se houver um erro de importação em algum momento.

O IIRC COMMITnão funciona em funções armazenadas porque toda a função é tratada como uma única transação.

Da documentação para$do$

O bloco de código é tratado como se fosse o corpo de uma função sem parâmetros, retornando void. Ele é analisado e executado uma única vez.

Estou assumindo que isso significa que o todo $do$é uma transação e, portanto, os commits dentro do bloco não funcionarão. Estou correcto?