Зато без изврата.
Всё имеет свою цену. Если у вас вот прям такая задача стоит, то логично использовать соответствующие инструменты. Или не использовать Postgres совсем, а например Elasticsearch.
Описанное Маркусом наивное решение подходит лишь для самых простых случаев, когда сортировки прибиты гвоздями в коде. Мне наверное так повезло, но все системы, в разработке которых я участвовал последние лет 20 (финансовая аналитика, страхование, банки и проч.), поддерживали множественные сортировки и фильтры по всем полям в гридах, во всех экранных формах. Юзер может кликать в заголовки колонок в гриде и выбирать одно или сразу несколько полей для сортировки, и менять её направление.
Это всегда динамический SQL, и значит индексы для чтения с диска "сразу в правильном порядке" тут не работают. Но при грамотном дизайне хранилища эта проблема решается. Идея в том, что индексы должны отработать раньше, еще до сортировок и лимитов, и это обычно какие-то особые хитрые индексы по всяким служебным полям в таблицах/mat. views.
В первую очередь большую БД хорошо бы распилить на мелкие кусочки и разнести их физически. Это сокращает размеры сегментов таблиц и высоту индексов, повышает степень параллелизма. Сначала оптимизатор выбирает ноду/шард/партицию, потом по комбинации индексов (partial/functional индексы в сочетании с бинарными рулят) получается уже небольшой кусок данных (ну например десятки тысяч строк), и потом только выполняются сортировки этого ограниченного набора — строго в оперативной памяти, это быстро. Иногда можно помочь оптимизатору избежать дисковой сортировки, подкрутив размер рабочей памяти у конкретных запросов. При скроллинге грида следующие порции выдаются повторными запросами быстрее, т.к. данные кешируются в буферах СУБД и ОС.
В статье указан тег PostgreSQL, соответственно про долгоиграющие транзакции с открыми курсорами можно забыть. Это связано с особенностями реализации MVCC и возможным распуханием таблиц и индексов. С Ораклом вроде бы должно прокатить, потому что там MVCC работает совсем по другому, через Undo сегменты. Но сейчас мало кто делает монолиты, уж точно не Тинькофф, значит каждый запрос к back-end (дай следующую порцию данных из курсора) направляется балансировщиком к разным нодам/репликам. Получается, никаких долгоживущих курсоров. Но в теории да, курсоры были бы тут очень кстати, особенно вкупе с уровнем изоляции Repeatable Read, чтоб исключить большинство аномалий чтения.
Да абсолютно незачем, согласен!
Но многие проекты, как известно, всё ещё не переехали даже на Java 11 LTS. Следующая же LTS версия 17 ожидается лишь в сентябре 2021.
Так что Lombok хоронить ещё рановато, и к тому же там куча других плюшек.
Здорово, что язык постоянно развивается и адаптируется под современные реалии!
Однако, с тех пор, как все поголовно начали использовать проект Lombok, многословность Java перестала являться проблемой. Теперь можно просто получать удовольствие, не заморачиваясь генерацией всего этого мусора, который только захламляет код и отвлекает от главного. Описанное в статье решается там одной аннотацией @Value.
Нужно и то и другое. Суррогатные ключи для ссылок между сущностями. Естественные ключи для констрейнтов, обеспечивающих идентификацию с точки зрения бизнеса, и только для тех сущностей где они действительно нужны.
Кажется, мы ходим кругами, и значит пора закрывать эту ветку.
Конечно, ничего переконвертировать не надо, как раз для этого и были придуманы суррогатные численные ключи, и рекомендованы к использованию как дизайн паттерн. Но при этом нужно понимать, что это локальные числа, и при миграции они легко могут меняться и это нормально, т.к. сами по себе они не имеют смысловой нагрузки. И ни в коем случае на них не следует навешивать какую либо логику. Иначе вам потребуется какой то умный генератор осмысленных идентификаторов, а не просто сиквенс.
Вы решили упростить себе жизнь при написании тестов, захардкодили айдишники? Имеете полное право, хозяин-барин как говорится. Но только не нужно, пожалуйста, утверждать что этот подход имеет право на жизнь в прочих условиях, и особенно когда речь касается продакшна.
Вы совершенно правы в том, что использование естественных ключей сложнее, так как они часто бывают составными и это требует больше кода. Но вот "зыбкой почвой" и моветоном как раз таки считается использование айдишников. Ох, не зря звенел звоночек :)
"Зачем?" — вы спрашиваете. Хотя бы для того, чтобы не городить эту синхронизацию сиквенсов. Для вашего кейса (нагрузочное тестирование) это может быть и не так важно, и действительно можно было бы закрыть глаза на это (хотя я бы такую имплементацию даже для тестов зарубил на корню на код-ревью). Но я видел как люди такое пихают в продакшн. Это просто недопустимо. Признайте хотя бы это :)
Вот именно, в том то и дело, что новые данные совсем не должны совпадать по ключам, потому что сами по себе эти ключи не несут никакой смысловой нагрузки, как мы с вами сказали выше. Совпадать должны значащие с точки зрения бизнеса атрибуты.
Хотя, конечно, программисту, который пишет тестовые сценарии, проверки по ID запилить намного проще. Однако, это является антипаттерном, и за это кое-где могут сделать атата. А как же быть? Да просто посчитать агрегаты по суммам, количеству и т.п. и проверять эти числа вместо ID.
А нельзя ли в таком случае разделить ответственность и назначить один из узлов мастером, ответственным за генерацию ID? И в принципе не суть важно откуда в нём берутся новые значения. Сиквенс подойдёт, конечно, лучше всего, поскольку мы говорим о БД.
Меня тоже немного смущает этот пост, каким-то запашком веет. Если вы решаете подобную задачу, то в вашей голове сразу должен зазвенеть звоночек: возможно, здесь что то не так с дизайном...
Суррогатные авто-инкрементные ключи нужны для обеспечения ссылочной целостности и только. Никакого смысла с точки зрения бизнеса они не должны нести, и соответственно их можно при миграции менять как угодно и генерировать по любому алгоритму: хоть со знаком "минус", хоть по убыванию, хоть с пропусками по 100500 тыщ.
Что, кстати, бывает очень полезно при использовании параллельных вставок с большого количества сессий, с предварительным кешированием ID порциями. При этом вставленные позже записи совсем не обязательно получают больший ID.
Вау, и правда, MSSQL так умеет делать, я погуглил. Спасибо за информацию )
Остальные основные игроки так не умеют. Postgres (топик о нём) до последней 12 версии всегда материализует результаты запроса в CTE, то есть удалять что-то из такой "как бы временной таблицы" в принципе бесполезная идея. Oracle и MySQL тоже этого не разрешают.
1) Из CTE действительно нельзя ничего удалять, то есть придется переписать ваш запрос так, чтобы удаление происходило из «t», что в итоге сведется к моему варианту, с тем лишь отличием, что мы будем использовать оконную функцию вместо «group by».
2) Этот вопрос предназначен для джунов и студентов-интернов, которые как правило даже и не слыхали об оконных функциях. А если и слыхали, то как правило, у них есть лишь небольшой опыт с MySQL, в котором отсутствует ROW_NUMBER().
3) Если сравнить планы выполнения, то окажется что вариант с простой группировкой намного проще и работает намного быстрее по сравнению с оконной функцией. При условии, что в таблице несколько миллионов записей, с оконной функцией вы просто устанете ждать окончания, не говоря о потраченных впустую ресурсах. А с простой группировкой это сводится к двум seq scan, и работает очень быстро.
Это очень похоже на один из вопросов, которые я всегда задаю на собеседованиях на уровень Junior Software Developer. Кандидат обязан знать основы языка SQL.
Примерная формулировка: есть таблица "t" с полями id, a, b, c. Нужно удалить записи с дублями по комбинации (a,b), оставив самые ранние записи. Для простоты предполагается, что id это суррогатный ключ, наполняемый из сиквенса, и его значения всегда растут.
Ответ:
delete from t where id not in (select min(id) from t group by a, b).
А разве это может защитить от слежки операторами мобильной связи? Триангуляция по вышкам все равно будет работать. В статье как раз написано что они являются основными поставщиками данных.
То есть если у вас есть обезличенная симка и старинный кнопочный телефон — то этого как бы уже достаточно. В крупных городах плотность вышек такова, что точность определения может быть близка к GPS.
Зато без изврата.
Всё имеет свою цену. Если у вас вот прям такая задача стоит, то логично использовать соответствующие инструменты. Или не использовать Postgres совсем, а например Elasticsearch.
А как насчёт FTS + GIN?
Описанное Маркусом наивное решение подходит лишь для самых простых случаев, когда сортировки прибиты гвоздями в коде. Мне наверное так повезло, но все системы, в разработке которых я участвовал последние лет 20 (финансовая аналитика, страхование, банки и проч.), поддерживали множественные сортировки и фильтры по всем полям в гридах, во всех экранных формах. Юзер может кликать в заголовки колонок в гриде и выбирать одно или сразу несколько полей для сортировки, и менять её направление.
Это всегда динамический SQL, и значит индексы для чтения с диска "сразу в правильном порядке" тут не работают. Но при грамотном дизайне хранилища эта проблема решается. Идея в том, что индексы должны отработать раньше, еще до сортировок и лимитов, и это обычно какие-то особые хитрые индексы по всяким служебным полям в таблицах/mat. views.
В первую очередь большую БД хорошо бы распилить на мелкие кусочки и разнести их физически. Это сокращает размеры сегментов таблиц и высоту индексов, повышает степень параллелизма. Сначала оптимизатор выбирает ноду/шард/партицию, потом по комбинации индексов (partial/functional индексы в сочетании с бинарными рулят) получается уже небольшой кусок данных (ну например десятки тысяч строк), и потом только выполняются сортировки этого ограниченного набора — строго в оперативной памяти, это быстро. Иногда можно помочь оптимизатору избежать дисковой сортировки, подкрутив размер рабочей памяти у конкретных запросов. При скроллинге грида следующие порции выдаются повторными запросами быстрее, т.к. данные кешируются в буферах СУБД и ОС.
В статье указан тег PostgreSQL, соответственно про долгоиграющие транзакции с открыми курсорами можно забыть. Это связано с особенностями реализации MVCC и возможным распуханием таблиц и индексов. С Ораклом вроде бы должно прокатить, потому что там MVCC работает совсем по другому, через Undo сегменты. Но сейчас мало кто делает монолиты, уж точно не Тинькофф, значит каждый запрос к back-end (дай следующую порцию данных из курсора) направляется балансировщиком к разным нодам/репликам. Получается, никаких долгоживущих курсоров. Но в теории да, курсоры были бы тут очень кстати, особенно вкупе с уровнем изоляции Repeatable Read, чтоб исключить большинство аномалий чтения.
Да абсолютно незачем, согласен!
Но многие проекты, как известно, всё ещё не переехали даже на Java 11 LTS. Следующая же LTS версия 17 ожидается лишь в сентябре 2021.
Так что Lombok хоронить ещё рановато, и к тому же там куча других плюшек.
Однако, с тех пор, как все поголовно начали использовать проект Lombok, многословность Java перестала являться проблемой. Теперь можно просто получать удовольствие, не заморачиваясь генерацией всего этого мусора, который только захламляет код и отвлекает от главного. Описанное в статье решается там одной аннотацией @Value.
Нужно и то и другое. Суррогатные ключи для ссылок между сущностями. Естественные ключи для констрейнтов, обеспечивающих идентификацию с точки зрения бизнеса, и только для тех сущностей где они действительно нужны.
Кажется, мы ходим кругами, и значит пора закрывать эту ветку.
Конечно, ничего переконвертировать не надо, как раз для этого и были придуманы суррогатные численные ключи, и рекомендованы к использованию как дизайн паттерн. Но при этом нужно понимать, что это локальные числа, и при миграции они легко могут меняться и это нормально, т.к. сами по себе они не имеют смысловой нагрузки. И ни в коем случае на них не следует навешивать какую либо логику. Иначе вам потребуется какой то умный генератор осмысленных идентификаторов, а не просто сиквенс.
Вы решили упростить себе жизнь при написании тестов, захардкодили айдишники? Имеете полное право, хозяин-барин как говорится. Но только не нужно, пожалуйста, утверждать что этот подход имеет право на жизнь в прочих условиях, и особенно когда речь касается продакшна.
Вы совершенно правы в том, что использование естественных ключей сложнее, так как они часто бывают составными и это требует больше кода. Но вот "зыбкой почвой" и моветоном как раз таки считается использование айдишников. Ох, не зря звенел звоночек :)
"Зачем?" — вы спрашиваете. Хотя бы для того, чтобы не городить эту синхронизацию сиквенсов. Для вашего кейса (нагрузочное тестирование) это может быть и не так важно, и действительно можно было бы закрыть глаза на это (хотя я бы такую имплементацию даже для тестов зарубил на корню на код-ревью). Но я видел как люди такое пихают в продакшн. Это просто недопустимо. Признайте хотя бы это :)
А должно было быть так: "дай мне наименование счета с номером АБВ-12345-001"
Вот именно, в том то и дело, что новые данные совсем не должны совпадать по ключам, потому что сами по себе эти ключи не несут никакой смысловой нагрузки, как мы с вами сказали выше. Совпадать должны значащие с точки зрения бизнеса атрибуты.
Хотя, конечно, программисту, который пишет тестовые сценарии, проверки по ID запилить намного проще. Однако, это является антипаттерном, и за это кое-где могут сделать атата. А как же быть? Да просто посчитать агрегаты по суммам, количеству и т.п. и проверять эти числа вместо ID.
А нельзя ли в таком случае разделить ответственность и назначить один из узлов мастером, ответственным за генерацию ID? И в принципе не суть важно откуда в нём берутся новые значения. Сиквенс подойдёт, конечно, лучше всего, поскольку мы говорим о БД.
Меня тоже немного смущает этот пост, каким-то запашком веет. Если вы решаете подобную задачу, то в вашей голове сразу должен зазвенеть звоночек: возможно, здесь что то не так с дизайном...
Суррогатные авто-инкрементные ключи нужны для обеспечения ссылочной целостности и только. Никакого смысла с точки зрения бизнеса они не должны нести, и соответственно их можно при миграции менять как угодно и генерировать по любому алгоритму: хоть со знаком "минус", хоть по убыванию, хоть с пропусками по 100500 тыщ.
Что, кстати, бывает очень полезно при использовании параллельных вставок с большого количества сессий, с предварительным кешированием ID порциями. При этом вставленные позже записи совсем не обязательно получают больший ID.
www.depesz.com/2020/01/07/finally-fixed-time-calculations-for-parallel-plans-in-explain-depesz-com
Вау, и правда, MSSQL так умеет делать, я погуглил. Спасибо за информацию )
Остальные основные игроки так не умеют. Postgres (топик о нём) до последней 12 версии всегда материализует результаты запроса в CTE, то есть удалять что-то из такой "как бы временной таблицы" в принципе бесполезная идея. Oracle и MySQL тоже этого не разрешают.
2) Этот вопрос предназначен для джунов и студентов-интернов, которые как правило даже и не слыхали об оконных функциях. А если и слыхали, то как правило, у них есть лишь небольшой опыт с MySQL, в котором отсутствует ROW_NUMBER().
3) Если сравнить планы выполнения, то окажется что вариант с простой группировкой намного проще и работает намного быстрее по сравнению с оконной функцией. При условии, что в таблице несколько миллионов записей, с оконной функцией вы просто устанете ждать окончания, не говоря о потраченных впустую ресурсах. А с простой группировкой это сводится к двум seq scan, и работает очень быстро.
А я всегда думал, что спринговая цепочка фильтров реализует паттерн "Chain of Responsibility". Разве нет?
Спасибо что поделились! Очень поучительно.
А почему у вас апгрейд на 11 версию постоянно упоминается в негативном ключе? Неужели настолько обратно несовместимо? В чём именно?
А если сразу на 12? Вот это реально даёт кучу ништяков, или снова нет?
Это очень похоже на один из вопросов, которые я всегда задаю на собеседованиях на уровень Junior Software Developer. Кандидат обязан знать основы языка SQL.
Примерная формулировка: есть таблица "t" с полями id, a, b, c. Нужно удалить записи с дублями по комбинации (a,b), оставив самые ранние записи. Для простоты предполагается, что id это суррогатный ключ, наполняемый из сиквенса, и его значения всегда растут.
Ответ:
delete from t where id not in (select min(id) from t group by a, b).
А разве это может защитить от слежки операторами мобильной связи? Триангуляция по вышкам все равно будет работать. В статье как раз написано что они являются основными поставщиками данных.
То есть если у вас есть обезличенная симка и старинный кнопочный телефон — то этого как бы уже достаточно. В крупных городах плотность вышек такова, что точность определения может быть близка к GPS.