Как стать автором
Обновить

«В карантин нагрузка выросла в 5 раз, но мы были готовы». Как Lingualeo переехал на PostgreSQL с 23 млн юзеров

Блог компании Lingualeo Разработка веб-сайтов *PostgreSQL *Разработка мобильных приложений *Изучение языков
Всего голосов 175: ↑102 и ↓73 +29
Просмотры 61K
Комментарии 787

Комментарии 787

Когда мы поделились планами с разработчиками, стало понятно, что команда не готова к изменениям. Большинство людей покинули компанию: остались только те, кто пришёл совсем недавно. Чтобы провести миграцию, мы решили заново собрать команду разработки.

Несколько не ясно. Т.е. уже работающим разработчикам озвучили новую задачу и все они сразу покинули компанию? Если не все, то оставшихся уволили/перевели в другие команды? Просто интересно, если пришлось менять команду, какова участь тех, кто по вашему мнению, не должен был в ней остаться? Или я чего-то не понял.
Бывшие разработчики сами решили свою участь, покинув проект. К сожалению, не все готовы выходить из зоны комфорта и штурмовать новые вершины. Со своей стороны мы честно сказали, что будет очень трудно, но интересно)
К сожалению, не все готовы выходить из зоны комфорта и штурмовать новые вершины.

А может проблема в вершинах? Может они увидели там огромный такой овраг? Может они решили, что все же не очень интересно — возвращаться во времена программирования на оракле.

Каждый вершитель своей судьбы. Вопрос не в инструментах, а в результате. Если разработчики могут обеспечить результат — действуйте. Если нет — то зачем обсуждать инструменты?
НЛО прилетело и опубликовало эту надпись здесь
Некоторые уже два года ищут))

Конечно, с таким бэкграундом кто их возьмёт))

Банки возьмут, с удовольствием. Поищите вакансии по фразам «PL SQL», «PL/SQL», «T SQL», «T-SQL».
2 года ищут работу? что за разработчик?

есть ещё вариант, что никто не хочет работать с начальством, которое говорит эту хранимку разрабатывать с отладкой 2 часа: то же знаете ли так себе удовольствие в 10-й раз объяснять новому руководству что подготовка данных для теста хранимки может занять 8 часов, если тебе аналитик не сказал, что в определенных ситуациях возможны дубликаты которых не должно быть. и потом этот аналитик ещё и увольняется.

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

в остальное мне сложно поверить так как бекграунд при найме не имеет никакого ровным счетом никакого значения:
можно нанимать хоть человека который ни 1 селекта или чтроки кода за всю жизнь до интервью не написал. имеет значение только — может он решить вопрос (говоря проще показать результат или нет) например с выводом данных, добавлением модуля, и прочей фигней, что нужна на данном проекте или нет. + подходит ли он по общению (грубо говоря может ли он не раздражая остальных понимать, что от него хотят и делать это). остальное играет роль только если вам нужен ключевой сотрудник который больше для удовлетворения эго руководства, чем для реальной необходимости.

В подавляющем большинстве случаев SQL сильно лучше для обработки данных, чем Python. Так что я прекрасно могу понять желание перенести все эти расчеты в БД. А если разработчик выучил Python и теперь пытается использовать его везде, где только можно и нельзя, то это, ИМХО, контрпродуктивный подход. Никак не пойму, почему все так боятся SQL.

Полностью согласен, тем более что возможности обработки разных типов данных (включая json) за последние 3-5 лет в БД сильно улучшились

"Боятся" не SQL, а отсутствия удобных инструментов разработки хранимок, триггеров и т. п. Со схемой-то полно проблем, но её хоть редактировать можно, а уж с ними...

Хранимки прекрасно разрабатываются в pdAdmin. Они небольшие (200 — 400 строк), их даже для крупного проекта не требуется более 200. Триггеры, при правильной архитектуре проекта, вообще не требуются (у нас нет ни одного триггера).

Если сразу с компактной (10- 15 таблиц) и гибкой (можно вводить сотни новых сущностей без изменения таблиц) структуры данных, то и сами хранимки получаются очень простыми и удобными.

Подписывайтесь — скоро новые статьи на эту тему будут

Этот This 'pdadmin' command line utility is a part of IBM Tivoli Access Manager?


Со сдержанным энтузиазмом жду статей: может действительно что-то сильно изменилось и теперь с хранимками хотя бы только на PgSql можно нормально работать, в частности:


  • легко переключаться между версиями кодовой базы для разных задач разработки, тестирования и даже продакшена
  • сливать разные версии с удобным средством разрешения конфликтов вплоть до уровня отдельных символов
  • проводить ревью изменений по конкретной задаче, видя конкретные изменения хранимой процедуры, а не имея последовательность CREATE OR REPLACE
  • проводит статический анализ всей кодовой базы на предмет хотя бы совпадений объявлений и использований таблиц, хранимок и т. п.
  • инструменты рефакторинга: переименование, выделение, инлайнинг

Когда в последний раз занимался исследованием вопроса (года 4 назад) то ничего сравнимого с современной IDE c исходниками под git/hg не нашёл. Основную проблему, почему за 40+ лет существования SQL ничего подобного не было создано, для себя обозначил как "Не смотря на заявленную декларативность языка, по факту он императивный. Мы пишем множество команд на изменение состояния схемы и подобных вещей, а не редактируем это состояние и даём одну команду на приведение текущего состояния к ожидаемому. Инструментов, позволяющих из двух описаний состояния сделать набор команд на приведение одного к другому ("сгенерировать патч"), не то чтобы совсем нет, но они очень ограничены и примитивны."

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

Тихо-тихо, visual studio очень отлично это делает! =) Database project там очень хорошо работает.

Где можно про это почитать?

Не тот случай, где MS развернулась лицом к FOSS

Хранимки прекрасно разрабатываются в pdAdmin. Они небольшие (200 — 400 строк), их даже для крупного проекта не требуется более 200. Триггеры, при правильной архитектуре проекта, вообще не требуются (у нас нет ни одного триггера).


Лол, на прошлой работе делал проект, в котором хранимок более 800шт, а одна из самых больших по размеру занимает более 1200 строк (впрочем, обычно я не приветствую такое, но в том месте необходимо было подготовить и передать дальше данные довольно сложной сущности, задача относительно уникальная и бить на кучу процедурок не было смысла). pgAdmin в принципе не вариант, редактор функций там убогий.

Использовать триггеры можно, например, для логирования изменений, это удобнее чем впихивать запись в журнал в каждое место, где эти данные меняются (хинт: гарантированно забудете в одном-двух местах вписать, да и вбухаете на это гораздо больше времени). Есть и другие задачи, для которых при нормальной архитектуре проекта предпочтительным.
Хранимки прекрасно разрабатываются в pdAdmin.

20+ лет пишу хранимки в PG, давно читаю хабр, вдруг оказалось, что тут можно постить до знакомства с разработкой и редакторами. Впечатлен.

Еще добавлю.
Похоже, современное поколение разработчиков привыкло к использованию инструментов и библиотек.
Когда я сказал, что написал на PostgreSQL функции для реализации поиска в графе это вызвало детское удивление — как сам?
А что такого? Задачка то элементарная, 3-й курс института.
Полностью поддерживаю. Процесс разработки становится настолько простым, что нет необходимости в инструментах, без которых разработка кода на PHP/… невозможна.

Папки с текстовыми файлами и текстового редактора с подсветкой SQL вполне достаточно даже для разработки крупного проекта.

За "простоту" приходится всегда платить. Всегда. Вопрос, в том: видите вы то, чем вы платите или нет.


Ощущение, что вы нашили серебренную пулю и обманули всех — оно ведь всегда ошибочное, оно сохраняется лишь до момента когда вы наткнетесь на понимание чем вы за это платите.


Не даром есть поговорка: "простота хуже воровства".

Есть еще поговорка: «все гениальное просто»

То что выглядит "просто" — может быть временным решением, которое эффективно решает ситуационную задачу, но завтра может оказаться "сильно упрощенным" для более сложной задачи. Возможно вы не четко очертили круг задач которые решаете при помощи хранимок в БД, что заставляет людей думать о том что при помощи хранимок вы решаете совсем все. Что с моей точки зрения, вводит в справедливое заблуждение. Опять же, с моей точки зрения, это не правильный пиар для такого подхода (лучше когда четко определили какие задачи решаются при помощи хранимок в БД и какие не стоит решать, и какие особенности при работе с хранимками в БД и как лечить).

И в статье, и в комментах я подчеркиваю, что в хранимках решается задача обработки данных! Это не единственная часть сложной системы, но очень важная.

Каких-либо сложностей в обработке данных внутри хранимок я пока не встретил. Если у Вас есть иной опыт — приведите примеры.

Обработка данных — это сильно расплывчатое понятие. Программирование, в принципе, занимается обработкой данных. Что вы имеете ввиду под этим?

Вот так:

Получение данных из таблиц, преобразование и сохранение данных в таблицах по определенным критериям

Формирование ответа с данными по определенным спецификациям

Критерий, где делать обработку, на мой взгляд следующий: если действие можно сделать с помощью одной/нескольких SQL-команд, то делать в хранимке. Если SQL бесполезен, то в микросервисах на других языках. Здесь главное понимать, какие есть возможности у SQL — не все об этом знают(

Другое дело!

на каком языке хранимки, что у вас скрывается под понятием «подготовка Json»?

С последним кстати вообще удобней всего на v8 работать внутри пг
Как у pl/v8 с поддержкой типа numeric при работе с json? В случае pl/python нам пришлось от парсинга json-а в питоне отказаться по причине потери точности.
А что, в PL/Python нельзя сделать
json.loads(something, parse_float=decimal.Decimal)
?
Уже не помню всех подробностей, это было года 3 назад и сперва писалось на 2м питоне, потом переписывалось на 3й. Возможно, просто не изучил тогда всю документацию по json.loads. Когда нам понадобилось реально работать с сохранением точности, в итоге воспользовались simplejson.
С другой стороны, если нужно возвращать json из pl/python функции, то смысла парсить json в python-е перед возвратом результата нет никакого.
не было проблем. К тому же парсинг json не должен терять точность вообще
Если рассматривать json узко — как строковое представление javascript-овой структуры, то конечно, по определению, потери точности не может быть.

Но в PostgreSQL jsonb хранит числа как numeric-и, а значит несложно завести значение, которое при переводе в javascript потеряет точность.

Реализовать поиск на графе — это одно. Реализовать его максимально эффективно — совсем другое.


Кто угодно умеет писать "шифротексты" ксором на ключ или более прошаренные могут вспомнить асимметринчное шифрование. Но почему-то пользуются библиотеками для этого.

Реализация графа это так этюд, чисто как иллюстрация, все равно рекурсивный CTE запрос эффективнее.
Аналогично реализовал в хранимке токенизацию текстов с иероглифами (когда идет сплошной список иероглифов, без проблемов, и их надо правильно разбить на слова и выражения, словарь прилагается в таблице).

Хранимка с рекурсивным CTE, 50 строк, примерно час ушел на разработку. Скорость обработки в 20 раз быстрее, чем скрипт на питоне. И по размеру кода в 10 раз меньше

Если у вас парсер в 20 раз быстрее в БД мне страшно представить сколько времени работал скрипт на питоне.


Было бы прикольно показать на конкретном примере, вот текст, на питоне разбираем с таким вот слоаврем за Х мс, а на постгре за Y.

Да, готовим статью с такими примерами. Я сам был поражен, когда увидел скорость работы в БД

А где-то можно уже почитать?

Я как-то думал, что PSQL медленнее Python, но заметно быстрее 1С. Надо потестировать…

Ок, пойду напишу пережиматель жпегов на SQL, там всё просто, стандарт открыт, библиотеки не нужны :)

