Comments 177
EXPLAIN ANALYZE DROP TABLE POSTGRES
Данные стараются не удалять
На моей практике Explain применялся к select
Для drop вижу первый раз
Тогда странно что нет ответа на классику
Delete drop truncate в чем разница
Вопрос как по мне чистый троллинг, а за инфу спасибо!)
А вот ни разу не троллинг вопрос про разницу между
--такой операцией
delete from table_a;
--и такой
truncate table table_a;
Это такой уже совсем букварь, что даже спрашивать неприлично.
Отвечу на Ваш вопрос как только прочитаю доку =)
По delete from
По truncate
delete - построковое удаление
truncate - внутренний механизм удаления массива строк данных с чисткой garbage
drop - удаление всей таблицы
Уж очень просто для шпаргалки. Понятно, что собеседование на питониста, а не на БДшника. Но, имхо, если в резюме хотя бы косвенно включено sql, то такие вещи должны быть на уровне таблицы умножения.
Хз, работаю аналитиком данных больше 2ух лет. Знаю хорошо SQL для аналитика (могу собрать любой стат тест используя SQL только). Но ни одной вещи из выше не использовал в работе.
Эти знания избыточны для некоторых профессий, где нужен SQL. Это как разработчика просить помнить работу специфичных аналитических функций sql, которые разработчик использует в жизни считанное число раз.
Понимаете, современное собеседование оно не про работу. Это что-то вроде викторины или спортивного состязания среди пары сотен кандидатов, с главным призом в виде трудоустройства.
Я был на гос службе и все примерно так как ты и говоришь)
Я подразумеваю, что данная информация станет точкой для дальнейшего более углубленного изучения
А не просто стишком для заучивания
С другой стороны тогда мне не нужно было называть статью шпаргалкой)
В любом случае спасибо за комментарий)
К счастью, есть и настоящие собеседования, и реальные коллективы разработчиков с реальными задачами и требованиями.
Как бы вот их только научиться сходу распознавать, ещё не вляпавшись в викторину.
Тоже соглашусь, эти вопросы исключительно из собесов на практике только оптимизация особо нужна
А нормализация? Основа архитектуры БД.
Последний раз 3НФ базу видел в ВУЗе, лет 20 назад. Слово нормализация довелось слышать за это время раз 10 максимум. На вопрос бы не ответил. Программирую и БД модели делаю постоянно. Что со мной не так?
Вот это и не так, что делая БД модели постоянно вы ни разу не увидели 3НФ.
Не так - использование СУБД, где ненормальные формы использовать проще и быстрее, чем нормальные.
Программирую и БД модели делаю постоянно. Что со мной не так?
У меня батя делал мебель, и называл шканты "чопиками". Это ему не мешало пользоваться шкантами, просто он не называл их правильно.
Хотите сказать, что аналитик данных не должен знать про транзакции и нормализацию?
Знаю хорошо SQL для аналитика (могу собрать любой стат тест используя SQL только). Но ни одной вещи из выше не использовал в работе.
Не использовали ни одной или не знаете ни одной?
Вы не знаете зачем нормализация, хотя работаете с базами в которых есть сложные выборки? Вы собираете статистику и не в курсе что могут прийти некорректные данные из-за записи в бд во время сбора данных? Вы агрегируете статистику и не в курсе про индексы? Собирая статистику Вы не в курсе про оптимизацию запросов? Ну право слово, Вы серьезно?
Эти знания избыточны для некоторых профессий, где нужен SQL. Это как разработчика просить помнить работу специфичных аналитических функций sql, которые разработчик использует в жизни считанное число раз.
Так в том и дело, что в шпаргалке нет ни одной специфической функции. Это как включить в резюме знание математики и не знать таблицу умножения. Типа - я математику знаю, но в специфичных вещах не разбирался, ага.
Ну не знаю, я работаю с SQL since 1999-го постоянно. И я уже забыл, что нормализация зовется нормализацией :-)
Для меня нормализованная БД как здоровое дыхание.
Для меня нормализованная БД как здоровое дыхание.
"Мой дядя администратор ДБ и тут не все так однозначно"© после нормализации - денормализация вполне логичный следующий шаг в некоторых ситуациях :)
Нубский вопрос про нормализацию и индексы: а аналитику разрешено изменять структуру БД? Добавлять свои индексы, если ему потребуется?
Аналитик может создавать временные денормализованные таблицы.
Обычно аналитик пишет очень-ужасный-запрос, который либо рушит БД целиком, либо сильно просаживаёт производительность. Во втором акте ДБА находит злодея. В третьем ДБА, аналитик и (если есть) разработчик БД соображают, чего бы им сделать, чтобы и бизнесу аналитика была, и простоев (или деградаций) не было.
Аналитиков просто надо сразу отсаживать на свою реплику
Это ОЧЕНЬ дорого. Датамарты им нарезать, и пусть балуются.
Это становится дорого когда это превышает 50-60Tb данных, до тех времен это гораздо дешевле чем работа DBA + DevOPS + Monitoring engineer которые следят за тем что бы их запросы не рушили продакшн.
На больших базах там да, уже проблемы. Но большинство проектов которым уже требуются аналитики с их выборками много меньше данных имеют.
ну нормальные аналитические субд позволяют разным ролям разные ресурсные приоритеты задавать, так что любой запрос от аналитика не отъест больше X% ресурсов субд.
Это да, но разговор то шел не об аналитических субд, а о субд для продакшна. В аналитических субд если аналитик1 затормозит запрос аналитика2 они могут выяснить свои отношения на ножах. А если в продакшн системе аналитик залочит базу в момент когда пришло много пользователей с рекламы, то бюджет уже будет потрачен без прибыли.
Только надо помнить что одна из стратегий репликации — "пока транзакция не реплицировалась на все реплики, она не может быть закомичена", и её нельзя использовать для аналитической реплики.
Узнайте в своей компании
"Так в том и дело, что в шпаргалке нет ни одной специфической функции. Это как включить в резюме знание математики и не знать таблицу умножения. Типа - я математику знаю, но в специфичных вещах не разбирался, ага."
Вот, кстати, у вас ненарочно очень хорошая аналогия получилась, которая показывает, в чём заключается обсуждаемая проблема. Потому что на самом деле, если человек знает, что такое умножение и как оно работает, ему никакая таблица не нужна.
А если спрашивать его "На заднике тетрадей сейчас печатают таблицу умножения на 1 или сразу начинают с 2? А на 10 печатают?", и по его ответам делать вывод о том, знает ли он математику, то получится такое вот "современное собеседование".
Так и в этой "шпаргалке" в основном представлены лишь договорённости седых профессоров Computer Science о том, что и как они будут между собой называть, и сколько концепций выделять. Знание/не знание этих вещей никак не отвечает на вопрос "вот если прямо сейчас дать этому человеку кривую БД - он её сможет починить?". Корреляция, безусловно, какая-никакая есть, но вряд ли достаточная.
ничего удивительного. Для аналитиков и бэкендеров в работе нужны разные компетенции СУБД. Для бэкендера выше перечисленные пункты из статьи - знать действительно важно. Транзакции, аномалии при конкурентых запросах, локи, дедлоки, как их избегать, какие индексы и для чего - все это важно понимать для бэкендера. Не понимая таких вещей можно написать код, который будет в принципе работать в дев-среде, но на проде при конкуретных запросах будут ловиться критические баги периодически, например ловить lost update. Или например, пришел на проект а там на БД уровень изоляции выставлен Repeatable Read - нужно понимать, что это, от этого зависит, как ты будешь писать код в некоторых кейсах. Т.е. все это не теоретические вопросы, а вполне себе практические важные вещи.
В статье еще не сказано про MVCC и vacuum - тоже важные штуки, которые нужно понимать.
А какие знания нужны для аналитика ?
которые разработчик использует в жизни считанное число раз
И чаще всего это 1-2 раза, при первом знакомстве с функцией :)
Про план запроса и оптимизацию всё же порой было бы не лишним знать, если у вас большие запросы. Всё же, куда приятнее, когда запрос работает минуту вместо часа. Или пару секунд вместо пяти минут
Уважаемый, это просто не ваша работа. Это собес для бэкендера, не для аналитика. Бэкендер должен посреди ночи наизусть это рассказывать :)
Согласен, поэтому поставил простой уровень для статьи)
Транзакция- это элементарная операция в базе данных.
Наоборот, это группа операций, которые выполняются как одно целое. В частном случае - да, в группе может быть одна операция.
К тому же на таком определении нет места вложенным транзакциям - а они есть...
Да, мне проще было запомнить с минимального примера
Да и потом так было в книге)
своим студентам объяснял как "механизм изменения одного консистентного состояние - на другое консистентное".
Если не ошибаюсь, это называлось «целостность» БД и, соответственно, «целостность данных».
"Консистентность данных" и "целостность данных" в БД - действительно одно и то же, но к транзакциям это отношения не имеет. Целостность данных, это про их логическую непротиворечивость, а транзакция подразумевает переход в устойчивое "физическое" состояние, когда внесённые изменения как-то зафиксированы, видны для других транзакций и т.д. Нарушить транзакцией целостность данных - вообще не проблема, например, удалить внешний ключ по столбцу, ну пусть там userId, и ввести туда id не существующего в таблице users пользователя. Такая транзакция успешно отработает, но целостность данных будет нарушена.
Ой нет кажется. Транзакция от одиночного апдейта тем и отличается, что должна перевести из одного логически непротиворечивого состояния в другое. Классическая сказка в статьях о транзакциях о переводе 100$ с одного счета на другой.
Ясно, что любая заказанная дичь формально тоже будет оформлена в виде транзакции БД, но это не повод говорить, что транзакции не связаны с логической целостностью.
Скорее "транзакции это механизм БД, позволяющий обеспечить логическую целостность". Но и для разрушений вы тоже можете их использовать.
предыдущий оратор прав, логическая целостность и физическая - перепендикулярные вещи. От транзакции требуется чтобы при апдейте таблицы она не сломалась на половине или не забыла индекс обновить или при откате все откатила как было. Требовать от нее проверять согласованы ли данные между собой бесполезно
но до сих пор помню, что есть разные уровни изоляции
Есть
«одно целое» разваливается на части
Нет. Но можно выключить механизм изоляции, т.е. нарушить этот самый I в ACID, чтобы получить доступ к сырым неконсистентным данным, если это нужно (например, когда их целостность для какой-то задачи менее важна, чем производительность чтения). Сама же транзакция останется неделимой, и либо выполняется целиком, либо откатывается целиком.
Ну, ладно, сейчас формально уже не всегда так, некоторые СУБД умеют в промежуточные точки сохранения, и внутри текущей транзакции теперь тоже можно прыгать между сейвпойнтами.
В группе по определению одна операция, ЕВПОЧЯ
Какие-то у вас странные группы. Вот я сделал две операции в транзакции, это что, уже не группа?
Вот я сделал две операции в транзакции, это что, уже не группа?
Если у вас две операции — это может быть группа по одной из операций. Или даже две группы по каждой из них. Однако, в рамках группы операция может быть только одна.
Какие-то у вас странные группы.
Да обычные…
В группе по определению одна операция
...коммутативная, ассоциативная и с единицей?
В группе по определению одна операция
Всегда есть огромное поле для экспериментов же.
Mvcc тоже важный вопрос который часто бывает на собесах втч для питонистов. Особенно из-за популярности постгрес.
Раз уж затронули NoSQL и транзакции, есть смысл добавить вопрос про CAP-теорему
Спасибо, не знал, добавлю обязательно!
Раз уж затронули NoSQL, то неплохо было бы знать какие вообще бывают СУБД помимо реляционных.
Данная информация предполагает знание основ языка запросов
Да в начале статьи я имел ввиду знание какие бывают СУБД
Типы СУБД и языки запросов никак не связаны.
Понимаю, мне следовало указать это. И всё же я подразумеваю, что если человек идет на собеседование то ответ на вопрос "какие вообще бывают СУБД" ему известен
Практика показывает, что с вероятностью 99% кроме "SQL и NoSQL" человек ничего не знает.
Но с другой стороны, знать СУБД иных типов, кроме реляционок и NoSQL (ну или гибридов тех и тех, вроде постгреса) - сейчас это уже скорее вопрос викторины на знание истории развития ИТ, и он тоже практического применения не имеет.
Нет никакого NoSQL. Есть реляционные СУБД и "все остальные, про которые я не в курсе".
"Все остальные" сейчас тоже примерно одинаковые.
Ну-ну, попробуйте запихнуть граф в ту же Монгу адекватно.
Зато граф прекрасно запихивается в реляционную СУБД...
Да-да, у меня даже статья об этом есть.
Посмотрел бы я как будет выглядеть SQL-запрос к графовой СУБД
Любую графовую СУБД можно представить в виде реляционной - пусть и крайне неоптимальным путем
Условно - каждое звено рафа можно считать отдельной таблицей, в которой перечислен список входящих вершин, значение и список исходящих вершин
Это очень сильно упрощение, но концептуально - оно будет работать
Тут много примеров таких запросов. SQL, правда, не самый приятный для этого язык.
Есть ли преимущество у NoSQL над SQL?
Самое главное не добавили - шардирование. Это основное преимущество NoSQL перед реляционными DB.
Спасибо, большое, это очень ценно!
Greenplum это же реляционная БД? Дистрибьюция (шардирование) там есть. Аналогично - Teradata, Vertica
Да, на основе постгреса разработанная
реляционная аналитическая, для OLTP совсем не подходит
и не только. Оракл например...
Что мешает шардировать реляционные бд?
Если уж партиционирование есть, например
Концепция мешает. Шардирование предполагает, что ваши данные можно сгруппировать в непересекающиеся между собой множества, которые затем можно разложить по разным узлам, если надо. Для NoSQL это естественно - там связей между таблицами нет, каждую таблицу условно можно раздерибанить горизонтально по любому набору атрибутов, который вам подходит, и разбросать независимо по нужному количесту узлов. У реляционок же таблицы связаны между собой, и если вы какую-то основную таблицу данных и можете разделить, то все её "шарды" всё равно будут ссылаться на одни и те же справочники, например. Поэтому по независимым нодам такую структуру раскидать не получится... ну а раз так, называть мы это будем не шардированием, а партиционированием.
Можно держать несколько копий одной и той же БД, например, справочники реплицировать между шардами, а самую тяжелую таблицу, распилить по, например, клиентам. Если у вас записи биллинга сотового телефона, то почему бы его не попилить
Можно, но это уже не шардирование само по себе, это реплицирование, со всеми его сложностями, синхронизациями/пересинхронизациями и т.д. Шардирование же подразумевает, что вы просто взяли и раскидали базу горизонтально между равнозначными узлами, не заботясь о том, как общий справочник тарифов, филиалов и гендеров абонентов отсинкать на каждый узел при любом его изменении.
1) это сложно (в реализации)
2) это имеет 1000 и 1 ограничений (см. предыдущий пункт)
3) это (может привести) к денормализации (см предыдущие два пункта)
У нас недавно был эксперимент длиной аж 6 месяцев, отказались. В результате материала набралось куча, но он будет весь похоронен в одной из трех систем документирований знаний в компании. Жаль.
10. Планировщик
Это такой встроенный механизм в СУБД, перед выполнением запроса он формирует "план" выполнения запроса.
Да ладно! Это не "планировщик", а "построитель плана запроса", иногда прозываемый также оптимизатором, ибо оптимизация плана выполнения является неотъемлемой частью его работы.
А планировщик - это именно планировщик, инструмент, который выполняет по заданному расписанию заданные процедуры. И который, кстати, есть далеко не в каждой СУБД. Вот, например, в MySQL - есть. Event Scheduler прозывается. Но в большинстве СУБД - отсутствует, и для периодического выполнения SQL-кода приходится использовать внешний планировщик.
Спасибо, буду знать эту неточность!
Не надо никаких спасибо. Это любители mysql принесли 8 главу своей документации, где эта штука у них называется optimizer и состоит из нескольких частей (query planner, собственно query optimizer и т.д.)
В postgresql (и других базах) это называют planner и в русскоязычном IT в аспекте БД (не только postgres) принято слово именно что "планировщик". Ну а то, что некоторые любители mysql словом "планировщик" с давних пор называют почему-то event/job scheduler, вас в общем случае волновать не должно, это у них там своя атмосфера.
Именно на собеседованиях щеголять столь вольными трактовками общепринятной терминологии может быть чревато, у собеседующего приподнимется бровь и посыплются уточняющие вопросы.
Ну так и вы туда же не скатывайтесь. В MS SQL тоже вполне себе "оптимизатор запросов" и джобы...
И в оракле тоже оптимизатор, а планировщиком Oracle Scheduler называют, который джобиками оркестрирует.
Так это что же получается... Во всех "нормальных" СУБД оптимизатор - это оптимизатор, а эти любители postgresql эту штуку назвали планировщиком и теперь всему свету пытаются навязать свое название и на собесах везде щеголяют столь вольным трактовками общепринятой терминологии... )))
Нет, это всё издержки переноса англоязычной терминологии на русский язык. Каким образом так случилось, что в русском scheduler стал планировщиком, я внятно объяснить не могу.
Schedule — это вообще-то график (дежурств), расписание (действий). Планирование как составление плана действий, здесь отсутствует, расписание — продукт планирования, а не процесс. И scheduler в этом смысле — это исполнитель расписания, а не его составитель.
Plan — это в некотором смысле алгоритм, последовательность шагов при исполнении какого-либо действия (из, например, расписания).
Optimize — в общем случае улучшение этого самого Plan.
Подытожим:
scheduler — хранит таблицу, расписание выполнения задач, и обеспечивает их запуск по этому расписанию;
planner — составляет план действий для исполнения конкретного запроса (а EXPLAIN его, этот план, показывает);
optimizer — часть planner'а (может и отсутствовать). Например, переупорядочивает действия, исключает повторные действия, и т.п.
Таким образом получается, что именно в PostgreSQL всё как раз таки и называется правильно:
Вопрос не о любви к MySQL, а о точности и однозначности терминологии.
Если на собеседовании попросят рассказать о планировщике - вот так, без какого-либо уточнения,- о чём Вы станете рассказывать? Поди угадай.. А если вопрос будет о построителе плана выполнения запроса - то гадать не требуется, вопрос совершенно однозначный.
В postgresql (и других базах) это называют planner
Ну Вы же сами ссылку даёте! что ж половину отрезаете-то? или если написать, что "В postgresql (и других базах) это называют Planner/Optimizer", то не прозвучит?
Зачем "угадывать", если можно "спросить"?) Это ведь не редкая ситуация, когда короткий термин означает разное в разных контекстах.
Дело в том, что у почти любого термина в конкретном контексте есть "дефолтное" значение. Статья - про собеседование для разработчика-"питониста". Согласитесь, что в таком собеседовании в принципе не бывает "Мы не спрашиваем за питон, все вопросы исключительно про SQL". А когда так, то вопрос о планировщике - это вопрос о планировщике либо внешнем (операционной системы или третьей фирмы), либо встроенном в некое приложение/библиотеку/среду/прочее. Но уж никак не о построителе/оптимизаторе запросов.
А ещё - про термин, использованный в тексте статьи и не разъяснённый ранее явно в разделе терминов и определений, трудно "спросить".
Как создавать индексы и какие они бывают написали, а как из использовать и на что они влияют (замедляют, например, insert/update) ни слова.
Про статистики, без которых оптимизатор работать не может тоже ни слова.
А ещё есть в природе кластерные индексы (не уверен насчёт PostreSQL)...
Пункт 9 - инедксы. Исправьте, пожалуйста
В заголовок добавьте слово постгресс, многие вещи из списка в разных СУБД сильно разные
13. Что будет если сделать EXPLAIN ANALYZE DROP TABLE POSTGRES
Table not found будет. Если вы ранее не создавали эту таблицу специально. Что же до БД - это с какого перепугу она вся из себя такая главная? Обыкновенная БД, не более того.
Я всегда выделял 2 основных. Это Шардирование и Репликация.
Партиционирование. Как шардирование, только не между инстансами, а между файлами (соответственно, дает возможность раскидать нагруженные части на одни накопители, ненагруженные — на другие).
Но к слову, существенная часть описанного в статье скорее относится к специализациям, вроде DBA.
Ну и как собеседования с такой шпаргалкой?
По нормализации, это, конечно сильно. Логическая избыточность тут вообще не к месту. Слабо ее определение дать? Или хотя бы в инете глянуть, что избыточность информации и логическая избыточность даже и не сестры. Смело для шпаргалки. Тут же за язык же поймают. И какой вывод сделают? Что набрался умных слов, толком в них не разобравшись?
Да и пример с телефонами оправдан только, если в системе человек может иметь несколько телефонов (а такое совсем не в каждой нужно, внутренний телефон в компании, как пример тому), и эти телефоны ему запихивают в одно поле через разделитель. Стоит, все же, глянуть чем первые три нормальных формы друг от друга отличаются и с примерами станет просто.
Ну и самый распространенный вопрос по типам JOIN-нов в SELECT странно даже не помянуть.
Но в целом статья норм, кмк. Особенно для первого раза.
За "долговечность" отдельное спасибо, запомню. Меня при вопросе про ACID всегда ставило в тупик — как бы нормально перевести на русский "Durability :))
Это скорее не шпаргалка для себя а план подготовки к собеседованию для тех, кто не очень в теме.
Если человеку нужна шпаргалка для ответов на эти вопросы, то к работе с БД его допускать не стоит.
Я на собеседованиях показываю SQL и прошу объяснить что будет в базе после его выполнения а также прошу объяснить как человек решит конкретную задачу, разумеется с подвохом (которые требуют знаний о ACID/MVCC, индексах и разных уровней изоляции) - сразу становится ясно кто зубрил а кто знает.
Выкладывайте задачу, поломаем голову все вместе.
А можно мне?
Обработка запросов с подтверждением "еще работаю"
Т.е.
1) Если в процессе обработки запроса была ошибка - все откатывается и следов в базе быть не должно.
2) Если запрос пришел повторно (такой-же по бизнесу, но с другим серийником по времени) до окончания обработки - сразу посылается ответ "уже обрабатывается, смотри запрос ID..", а не происходит засыпание на какой-нибудь блокировке.
3) Параллельной обработки одного и того же (потому что повторный запрос совсем не видит, что там первый успел сделать из за того, что тот еще ничего не закоммитил) быть не должно.
4) Read uncommited режим недоступен.
Делаем табличку-журнал с полями "id", "вид бизнес-задачи", "время начала", "время окончания", "id сеанса" + какой-нибудь "desc" по вкусу
При запуске работает следующая логика (можно инкапсулировать в хранимку):
1 открываем транзакцию
2 идем в наш журнал и ищем незаконченную(время окончания is null) запись с нашим типом бизнес-задачи
3
Если не нашли: вставляем новую нашу запись о старте
Если нашли, то берем id-сеанса и проверяем его существование. Если его уже нет завершаем(ставим дату окончания) с отметкой в desc что сеанс умер и вставляем нашу новую запись о старте. Если сеанс еще работает, то возвращаем соответствующее сообщение (в этом варианте - тут конец. закрыть транзакцию конечно надо).
4 Закрываем транзакцию
5 Выполняем нашу бизнес-задачу в новой транзакции, в случае ошибки - откатываем
6 Ставим отметку в журнале об окончании, можно в desc что-то написать
(id сеанса скорее всего потребует еще дату начала сеанса для однозначной идентификации, в общем это особенности субд, в тексте упростил просто до id-сеанса)
Примерно так, если правильно понял задачу. Вроде должно работать
Хороший план
Если нашли, то берем id-сеанса и проверяем его существование.
Я так понял сеанс в данном случае - это сеанс обработки.
Тогда - проверяем в чем и как? Потому что прошлое сообщение на какой-то на другой узел кластера или вообще в другой дата-центр ушло. Поднимать еще что-нибудь рядом для хранения сеансов, когда у нас и так база вроде как есть?
А как это 'что-нибудь' заполнять и инвалидировать? Когда сеанс уже умер, а запись об его существовании осталась?
5 Выполняем нашу бизнес-задачу в новой транзакции, в случае ошибки - откатываем
6. Ставим отметку в журнале об окончании, можно в desc что-то написать
Между 5 и 6 все умирает. После восстановления cледующее повторные сообщения будут пытаться сделать работу заново, хотя она уже сделана.
Потому что прошлое сообщение на какой-то на другой узел кластера или вообще в другой дата-центр ушло
Про распределенную систему не было ни слова, поэтому решение предложено в пределах 1 базы
При распределенных вычислениях, думаю без координатора не обойтись, нам нужен сервис который возьмет на себя эту роль. Таким образом мы выносим обеспечение требований AСID с уровня БД, на уровень системы.
Между 5 и 6 все умирает. После восстановления cледующее повторные сообщения будут пытаться сделать работу заново, хотя она уже сделана.
Думаю придется выбирать компромисс:
-Закрываем транзакцию после 6 пункта, но тогда все ждут пока идет работа...
-Либо, что скорее всего предпочтительнее: быть готовым к повторной обработке (предусмотреть такую вероятность).
Про распределенную систему не было ни слова, поэтому решение предложено в пределах 1 базы
Виноват.
База и имелась в виду одна. Просто обработчиков сообщений много, которые все к одной этой базе прицеплены.
Тогда ответ остается тот же с пояснением по "Между 5 и 6 все умирает". Сеансы имелись ввиду субд-шные, вытаскиваются запросом к системным таблицам/представлениям. если бд одна, то логично воспользоваться ее механизмами.
А у вас есть готовое решение этой задачи? Если есть, хотелось бы узнать его
Нет. Уже несколько раз разным разработчикам в специализации по базам данных задавал - как-то то ли ответа уже не помню, то ли понравившегося мне не было.
К сожалению в общем случае эта задача неразрешима
Основная проблема в отсутствии координатора, если мы предположим что мы используем oracle (возможно enterprise) то мы можем использовать встроенные механизмы (очереди сообщений, обработчики, scheduler) но это будет платформенно зависимое решение, в принципе можно конечно попытаться завязаться на триггеры, и реализовать обработчик очереди внутри почти любой СУБД(с использованием некоторого механизма запуска процедур по событию) но это все равно будет костыль, и останутся вопросы с отработкой, к примеру, случаев разрыва связи и потенциальные простои сервиса. Т.к. есть в вашей постановке неопределенность касающаяся пункта "уже выполняется смотри запрос id" т.к. ничего не мешает запросу с id заселится после этого ответа.
и что будет если выкинуть пункты 1 и 4 ???
Способ с номерами версий.
Создаем журнал для фиксации запросов. В журнал добавляем числовое уникальное ненулевое поле "номер версии" для блокировки создания записи с одинаковым номером версии, поле для статуса операции [started, success, failed] и другие поля, например, время старта, время окончания операции.
При добавлении записи в журнал сначала проверяем, не ли уже запущенной такой-же операции, если нет, то находим последнюю запись и номер версии, увеличиваем номер версии на единицу, добавляем запись в новым номером версии в журнал. Если паралельно пришло несколько запросов с таким-же номером, то, естественно, сможет добавиться только один, на остальных поднимется исключение.
Если запись добавилась, то в эту запись пишем статус "started", время начала операции и т.д., запускаем нужный процесс, отдаем пользователю ответ, что операция стартовала при необходимости.
Если запись не добавилась, поднялось исключение, то выполняем запрос для получения информации из записи с таким-же номером версии, отдаем пользователю, что операция уже выполняется.
По-окончании операции изменяем статус операции, устанавливаем время окончания операции и т.д.
Более подробно об этом способе здесь:
https://github.com/cosmicpython/book/blob/master/chapter_07_aggregate.asciidoc
Я бы делал так:
Клиент создаёт задачу в своей локальной базе.
База синхронизируется с обработчиками.
Обработчик видит новую задачу и начинает обработку, чекинясь в задаче.
База синхронизируется между обработчиками.
Если обработчик видит, что другой обработчик зачекинился раньше, то отменяет задачу.
Перед обработкой задачи вставляется небольшая задержка на синхронизацию базы.
Падение обработчика приводит к его перезапуску и продолжению обработки задачи.
По завершению, обработчик пишет в задачу резолюцию
База синхронизируется с клиентом и он видит её статус.
Способ с шиной сообщений и редиса. Событийно-ориентированный.
Посылаем команду на запуск процесса по шине. Обработчик принимает команду, проверяет в редисе в соответствующем set или hash контейнере эту команду, если ее в наборе нет, то добавляет ее в набор и стартует процесс, сообщает пользователю о старте процесса. Процесс заканчивается, убирает в редисе из набора команду, сообщает пользователю.
Если в наборе уже есть команда, то значит процесс уже выполняется, тогда обработчик получает информацию из этого hash-контейнера и отправляет отлуп пользователю.
тоже достаточно понятный вариант
(ну или поправишь неточности в существующих)
ну поехали....
В первом пункте сначала говорите про избыточность, потом приводите довольно неудачный пример, который может быть совсем не про избыточность (если номера не уникальны)...
Ну и потом еще фразы: " мы разбиваем таблицу на части или делаем декомпозицию, приводя таким образом таблицу к нормальной форме" и "декомпозиция - это одна из вариаций нормализации"... Декомпозиция это не вариация нормализации, а всего лишь инструмент, а читая ваш текст создается впечатление что разбить данные на таблицы = нормализация...
Так MongoDB может выигрывать у PostrgeSQL в запросах, которые подразумевают много связей и за которыми постгрес полезет в другие таблицы
Тут вы отождествляете РСУБД и реляционную модель данных, что немного не верно... А если в реляционной СУБД хранить данные в не нормализованном виде, скажем в 1 таблице id и json с объектом, тогда уже не надо лазить по таблицам и все становится не так однозначно...
Транзакция - это элементарная операция в базе данных.
Однако транзакция может состоять и из нескольких операций
Что? Транзакция - не элементарная операция, а атомарная тогда уж...
Повлиять на скорость выполнения запроса можно различными способами. Я запомнил их так: изменить зам sql-запрос, обновить статистику планировщика, денормализация и 4 вариации изменения параметров планировщика:
и дальше не 4, а 7 пунктов частично повторяющих только что сказанное...
Обновление статистики - это какая-то разовая оптимизация, это вопрос регламентного обслуживания или вы предлагаете перед каждым запросом обновлять статистику?
денормализация: создание временных таблиц или создание индексов
Вы уверены что это про денормализацию?
изменение параметров планировщика...
Слышал про такое... ) На практике ни разу не приходилось вмешиваться в работу планировщика (правда это были не postgresql), обычно планировщик лучше знает как ему выбирать данные, а все проблемы решаются исправлением своих рук и мозгов
Какие есть концепции масштабируемости БД Я всегда выделял 2 основных.
Ожидал сейчас прочитать: вертикальное и горизонтальное, а тут...
SELECT в интернетах выполняются во много раз чаще, чем INSERT'ы, на этом фоне репликация выглядит привлекательнее
не увидел прямой связи...
Все выше сказанное относится к теме "Повышение отказоустойчивости"
да ну? а я знаю примеры решающие задачи оптимизации и даже репликации для ограничения доступа в другой сетевой сегмент.
Я рекомендую её к прочтению разработчикам любого уровня
Прям не знаю... книга такая плохая или вы недопоняли материал...
Спасибо за столь объёмный комментарий. Некоторые ответы составлены с "выжимками" из книги. А некоторые полностью взяты оттуда, поэтому Вам точно рекомендую её прочесть: уверен, что Вы поймёте её более правильно!
SELECT в интернетах выполняются во много раз чаще, чем INSERT'ы, на этом фоне репликация выглядит привлекательнее
не увидел прямой связи...
Видимо, имелось ввиду, что реплики обычно READ ONLY и писать можно только на мастера.
Кажется исходя из того, что это копи запись считается завершенной, когда попала во все. И это более тормозное требование, чем пропускная способность на запись одного мастера.
Но в таком случае не понимаю фразы из статьи
То есть по сути это полное копирование СУБД на другой сервер.
Таким образом контроллер сам будет решать в какую ноду и что ему записывать.
Что-то одно должно быть, или полная копия, или решать куда что писать. Для полной копии ничего решать не надо — пиши во все ноды.
Сам когда-то собрал Чаво по SQL. привожу линк на пост.
Автор молодец, что постарался. В попытках что-то объяснять другим и рождается собственное углублённое понимание предмета, это я вам как преподаватель ответственно заявляю. Однако сейчас у статьи недостатков больше, чем достоинств, настолько, что даже руки не поднимаются - исправлять косяки придётся буквально в каждом вопросе.
Я вам хочу посоветовать другое. Не гонитесь за составлением шпаргалок по тому, о чём могут спрашивать на собеседованиях. Это всё равно что строить своё образование, изучая те вопросы, которые вам встретились при просмотре телепередачи "Своя игра" с Онотоле. Вопросы на собеседованиях очень часто бессистемные, они могут быть просто о том, что вопрошающему конкретно сейчас пришло в голову, и он вообще не факт, что сам умеет спрашивать о главном, а не второстепенном. Приведу только один пример у вас - про индексы. Вас, видимо, спрашивали о том, какие они бывают и каким синтаксисом создаются. Но поверьте, это совсем не самое главное из того, что стоит знать про индексы. Гораздо важнее понимать их суть, что они из себя представляют внутри - чтобы понимать, почему какой-то запрос индексом ускоряется, а какой-то нет - а у вас про это вообще ничего не сказано. Если собеседующий будет от вас требовать в первую очередь синтаксис, а не суть - бегите оттуда, пока не поздно.
Спасибо, за список. Я бы добавил в шпаргалку пункт про VACUUM, MVCC и зачем это все нужно.
Встречал я как-то задачку на собесе, решить не смог, хотя по словам собеседующего, это базовый уровень. Кто хочет, может попытаться с ней справится.
Есть таблица 3 полями, в ней хранится id, время посещения сайта и ip:
id | ip | time(timestamp)
Нужно вывести все уникальные ip адреса, и время третьего посещения. (Вложенные запросы делать нельзя)
Ну это какой-то совсем лютый базовый уровень, как по мне, если без вложенных запросов.
WITH a AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ip ORDER BY time) AS RowNumber,
ip,
time
FROM mytable
)
SELECT ip, time FROM a WHERE RowNumber = 3Спасибо, хорошее решение. Благодаря Вам узнал что-то новое для себя
А CTE разве не будет считаться подзапросом? А ещё у вас выпали IP адреса, у которых было меньше 3-х посещений.
А CTE разве не будет считаться подзапросом?
Формально есть подзапросы, есть CTE, ну т.е. вроде как условию задачи соответсвует, хотя с точки зрения плана выполнения один фиг. Ну и лично я затруднился бы сказать, что хотел бы видеть вопрошающий, запретив использовать и обычные подзапросы, и СТЕ.
А ещё у вас выпали IP адреса, у которых было меньше 3-х посещений.
Ну да. На собеседовании я бы уточнил, надо ли их выводить :) Вполне вероятно, что и не надо, т.к. дополнительных кунштюков SQL это не требует, а писанины больше.
Ну да, хитрая задачка какая-то: без вложенного я не смог сделать
На оракле можно было бы решить через model, например -
Запрос
with journal as
(
select 1 as id, '127.0.0.1' as ip, to_timestamp('01.01.2010 10:10','dd.mm.yyyy HH24:mi') as time from dual union all
select 2 as id, '8.8.8.8' as ip, to_timestamp('02.01.2010 11:15','dd.mm.yyyy HH24:mi') as time from dual union all
select 3 as id, '127.0.0.1' as ip, to_timestamp('03.01.2010 12:20','dd.mm.yyyy HH24:mi') as time from dual union all
select 4 as id, '127.0.0.1' as ip, to_timestamp('04.01.2010 13:25','dd.mm.yyyy HH24:mi') as time from dual union all
select 5 as id, '8.8.8.8' as ip, to_timestamp('05.01.2010 14:30','dd.mm.yyyy HH24:mi') as time from dual union all
select 6 as id, '127.0.0.1' as ip, to_timestamp('06.01.2010 15:35','dd.mm.yyyy HH24:mi') as time from dual union all
select 7 as id, '8.8.8.8' as ip, to_timestamp('07.01.2010 16:40','dd.mm.yyyy HH24:mi') as time from dual union all
select 8 as id, '127.0.0.1' as ip, to_timestamp('08.01.2010 17:45','dd.mm.yyyy HH24:mi') as time from dual union all
select 9 as id, '127.0.0.1' as ip, to_timestamp('09.01.2010 18:50','dd.mm.yyyy HH24:mi') as time from dual union all
select 10 as id, '1.1.1.1' as ip, to_timestamp('10.01.2010 19:55','dd.mm.yyyy HH24:mi') as time from dual union all
select 11 as id, '1.1.1.1' as ip, to_timestamp('11.01.2010 20:55','dd.mm.yyyy HH24:mi') as time from dual
)
select ip, third_visit
from journal j
model
return updated rows -- будем брать только те строки, что попали под правила
partition by (j.ip)
dimension by (
-- нам понадобится номер посещения по IP
row_number() over (partition by j.ip order by j.time, j.id) as num_visit,
-- и всего посещений
count(*) over (partition by j.ip) as total_visits
)
measures (cast(null as timestamp) as third_visit, j.time)
rules
(
-- возьмём дату третьего посещения, если всего посещений >= 3
third_visit[num_visit = 3, total_visits >= 3] = time[cv(), cv()],
-- возьмем пустую дату для первой строки по IP, если всего посешений меньше 3
third_visit[num_visit = 1, total_visits < 3] = null
)
/Вывод:
IP THIRD_VISIT
--------- ------------------------------
1.1.1.1
127.0.0.1 04-JAN-10 01.25.00.000000 PM
8.8.8.8 07-JAN-10 04.40.00.000000 PMЯ считал, что нужно найти третье посещение, а не третье посещение за день. Но это что-то нифига не базовый уровень :D Базовый уровень - это подзапрос с аналитикой.
Попробовал в SQLite без CTE, извиняйте если не запустится в других СУБД.
SELECT DISTINCT ip, nth_value(time, 3) OVER win AS time
FROM visits
WINDOW win AS (PARTITION BY ip ORDER BY time RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);del
Ну во первых EXPLAIN ANALYZE DROP ... выдаст ошибку, потому что для DROP explain сделать нельзя, а вот услышать на собеседовании вопрос как сделать ничего не сломав EXPLAIN ANALYSE delete from ... это вполне возможно, и иногда даже нужно. Правда ответ в документации по EXPLAIN есть, но кто же ее читает :-) (TLDR: начать транзакцию, а после сделать rollback)
Интересно, при собеседовании в какие компании у python-разработчика спрашивают так много про SQL? Больше похоже на собеседование администратора баз данных (кстати, в профиле автора указано, что он как раз админ БД).
Python разработчик почти в 100 процентах случаев сталкивается с SQL, и это не много а самые необходимые основы. Для ДБА это тоже необходимые знания, но там список вопросов побольше будет.
Эти вопросы собраны со всех моих python-собесов. Около дюжины их было. И - нет - у меня в профиле не совсем так написано - смотрите внимательнее - это ловушка для админов БД)
В секции индексов я бы дополнил патерн создания индекса при помощи CONCURRENTLY. Из разряда когда нужно создать индекс на нагруженной записью таблице не блокируя ее. Но CONCURRENTLY нельзя выполнять в транзакциях.
Смущает заголовок: "Шпаргалка по SQL, которая выручает меня на собесах".
В идеале, хорошая шпаргалка должна выручить ровно один раз, после чего вы получите желаемую работу, после года которой, опять же, в идеале, ваш уровень знания SQL должен вырасти настолько, что указанная шпаргалка перестанет быть актуальной. А если она прям постоянно выручает, то стоит задуматься, а точно ли она помогает достичь цели собесов...
Шпаргалка по SQL (postgres), которая выручает меня на собесах