GER - это механизм рекомендаций, реализованный в Node.js с использованием Knex.js для запроса Postgres. В API GER есть метод set_action_weight (действие, вес), который:

  1. обновить вес действий ИЛИ
  2. вставьте действие с этим весом, если оно не существует.

Для реализации этой функции GER необходимо Обновить запись действия. Учитывая, что upsert недоступен в Postgres, я нашел два возможных обходных пути:

  1. реализовать его с помощью нескольких запросов к Postgres в приложении
  2. объединить запросы в один оператор для отправки в Postgres

Подход с несколькими запросами

Самый простой подход к реализации upsert - это непосредственно в приложении путем многократного вызова Postgres.

Чтобы реализовать это, функция set_action_weight (action, weight) сначала проверяет, существует ли уже действие, используя count:

@knex.select('*').from('actions')
.where(action: action).count()
.then( (count) =>
    ...

Если действие не существует (т. Е. Счетчик равен 0), вставьте запись, в противном случае обновите ее:

if count == 0
  @knex('actions')
  .insert(({action: action, weight: weight})
else
  @knex('actions').where(action: action)
  .update({weight: weight})

Этот код может вызвать состояние гонки, при котором, если два действия добавляются одновременно, они могут попытаться вставить запись. Указанные действия должны быть уникальными в GER, это вызовет ошибку нарушения уникального ключа (код которой 23505), которую необходимо обработать:

.catch( (error) ->
  if error.code != '23505'
    throw error
)

Основные проблемы этого метода:

  1. Хотя код достаточно прост, он требует некрасивой обработки исключений.
  2. Каждый раз, когда вызывается этот метод, он будет вызывать базу данных несколько раз, что может повлиять на производительность.

Подход к единому утверждению

Upsert также можно реализовать в одном операторе Postgres, как описано здесь.

Knex можно использовать для построения строк запроса с помощью функции toString, вывод которой можно объединить в один оператор.

Сначала создается оператор вставки:

insert =
  @knex("actions")
  .insert({action: action, weight: weight})
  .toString()

Чтобы обойти эту ошибку, необходимо заменить значения на select:

insert.replace(/\svalues\s\(/," select ")[..-2]

Затем можно построить запрос на обновление:

update =
  @knex("actions")
  .where(action: action)
  .update({weight: weight})
  .toString()

Затем можно создать единственный оператор для первой блокировки таблицы, что исключает возможность возникновения состояния гонки. Затем можно использовать какой-нибудь причудливый SQL, чтобы сначала попробовать обновить запись, и если столбцы не обновлены, он попытается вставить:

query =
"BEGIN;
 LOCK TABLE actions IN SHARE ROW EXCLUSIVE MODE;
 WITH upsert AS (#{update} RETURNING *)
 #{insert} WHERE NOT EXISTS (SELECT * FROM upsert);
COMMIT;"

Затем Knex может отправить этот оператор с помощью:

@knex.raw(query)

Этот метод с одним оператором для upsert полностью выполняется внутри Postgres и исключает возможность состояния гонки. Однако он добавляет довольно сложный SQL, специфичный для Postgres, который может быть трудно поддерживать.

Сравнение методов

Чтобы правильно сравнить эти методы, необходимо измерить фактический прирост производительности. Это потому, что оптимизация без показателей не является оптимизацией. Итак, я провел с ними этот тест:

ger = new GER()
start_time = new Date().getTime()
promises = []
for x in [1..1000]
 promises.push ger.set_action_weight('buy', 1)
q.all(promises)
.then(->
 end_time = new Date().getTime()
 time = end_time - start_time
 per_time = time/1000
 console.log "#{per_time}ms"
)

Этот тест выполняет set_action_weight 1000 раз одновременно. Дожидается их завершения и вычисляет среднее время.

Для метода с несколькими запросами потребовалось в среднем 1,16 мс на вызов, а для одного оператора - 0,99 мс.

Это показывает, что метод с одним оператором примерно на 10% быстрее, чем с несколькими запросами.

Вывод

Я понимаю, что сравнительный эксперимент не является реалистичным тестом двух методов, но он показывает, что одиночный оператор работает быстрее, чем просто немного. Вот почему в GER используется метод с одним оператором, который, мы надеемся, поможет сохранить его производительность в зависимости от масштабов его использования.

Upsert - это общий шаблон, который можно использовать в разных приложениях. Так что это удобный инструмент, особенно если требуется производительность.

Ссылки / Спасибо

Двигатель G ood E nough R (GER).

Спасибо Autaux за изображение.

Искусство Интернета SQL: базовый UPSERT в PostgreSQL. Это отличная статья об upsert в Postgres.

Семь баз данных за семь недель: Наверное, стоит прочитать эту книгу.