"Не тот случай"

Не тот случай, где MS развернулась лицом к FOSS

что ставит любой диалог с вами в лог тупик - ибо ваш ответ будет известен ещё до постановки вопроса/ предложения вам примера - вы с успехом продемонстрировали ваше отношение к вопросу диалога ниже.

ключевое для вас это "не тот случай" - именно для вас это недостаточно идеальный.

инструменты есть. за 1 день вы не получите достаточно знаний что бы грамотно их использовать. ну это как сделать вывод о java за 1 ночь.

получается странная штука - программист об "отсутствии каких то инструментов" говорит.

а руководство совсем другие причины называет.

и мне кажется что "отсутствие удобных инструментов" в данном случае наименее вероятная (и субъективная) причина "боязни sql". жаль что этот короткий и очевидный факт мне не удалось изложить коротко.

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

а раз удобные инструменты не снизят порог входа для сотрудника, а так же не нивелируют факт "желательно для работы нанять дба + архитектора" - не в "удобных инструментах" дело.

sql боятся имхо именно по причине понимания, что можно не суметь контролировать процесс доработок и переноса, так же очевидно как на оопе.

ну и второй факт - наивно предполагать, что в эпоху капитализма и прибыли что то, что подходит под категорию "неудобно и ненужно" будет поддерживаться годами, а так же все про это будут знать и не любить. такие вещи отмирают очень быстро, а не развиваются до версий 2022.

"sql раньше все использовали его вот и скопилось" - раньше и паскакаль использовали и асм. тем не менее они формально для бизнесса мертвы (очевидная причина: не потому что эти языки убоги - а потому что специалистов по ним на рынке найти маловероятно массово).

косвенно: раз sql server уже 2022 - наверно "отсутствие удобных инструментов" не причина боязни сиквела для мира в целом. для вас же никто не сомневается. но в бизнесу субъективные переживания "очередного кандидата" (вас, меня, ещё вон того парня)... ну вы поняли. )

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

"Обработка данных" это что? Например, определить что у пользователя в списке покупок есть 3 товара одной категории, и если у пользователя в настройках задано "оповещать о скидках" то отправить смс — это всё в храникмке должно быть?

Речь не только про хранимки. Например нам нужно получить некоторое аналитическое представление каких-то данных в БД. Часто вижу, что это начинает лопатиться на бэке. С аргументацией "логики в БД быть не должно". Ну начнем с того, что даже фильтр по какому-то полю относится к логике. WHERE тоже на бэке будете делать? При тщательном рассмотрении можно понять, что БД — это и есть отражение предметной области с которой вы работаете. А если это не так, то Вас ждут проблемы. Где та грань, что нельзя делать в БД? Прям реально любопытно. Сможете рассказать?

Всё что не относится к SELECT/WHERE/GROUP BY/ORDER BY в бд быть не должно. В частности обработка ошибок, любые "если то-то то сделай то-то", контракт для конечного пользователя, и так далее.


Даже то что делается аналитическими функциями часто имеет смысл делать на беке, просто потому что так проще. Если нет конкретных бенчей которые показывают что система тормозит от таких запросов, тогда конечно.

А почему имеет смысл делать это на бэке? Я про аналитические функции. Давайте возьмем простейшее. Нужно посчитать SUM() по разным срезам и, возможно, фильтрам. Допустим где-то по месяцам разбить, а где-то по годам. Причем само отношение, которое мы будем группировать, получается путем джойна 5 таблиц. Итак, что вы будете делать в БД, а что на бэке? Мой вариант — создать вьшку, которая джойнит эти 5 таблиц, а потом обращаться к ней с разными WHERE и GROUP BY, применяя SUM(), как агрегирующую функцию. Это будет в 5 раз короче, чем то же делать на бэке. Это будет быстрее. Это будет дешевле по памяти. Это будет удобнее поддерживать. В частности это удобнее читать. А какой Ваш вариант?

Тут нет никакой сложной логике, и это можно считать на БД.


Хотя даже этот вариант в современном софте будет лежать в коде. Вьюшка будет сделана к модели БД в коде, после чего запросы улетят в БД и вернутся с ответом. Логика останется на беке, правда исполняться будет на базе. Типичный ОРМ в общем.

Все очень просто:

1. Фронт дергает ручку «Список покупок»
2. Прокси-сервис получает запрос и дергает соответствующую хранимку в базе (в этой точке возможен гибкий роутинг к слэйвам, например)
3. Хранимка формирует ответ в виде json. В ответе есть атрибут с инструкцией для прокси-сервиса: «вызови микросервис sms_sending, вот ему json с параметрами»
4. Прокси-сервис выполняет инструкцию
5. Прокси-сервис отправляет готовый ответ на фронт (п. 4 и 5 могут параллельно выполняться, если независимые)

Что нужно:
1. Разработать хранимку на 50-100 строк
2. Время разработки и отладки: 1 — 2 часа
3. Скорость отклика: 1 — 2 мсек (если структура данных правильная)

Зачем здесь нужны прослойки на PHP/…?
Зачем здесь нужны прослойки на PHP/…?

Повторюсь — проще найти десяток джуниоров для бека, чем одного мидла для БД.
Я другой причины для переноса бизнес-логики на бек пока не вижу.
И эта причина подтверждена на практике.

А ваш "прокси сервис" на чем написан?

Go

Ну вот если бы ваш "прокси сервис" отвечал за логику а бд за данные то было бы ближе к распространенным подходам.

Простой перенос обработки данных в базу упростил и удешевил систему в 10+ раз. Об этом опыте мы и рассказываем
А Вы не рассматривали Virtuoso Universal Server? он есть платный, с закрытыми патчами, и есть бесплатный, открытый?
Или Sedna, про неё на Хабре тоже писали.

а как организован процесс тестирования/деплоя всего этого хранимого счастья? Как работает параллельная разработка фич? Это ж никакого version -контроля? Или он через какие-то миграционные скрипты?

Время разработки и отладки: 1 — 2 часа — с 0 такое можно сделать только если:
— вы уже делали такое.
— структура данных отлдажена
— формат json отлажен и есть метод формирования.
— есть механизм аналогичный шине который туда сюда ответы гоняет.

в остальном — задания выданные в таком формате вряд ли возможно выполнить за пару часов, если не разбираться хорошо в архитектуре.
ИМХО с аналитиками это вопрос денег.
Есть деньги на сервера и OLAP-решения, то удобнее работать в них.
Нет. Используем «модные технологии», например Hadoop+Spark.
У меня был один пример OLAP-решения, которое работало на два порядка медленнее, чем явно написанные в скрипте запросы к БД. Разумеется, это было не только дорого (платить за ресурсы), но и бесполезно (ответ на простой запрос занимал часы вместо секунд). Всё-таки хочется не только разрабатывать с комфортом, но и пользоваться без боли тем, что разработано.
Вообще странно. Т.к. обычно в OLAP-кубы выгружают данные, таким образом, чтобы быстро получить срез, по той или иной аналитике.
При этом — да, загрузка данных может быть и долгой.
Но обычно это не критично.
но и бесполезно (ответ на простой запрос занимал часы вместо секунд)

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

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

1. С фронта идет через прокси-сервис запрос к базе: «дай список покупок»
2. Хранимка выбирает необходимые данные и возвращает в json на фронт (также через прокси)
3. Если нужно сделать дополнительные действия (отправить SMS), то в ответе в отдельном атрибуте идет инструкция прокси-сервису «вызывай микросервис sms_sending, вот ему json с данными)
4. Проксик все эти инструкции обрабатывает

Итог:

1. Фронт дернул один! запрос к бэку
2. Хранимка сделала один! запрос к базе
3. Все дополнительные действия сделаны в фоне

Размер такой хранимки — 50-100 строк (это если запрос достаточно сложный)
Время разработки и отладки — не более 1 часа
Скорость ответа — 1-2 мсек.
получается, всё-таки, не вся логика в базе, а есть и на go?
В базе данных логика обработки данных.

Естественно, любая более-менее сложная система включает в себя не только обработку данных — например, взаимодействие с другими системами. Эту логику нет смысла тащить в базу данных — не для этого они созданы.
НЛО прилетело и опубликовало эту надпись здесь
для метео моделей обычно использую специализированные БД, например spark
Не стоит ли в этой формулировке заменить «Python» на «любой нехранимый в БД язык»? Это же глобальное деление — операции с данными, в основном, стоит положить в БД, а остальные — нет. К примеру, рассылку почты лучше делать не хранимым языком, не так ли?
Совершенно верно — об этом статья и написана. Обработка данных должна производиться базой данных, с помощью SQL. Зачем для этого городить прослойки? А взаимодействие с внешним миром — через микросервисы на любом удобном языке.

При рассылке почты основная проблема не в том, как отправить письмо, а в том, кому и что отправить. Вопросы, кому и что отправить, решаются на стороне базы (это и есть данные), а сам процесс отправки нужного текста на нужный адрес — это уже микросервис

Да откуда у вас "должно"? Вы не у себя в команде на код ревью :)

Тем более, что тот же Python можно для хранимок использовать.
Нужно ли — это другой вопрос.

> В подавляющем большинстве случаев SQL сильно лучше для обработки данных, чем Python

Ну как бы сказать… Нет. Вообще ни разу. Просто он привычнее людям, которым надо декларативно описать выборку, а не программировать. И у этого есть как плюсы, так и минусы.

> Так что я прекрасно могу понять желание перенести все эти расчеты в БД.

Только вот PostgreSQL — не аналитическая база. Это обычный OLTP, изначально не заточенный так под аналитическую нагрузку, как, скажем, Vertica. И в аналитических базах часто есть коннекторы и расширения, позволяющие делать аналитику куда удобнее. Но тут уж своя мотивация и своя модель данных.
> В подавляющем большинстве случаев SQL сильно лучше для обработки данных, чем Python

Ну как бы сказать… Нет. Вообще ни разу. Просто он привычнее людям, которым надо декларативно описать выборку, а не программировать. И у этого есть как плюсы, так и минусы.

Интересно будет посмотреть, как на Python делается JOIN нескольких таблиц. И сколько он будет работать (особенно в том случае, если однажды соотношение количества отбираемых записей сильно поменяется).

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

например


import myorm

