Генераторы SQL и ORM в разработке

курсорная пагинация

А расскажите про это подробнее пожалуйста. Не знаю у кого спросить.

Вот про выдачу наружу в клиента открытых курсоров из БД. Неважно каким способом - вот сам факт, что из БД ушёл открытый курсор и где-то там болтается в клиенте - это точно не должно смущать? Не таким ли путём появляются конфигурации с max_connections=100500?

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

Нет. Курсорная пагинация - это пагинация не на основе LIMIT/OFFSET, а на основе фильтрации по индексированному столбцу.

Вот пара ссылок для затравки:

https://web.archive.org/web/20240525011408/https://www2.sqlite.org/cvstrac/wiki?p=ScrollingCursor

https://blog.sequinstream.com/keyset-cursors-not-offsets-for-postgres-pagination/

Это не имеет ничего общего с server/client side курсорами в SQL, которые, в общем, тоже неэффективное средство пагинации.

пагинация не на основе LIMIT/OFFSET, а на основе фильтрации по индексированному столбцу

так оно ничерта не работает же.

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

Как мы ему доставим этот кусок? Или значением в упакованном виде, в том же json? Или ссылкой на курсор/временную таблицу?

Меня как ребята научили json'ами плеваться из процедур - так и делаю. Какой именно магией готовлю нужный кусок и по каким индексам - никого не касается вне БД. Keyset/Offset - фас/профиль.

Но честно - у меня нет никакой теоретической базы почему ребята меня учили именно так. Вот как вариант - чтобы быстро закрывать соединения. Отдал json - и забыл. Пусть там бэк дальше сам складывает в свой Редис готовое, если считает нужным.

из БД ушёл открытый курсор и где-то там болтается в клиенте - это точно не должно смущать?

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

Люди добрые, скажите, а squirrel еще используется при работе с БД

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

Ну и тот косяк, что он GPL, а в go зачастую MIT. Тоже плюсов ему не добавляет.

так оно ничерта не работает же.

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

Когда у нас при миграции с версии на версию в Постгресе отвалились те самые автоинкрементные поля, пришлось вспоминать как это в Оракле делают и переписывать.

А проблема с долгими транзакциями (точнее, ответом) решается выбором профиля соединения (shared или dedicated) перед запуском. Например, служебные/быстрые действия через пул, а долгий аналитический запрос - через выделенное соединение. А если клиент зашел через шаред, то ведь можно инициировать долгое задание и закрыть соединение. Т.е. можно эмулировать на уровне БД.

Вся фишка последовательностей на уровне БД в глобальности и при этом в хорошей производительности.

А что не так с триггерами? :) Можно, например, защитить действия при коннекте юзера.

решается выбором профиля соединения (shared или dedicated) перед запуском

А это что ещё за хрень? Не может быть, чтобы это был уровень БД. Вот как раз всякой подобной чепухой бэк и занимается. С собственным пулом соединений, их распределением по своим клиентам, кэшированием ответов.

Же.

Т.е. можно эмулировать на уровне БД.

можно, на Оракле …

ну хорошо, давайте ситуацию разберем чуть более подробно.

1. стабильности результатов без создания слепка добиться никак не получится, т.е. вполне ожидаемо, что потребитель результатов может наблюдать как и пропуски (когда что-то удалили пока он листал), так и дубликаты (когда что-то добавили пока он листал)
2. типичный пользователь на одной странице выхлопа может более-менее достойно оценить взглядом ну от силы 50-100 строк, точно не больше (даже насчет 50 возникают сомнения, например, гугл сейчас выдает по 10 результатов на страницу, хотя готов поклясться, что раньше было больше)
3. дальше пятой страницы тот же типичный пользователь просто не пойдет, т.е. реальность такова, что:

• либо мы имеем дело с реальной потребностью выгрузить все, и тут мы должны предоставить такую возможность без всякого дрочева с пагинацией
• либо мы в UI наводим «красоту» за счет задрачивания базы, а в реальности все наши приседания нужны исключительно радо того, чтобы какой-то crawler не ложил БД

итог простой: выгружайте слепок 1000 записей вместо 50, храните их где угодно, и не имейте никому мозг (я тут могу еще накидать идей про то, что нужно еще специальные индексы делать для такой пагинации, из-за чего БД будет страдать, но пока и вышеизложенного достаточно на мой взгляд)

А что не так с триггерами? :)

триггеры полностью убивают возможности кеширования со стороны приклада. т.е. если я делаю insert/update, то я ожидаю, что состояние БД будет синхронизировано с кешем в приложении, как только это становится неправдой (где-то посередине мои данные меняют), мне приходится после insert/update делать еще select

