Comments 51
По вашему, вы сделали так, как нужно?
Да, поиск работает, и гораздо быстрее чем изначально.
А как этот ад поддерживать-то и дорабатывать теперь?
Минуса-то аргументируйте…
Мой аргумент — это то чтобы дополнить такой запрос на 2 экрана, надо потратить от получаса времени, чтобы разобраться как он и почему работает и как его исправить. И это надо будет делать КАЖДЫЙ раз при встрече с таким запросом. И протестировать его и отладить по частям неудобно. И план запроса по такой бандуре сложен и непонятен.
Мой аргумент — это то чтобы дополнить такой запрос на 2 экрана, надо потратить от получаса времени, чтобы разобраться как он и почему работает и как его исправить. И это надо будет делать КАЖДЫЙ раз при встрече с таким запросом. И протестировать его и отладить по частям неудобно. И план запроса по такой бандуре сложен и непонятен.
Написать правильный sql запрос — это отдельное искусство, далеко не все программисты (особенно на ruby on rails) способны это сделать. Да и сам фрэймворк и любые ORM не располагают к тому, что бы сочинять сложные sql запросы.
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7Весьма странное место. Зачем нужно проверять даты за год назад?
О да, отличное решение. Давайте грузить базу одним длинным запросом с кучей join'ов, ведь такое решение отлично масштабируется и вообще не испытывает никаких проблем (спойлер: нет).
Когда у вас мало пользователей, то да, вообще никаких проблем. А они возникнут ровно тогда, когда их количество увеличится, и скажем, у вас будут постоянные локи на таблицы. Что вы будете делать тогда с запросом, который будет из-за этого адово тупить?
Я так же напомню, что в postgres даже нет грязного чтения, только repetable read.
Когда у вас мало пользователей, то да, вообще никаких проблем. А они возникнут ровно тогда, когда их количество увеличится, и скажем, у вас будут постоянные локи на таблицы. Что вы будете делать тогда с запросом, который будет из-за этого адово тупить?
Я так же напомню, что в postgres даже нет грязного чтения, только repetable read.
На самом деле автор хотел донести то что он убрал нагрузку с бэкенда и сложил на откуп БД.
С одной стороны на одном и том же железе получился прирост производительности, с другой стороны его решение не оптимально и требует доработки
С одной стороны на одном и том же железе получился прирост производительности, с другой стороны его решение не оптимально и требует доработки
Проблема в том, что база данных и так почти всегда bottleneck. Не понятно, зачем еще больше выносить на нее нагрузку.
Не вся логика должна быть заложена в коде, в крупных продуктах очень много сделано через хранимые процедуры, тем более раз мы оперируем с данными из бд, то сомневаюсь что какой-то язык будет работать быстрее чем нативный SQL
Тот же самый birt-viewer все отчеты строит в БД, потому что он оперирует полученными данными для отображения
Тот же самый birt-viewer все отчеты строит в БД, потому что он оперирует полученными данными для отображения
хранимые процедуры это даже хуже огромных запросов.
И вы серьезно думаете, что например Java медленнее чем pgSQL?
И вы серьезно думаете, что например Java медленнее чем pgSQL?
Вы путаете мягкое с тёплым
Во-первых, sql выполняет именно то для чего он создан, запросы к бд
Во-вторых, приложению сначала надо получит данные из бд, а потом уже с ними выполнять некие действия
В-третьих, мы все знаем что Java очень любит память
Для текущего решения делать выборку средствами БД самое разумное. Но никто не отменял оптимизацию запросов и индексирования таблиц
Я думаю, что для перечисленных вариантов с джойнами база выполнит запрос с меньшим числом операций чем вы это проделаете в своей программе на яве. Либо, вы умеете делать Все оптимизации которые делаются в базе и пишите их каждый раз когда надо вытащить данные. Но даже если так, запрос будет быстрее потому что
— при выполнении можно зачитать данные только из индекса (например для выполнения exists
— не произойдет конфликта согласованности данных только если вы не используете уровень изоляции serializable что дорого
— у базы есть статистика о кардинальности связей и она может применя разные
алгоримы для соединения таблиц
— база кеширует данные таблиц и индексов в памяти
Это просто 4 фактора что на вскидку в голову пришли.
В итоге чтении данных потребуется меньше операций чтения с диска (что на самом деле является единственной проблемой при чтении как в задаче с поиском) причем меньше не в константу раз а лучше.
— при выполнении можно зачитать данные только из индекса (например для выполнения exists
— не произойдет конфликта согласованности данных только если вы не используете уровень изоляции serializable что дорого
— у базы есть статистика о кардинальности связей и она может применя разные
алгоримы для соединения таблиц
— база кеширует данные таблиц и индексов в памяти
Это просто 4 фактора что на вскидку в голову пришли.
В итоге чтении данных потребуется меньше операций чтения с диска (что на самом деле является единственной проблемой при чтении как в задаче с поиском) причем меньше не в константу раз а лучше.
«хранимые процедуры это даже хуже огромных запросов» — вот так просто, как шашкой рубанул. Хуже огромных запросов только кривые руки девелоперов. А процедуры и функции — это то, что надо.
А зачем ему грязное чтение? Он версионник. Конечно, его вакуум — отдельная и больная тема, но с чего бы ему «адово тупить» на локах, когда пишущие не лочат читающих?
Официальная документация с вами не согласна. Локи от update отлично конфликтуют с локами для select.
А нет, это я читаю не тем местом.
Локов нет, но есть нюансы. Например, в Oracle есть знаменитый «Snapshot too old». Ну и select (в Oracle точно, возможно и в PostgreSQL, этот момент не помню), в некоторых случаях может менять данные, но это всё экзотика. В первом приближении, можно считать, что в версионниках читающие не блокируют пишущих и наоборот (для того версионники и придуманы). Причём, в PostgreSQL версионное чтение экстремально дешёвое (дешевле чем в Oracle с его UNDO), поскольку версии не восстанавливаются, а просто читаются с диска и из кэша. Но за это приходится расплачиваться вакуумом.
Подозреваю вы под словами селект может менять данные понимаете его реализацию select for update. При последовательном чтении строк действительно происходит пометка блока с зачитанными строками чтобы можно было реализовать select for update skip locked. Но такой паттерн это реализация очередей как правило и там не требуется апдейтов обычно, и проблем нет. Это про оракл.
А поясните, что значит версии не восстанавливаются и почему чтение радикально дешевле чем в оракле? В оракле есть три уровня изоляции и в пг вроде тоже read commited read dirty и serializable
Третий реально требует поиска блока нужной версии в анду, а read committed позволяет просто найти оригинальную версию блока в анду причем его адрес в самом блоке и записан так что там небольшие потери. А как постгрес действует?
А поясните, что значит версии не восстанавливаются и почему чтение радикально дешевле чем в оракле? В оракле есть три уровня изоляции и в пг вроде тоже read commited read dirty и serializable
Третий реально требует поиска блока нужной версии в анду, а read committed позволяет просто найти оригинальную версию блока в анду причем его адрес в самом блоке и записан так что там небольшие потери. А как постгрес действует?
думаю, под «select в некоторых случаях может менять данные» подразумевается история в pg про обновление хинт битов при первом чтении вставленной строки. суть в том, что pg версионник, то есть под капотом в его таблицах лежат незакомиченные и удаленные строки, которые периодически чистит автовакуум. при выборе, какие строки убить, автовакуум ориентируется на хинт биты (они нужны еще для кучи разных вещей, не только для этого). когда вы начали транзакцию, но еще не закоммитили ее, строка все равно появляется таблице, а в хитн битах у нее пусто. как только транзакция коммитится, информация об этом замечательном факте попадает в clog. к сожалению, понять в момент коммита, в каких строках таблиц транзакция успела поменять данные проблемно, да и часть страничек может быть вытеснена из буферного кеша (а повторно считывать их дорого). поэтому в строках из закомиченной транзакции хинт биты остаются пустыми. а вот как только мы запросим через select одну из таких строк, pg проставит им хинт биты, что вызовет запись на диск при чтении данных. ну а если таких строк было много, то первое чтение может породить серьезную нагрузку на диск за счет вытеснение грязных страничек их общих буферов pg. это порой ставит в тупик не наступавших на эти грабли людей.
Нет, select… for update, это вообще не совсем select, а DML в чистом виде. В том что касается Oracle, я говорил о ситуации, когда честный select просматривает блоки с устаревшими блокировками ранее закрытых транзакций и очищает их, что приводит к записи на диск. Про PostgreSQL вам подробно ответили выше. От себя лишь добавлю, что версионное чтение в Oracle может быть весьма накладным, поскольку включает в себя собственно чтение текущего состояния блока, а затем чтение UNDO, для приведения состояния этого блока на требуемый момент в прошлом. Если UNDO не хватает — получаем ORA-01555. В отличии от Oracle, PostgreSQL хранит исторические данные там же где и текущие и ему, грубо говоря, нет разницы, читать то или другое.
А давайте вы напишете более-менее реалистичный сценарий? Здесь описан поиск и нет никаких UPDATE и уж тем более нет EXCLUSIVE блокировок. Данные только читаются. Адово тупить будет только разве что плохо написанный код.
Я бы поднял Solr/ElasticSearch c денормализованными данными, оптимизированными под нужный вид поиска.
Быть может, это было бы больше кода, связанного с обновлением поискового индекса, но решение точно получилось бы проще и масштабировалось бы лучше.
У нас по такому же принципу поиск организован
Вроде ничего решение, правда оно кажется очень трудоемким по реализации обновления данных, а также объем данных будет очень и очень большим. Кроме того, мы неоднократно встречались с ситуацией, когда какая-то нода отстает в кластере или же индекс совсем развалился. Обычно непросто понять, что произошло. Тут это еще как-то мониторить отдельно нужно. С другой стороны, по нашему опыту PostgreSQL великолепно держит нагрузку. Наше решение позволило «малой кровью» точечно переписать проблемную часть проекта.
Проблема вашего решения в наличии связи между бизнес-требованиями и структурой базы. Это проблемно, т.к.
- при изменении требований возможно придется делать несовместимую миграцию базы (долго и даунтайм)
- может появиться рассинхрон между данными и производными полями. У поискового индекса такой проблемы нет, т.к. его можно быстро перестроить.
- народ может повадиться делать селекты не по данным, а по производным полям, добавленным для поиска. Это значительно увеличит хрупкость системы.
Трудоемкость — это довод, если умеете лепить хитрые запросы и не умеете в индекс. Но на вашем месте я бы как минимум вынес производные поля как минимум в отдельные таблицы.
Запрос, конечно, выглядит монструозно. Саппорт (или следующий разработчик) вас за него помянет добрым словом, когда придется вносить какие-то изменения или искать баги.
Почему было не написать логику в Stored Procedure? Ведь тогда этого монитра можно было бы побить на куски, которые человек был бы способен понять.
Почему было не написать логику в Stored Procedure? Ведь тогда этого монитра можно было бы побить на куски, которые человек был бы способен понять.
Может запрос и выглядит устрашающим, но только если его рассматривать целиком.
На деле он строится из кусков, это всего лишь несколько CTE следующие друг за другом. И в приложении каждое CTE строится отдельно, и более того, они переиспользуются в других подобных запросах.
И если рассматривать запрос отдельно, по шагам, как в статье, то ничего страшного в нем нет.
На деле он строится из кусков, это всего лишь несколько CTE следующие друг за другом. И в приложении каждое CTE строится отдельно, и более того, они переиспользуются в других подобных запросах.
И если рассматривать запрос отдельно, по шагам, как в статье, то ничего страшного в нем нет.
И чем же хранимая процедура упростит внесение изменений или поиск багов? На куски можно побить и в коде приложения с ровно таким же успехом.
А зачем в таблице Properties поля dest_type и dest_id? Почему не сделать place_id и district_id? Или только district_id.
Это так называемая полиморфная связь. Сейчас property можно привязать либо к place, либо к district. Представьте, что появляется третья (четвертая, пятая, ...) сущность к которой можно будет привязать property. При такой связи в структуру таблицы properties не придется вносить изменений.
Как на счёт отдельного инстанстанса Hot Standby (один или даже несколько) для масштабирования производительности такого решения.
Если боретесь за оптимизацию, то конечно надо всю логику переносить в базу данных. При этом, если стоит выбор между хранимыми процедурами и SQL, то однозначно SQL. SQL будет работать быстрее при условии, если вы используете все возможности SQL. Когда боретесь за производительность надо читать планы запросов и понимать где узкие места и что делать в той или иной ситуации. В приведенных запросах без планов тяжело сказать все ли вы выжали, но на вскидку можно сказать что использование DISTINCT или DISTINC ON это уже не эффективно. Есть паттерны как обходиться без DISTINCT. Оптимизированный запрос не значит доступен в понимании другими разработчиками, тем более если он не является программистом БД.
А как обойтись без DISTINCT если нужны уникальные записи?
Например так
WITH RECURSIVE t AS (
(SELECT min(val) AS val FROM test)
UNION ALL
SELECT (SELECT min(val) FROM test WHERE val > t.val)
AS val FROM t WHERE t.val IS NOT NULL
)
SELECT val FROM t WHERE val IS NOT NULL;
(Автор данного запроса Максим Богук.)
WITH RECURSIVE t AS (
(SELECT min(val) AS val FROM test)
UNION ALL
SELECT (SELECT min(val) FROM test WHERE val > t.val)
AS val FROM t WHERE t.val IS NOT NULL
)
SELECT val FROM t WHERE val IS NOT NULL;
(Автор данного запроса Максим Богук.)
Хм, интересно. Я полный нуб в Postgres, не знал что можно так, спасибо
Возник реальный интерес — где и почему может быть запрещён DISTINCT (который максимально оптимизирован в движке БД на языке C++, в противовес интерпретатору T-SQL)?
Никто не говорит, что надо запретить DISTINCT. Если в таблице по какому-то поля имеются дублирующиеся значения, то чтобы отобрать только уникальные значения выполняется полное сканирование всей таблицы. В приведенном выше запросе выборка уникальных значений выполняется с использованием индекса (при условии наличия индекса на поле val), соответственно не надо выполнять полное сканирование таблицы. Но надо понимать что, если данные по этому полю имеют низкую селективность, то индекс работать не будет. Поэтому для правильно оптимизированного запроса надо знать свои данные и понимать где вы можете заставить работать индекс, а где нет. А вообще если данные в выборке дублируются, то чаще всего это недостаточно правильно написан запрос и проще всего естественно написать DISTINCT.
Sign up to leave a comment.
Cила PostgreSQL