let data = myorm.sql("SELECT TOP 10 * FROM A JOIN B ON A.Id = B.OtherId")

Это "джоин в питоне" или уже нет?

Ну вообще-то это JOIN в SQL, который вызывается из Python

А как по-вашему по другому в языках с БД взаимодействуют? Всё в память грузят и на клиенте бегают в циклах? Нет конечно, оно вот так и выглядит, и когда говорят "логика на бекенде" имеют в виду именно такое. Иногда оно спрятано за каким-нибудь LINQ, но всё сводится к тому чтобы сгенерировать SQL на бекенде и выполнить его на базе.

До появления SQL, существовал API для доступа к табличкам. По сравнению с SQL, работал довольно быстро для задач типа работы с одной табличкой. Проблема в том, что джойнить приходилось руками и зачастую это приводило к тому, что проще было в значительной мере отказаться от нормализации, либо хранить какие-то коды без расшифровок (расшифровки при этом лежали в самом приложении).

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

Большинство ORM позволяют перехватить фазу генерации SQL и вставить свой, как угодно оптимизированный, включая обращение к хранимке :) Задача ORM не автоматическая генерация SQL запросов, это лишь для удобства добавляют.

А не надо ими генерить запросы сложнее простейших.
Пишите руками. Как все и делают.

А все простейшее они вам отлично сгенерят. Время секономите, да и не набажите при миллионом написании одного и того же.

На самом деле уровень простейшего довольно высокий. Оконных функций от ОРМ ожидать не стоит, но при прямых руках они джойнят и фильтруют успешно. Это покрывает большую часть всех запросов.
В среде Oracle очень популярно использование хинтов (не лучшая практика, но в некоторых случаях это прям очень желательно. Например — совершенно легальный в нормальном коде хинт cardinality для функции, возвращающей набор данных или массив (в postgresql эта самая cardinality — часть конструкции create function). Понятно, что функцию в ORM не вызвать, но при использовании подзапросов (характерных для работы с историчными данными) часто приходится использовать всякие NO_UNNEST и еще что-то, хз, правда, как с последними версиями, я от оракла несколько отошел). Они могут появляться и в достаточно простых запросах.

Типичная ситуация — DBA ловит проблемный запрос, предлагает хинты, чтобы заставить хоть как-то его работать, а разработчики ему — а мы не можем, у нас запрос сгенерённый в ORM.
В среде Оракл принято писать что хинты это антипатерн. Не надо так делать. Можно если совсем никак, но не надо.

Раньше, до всех этих мапредьюсов и nosql так приходилось делать. Просто выбора не было. Но сейчас зачем?

Не нужно всех этих гиганских перегруженых sql баз. Возьмите подходящую технологию и сделайте на ней. Их валом на любой вкус и потребность.

Взять миллионы строй из sql базы повертеть их и записать миллионы строк в sql базу вам не надо. Бывают исключения, но в общем просто не надо.
Взять миллионы строй из sql базы повертеть их и записать миллионы строк в sql базу вам не надо. Бывают исключения, но в общем просто не надо.

Зависит от входных требований. Если для удовлетворения каких-то из них требуются (напрямую или косвенно) ACID-свойства СУБД, то самый дешевый способ их обеспечить — применить SQL СУБД.

Кстати, замечу, что PostgreSQL тоже является nosql СУБД и успешно позволяет хранить документы, которые плохо ложатся на какую либо схему. Так что при необходимости, можно совместить две части данных в одном месте, чтобы расширять зоопарк технологий.
Ну так я это и написал — SQL отлично подходит, когда надо сделать выборку с джойнами из реляционной СУБД, и… собственно, все. Дальнейшая бизнес-логика по обработке этих данных — векторные вычисления, машинное обучение, генерация графиков и отчетов — гораздо удобнее делать на специализированном языке.

Более того, тот же Python выстрелил здесь, потому что код на нем, написанный аналитиками, потом могут относительно легко поддерживать обычные разработчики на рынке, в отличие от академических инструментов потипу R или Matlab.

Есть гибридные решения с попыткой натянуть сову на глобусSQL на аналитику потипу Clickhouse, но они весьма специфические.
Как сказать. Если надо обсчитать несколько миллионов строк и результаты в виде еще пары миллионов строк положить обратно в базу, появляются нехилые накладные расходы на перенос данных туда и обратно, которые сильно снижаются, если есть возможность обсчитать максимально близко к БД. Например, в Oracle можно сделать какой-нибудь расчет на видеокарте, подключенной к серверу, где развёрнута СУБД, в тех случаях, когда это оправдано, производительность упирается… в пропускную способность PCI-E шины. Очевидно, если делать подобные расчеты, подключаясь к БД по сети, скорость будет на порядки меньше.

Генерация графиков и отчетов — это вспомогательная задача, не имеющая отношения к бизнес-логике. Нужно взять данные и заполнить шаблон. Изредка бывают хитрые варианты, требующие постобработки данных в движке, который генерирует визуальное представление отчета (типа построения cross-tab), но это тоже по сути задача заполнения шаблона (при этом основная бизнес-логика по обработке данных может происходить в БД).

Машинное обучение, математические расчеты и т.п. — тут соглашусь, такие задачи точно не для СУБД. Но наличие таких задач не противоречит утверждению «В подавляющем большинстве случаев SQL сильно лучше для обработки данных, чем Python», если к нему добавить фразу «в тех случаях, когда SQL применим».
Если надо обсчитывать миллионы строк и делать аналитику по ним — то там база либо вообще нафиг не нужна (кладем файлы на сетевую распределенную ФС типа HDFS или Ceph и поверх этого периодически пересчитываем модельки), либо берется специализированная, часто append-only аналитическая база, в которой нет в принципе никакого «переноса данных туда и обратно». Есть, кстати, и третий вариант — шина данных потипу Kafka и лямбда/каппа-архитектура поверх нее.

> Генерация графиков и отчетов — это вспомогательная задача, не имеющая отношения к бизнес-логике.

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

> Но наличие таких задач не противоречит утверждению «В подавляющем большинстве случаев SQL сильно лучше для обработки данных, чем Python»

Противоречит, потому что реально большинство задач — это именно аналитика, математика, машинное обучение и те самые графики, а не только делание выборок. Поэтому я за то, чтобы оставить кесарю кесарево, а бизнес-логику писать уже на нормальном языке, а не на языке запросов к БД.

Какой хороший работодатель...

Я даже знаю что это за овраг! Это перенос всей логики в БД. Это адский трэш на мой взгляд
Разбить текст для перевода довольно трудно… Функция была очень громоздкой, перевод мог занимать много времени.

Поскольку система должна отражать только свои внутренние тексты, то разбивку на слова можно делать при добавлении материала в базу. Скорость функции будет не важна.

По мне запихивание логики в базу имеет несколько потенциальных проблем
  • Языки базы данных уступают по выразительности обычным языкам.
  • Проблемы с версионностью и, вероятно, тестированием.
  • Если сервер базы данных упрется в свои ограничения, то поправить будет сложно. Хотя, если он у вас там json отдает, то это случится не скоро.

Сложилось впечатление, что вы переехали с монолита на php, на монолит на PL/pgSQL.

У вас достаточно статичный контент, который лично я бы максимально закешировал в оперативной памяти и отдавал оттуда. В чем хранить данные было бы не принципиально.
Спасибо за вопросы!

Отвечу с конца:

1. «У вас достаточно статичный контент»: у нас очень динамичный контент по пользователям, каждую секунду обрабатываются сотни транзакций, в т.ч. по обновлению словарей пользователей, прогресса, статусам тренировок и т.д. Т.е. база данных постоянно обновляется.

2. «Языки базы данных уступают по выразительности обычным языкам.»: на мой взгляд, самый удобный и наглядный язык для работы с данными — это SQL. А если в рамках одного SQL запроса можно и распарсить json, и за доли миллисекунды получить данные из таблиц с миллиардами записей, и ответ запаковать в json — то что может быть проще и выразительней)

3. «Проблемы с версионностью и, вероятно, тестированием.»: у нас все хранимые функции поддерживают параметр apiVersion, поэтому как раз проблем с версионностью нет.

4. «Если сервер базы данных упрется в свои ограничения»: тут уже зависит от умения архитектора базы данных. Для обработки сложных запросов мы используем слэйвы, мастер только пишет данные и реплицирует их на слэйвы. Также архитектура позволяет поддержать модель мультимастера.

5. «разбивку на слова можно делать при добавлении материала в базу»: это не всегда эффективно, особенно если размер материала измеряется мегабайтами. Также разбивка на слова периодически перерассчитывается по мере улучшения внутренних словарей (например, добавляются новые устойчивые выражения).

Надеюсь, ответил на основные вопросы)
это не всегда эффективно, особенно если размер материала измеряется мегабайтами

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


Также разбивка на слова периодически перерассчитывается по мере улучшения внутренних словарей (например, добавляются новые устойчивые выражения)

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

Спасибо, Ваш вариант тоже хорош — рассмотрим его
Отвечу также по пунктам.
1. Я имел в виду часть, относящуюся к словарям, текстам, заданиям и прочее. По большому счету в память можно положить и базу пользователей и их действий, которую обновлять по pg_notify, к примеру. Сама информация, кроме финансовой составляющей, у вас достаточно не критична, так что in-memory в данном случае очень неплохо подходит.
2. Для работы с данными, конечно. Для реализации бизнес-логики — не всегда. База не имеет внутри какой то магии по работе с json. php справился бы с этим не хуже.
3.4. Ок.
5. Пересчитали и положили опять таки на сервер. Какая разница сколько весит исходный материал? Более того, пользователь сразу всю книгу (а речь видимо о них) не читает. При загрузке страницы сразу можно отдавать её разметку на фразы (это копейки).

В целом я могу понять, почему старая команда не согласилась. Решение использовать базу на полную катушку спорное. За нее еще лет 15 назад топил Том Кайт, и мне казалось, что на модную ныне микросервисную архитектуру она не очень ложится.
База не имеет внутри какой то магии по работе с json

Тут вы не правы, если речь идёт про постгрес. Он умеет, например, индексы по полям в json(b).
Скорее всего ТС этим не пользуется, но речь была про то, что магия там всё-таки есть некоторая.

Поддержу. PostgreSQL великолепно работает с json.
Это, если хранить данные в json-полях. Насколько я понимаю, в данном случае работа с json ограничивается распарсить и потом составить, т.к. необходимости в json-полях нет. В таком контексте, где работать с json разницы особо нет.
Тут скорее вопрос в том, как система умеет обращаться с json-типом, с json-массивами, как умеет ими манипулировать. В PG очень широкий спектр команд для этих целей — покрывает практически все потребности.
1. «в память можно положить и базу пользователей»: это зависит от размера пользовательской базы. У нас счет уже пошел на терабайты…