Когда у нас при миграции с версии на версию в Постгресе отвалились те самые автоинкрементные поля, пришлось вспоминать как это в Оракле делают и переписывать.

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

Не, это норм.

PGSql, T-SQL

- вот оба надо знать. И ещё, на T-SQL у тебя оптимизатор такой офигенский, ты ему всякий бред пишешь, а он нормально отрабатывает. А в PG ты узкие места ищешь. Ну и переменные в T-SQL прям в запросах хороши, без обычных костылей PostGres. ORM тебе не будет делать более оптимальный запрос для постгреса и не будет переносить костыли (подзапросы к переменным либо выполнение как процедуры) для переменных в постгрес, а использует переменные на клиентской части.

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

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

итог простой: выгружайте слепок 1000 записей вместо 50, храните их где угодно

Допустим, у меня в час пик может быть 500,000 одновременных пользователей. И у каждого своя выборка. Мне кажется, от 500,000 слепков поплохеет чему угодно.

мода нынче ... вместо межпрограммного взаимодействия ОС HTTP

Гыгы. В первом европейском стандарте коммуникации силовой зарядки электромобилей постоянным током вместо CAN или подобного поднимается ethernet powerline, поднимается httpd, и обмен фигачится через богомерзкий SOAP (если не в курсе - это из конца 90-х, вместо нынешних API+JSON все те же словари-структуры клали в XML, паковали в zip и отправляли по http). Стандарту лет 15, наверно. И да, контакты powerline ни для чего кроме связи не используются.

Допустим, у меня в час пик может быть 500,000 одновременных пользователей. И у каждого своя выборка. Мне кажется, от 500,000 слепков поплохеет чему угодно.

ну там слепок не на бэке, и не в БД, а у конечного пользователя, т.е. попросили 50, мы выгрузили 1000 (там на бэке потери между 50 и 1000 совершенно несущественные), 950 у него где-то в памяти браузера тусуются, а вот если пользователь-таки надумает дальше 20-й страницы идти, то да, придется поднапрячься (т.е. почти никогда)

ну там слепок не на бэке, и не в БД, а у конечного пользователя, т.е. попросили 50, мы выгрузили 1000 (там на бэке потери между 50 и 1000 совершенно несущественные), 950 у него где-то в памяти браузера тусуются

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

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

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

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

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

И ничего не поделаешь. Спорить и объяснять нередко оказывается бесполезно - жираф большой, ему видней.

Мне кажется, от 500,000 слепков поплохеет чему угодно.

Слепок это 1000 ID. То есть 500,000 слепков это 500 миллионов индексов = 4 гигабайта. Если на сервере 500,000 одновременных пользователей, то 4 ГБ ОЗУ под кэш почти ни о чём.

И даже 4 ГБ кэш на диске лучше, чем повторный запрос миллион раз в минуту.

Поэтому надо для пагинции делать отдельный столбец и выбирать запросом вида where page_index between 2000 and 2100. Если она нужна нормальная, а не для вида.

Почему я не могу долистать до 30-й страницы?!!!!

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

Не, это норм.

Не, норм - это когда у тебя под руками всегда твоя БД. И ты несешь ответственность в этой зоне. И ты сам решаешь, сколько тебе нужно dev-stage-prod и кого туда пускать, и как между ними мигрировать.

Ты - такое Мама данных. Или кладовщик. Или каптёр со своей каптёркой.

А когда наведенный порядок в твоей каптёрке или детишек надо отдавать каким-то левым перцам за тридевять земель - нифига не норм. Не умею так работать. ( Пусть берут ОРМ и трахаются сами.

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

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

• семью
• любовниц
• непосредственных подчиненных
• прочие кружки члена

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

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

либо мы в UI наводим «красоту» за счет задрачивания базы, а в реальности все наши приседания нужны исключительно радо того, чтобы какой-то crawler не ложил БД

Я давно далёк от web фронтенда, но это у тебя как-то совсем web.0.9: по-моему мы грузим сначала то, что просит клиент, в фоне подгружаем то, что ему понадобится, против краулера задержки на бэке, всё разумно. Ну и страницы листаются как в вконтакте - скролл выкидывает из буфера первые страницы, подгружает следующие (чтобы браузер не охреневал от непрерывно быстрорастущего буфера), номер странички на пагинаторе переключается.

давно (больше века) как построенно и продолжается убигдатыватся:

https://www.google.com/search?q=Agar - The government machine[RevolutionaryHi...

Может, может. Два варианта подключения, можно активировать оба. Размер карусели тоже регулируется. При работе через шаред-подключение у пользователя иллюзия непрерывного коннекта. Ещё, шаред-подключение может принимать и http-запрос, но производительность http-листенера под вопросом. Вся эта кухня со времён моего знакомства с ораклом - 8.7i :)

