Блог

Я страдаю от проблем с производительностью при вставке миллионов строк в базу данных PostgreSQL.

Я отправляю объект JSON, который содержит массив с миллионом строк.

Для каждой строки я создаю запись в таблице базы данных. Я также пробовал это с несколькими вставками одновременно, но проблема остается.

Я не уверен, как с этим справиться, я читал, что команда КОПИРОВАНИЯ выполняется быстрее всего.

Как я могу улучшить производительность?

Мой объект JSON с журналом в виде массива: журнал массива содержит миллионы строк.

 {"type":"monitoring","log":[
["2016-10-12T20:33:21","0.00","0.00","0.00","0.00","0.0","24.00","1.83","-0.00","1","1","-100.00"],
["2016-10-12T20:33:23","0.00","0.00","0.00","0.00","0.0","24.00","1.52","-0.61","1","1","-100.00"]]}
  

Мой текущий код (я создаю динамический оператор, чтобы я мог выполнять несколько строк одновременно):

 IF(NOT b_first_line) THEN
          s_insert_query_values = right(s_insert_query_values, -1); --remove te leading comma

          EXECUTE format('INSERT INTO log_rlda
                  (record_node_id, log_line, log_value, timestamp, record_log_id)
          VALUES %s;', s_insert_query_values);

          s_insert_query_values = '';
          i_num_lines_buffered = 0;
        END IF;
      END IF;
  

s_insert_query_values содержит:

Каждое значение внутри массива в «log» должно быть вставлено в свою собственную строку (в столбце: log_value). Вот как выглядит ВСТАВКА (ссылка на s_insert_query_values):

 INSERT INTO log_rlda
                  (record_node_id, log_line, log_value, timestamp, record_log_id)
          VALUES  
     (806, 1, 0.00, '2016-10-12 20:33:21', 386),
     (807, 1, 0.00, '2016-10-12 20:33:21', 386),
     (808, 1, 0.00, '2016-10-12 20:33:21', 386),
     (809, 1, 0.00, '2016-10-12 20:33:21', 386),
     (810, 1, 0.0, '2016-10-12 20:33:21', 386),
     (811, 1, 24.00, '2016-10-12 20:33:21', 386),
     (768, 1, 1.83, '2016-10-12 20:33:21', 386),
     (769, 1, 0.00, '2016-10-12 20:33:21', 386),
     (728, 1, 1, '2016-10-12 20:33:21', 386),
     (771, 1, 1, '2016-10-12 20:33:21', 386),
     (729, 1, -100.00, '2016-10-12 20:33:21', 386),
     (806, 2, 0.00, '2016-10-12 20:33:23', 386),
     (807, 2, 0.00, '2016-10-12 20:33:23', 386),
     (808, 2, 0.00, '2016-10-12 20:33:23', 386),
     (809, 2, 0.00, '2016-10-12 20:33:23', 386),
     (810, 2, 0.0, '2016-10-12 20:33:23', 386),
     (811, 2, 24.00, '2016-10-12 20:33:23', 386),
     (768, 2, 1.52, '2016-10-12 20:33:23', 386),
     (769, 2, -0.61, '2016-10-12 20:33:23', 386),
     (728, 2, 1, '2016-10-12 20:33:23', 386),
     (771, 2, 1, '2016-10-12 20:33:23', 386),
     (729, 2, -100.00, '2016-10-12 20:33:23', 386)
  

Решение (i_node_id_list содержит идентификаторы, которые я выбрал перед этим запросом):

 SELECT i_node_id_list[log_value_index] AS record_node_id,
                    e.log_line-1 AS log_line,
                    items.log_value::double precision as log_value,
                    to_timestamp((e.line->>0)::text, 'YYYY-MM-DD HH24:MI:SS') as "timestamp",
                    i_log_id as record_log_id
              FROM (VALUES (log_data::json)) as data (doc),
                json_array_elements(doc->'log') with ordinality as e(line, log_line),
                json_array_elements_text(e.line)     with ordinality as items(log_value, log_value_index)
              WHERE  log_value_index > 1 --dont include timestamp value (shouldnt be written as log_value)
              AND  log_line  > 1
  

Вам нужны два уровня неинвестирования.

 select e.log_line, items.log_value, e.line -> 0 as timestamp
from (
  values ('{"type":"monitoring","log":[
  ["2016-10-12T20:33:21","0.00","0.00","0.00","0.00","0.0","24.00","1.83","-0.00","1","1","-100.00"],
  ["2016-10-12T20:33:23","0.00","0.00","0.00","0.00","0.0","24.00","1.52","-0.61","1","1","-100.00"]]}'::json)
) as data (doc), 
  json_array_elements(doc->'log') with ordinality as e(line, log_line), 
  json_array_elements(e.line)   with ordinality as items(log_value, log_value_index)
where log_value_index > 1;
  

Первый вызов json_array_elements() извлекает все элементы массива из log атрибута. with ordinality Позволяет нам идентифицировать каждую строку в этом массиве. Затем второй вызов получает каждый элемент из строк, снова with ordinality позволяя нам узнать позицию в массиве.

Приведенный выше запрос возвращает это:

 log_line | log_value | timestamp            
--------- ----------- ----------------------
       1 | "0.00"    | "2016-10-12T20:33:21"
       1 | "0.00"    | "2016-10-12T20:33:21"
       1 | "0.00"    | "2016-10-12T20:33:21"
       1 | "0.00"    | "2016-10-12T20:33:21"
       1 | "0.0"     | "2016-10-12T20:33:21"
       1 | "24.00"   | "2016-10-12T20:33:21"
       1 | "1.83"    | "2016-10-12T20:33:21"
       1 | "-0.00"   | "2016-10-12T20:33:21"
       1 | "1"       | "2016-10-12T20:33:21"
       1 | "1"       | "2016-10-12T20:33:21"
       1 | "-100.00" | "2016-10-12T20:33:21"
       2 | "0.00"    | "2016-10-12T20:33:23"
       2 | "0.00"    | "2016-10-12T20:33:23"
       2 | "0.00"    | "2016-10-12T20:33:23"
       2 | "0.00"    | "2016-10-12T20:33:23"
       2 | "0.0"     | "2016-10-12T20:33:23"
       2 | "24.00"   | "2016-10-12T20:33:23"
       2 | "1.52"    | "2016-10-12T20:33:23"
       2 | "-0.61"   | "2016-10-12T20:33:23"
       2 | "1"       | "2016-10-12T20:33:23"
       2 | "1"       | "2016-10-12T20:33:23"
       2 | "-100.00" | "2016-10-12T20:33:23"
  

Результат приведенной выше инструкции затем может быть использован для прямой вставки данных без зацикливания на них. Это должно быть намного быстрее, чем выполнение множества отдельных вставок.

Однако я не уверен, как вы можете интегрировать правильное record_node_id или record_log_id в приведенный выше результат.