5. Да, при отдаче материала мы сразу и отдаем готовую разметку. Разница лишь в том, что мы ее рассчитываем при первом запросе, и только ту страницу которая запрашивается. Это решает проблему парсинга больших материалов.
1. Разумеется в памяти не надо держать, что вот этот пользователь заходил пять лет назад в такое то время и сделал то-то и то-то. В памяти только то, что нужно в данный момент. Такой информации будет едва ли сотни мегабайт.
5. То же вариант. Но лично я бы сразу рассчитывал, при добавлении. С местом, для хранения, сейчас проблем нет, в отличии от мощностей, как уже упомянули выше.
1. Как я сказал, это зависит от проекта. У нас совершенно другие порядки по объемам данных, с которыми необходимо быстро работать. Тут важно правильно структурировать данные, чтобы в условиях ограниченности ресурсов быстро обращаться с большими таблицами.

5. Да, согласен, скорее всего поддержим такой гибридный вариант. Спасибо!
Также архитектура позволяет поддержать модель мультимастера.

А с каких пор в PostgreSQL появился мультимастер? Или у вас не ванильный PostgreSQL?

У нас админы тестировали одну из платных кластерных модификаций PostgreSQL, производительность по транзакциям была так себе.
мы перенесли всю логику внутрь

А язык какой? PL/php?


У постгреса хоть процедурный язык и хороший, и сторонние можно приделать к хранимым процедурам, но поддержка кода в БД — это кошмар.
Нормальной работы с VCS нет, миграторы для функций не подходят совершенно. Да и те, что есть, каждый имеет "свой путь" и везде неудобно интегрироваться с git в любом случае (и в принципе ни с чем неудобно).


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

Спасибо за вопрос! Действительно, разработчикам, привыкшим писать на PHP / Go / Ruby / ..., непросто понять (и принять) философию разработки хранимок. Но когда понимаешь в полной мере возможности хранимок PostgreSQL, вопросы по инструментарию уходят сами собой.

«поддержка кода в БД — это кошмар.»: код у нас стал примерно в 50 раз меньше, в основном это SQL запросы. Если умеешь их читать (и писать), то проблем нет. А времени и ресурсов экономится прилично.

«миграторы для функций не подходят совершенно»: а зачем они нужны? Гораздо проще и эффективнее написать хранимку с нуля — много времени это не занимает.

«неудобно интегрироваться с git»: не требуется такая интеграция, т.к. код очень компактый. Делайте периодически снапшоты хранимок (это текстовый файл на пару мегабайт), и все.

По быстрой и удобной разработке хранимок скоро будет цикл статей…

А когда хранимки уже написаны и изменяются в процессе разработки?


Как изменения сравнить? Как узнать, что последний раз хранимку такую-то менял Петя Петечкин -дцатого -бря прошлого года?


С хранением хранимок обычно нет проблем, есть проблемы с их эволюцией и отладкой.

Хранимки достаточно изолированы, поэтому часть хранимок пишет один разработчик, часть — другой, со взаимным код ревью. Т.к. код очень компактный, то и на разработку, и на отладку, и на ревью уходит немного времени (в 10+ раз меньше, чем написание аналогичного кода на PHP или Go).

Посмотреть снапшот от такого-то числа большой проблемы нет (как я писал выше, это текстовый файл на пару МБ), хотя не припомню, чтобы в этом была необходимость.

Разработка и поддержка хранимок кардинально отличается от разработки кода на не-SQL языках. Некоторые секреты раскроем в ближайших статьях…
не припомню, чтобы в этом была необходимость

Потому что то бесполезно. В процессе разработки единица изменения кода — это отдельная задача, а не сутки/недели. Поэтому важно знать "почему сделано было такое изменение", "в рамках какого блока задач", а не в какой день и потом искать в трекере задачи за это число.


За день может быть сделано много отдельных правок, и одна большая правка может оказаться в снепшоте, несмотря на то, что её делали две недели.


Какой бы не была разработка хранимок философски отличной, отслеживать изменения должно быть удобно. А их неудобно в таком виде, как вы говорите.

Это дело привычки) Мы выпускаем еженедельные релизы, один релиз — это изменения в 5 — 10 хранимках. Тут важен не измененный код, а конечный результат. Как я говорил, хранимка — это не сотни атомарных команд, это один / несколько SQL запросов. Если с этим регулярно работаешь, то проще его прочитать и оценить его правильность, чем смотреть историю изменений.

Но опять-таки, это мой личный опыт. Сколько людей — столько и мнений
попробуйте посмотреть sqitch и pgtap

Справедливости ради, что мешает хранить хранимки в гите?

А вы видели хоть один удобный способ для этого?
Каким образом их хранить так, чтобы на базу накатывать прямо из гита?
Чтобы изменения в одной хранимке можно было сравнить с этой же хранимкой, а не искать по тысячам файлов с миграциями? Не делать pg_dump на несколько десятков мегабайт и пр.
Чтобы без всяких магических комментариев, без хранения схемы в XML, без утилит со смутной документацией?


Если вы знаете, как хранить хранимки в гите без костылей, поделитесь секретом.

Ну миграция хранимки в данном случае это просто деплой. Накатывается из гита обычным шагом какого-нибудь тимсити. То есть у нас есть мастер-ветка в гите, туда нафигачили всякого. После этого пришла пора релизиться — просто запускается мигратор который все скриптики в папке. А в папке лежат скрипты создания хранимок GET OR CREATE.


Пониаю, звучит смешно, вроде 2020 год на дворе, но в целом так жить можно.


Второй вариант мы даже в реальности использовали, когда писали хранмики в монге. Работало это по принципу EF миграций: есть системная коллекция MigrationsHistory. Есть папка со скриптами в гите. При релизе мигратор берет все файлы из папки, который выглядят как add_some_storedp_19-05-2018-16-05.js. После этого определяет, какие нужно накатить. После этого основываясь на таймстампе в имени файла по-очереди накатывает их, после накатывания инсертит в коллекцию MigrationsHistory имя файла.

Да не смешно это звучит, а грустно (:

Похожим образом flyway работает (инструмент для накатки миграций в Java)
Пониаю, звучит смешно, вроде 2020 год на дворе, но в целом так жить можно.
— почему смешно?

Потому что SQL это язык запросов. По общим возможностям не связанным с обработкой множеств он хорошо если 5% функционала языков общего назначения имеет.

Потому что SQL это язык запросов
— а, то есть вы хотели сказать:
«понимаю использовать sql в 2020 году СМЕШНО, потому что это язык запросов, но в целом так жить можно». Правильно интерпретировал вашу фразу?

и что входит в язык зпросов, что есть в остальных языках? то есть что общего с остальными языками у которых +95% функционала.

о каких возможностях sql идет речь?
я просто не понимаю, как вы рассчитали цифру 5%.
и что входит в язык зпросов, что есть в остальных языках? то есть что общего с остальными языками у которых +95% функционала.

Обрабокта ошибок (писать EXCEPT в каждой хранимке это не смешно), переиспользование кода (я могу какое-нибудь хитросделанное шифрование в одну строчку написать, импортировав какую-нибудь библиотеку), тулинг (для веба есть утилиты вроде сваггера, ELK библиотек, ...), и так далее и так далее. Я языки без генериков-то не воспринимаю как нормальные, там приходится очень много копипастой заниматься из-за этого, а SQL на порядок менее приспособлен.




В качестве эксперимента что у нас есть логика в БД, подумайте как будет выглядеть например реализация вызова удаленного сервиса асинхронно по https с exponential backoff стратегией ожидания между неудачными запросами, и структурное логгирование возможных ошибок в эластик (чтобы потом можно было по отдельным полям фильтровать). Это достаточно тривиальная и распространенная задача на бекенде, как делать такое в SQL плохо представляю. В качестве бонуса желательно иметь сгенерированные клиенты для JS/C#/Java/C++, чтобы можно было эту логику вызывать внешним сервисам, не задумываясь о том как оно устроено внутри.

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

я могу какое-нибудь хитросделанное шифрование в одну строчку написать, импортировав какую-нибудь библиотеку
— вопрос только зачем? можно демки сжимать в 64 кб, которые потом разворачивались на несколько мб… только в работе (в жизни) это никак не нужно. поэтому не понимаю зачем?
представьте что пришел другой человек и он не понимает сразу, что написано вами в 1 строчке. и этот человек явно не на позиции джуна. а ему поддерживать ваш код. и ему нужно дергать вас, что бы вы ему это поясняли и ещё много подобных вещей.

тулинг
— что такое тулинг?
Я языки без генериков-то не воспринимаю как нормальные
что такое генерики? скажите то что вы не воспринимаете… любой работодатель именно на это ориентируется?
а SQL на порядок менее приспособлен
— для чего???? для копипасты??????

В качестве эксперимента что у нас есть логика в БД, подумайте как будет выглядеть например реализация вызова удаленного сервиса асинхронно по https с exponential backoff стратегией ожидания между неудачными запросами, и структурное логгирование возможных ошибок в эластик
— для начала попробуйте разговаривать на человеческом пожалуйста. я половины не понял.
второй момент: вроде я вас не просил мне задачки давать, а попросил пояснить, что не так с sql в 2020 году. я что задал слишком сложный вопрос?

В качестве бонуса желательно иметь сгенерированные клиенты для JS/C#/Java/C++, чтобы можно было эту логику вызывать внешним сервисам, не задумываясь о том как оно устроено внутри.
— какого бонуса?

p.s. фразой про хитрое шифрование вы похвалиться решили? слушайте я не задавался вопросом, лучший вы в рф разработчик или нет. у меня даже вопроса на эту тему не возникало. я только спросил правильно ли я понял фразу про sql. а вы мне в ответ «я это могу и это могу»… мне было бы достаточно, если бы вы могли на вопрос ответить и желательно без использования терминов через каждое слово. ну что бы хоть кому то, кроме вас было понятно.
если, конечно, это не слишком сложный вопрос. все что я хотел понять почему смешно использовать sql в 2020 году. а не кто во сколько строк шифрование делает или решать
тривиальная и распространенная задача на бекенде… по реализация вызова удаленного сервиса асинхронно по https с exponential backoff стратегией ожидания между неудачными запросами, и структурное логгирование возможных ошибок в эластик
(на которые видимо достаточно пары часов с тестом раз это тривиальные задачи ).
Если вы знаете, как хранить хранимки в гите без костылей, поделитесь секретом.

sqitch и pgtap
без всяких магических комментариев
без утилит со смутной документацией

sqitch тут слабо вписывается, он такой же мутный как и другие инструментарии для миграций, и для хранимок чем-то особым не выделяется

Для постгреса пока удобного метода не нашёл (смотрю на FlyWay), для Sql Server используем https://www.red-gate.com/products/sql-development/sql-change-automation/. Он позволяет хранить миграционные скрипты + текущий снапшот схемы данных.

Может, такие способы не показывают потому, что они слишком просты?)
Скажем, есть у нас папка, в которой лежат исходники всех хранимок некоторой схемы БД (вроде account/create.sql и т.п.) и там же файлы с их тестами.
Как делать миграцию? Удалили схему и в одной транзакции накатили все файлы. Если тесты пройдут — делаем коммит. Это удобно или это с костылями?