Ну, тут зависит от того, где центр приложения. Среднего звена вообще может не быть :) Можно закешировать на слое хранимок. Но если серъёзно, то во многих случаях без триггеров не обеспечить железную надежность - пример с реализацией автогенерации через последовательность уже называли. +есть же триггеры не только на манипуляции с данными, но и на другие события (хотя, речь конечно о данных).

В таком случае, можем сильно потерять на latency ответов

может можем, а может и не можем… кто его знает-то.

вот тут я часть какой-то работы уже сделал и описал довольно большую часть сценариев, в которых идеи о монотонном ключе не выдерживают какой-либо критики, давайте я остаток еще поделю на 2 или на 4:

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

• а эта стратегия позволяет прыгать через страницы? нет, не позволяет, там придется так или иначе лепить все эти пресловутые limit/offset
• а эта стратегия позволяет ходить назад? тоже не позволяет - либо опять же лепить limit/offset, либо хранить слепки

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

И да и нет. «Универсальный» ORM беднее чем нативные SQL.

Путь на самом деле такой:

1. ORM.
2. Через год, много где голый SQL.
3. Через год, Свой ORM. Но не просто отрицание ORM а со снятием, удобно встроен в код, нет лишнего копирования.
4. Через год, Свой ORM + Частично кое-где голый SQL.
5. Через год, Свой допиленный ORM.
6. -> 4

Да ну, ерунда какая-то. Существующие ORM, которым уже много лет, обычно имеют в себе кучу всего. В том числе, расширения под конкретные СУБД. Надо тебе CTE, Window Functions, JSONB, и т.д. и т.п., обычно всё это в зрелом ORM уже есть.

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

Но одному разработчику показалось, что существующий ORM работает «недостаточно быстро». Учитывая вышесказанное, конечно же, без цифр. И нигде к системе даже не предъявляются требования по производительности, чтобы можно было сказать: «Нам надо уложиться в X минут, а у нас работает Y минут, где Y > X».

Всё это решение и обсуждение происходило без меня. Я же столкнулся с адским query builder’ом на строках, в котором всё прибито гвоздями, который невозможно понять (документации, примеров, тестов - нет, конечно же). Который ломается на каждом шагу. Добавишь колонку в таблицу, он ломается. Удалишь колонку - тоже ломается. Потому что там всё примитивно, захардкожено жёстко.

И вот с этим говном, приходится работать, теряя не минуты, не часы, а дни и недели времени. Хотя существуют нормальные ORM, которые можно было использовать. А не нравится производительность - можно было бы хотя бы готовый Query Builder взять.

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

Другого варианта я просто не вижу. Потому что опыт без ORM на чём-либо минимально сложном - это ад и Израиль.

… а потом, внезапно, оказывается, что ты пишешь «свой орм» фултайм, а основной продукт - на подсосе. и молись, чтобы собственник этого не узнал

Более предметно. К примеру дано такое. (Код не мой ИИ нагенерил)

package main

import (
    "gorm.io/gorm"
    "gorm.io/driver/postgres"
)

type User struct {
    ID    uint
    Name  string
    Age   int
    City  string
}

func main() {
    dsn := "host=localhost user=gorm password=gorm dbname=gorm port=9920 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    // GROUP BY с подсчетом количества
    var results []struct {
        City  string
        Count int
    }
    
    db.Model(&User{}).
        Select("city, COUNT(*) as count").
        Group("city").
        Find(&results)
}

Все хорошо и понятно. И работает. Появляется задача. Теперь Name это структура.

// Структура для имени
type FullName struct {
    Title     string `gorm:"size:10"`  // Mr, Mrs, Dr
    FirstName string `gorm:"size:100"`
    LastName  string `gorm:"size:100"`
    MiddleName string `gorm:"size:100"`
}

И вот тут, уже надо писать свой. Value() (driver.Value, error) А дальше больше.

За такое как эта FullName бьют. Анна Мария Хорхе Сантино Лопес: где тут FirstName, MiddleName и LastName?

Использование чужого ОРМ не запрещает где неудобно его не использовать, а писать запросы. Но при этом удобства сохранются.

Такого чтобы писали свой ОРМ давно не видел, ну то есть 15 лет назад, да это было модно, сейчас есть все готовое.

Другого варианта я просто не вижу.

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

Нюанс в том, что тогда самому этому человеку неудобно. )

Но вариант же.