Если у вас десяток хранимок — удобно.


Если сотни и тысячи — уже как-то не особенно удобно

У нас с десяток таких схем, в каждой, скажем 10 файлов по 5-10 хранимок. Вроде удобно
У нас с десяток таких схем, в каждой, скажем 10 файлов по 5-10 хранимок. Вроде удобно
Если сотни и тысячи — уже как-то не особенно удобно
— чем конкретно неудобно, можете пояснить?

А при разработке правите файл и на каждый чих удаляете-накатываете?

При разработке, на каждый чих, можно и просто файл накатить через тот же psql. Но в общем случае, в т.ч. при деплое — да, удаляем схемы и создаем заново, в одной транзакции с тестами.

Противоречит, по-моему, вашему


Может, такие способы не показывают потому, что они слишком просты?)

Может не показывают потому, что разработчики на обычных ЯП скажут "мы от подобных сложных схем разработки ещё в 70-х отказались" Шутка. :)

Мы используем Flyway (правда для Оракла), можно использовать Liquibase.
И там, и там, есть repeatable миграции. Во Флайвее это те, что начинаются с буквы R. А в Liquibase это делается через атрибут runOnChange.

Потому что то бесполезно
— если не уметь, и отрицать любую возможность — то действительно бесполезно.
Поэтому важно знать «почему сделано было такое изменение»
— кому важно в каком случае? необходим пример.

За день может быть сделано много отдельных правок
— это из опыта — «никогда не работал с сиквелом, но учить остальных буду»? привидите конкретный пример.
отслеживать изменения должно быть удобно. А их неудобно в таком виде, как вы говорите.
— что мешает хранимки хранить в собственном приложении к своей же бд, или в тфс гите и т.п. вещах? в чем неудобство?
инструменты есть, они вполне удобны для 90% случаев, почему для хранимок по другому?
Каким образом их хранить так, чтобы на базу накатывать прямо из гита?
— это прямо необходимость накатывать из гит?
Чтобы без всяких магических комментариев, без хранения схемы в XML, без утилит со смутной документацией?

чем плохи комментарии?
чем плохи утилиты?
зачем сравнивать предыдущий код?
Если вы знаете, как хранить хранимки в гите без костылей — что за костыли в гите можно пример?

В чём проблема хранить под гитом файлы с create or replace хранимок? При мердже в мастер просто запускаются все скрипты. Точно также можно отслеживать построчные изменения. Тестирование до мерджа осуществляется на локальных и тестовых инстансах бд. Разве что пошагового режима отладки нет, зато есть встроенный eval для быстрых проверок кода.


Alter table для таблиц нужен только чтобы не терять данные, а на самом деле он даже усложняет разработку, так как не видно полное состояние бд в коде.

Хранить не проблема, конечно, если не считать нарушения семантики в простых случаях: храним не исходный код процедуры, а команду на её создание замену, но шаг влево-шаг вправо:


  • удаление процедуры как делать? Или сначала дропаем все-все процедуры, а потом создаём всё с нуля?
  • переименование — аналогично. Не заморачиваемся, дропаём всё и создаём с нуля?
  • проверка того какая версия (коммит, ветка) накатаны на базу, хотя бы проверить вот накатана конкретная версия или какая-то другая?
А в чём проблема то дропнуть всё в рамках одной транзакции и создать заново? До коммита изменения видны не будут. Вас ведь не смущает, что при деплое условный JAR, бинарный исполняемый файл или php-файл тоже заменяется, а не каким-то волшебным образом патчится обычно?

Они указали, что вся их система разбита на несвязанные модули, так что накатывание изменений вряд ли долго происходит, ибо в одном модуле хранимок немного. Плюс в отличии от модификации таблиц создание хранимок очень быстрое, так как не надо перелопачивать огромные массивы данных, а просто сохранить на диск несколько сотен строчек текста. Многомегебайтные исходники или бинарные файлы деплоятся явно не быстрее будут.

Зачем нужен git? Чтобы можно было понять какую конкретно строчку и кто изменил в каждый момент времени. SQL-скрипт под git с кучей create function отлично отслеживается. Это не схема БД, которую нельзя каждый раз дропать, так как данные нельзя терять и приходится на каждое изменение писать отдельный скрипт с alter table. Такое действительно git не может отследить.

По факту проблема только с последним пунктом из перечисленных, хотя и легко напрашивается костыль с какой-нибудь табличкой с одной строчкой с версией (в конце миграции перед COMMIT делать обновление) или ещё чего. Да и в общем-то, версию задеплоенного обычного приложения без дополнительных телодвижений тоже может быть нелегко узнать.

Я вот единственную реальную проблему вижу в ограниченности SQL самого по себе, но они же не отказались совсем от бекэнда. Какие-то задачи на SQL действительно выразительнее решаются, нежели на обычных языках, какие-то очень болезненно. Тут главное правильно разделить. Ну и разработчиков им труднее будет найти на недостаточно популярную технологию. Но никаких проблем с контролем версий быть не может при правильном подходе. Вы просто экстраполируете негативный опыт с миграциями схемы БД, где из-за невозможности просто пересоздать схему действительно всё не гладко.
А в чём проблема то дропнуть всё в рамках одной транзакции и создать заново?

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


По факту проблема только с последним пунктом из перечисленных

По факту проблема в том, что нет нормального тулинга для синхронизации текстовых файлов в каталоге (опционально под управлением гита) и хранимок и подобных сущностей в базе. Его создание очень упростилось бы если бы SQL базы давали доступ к своему внутреннему стейту, но они дают возможность управлять им только через последовательность команд.

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

Это решается запретом на то чтобы лезть ручонками в БД напрямую. Хранимки или нет, не принципиальо уже, общее правило.

Правила разработчиков админам часто не писаны.

А зачем им лезть в БД? У нас например на них просто не ставят задачи связанные с БД сложнее "снять снепшот"/"раскатить снепшот". А без задач они никуда не лезут.

Метрики снимать, например, профайлинг. Не разработчиков же на продакшен базу пускать :)

Метрики обычно сама база снимает.


А тюнят запросы как раз разработчики когда заводится задача на оптимизацию :shrug:

Значит у нас были необычные админы, которые хранимками профайлили наши хранимки построчно. )

У нас используют pgCodeKeeper.
Делайте периодически снапшоты хранимок (это текстовый файл на пару мегабайт), и все.

А где лежат эти снапшоты?
А как понять кто и по какой причине поменял ту или иную хранимку / ввёл новую версию хранимки?

Снапшоты лежат в гите. А изменений версий описано прямо в хранимке.

О, уже хорошо!


А они попадают туда вручную и расчёт на то, что разработчик не забудет положить очередной снапшот в git или есть какая-то автоматика?


А есть возможность убедиться в том, что в конкретной ветке гита лежит актуальная версия снапшота, соответствующая боевой / тестовой базе?

Удаляю коммент, чтоб не множить сущности, уже написали выше, что в git можно хранимки хранить и оттуда же и деплоить.
поддержу, я видел во что превращаются здоровые базы и решения этому нет, ну просто никакого. 20 лет все жду хоть какого то намека на интеграцию бд с VCS но вроме костылей так ничего и не появилось.
А как обстоят дела с юнит-тестами после миграции? Какие-нибудь решения применяли для тестирования бизнес логики реализованной в БД?
Юнит-тест для хранимой процедуры в базе данных (хранимки) — это API-вызов с определенным набором параметров внутри JSON. Ответ хранимки — тоже JSON. Для проверки правильности достаточно сматчить параметры запроса и параметры ответа. Для этих целей используем собственный инструмент
это API-вызов с определенным набором параметров внутри JSON

Хранимая процедура получается вообще не содержит сложной логики и не вызывает другие хранимые процедуры? На первый взгляд мало похоже на юнит тесты, скорее на интеграционное тестирование.
А тестовая среда(база данных над которыми производит вычисления хранимка) одна на всех разработчиков? Откуда данные там появляются на проверку?
Да, секрет хранимых процедур в том, что как правило это один или несколько SQL запросов. Другие хранимки вызываются достаточно редко.

Процесс наполнения базы данных для юнит-тестов зависит от сложности самой базы. У нас достаточно компактныая структура (всего 12 таблиц, в каждой по 4-5 колонок), поэтому первичное наполнение данных не представляет сложности — это просто SQL-скрипт
Спасибо за ответы! Почему-то казалось, что бэкенд LinguaLeo содержит много элементов сложной бизнес логики.
С простой структурой БД ваше решение становится интересным прецедентом)))
Спасибо, будем делиться опытом)
Вы же пишете, что у PG все отлично с JSON. Значит и сматчить внутри БД не проблема.
В этом случае можно не только загрузить код в БД, но и прогнать все его тесты в той же транзакции. Не думали об этом?

Спасибо, что поделились опытом — очень смело с вашей стороны.
Архитектура сервиса — мрак, а всё, что написано в статье про БД — адЪ и погибель.

Спасибо за подробный комментарий)
Есть и те кто поддерживает ваш поход, например я

Пожалуйста, обязательно напишите через два года статью о состоянии вашего бэкенда, о том сколько инженеров текущего состава у вас еще работают, особенно Олег Правдин.

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

Очень интересно как чувствует себя проект сейчас. Прошел уже почти год и вероятно накопилась какая-то практическая статистика по использованию данной архитектуры. Буду признателен, если появится новая статья с результатами и цифрами.

Там мелькали интересные твиты по теме
Не понял, а какая все-таки решалась бизнес-задача тут: «Я предложил руководству полностью сменить философию бэкенда: перенести бизнес-логику в базу данных, а саму базу данных MySQL заменить на PostgreSQL»?

Со стороны видится, что:
1) проблема бэкенда была не в том, что бизнес-логика не в БД;
2) почему решили менять MySQL на PostgreSQL? Смена БД — шаг серьезный, и делать его без каких-то очевидных профитов едва ли стоит. Какая мотивация была у вас?
Спасибо за вопрос!

Вы верно сказали, что миграция должна быть оправдана. В нашем случае мы поставили такие KPI:

1. Снизить стоимость разработки в 5+ раз
2. Увеличить скорость разработки в 5+ раз
3. Снизить стоимость владения в 10+ раз
4. Сделать структуру прозрачной

Вроде удалось выполнить, но только с помощью переноса бизнес-логики в БД. Альтернативных вариантов найти не удалось.

Подробнее о процессе миграции можно посмотреть в моем докладе на PGConf'20:
www.youtube.com/watch?v=yHWFunXpZDU
Альтернативных вариантов найти не удалось.

А какие варианты рассматривали и как анализировали — подходят они для достижения требуемого или нет?

Смотрели популярные базы данных: MySQL, MongoDB. Также проанализировали дружественные проекта на PHP — как там идет разработка, сколько занимает времени и т.д.

Вопрос — как сравнивали? Внутреннее строение? Наличие/отсутствие определенных видов индексом? Сравнительная производительность на определенных видах запросов? Можно вот MySql vs Postgres: вот 10 параметров по которым мы сравнивали, и вот по 7 из них постгрес оказался лучше.


Пока что это выглядит "я сходил на конфу и там рассказали, какой mysql отстой, а друг с Highload приехал, сказал что там на постгре такую нагрузку держат, что жуть. Пойду расскажу начальству про полную смену парадигмы!"

Я пишу не о базе данных, а о философии разработки бэка. Уверен, что аналогичный подход можно реализовать в любой качественной базе данных. Мы используем PG, поэтому эту базу и упоминаем
Мне кажется, вы немного не на тот вопрос ответили. Дело не в том, «в какую другую базу перенести логику», а в том, нафига вообще это делать, неужели не нашлось других способов удешевить и упросить разработку, и пришлось дойти до такого? Неужели вообще никаких альтернатив?
  1. Снизить стоимость разработки в 5+ раз
  2. Увеличить скорость разработки в 5+ раз
  3. Снизить стоимость владения в 10+ раз

Вот тут было бы интересно узнать, каким образом снимались метрики, и каким образом сравнивались =)

Вроде удалось выполнить

По ходу, на глазок :)

Оценили, сколько часов разработки и тестирования уходило на решение схожей задачи в старой архитектуре и в новой.

А стоимость владения оценили по нагрузке процессоров и памяти при равном количестве онлайн-пользователей. В итоге снизили мощность серверов при сохранении достаточно большого резерва.
Оценили, сколько часов разработки и тестирования уходило на решение схожей задачи в старой архитектуре и в новой.

По какому количеству задач эта оценка была проведена?


А оценка включала в себя затраты на исправления, которые могли случиться после реализации задачи? На каком интервале времени считались такие оценки?

Поскольку мы полностью переписывали бэкенд, то и в старом, и в новом бэкенде решались десятки схожих задач. Время решения старых задач оценивали по jira (когда завели, когда закрыли).

«А оценка включала в себя затраты на исправления...»

Да, оценивали от начала разработки до стабильного релиза.
Время решения старых задач оценивали по jira (когда завели, когда закрыли).

А время решения новых как оценивали?

Аналогично: постановка задачи… стабильный релиз
Получается вы сравнивали время на реализацию задач между
1. Проекте с кучей легаси кода, отсутствием документации, и отсутствием людей кто вообще знает хотя бы 80% проекта.
2. Проектом абсолютно новым (с сохранением той же бизнес логики), написанном с нуля, документированном, и наличием специалистов знающих этот проект.

И если да, то вы не задумывались что возможно, ну вдруг, всякое бывает, что ускорение не связанно с новой архитектурой?
Так функции на PHP тоже писали те разработчики, которые знали проект. То, что получилось в итоге много легаси и без документации — проблемы как организации работы, так и способов решения задач.

Никто не заставлял разработчиков делать 500 различных таблиц (сейчас всего 12, и их структура не меняется, хотя каждую неделю добавляется по несколько новых сущностей и фичей). Конечно, 500 таблиц сложнее вести, чем 12.

Никто не заставлял разработчиков обрабатывать данные в PHP и микросервисах: 1 млн строк кода сложно документировать, 30 тыс. строк гораздо проще.

Поэтому мы полностью поменяли философию разработки (как процесс, так и инструментарий), что иметь прозрачную систему и не плодить легаси и
Вы получили систему которую смогли проанализировать по частоте запросов, времени выполнения и т.д.
Первоначально когда «прошлые» разработчики писали, пытались сделать «нормализацию» данных. При этом 500 таблиц — это не проблема.

Есть-ли предположение что можно сейчас написать обычную трехзвенку на java/kotlin/python и любой БД с лучшей производительностью чем у Вас?

Если данные все равно берутся из базы, то как внешние прослойки смогут обеспечить лучшую производительность, чем прямые запросы в базе?

Вопрос не только в производительности (здесь не проблема обеспечить запас, достаточный для любого крупного проекта с десятками миллионов онлайн-пользователей), но в скорости разработки, в возможности расширения функционала. Любой код для работы с данными вне базы — это излишний код, зачем на него тратить время и ресурсы?

Ну вот вы же пишете какие-то то ли прокси, то ли API гейтвеи. Это у вас лишний код или нет? Почему он не в базе?


И вообще, а бывает код для работы не с данными? Ну кроме sleep(100) и то, 100 — это данные.

Вариантов микросервисов, которые работают не с данными, множество:
1. Отправить SMS с таким-то текстом на такой-то номер
2. Отправить письмо, пуш,…
3. Дернуть АПИ внешнего сервиса с такими-то параметрами
4. Слушать нотификации и отправлять данные в хранимку
  1. Текст СМС, номер — данные
  2. Текст, адрес — данные
  3. Параметры — данные
  4. Отправлять данные

Похоже у вас какое-то оригинально определение данных, слабо пересекающееся с общепринятыми.

Вопрос в том, кто эти данные подготовил. В нашем случае эти данные готовит база. Микросервис выполняет задание, данные для него передаются в json-параметре

Похоже для вас данные — это только то, что проходит через базу.

в 95% случаев это так и есть, и тут лучше использовать SQL, для остальных 5% случаев можно воспользоваться прикладным языком.

Вот прямо всегда если данные проходят через базу, то лучше использовать SQL? Чем лучше, например, в рамках записи новых данных, проводить валидацию этих данных в инсерт запросе (с трудом представляю даже как), а не сначала их проверить в приложении, а уже потом вставлять?

Один SQL запрос может включать в себя десятки селектов, инсертов, апдейтов и т.д. Поэтому вы можете в одном запросе:
1. Распарсить json
2. Валидировать атрибуты
3. Выбрать данные из множества таблиц исходя из этих атрибутов
4. Провести дополнительую валидацию
5. Обновить / вставить данные в таблицу 1
6. Удалить данные в таблице 2
7. Сформировать ответ в json

Почему это удобнее:

1. Все данные уже в памяти, во временных таблицах (CTEs): вам не надо каждый раз что-то выбирать из таблицы, сохранять в массиве, обрабатывать массив, потом опять считать данные, и т.д.

2. Логика в одном месте (а не разбросана по микросервисам), ее легко проверить (понятно, что из чего получается), легко накатить (одна хранимка)

3. Про скорость разработки и размер кода уже много раз писал

Похоже у нас ещё и разное понимание, что такое "SQL-запрос".

Постгре позволяет делать INSERT/UPDATE/DELETE в CTE
Многие вещи без БД делать слишком затратно и приложение может превратиться в однопользовательское. Например, проверка на уникальность.
А я в свою очередь, жду посмотреть как будет реализоваться транзакционная и ссылочкая целостность между микросервисами и Row Level Security.
Если микросервисы не лезут в базу, то какие тут могут быть проблемы? Все обновления в базе идут через хранимки, они выполняются внутри базы по транзакционной модели.

Приведите примеры, когда могут возникнуть проблемы целостности
В моем случае как раз микросервисы и лезут в базу. Хранимок нет. А каждый запрос это ведь отдельная транзакция. Как в этом случае будет реализована транзакционная целостность будет интересно посмотреть.
Пока не ясно как будет реализовано — каждому сервису своя база или одна база на все сервисы. В первом случае, как обеспечить ссылочную целостность по внешним ключам, опять таки интересно будет посмотреть.
В общем кейс обещает быть интересным.

Один запрос к сервису — одна транзакция by design. Никаких проблем.


Ссылочной между сервисами нет by definition :)


RLS как и любая другая S — в чём проблемы?

Учитывая, что у них все-таки используется mq — технически это не одна транзакция, в зависимости от того, дергается ли хранимка на стороне консюмера.

Ну и опять же — хорошо, когда вы владеете всеми данными. А тут поскольку есть все-таки какие-то сервисы, то сценарий
1. Обновить сущность А
2. Сходить на внешний сервис за данными
3. Обновить на их основании сущность Б
либо лишится транзакционной целостности, либо поменяет местами шаги 1 и 2 и тем самым превратится в другой сценарий, где обновление сущности А условное.
1. Обновить сущность А
2. Сходить на внешний сервис за данными
3. Обновить на их основании сущность Б

а тут то какая проблема?
трензакция открывается сущность обновляется, комит роллбак
если комит — получаем данные из сервиса шины файла таблицы обновляем Б

хотя по класике ты сначала получаешь данные для обновления передаешь их в бд — дальше целиком обновляешь сущность или необновляешь в бд.

и не понимаю, какая транзакционная целостность в данном случае будет нарушена? можно поподробнее.
Проблема 1. Внешний сервис тоже содержит транзакцию. И может случиться ошибка между коммитом во внешнем сервисе и продолжением работы в текущем. Вероятность низка, но отлична от нуля. Значительно снизить такую вероятность можно переходом на mq, полностью исключить — использованием распределённых транзакций (но с ними надёжность системы упирается в надёжность координатора).

Проблема 2. Если внешний сервис будет тупить, то получите долгоиграющую транзакцию, что может плохо сказаться на работе БД, а также занимает подключение в пуле (надеюсь, не нужно объяснять, зачем нужен пул подключений), а также чревато блокировками других сессий.
Так сейчас никто и не делает распределенные транзакции. Все в саге. :-)
Только не всегда это возможно. Внешний сервис может быть реально внешним — со своим API, в котором не предусмотрены компенсирующие транзакции.
надеюсь, не нужно объяснять, зачем нужен пул подключений
— нужно.

Значительно снизить такую вероятность можно переходом на mq
— что такое mq?

где применяется архитектура описанная в проблеме 1? насколько я понимаю, помимо того, что в проблеме 1 используется бд, у которой очередь транзакций, есть своя, есть ещё внешний сервис/апи/приложение, транзакции которой имеют не меньший приоритет. и, если будет ситуация, что в бд, в результате нагрузки (к примеру), транзакция прошла через 30 секунд вместо 1, а в апи это было принято как «таймаут вышел, я откатываю», в результате чего, этот откат начинает влиять на бд (аналог бд с чем и шла работа).

в проблеме 2 тоже возникает подобная тразакционная целостность?

а также чревато блокировками других сессий.
— в каком случае будет возникать блокировка?
— нужно.

подключение к БД — довольно дорогая операция, поэтому целесообразно их переиспользовать и заводить заранее (до появления нагрузки, требующей работу с БД). В некоторых случаях используется пул со стороны БД — pg_bouncer, но он имеет свои накладные расходы и ограничения, а по сути это такой-же пул.

— что такое mq?
message queue, выше уже приводили это сокращение.

транзакция прошла через 30 секунд вместо 1, а в апи это было принято как «таймаут вышел, я откатываю»
Проблема не в таймауте, а в том, что когда сервис отдаёт ответ, он не может получить гарантий того, что этот ответ полностью успешно получен. С точки зрения общей надежности системы, вероятность неполучения достаточно мала. Но с точки зрения базы данных это практически гарантирует потерю консистентности.

— в каком случае будет возникать блокировка?
Клиент F5 понажимал, прилетело несколько запросов работающих с теми же данными.

Ну вы ведь сравнивали с дремучим legacy. Может быть новый чистый PHP/go код был бы сравним с новым кодом в PG?

Посмотрел презентацию, логика какая-то есть, но все это похоже на какое-то массовое велосипедостроительство и нецелевое использование инструментов.

Особенно, непонятно для чего нужна реляционная база данных с тремя ключами и бинарным значением, если есть redis.

Похоже, что человек хорошо знает PostgreSQL и немного зациклился на этом инструменте. Хотя тут в комментариях упоминаются какие-то микросервисы и какой-то шибко умный прокси, возможно, в PostgreSQL разумная часть данных и логики, а про остальное просто забыли сказать.

Не понятно, чем не подошел типичный подход растаскивания монолита на микросервисы, без потери команды, данных и даунтайма. Не понятно, почему не применять более широкий набор БД и сервисов, есть же кеширование, полнотекстовый поиск, Redis, MongoDB, какие-то фоновые действия, основанные на менеджерах очередей, типа RabbitMQ или Apache Kafka.
Не понимаю зачем городить зоопарк из БД если PostgreSQL прекрасно справляется с задачей? Чем больше у вас источников данных, тем сложнее приложение, самим придётся придумывать как выполнять согласованные чтения, трудности с согласованными коммитами, короче нет смысла изобретать велосипед, лучше изучить возможности БД.
Зачем нужна Монга, если Постгре работает быстрее
www.youtube.com/watch?v=SNzOZKvFZ68
и всё можно дёрнуть 1 декларативным запросом и не париться о согласованности данных

Как PostgreSQL справляется с полнотекстовым поиском? Или с очередями? Redis и MongoDB за счет своей простоты могут масштабироваться горизонтально, хотя я тоже сторонних использовать их только для нишевых задач. А есть еще граф, который я не упомянул. Думаю, что большая часть этого зоопарк есть на проекте в "микросервисах".

В нашем случае необходимости в «зоопарке» не было — наоборот, мы упростили всю систему, исключив rabbit, kafka, elastic search и др. FTS (full-text search) в PG позволяет находить необходимые данные в базе из миллионов документов за 2-3 мсек — для наших задач более чем достаточно.
А в моем случае поступают именно наоборот — усложняют схему добавляя «redis, kafka, elastic search и др.» Плюс модное увлечение микросервисами — каждый микросервис- отдельная база.
Почему для СУБД оставляют только хранение данных — я аргументов не слышал. Как будут реализована транзакционная и ссылочная целостность — пока тишина.
Очень интересно наблюдать — чем все кончится.

Вы видимо не видели хранимок на 5к строк которые делают все, что можно. Языки СУБД очень бедные по сравнению с любым языком общего назначения, и попытка выполнять бизнес-логику в БД превращается в адок.


Но вы можете продолжать считать, что те разработчики которые годами пилили систему свалили потому что глупенькие и не оценили гениального манёвра руководства.

Глупостей можно наделать при использовании любого инструмента, если его использовать не по назначению.

«Языки СУБД очень бедные»: для работы с данными достаточно SQL, а этот язык есть только в СУБД.
Почему для СУБД оставляют только хранение данных

Ну я считал и, пожалуй, до сих пор считаю, что специализированное решение имеет более широкий функционал и высокую производительность.
Специальные менеджеры очередей явно проще в использование. Если говорить об Elasticsearch, то там огромные возможности по морфологии, синонимам и многому другому.

Как будут реализована транзакционная и ссылочная целостность — пока тишина.

Что касается целостности данных, то даже в случае применения реляционных БД их далеко не всегда обмазывают транзакциями и внешними ключами. В данном случае, PostgreSQL так же используется скорее как NoSQL база и целостность данных и даже их структура данных не гарантирована.

Если у вас критические данные, типа платежей, то их конечно же стоит делать строго с транзакциями и хранить в одной базе с ключами.
Кто сказал, что PG — это NoSQL база? Я писал про подход «SQL — NoSQL», когда классические SQL-подходы (первичные ключи например, которые и обеспечивают уникальность) совмещаются с гибкостью NoSQL.

Каждый вызов хранимки — это изолированная транзакция, с автоматическим коммитом (или роллбэком на exception)

У вас значения полей в json как-то участвует в ограничениях FOREIGN CONSTRAINT KEY ?

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

Давно хотел попробовать PostgreSQL, но Uber отбил желание, возможно, время пришло.
Отличное решение) За последние 5 лет PG сильно вырос. Надеюсь, не разочаруетесь
PG безусловно очень мощный инструмент. Хоть я и со скепсисом отношусь к применяемым вами решениям (видел аналогичные решения, не впечатлило), всё же интересно, переживет ли этот подход ближайшие 2-3 года, и не будет ли статей на тему «Как мы возвращали блудный API в лоно монолита» :)

Редис in-memory если что

Да, в этом плане размер хранилища несколько ограничен.
Конечного пользователя все ваши технические нововведения и внутреняя кухня не особо волнует. После того как вы год назад угробили пользовательские словари (включая мой на более чем 10 тысяч слов), ушел от вас недоиспользовав 7 месяцев Premium-доступа, хотя исправно платил неколько лет. Отвратительный user-experience и нулевая реакция на обратную связь. Спасибо за урок о том, что нельзя доверять онлайн-сервисам свою информацию, если ее нельзя в любой момент (до каких-либо принудительных изменений!) скачать в исходном виде. К вам не вернусь и другим не советую.
Спасибо за комментарий — учтем ваши пожелания
Как написано выше в статье, в наследство нам достался «черный» ящик, в котором данные были разбросаны по сотням таблиц, без документации. Вытащить данные из такого ящика без потерь не представлялось возможным. Печально, что ряд пользователей это задело(

Но для этого и делался этот шаг, чтобы архитектура и структура стали прозрачной, чтобы и нынешнее, и будущие команды разработки могли развивать продукт, а данные пользователей не терялись
А почему не вытащили данные постфактум? мне кажется когда происходят глобальные обновления инфрастукруты, то необходимо старую на некоторое время оставлять, на всякий случай.
Но у вас же была рабочая система. Раз Areskol мог зайти в личный кабинет и посмотреть свои словари, значит ваша система могла эти словари прочитать из базы и упаковать хотя бы в HTML. Соответственно, при наличии желания вы могли бы загодя написать хотя бы самые костыльные миграторы, которые, в самом плохом случае, притворяются пользователем, тянут HTML, парсят и запихивают в новую базу. Это дичь, конечно, но это лучше, чем подход Большого Лебовского.
Такая же мысль возникла. Мало того, такой кейс мог бы помочь и в последующем тестировании. Понятно, что это все ресурсоёмко. Весь вопрос в приоритетах.
Печально, что ряд пользователей это задело(

Так это ваше решение было уничтожить наши словари?

"данные были разбросаны по сотням таблиц, без документации. Вытащить данные из такого ящика без потерь не представлялось возможным "


А можете подробнее рассказать? Откуда потери взялись вдруг? Не смогли со структурой разобраться и грохнули часть данных?

Отвечу сразу на все вопросы выше: данные пользователей были разбросаны по сотням таблиц, при этом никакой документации, как эти данные взаимосвязаны, не было. Поскольку эти структуры делала не одна команда, то получилось труднопроходимое болото(

Мы честно дебажили монолит из 1 млн. строк, пытаясь выстроить это болто осушить. В итоге более 99% данных пользователей мы смогли восстановить, что очень хорошо — мои первые оценки были не более 80%

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

То есть вы просто дропнули прод базу с юзер данными? Серьезно?

И все бекапы на всякий случай.

Они такие с рождения. Лет десять назад пришел по стать с хабра из любопытства. Не впечатлился и забыл о них. А потом стала приходить их рассылка, на которую я не подписывался. Отписаться невозможно — нужно залогиниться. Пароль не помню и восстановление не работает. Написал в саппорт — ответили, что все работает как надо и нечего важных людей отвлекать пустяками. Написал вроде и на Хабре коммент… не помню. Помню точно, что пометил их письма как спам. Оказывается легко отделался.

LinguaLeo

— В словарях можно добавить вариант произношения (UK / US)? например как у них https://dictionary.cambridge.org/dictionary/english/cog
Некоторые слова у Вас встречаются с американским акцентом

— Как добавить 2 и более значения для слова в словарь и пример к каждому переводу? т.к. тот же git commit в определенных ситуациях можно перевести как «мерз… ц соверши что то плохое»

— Как сделать сортировку по значению слов?
Спасибо за вопросы! Если не возражаете, передам их коллегам по продуктовой линии. Также они планируют подготовить ряд статей по развитию продукта.
Добрый вечер!

— В словарях можно добавить вариант произношения (UK / US)?

Да, в настройках профиля можно выставить вариант озвучки.

— Как добавить 2 и более значения для слова в словарь и пример к каждому переводу?

Переводы можно добавлять у себя в словаре (СКРИН)

Как сделать сортировку по значению слов?

При клике значения одного слова/выражения отсортированы по частоте, т.е. вы видите топ-5 популярных значений (СКРИН)

Оргструктура до и после изменений: отказались от технического директора в разработке и упростили структуру в продуктовом отделе. Сейчас продуктовому дизайнеру не нужно общаться с двумя уровнями менеджеров, чтобы передать идею на фронт

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


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

Ээээ?...

Петр, когда ты работал в компании, вопросы разработки координировались техническим директором, он взаимодействовал с продуктовым отделом и распределял задачи по разработчикам. Сейчас все стало гораздо проще) И, на мой взгляд, эффективнее.
Сейчас все стало гораздо проще) И, на мой взгляд, эффективнее.

Главное, чтобы это было не на чей-то взгляд, а на самом деле)

Команды менялись, новички не всегда понимали, как работают старые части системы, в итоге код Lingualeo постепенно превратился в чёрный ящик: непрозрачная логика в бэкенде, перегруженный фронт, обилие костылей, большие пробелы в документации.

И конечно же сейчас, не дай бог что произойдёт с разработчиком БД, подобной ситуации конечно же не произойдёт, потому что… Почему?

Петр, сейчас совсем другая ситуация: бэкенд стал прозрачным и гораздо компактнее. Поэтому при любых изменениях в команде новые разработчики смогут в короткий срок (1 — 2 месяца) войти в курс дел и продолжить поддержить и развивать систему
А что будет через 2-5 лет? когда этих процедур станет раз в 15 больше? чем эта ситуация будет отличаться от той от которой вы ушли?

Разработчик которые умеют и готовы работать на PL/SQL еще меньше чем разработчиков по общим языкам, как планируете решать этот вопрос?
А затраты на инфраструктуру на 10 000 активных пользователей превышали 1 000 $ в год.

Вроде и немного совсем.

миграцию сервиса с 20 миллионами пользователей

А тут как-то сильно больше.

Где-то опечатались?

Я полагаю, что первое — это в пересчёте, на каждые 10к пользователей = $1к


Для 20млн сумма чуть больше будет, если умножить.

Но выдерживали они все-равно не более 2000 ;)

Тут нет опечатки: «на 10 000 активных пользователей превышали 1 000 $ в год» означает, что на каждые 10 тыс. активных пользователей требовалось около $1000 в год. Это не значит, что всего 10 тыс пользователей)

На самом деле это дорого, учитывая большинство пользователей на бесплатном тарифе
То есть инфраструктура стоили $2млн в год?
Вы неверно интерпретируете цифры. 20млн пользователей неравно 20млн активных пользователей. ИМХО, активных меньше.
Да, все верно — 20 млн зарегистрировавшихся пользователей не равно 20 млн активных пользователей. Но инфраструктура все равно была дорогой(
Наконец, продукт стал безопаснее. Раньше, когда вся бизнес-логика была в прослойке на PHP, оттуда из разных функций шли запросы в базу данных. Открытая для SQL-запросов база данных — это проблема: можно сделать SQL-инъекцию и заставить её выполнить опасный код, например, удаление данных. Сейчас снаружи не приходит ни одного SQL-запроса, потому что мы перенесли всю логику внутрь».

Хм, а разве сейчас какие-то адаптеры к БД не экранируют параметры в SQL запросах, чтобы не сыграл SQL Injection?


И разве хранимки каким-то дополнительным образом от этого защищены? Вот тут говорят что в некоторых случаях при некотором стечении обстоятельств это всё-таки возможно, поправьте меня, если я не прав

И разве хранимки каким-то дополнительным образом от этого защищены?

Их не нужно дополнительно защищать, они нормальным образом от этого защищены. С точки зрения глупого "адаптера", запрос к БД — это просто кусок текста, а для БД запрос — это горсть синтаксических единиц с определенным смыслом. Поэтому если вы передаёте в аргумент функции строку, она ничем кроме строки быть не может, и даже если там будет true; --, оно как строка без какого-либо предположения будет обработано.


Наоборот, в хранимке нужно специально постараться и взять арбалет, завести, приставить его к колену и выстрелить, чтобы словить инъекцию. Но динамический SQL в котором участвует текст который получается от пользователя, это уже залог успеха. Такого не должно быть в нормальных условиях.


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

Наоборот, в хранимке нужно специально постараться и взять арбалет, завести, приставить его к колену и выстрелить, чтобы словить инъекцию.

Ну, например, классический косяк джунов — принять имя поля для сортировки и прокинуть его в запрос. В случае хранимок что-то изменится, что не даст джуну так выстрелить?

принять имя поля для сортировки и прокинуть его в запрос. В случае хранимок что-то изменится, что не даст джуну так выстрелить?

Зависит от того, какой подход к работе с хранимками. Если использовать информацию из статьи, а именно:


…, переносим туда расчёты и создание JSON. … прокси-сервис, который направляет запросы в нужную базу данных, а затем отдаёт готовый JSON

То прокси-сервису ничего не нужно делать, кроме как принять аргументы и передать их в постгрес:
select f($1, $2, $3, ...)


Если весь код написан в таком виде (из предположения, что новые методы всё же вручную пишутся, а не динамически из пути запроса и query генерируются), то сделать ошибку можно разве что специально.


В случае, если входные данные — это тоже json, то аргумент вообще один будет, а невалидный json просто распарсить не получится.

Полностью согласен — одного json-параметра достаточно для любой хранимки)

С чего бы он не валидный.
{"order": "field; SELECT * "} — вполне валидный json

Будет простой ответ, что параметр невалидный. Нет такого варианта для параметра «order»

Именно что валидный, но order — это строка. то что у вас SELECT * в строке — никакой роли не играет.


Если предположить, что не используется динамический SQL (т.е. поля из JSON никогда не попадают в EXECUTE PL/pgSQL, к примеру), то ничем, кроме строки, order стать не может, просто потому что к тому моменту, когда постгрес наткнётся на содержание этого поля в JSON, весь запрос уже будет построен, и это значение в виде строки.


Вы, вероятно, предполагали что значение поля order подставлялось бы напрямую в order by "field", но проблема в том, что без динамического построения запроса это сделать невозможно.


Но задача наверняка ведь не ставится в виде "послать имя поля для сортировки и использовать как есть в raw sql". Изначально есть необходимость на основе выбора пользователя подобрать критерий для сортировки.
И в таком случае уже делать то, что вы хотите — не обязательно.


Ну и даже если в таком виде делать, у постгреса есть варианты безопасно передать что угодно в запрос. Например через format('%I', request->>'order') или quote_ident(request->>'order').

но проблема в том, что без динамического построения запроса это сделать невозможно.

Но как хранимки защищают от неиспользования создания динамического запроса? Да никак в общем. К этому и подвожу — нет никакой повышенной безопасности. Как в коде можно динамически сунуть параметр забыв про плейсхолдер, так и в хранимке.

Но как хранимки защищают от неиспользования создания динамического запроса?
Их можно запретить гайдлайнами (:

В хранимках самих по себе не "повышенная безопасность", это в вызове SQL из кода приложения безопасность — пониженная.


Встроенный в PHP/Go/Ruby запрос на SQL — это не нативный код, а просто строка. И работать с SQL кодом как со строкой там не то что просто, зачастую это намного проще чем в плейсхолдеры пихать параметры и это не выглядит как что-то криминальное.


Если вы пишете нативно на SQL или pgSQL, то чтобы выполнить запрос из строки, нужно наоборот кучу телодвижений приложить. Ну и код внутри строки не будет красиво подсвечиваться.

Для того, кому не выглядит "как что-то криминальное" пихать параметры не в плейсхолдеры — не выглядит как что-то криминальное и генерировать запросы в хранимке.

Если задаться целью — можно и плюшевым мишкой убиться.
Никто не говорит, что совсем нельзя сделать небезопасно. Но в нативной для SQL среде выполнения писать безопасный SQL тупо проще небезопасного.


Если взять python, например, то для человека, который пишет на python, но слабо подкован в вопросе безопасности SQL запросов, написать такой вот код в принципе естественно:
pq.query('select * from table where x={};'.format('f'))


Вместо вот такого:
pq.query('select * from table where x=$1;', ('f',))


Потому что "а чё такова, запрос — это строка". Ну и как бы, тут параметр, тут параметр.


Когда как в хранимой процедуре это разница примерно между вот этим:


language plpgsql
as $$
begin
execute 'select * from table where x = ' || $1;
end;
$$;

И вот этим:


language sql
as $$
select * from table where x = $1;
$$;

Вроде не сильно сложно, но если в запросе генерируется JSON в ответ и один-другой JOIN проскакивает, то разница в сложности подхода уже будет более заметна. Вам нужно специально писать код, который будет во всём хуже.


В случае с такими же запросами из какого-либо языка, сложность написания абсолютно одинаковая что с нативными способами форматирования строк языка, что с плейсхолдерами движка БД.


Плюс БД предоставляет из коробки более простые и удобные механизмы для динамического SQL, если всё таки очень нужно, и при этом не надо идти на компромисс между читабельностью и безопасностью.

А как сделать IN с набором данных, которые пришли из json?

У JSON есть свои операторы для работы с включениями. Не вспомню, какие именно, можете почитать в документации "JSON functions and operators", в разделе для jsonb их побольше даже.

Любые данные из json вы можете сохранить в переменных, и использовать эти переменные в SQL-запросах. Главное — не делать запросы через EXECUTE — тогда и проблем не будет.

в C# генерируется вариант:


WHERE Foo IN ($p1, $p2, $p3, ...) и каждый элемент массива передается отдельным аргументом.

И, кстати, на каком-то количестве (что-то типа нескольких тысяч, например, интов) анализатор квери на стороне DB (в моём случае SQL Server) весело умирал)
Пишем батчинг и радуемся что у нас есть хорошее место для этого и хороший язык для его написания.

Посчитать разность в обе стороны для двух множеств это вообще страдание. Особенно когда одно из них прилетает извне в твою апишку.
В память оба могут и не влезть.
Писать в таблички дорого.
IN падает.
Батчинг с вычисление разности в обе стороны тормозит.
Все плохо.
В случае postgresql в таких случаях можно написать что-то типа (если работать json-параметром внутри которого массив значений)
where xxx in (select cast(x as xxx_type) from jsonb_array_elements(cast($1 as jsonb) -> 'xxx_values') x)
или даже проще (если параметр передать как массив)
where xxx = any ($1)
Последнее, кстати, должно и в других БД с поддержкой массивов работать.

Кстати, oracle при попытке использования более 1000 значений в IN радостно пошлёт.
Если у вас хранимка на входе принимает только json c набором параметров, то какие могут быть поля сортировки? Вся бизнес-логика внутри хранимки, наружу ничего не торчит

в json не может быть поля с именем поля для сортировки?

Нет, конечно. В json нет названий полей, там есть только параметры. Например
{«sort»: «first_name»}

Далее уже в хранимке я обрабатываю этот параметр и учитываю его в SQL-запросе. Но никоим образом не подставляю это значение в SQL-запрос как есть

Но это же не означает, что этого вообще не может быть, и какой-нибудь junior-разработчик так по недосмотру может сделать и в таком случае конкретно тот абзац про безопасность хранимок по сравнению с обычным запросом имеет мало смысла?

Петр, естественно напортачить можно в любой системе, даже очень надежной. Мы говорим не про возможности сделать «дырявую» систему, а возможности сделать надежную систему

Так возможность сделать надёжную систему и в обычном SQL-запросе есть

Я тоже не понял автора про повышение надежности. Было бы лучше, если бы данное утверждение было бы продемонстрировано на конкретных примерах.


Как я понимаю, как можно избежать попадания мусора в БД — это жесткие правила валидации входных данных и принудительное экранирование любых строк, а разработчикам с минимальным опытом давать только интерфейсы которые находятся под соответствующей защитой. Данный тезис я могу хоть как то объяснить.

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

О чём и речь. Суть-то не в технологии, а в правильном её использовании. И в обычных запросах вроде давно уже принято использовать плейсхолдеры параметров (кажется, так это называется?).

В таком тонком вопросе лучше не создавать условий для инъекций, тем более что SQL тоже развивается.

«И разве хранимки каким-то дополнительным образом от этого защищены? „

Все зависит от того, как у вас устроены хранимки. Если там нет исполняемого кода (в который подставляются параметры хранимки), то вариантов инъекции я пока не встречал, на PGConf тоже обсуждали эту тему в применении к хранимкам.
Хм, а разве сейчас какие-то адаптеры к БД не экранируют параметры в SQL запросах, чтобы не сыграл SQL Injection?

Экранирование это 20 век, 21 это подготовленные запросы, на 100% защищённые от инъекций.

Подготовленные запросы бывают ограничены. Например, подготовить запрос для WHERE t.value =? не проблема, а вот для ORDER BY? — проблема

вот так можно
order by case when $1 = 'asc' and $2 = 'id' then id else null end asc,
         case when $1 = 'asc' and $2 = 'parent' then parent else null end asc,
         case when $1 = 'desc' and $2 = 'id' then id else null end desc,
         case when $1 = 'desc' and $2 = 'parent' then parent else null end desc
Не будут использоваться индексы при сортировке