Comments 84
Почему не Excel?
Первоначально PostgreSQL назывался POSTGRES, ссылаясь на свое происхождение как преемника базы данных Ingres, разработанной в Калифорнийском университете в Беркли
В свою очередь "Ingres была создана как научно-исследовательский проект в Калифорнийском университете в Беркли в начале 1970-х годов."
Я же познакомился с этой СУБД в середине 80-х годов. СУБД Ingres входила в состав операционной системы МОС ЕС, советского клона операционной системы Unix для ЕС ЭВМ. При чем она работала и на больших машинах и на персоналках ЕС18xx. И мы тогда тоже пропагандировали постулат одной ОС (Unix) и СУБД Ingres:
Я присоединяюсь к словам автора:
я никого не сбиваю с толку, предлагая пользоваться
Postgres
К ним стоит прислушаться.
есть клон mysql не от оракла, называется mariadb и думаю большинство уже mariadb используют. у mysql есть киллер фича - там сильно круче ядро, сторидж енжин не один (и они легко подключаются) и главное - реализован UNDO log в стиле оракла. в результате нет vacuum и апдейты на таблицы с индексами не так бьют производительность. почитайте историю Uber, они очень подробно расписали проблему и почему они свалили на mysql. когда-то постгрес пытался UNDO log реализовать, но говорят Zheap инициатива сдохла, потому что в постгрес сторидж енжин прибит гвоздями и как у mysql не подменить.
но с другой стороны у mysql как-то криво реализовано чтение, похоже у них нет понятия латча и при апдейте всего одной строки на таблицу без индекса, он врубает full scan который блокирует нафиг всю таблицу. плюс у mysql сильно хуже оптимизатор.
Если брать профессиональные базы за деньги, то MSSQL вне конкуренции. А в нашей специфике конечно мне, как спецу по MSSQL одна дорога, на теплотрассу
Мне вот недавно потребовалось сделать на mssql аналог intarray из postgres. Это когда в колонку пишется массив int-ов, и дальше можно фильтровать выражениями типа (1|3)&4&5. И мы расшибли лоб, и так ничего вменяемого и не придумали.
Важна не эта конкретная задача, а то что в mssql если чего-то нет, то это даже сколхозить не из чего: типы данных не добавить, даже массив положить в колонку не во что, и передать в функцию нельзя. T-SQL - просто издевательство, а альтернатив нет. Что-то можно изображать на CLR, но облачные mssql в него не умеют.
И вроде вот они данные, лежат в правильных b-tree как надо, алгоритмы понятно какие надо сделать. Но подлезть не дают.
Обидно даже за нее - т.к. на низком уровне она хорошо сделана.
Мне недавно понадобилось на Феррари перевезти картошку, как я делал это на Жигулях, но не получилось. Чтоб я не делал.
Хотя и там и там ТС. И там и там передвигаться предназначено. Но объективно по разному сделано(хотя в основе мотор и колеса).
Не понятно зачем сложности такие, но ок. В MsSQL есть пользовательские типы - реализуй любой. В MsSQL есть расширенные процедуры - создавай свою логику над данными.
Ну по сути как и в постгре. Никто не обещал хранить и обрабатывать "любые данные" (хотя для них есть двоичные типы/поля или даже filestream). Мне вот постгря не нравится после перехода с MsSQL, там неудобно, тут избыточно, там кавычки одинарные/двойные не забывай писать, там тип неудачный. Но потихоньку вкатываюсь и где-то привыкаю к "неудобству", где-то улыбаюсь от "красивой" фичи, а где-то использую не замечая разницы концептуальной.
Зы: но администрирование постгри - это дичь. Там где MsSQL из коробки работает на 100%, постгрю настраивать/подстраивать для тех же результатов (на одной ОС/машине). Но это дело привычки и навыков.
Да, я вот слышал что для бэкапа нормального (не костыля в виде pgdump) надо на Постгре собирать кластер и делать холодный бэкап и на второй ноде
При этом не забыть скопировать все файлы. Потому что симлинки могут ввести в заблуждение. И про то, что бэкап неполный, вы не узнаете до момента, когда обратитесь к таблице, которой не повезло
Зы: но администрирование постгри - это дичь. Там где MsSQL из коробки работает на 100%, постгрю настраивать/подстраивать для тех же результатов (на одной ОС/машине). Но это дело привычки и навыков.
Отчасти это "заслуга" академического прошлого базы, пошедшей от проекта университета Беркли, как написали выше, а отчасти от Unix way. Когда я первый раз узнал, что каждая таблица в PG это 3-5 файлов (Карл!), после оракл где все таблицы можно упаковать в 1 файл и настроить их так, чтоб фрагментация была минимальна, у меня глаза на лоб полезли. Понятно, что сейчас в SSD разница в доступе сильно нивелировалась, но раньше на хардах, это ж какой ад наверное был!
Примерно то же самое с настройками - база из коробки преднастроена так, чтобы запуститься на любом тапке, ни о какой производительности речь совершенно не идет, когда ставят такую цель. И да, много чего надо покрутить в конфиге, чтоб оно нормально заработало
А я вот наоборот, при работе с мускулом, если есть такая возможность, включаю режим innodb_file_per_table. Потому что когда у вас innodb упадёт, а она упадёт по питанию или ещё как, снести table_storage для одной таблицы с каким-нибудь журналом будет сильно быстрее и проще, чем двое суток вытаскивать данные из одного гигантского файла (я уж молчу про разделы, хотя оно и такое умеет, вообще без ФС)
Обычно постгресу ставят в минус тот факт, что он работает с файлами исключительно поверх ФС, не занимается самостоятельно кэшированием и т.д. Но почему-то забывают про жирнющий плюс от этого решения.
В постгре и оракл после падения по питанию обычно достаточно запустить базу - она сама восстановится если wal/redolog файлы целые. Но ведь если мы пишем через операционку, она ведь гарантирует что всё честно записано на диск, правда-правда (fsync, и всё такое)? :-)
С файлами обычно приходится возиться когда уж совсем всё пошло не так.
Когда я первый раз узнал, что каждая таблица в PG это 3-5 файлов (Карл!), после оракл где все таблицы можно упаковать в 1 файл и настроить их так, чтоб фрагментация была минимальна, у меня глаза на лоб полезли. Понятно, что сейчас в SSD разница в доступе сильно нивелировалась, но раньше на хардах, это ж какой ад наверное был!
а как одно зависит от другого? Диск это блочное устройство, он про файлы ничего не знает. Даже если у вас 1 файл на все таблицы, то скорее всего там есть выравнивание по размеру блока или даже кратно ему. Единственное на что влияет количество файлов в директории это на время операции ls ;)
Как это не влияет? Допустим у нас full scan. Тогда если 1 файл и небольшое число экстентов на таблицу (косвенно зависит от свойств таблицы - сколько места добавлять когда оно кончается), то имеем последовательное чтение. А если записью в таблицу/файл ведает операционка, то получим кучу раскиданных по диску блоков. и полное чтение файла выльется в рандомный доступ к диску, что для HDD сильно медленнее.
Понятно что пример несколько утрирован, но если уж нам по каким-то причинам нужен fullscan (аналитику например делаем), то и так запрос будет не быстрым, а мы его еще дополнительно кратно замедляем.
Хорошо, если с фулскан пример так себе, то допустим нам надо прочитать 1 строку. В оракле мы прочитаем 1 блок диска - 1 чтение. В постгре в каких-то случаях на придется прочитать данные из 3 файлов - 3 операции чтения.
А если записью в таблицу/файл ведает операционка, то получим кучу раскиданных по диску блоков
Зачем операционке раскидывать блоки одного файла по диску? По-моему наоборот, она будет всеми силами стараться класть блоки этого файла на диск оптимальным образом: близко друг к другу и упорядочено, и еще дефрагментацию будет запускать время от времени. А когда у вас 1 огромный файл, который содержит в себе все сразу - то такую дефрагментацию придется делать самой базе. Т.е. На уровне базы надо придумывать алгоритмы дефрагментации и работать с диском напрямую. Наверно можно извлечь плюсы, но и кактус надкусить тоже возможно.
Хорошо, если с фулскан пример так себе, то допустим нам надо прочитать 1 строку. В оракле мы прочитаем 1 блок диска - 1 чтение. В постгре в каких-то случаях на придется прочитать данные из 3 файлов - 3 операции чтения.
так и в оракле чтобы прочитать 1 блок с диска скорее всего придется читать еще какие-то другие блоки, чтоб понять откуда читать этот блок. В общем это уже все из разряда микробенчмарка и реальное влияние тут не спрогнозировать теоретически, потому что могут быть дополнительные побочные факторы.
Как-то я себе с трудом представляю операционку, которая в заботе о монолитности файла оставляет дыры в свободных блоках HDD или тем более что-то куда-то двигает. Главная забота операционки - транзакционное поддержание целостности файловой системы.
Дефрагментация файлов на серваке СУБД? Такая себе затея. И так диски база дрючит, а тут еще дополнительная нагрузка.
На самом деле наш спор в нынешнее время сугубо академический. С приходом SSD никто кроме контроллера не знает где физически живет блок и никто кроме контроллера его не подвинет.
свободное место конечно не оставит, это может сделать и база ;) в принципе я думаю никто не мешает сделать схожий механизм работы с ораклом, просто в оракле один файл, а в постгресе 3 на каждую таблицу, а сам характер взаимодействия похожий. Я часто встречаю мнение, что 1 файл лучше чем 100, но в ситуации когда эти файлы большие, читаются произвольно и независимо - на мой взгляд разницы никакой в скорости нет, по этому за ваше утверждение зацепился.
Как-то я себе с трудом представляю операционку, которая в заботе о монолитности файла оставляет дыры в свободных блоках HDD
Это называется delayed allocation.
или тем более что-то куда-то двигает.
А это copy-on-write.
С приходом SSD никто кроме контроллера не знает где физически живет блок и никто кроме контроллера его не подвинет.
Я даже более скажу, современные СХД с WAFL или подобными приемами сами эффективно такие проблемы решают.
В PostgreSQL, таблица без индексов и out-of-storage полей - один файл. Out-of-storage - отдельный файл. Каждый индекс - тоже отдельный файл.
Если речь идет об одной физически первой записи таблицы без out-of-storage полей, то в и в Oracle, и в PostgreSQL это будет чтение одной страницы. Аналогично, при наличии out-of-storage полей, например, строки длинее 4000 байт (упрощенно, так как тут еще отдельная тема с TOAST, но мы ее опустим), в обоих СУБД потребуется чтение двух страниц. Ну и наконец, если запись с out-of-storage полем извлекается по первичному ключу и в индексе она оказалась уже в корневой странице индекса, то в обоих СУБД будет прочитано три страницы.
А количество файлов - это уже проблемы файловой системы. Собственно говоря, это и есть основная причина более медленной работы PostgreSQL под Windows (на NTFS), чем под Linux (на XFS или EXT4).
Что касается самостоятельного распределения страниц в едином файле, то тут у СУБД возможностей меньше, чем у файловой системы. Потому что она, в отличии от файловой системы, не имеет понятия, в каких экстентах (непрерывных участках) расположен файл. То есть, последовательно расположенные в файле страницы БД могут оказаться в разных экстентах, а файловая система не знает и не может знать, что они относятся к одной таблице. Тогда как если данные одной таблицы - отдельный файл, то файловая системе может оптимизировать их размещение, зная физическую структуру экстентов на диске. Для понимания, например, в NTFS, максимальный размер екстента всего лишь 2 МБ. То есть, на NTFS любой файл больше 2 МБ точно фрагментирован, как минимум, на два экстента и изменить это нельзя.
В PostgreSQL, таблица без индексов и out-of-storage полей - один файл
А как же карта свободного пространства и карта видимости? ;-) 1 таблица всё-таки 3 файла даже если нет TOAST. Хотя карта свободного пространства есть и в оракл (в системных таблицах кажется), так что чтений будет плюс-минус одинаково.
Что касается самостоятельного распределения страниц в едином файле, то тут у СУБД возможностей меньше, чем у файловой системы. Потому что она, в отличии от файловой системы, не имеет понятия, в каких экстентах (непрерывных участках) расположен файл.
Поэтому оракл создает файл нужного размера сразу, чтоб фрагменированность была минимальной. Есть конечно autoextent, но тут уж разработчику решать включать его или добавлять дополнительные файлы к tablespace
С другой стороны ФС совершенно ничего не знает про данные - какой прирост данных будет в файле, с какой интенсивность. А база знает если это ей сказал разработчик или если включен сбор статистики. Вроде ораклоиды призывали отказаться от ерунды вида ручного задания свойств таблиц, мол база умная, сама порешает, но что-то у меня сомнения.
А как же карта свободного пространства и карта видимости?
А они не нужны для вышеописанных трех сценариев выборки одной записи.
Поэтому оракл создает файл нужного размера сразу, чтоб фрагменированность была минимальной.
Я же указал выше, что фрагментированность всё равно будет. Причем сам Oracle даже не будет знать, где в файле заканчивается один экстент и начинается другой. И, тем более, ничего не знает о sunit и swidth, что весьма актуально уже для любого RAID, в том числе и на SSD.
С другой стороны ФС совершенно ничего не знает про данные - какой прирост данных будет в файле, с какой интенсивность.
СУБД тоже об этом мало знает. Обычно, только в рамках одной транзакции. Но и файловая система будет сбрасывать кеш на диск только после завершения транзакции и получения sync(), а лишь тогда аллоцировать место на диске под закешированные данные. При этом статистикой записи в каждый файл она тоже располагает, эвристически вычисляя allocsize в каждом случае.
Ну, в лоб бы я попробовал 2 варианта: кастомный дата тайп и свежую STRING_SPLIT. Потом, возможно, ещё "нормализовал" бы в COLUMNSTORE. Можно покопаться в сторону битовых масок.
А у вас @jakobz какие были итерации и что получилось в итоге? Просто интересно...
Что-то можно изображать на CLR, но облачные mssql в него не умеют.
Из-за безопасности, поди? SQLCLR всем хорош. Я бы даже сказал, это киллер-фича MS SQL. Но из-за секьюрных ограничений даже в локальной инсталляции с ним слишком много геморроя.
Я уже указывал выше, что из-за архитектуры MS SQL один процесс и множество нитей, уложить всю СУБД, CLR там легко и просто. При этом возможности python или rust в качестве процедурных языков PostgreSQL даже шире, чем у CLR.
Понятно, что бесплатного ничего не бывает. Зато можно удобно из триггера через OLE Automation сгенерировать отчёт по таблице и приаттачить блобом. Я так делал. Я даже управление всем этим хозяйством обобщил в виде таблицы с правилами. Люблю таблицы.
Не знаю, как с поддержкой чисто майрософтовских технологий типа OLE обстоят дела в питоне, но подозреваю, не так хорошо, как в дотнете. Кроме того, это просто очень комфортный способ писать процедурный код (хотя мне кажется, слово «императивный» тут подошло бы больше). Для меня — вообще самый. В первую очередь благодаря FCL.
Не знаю, как с поддержкой чисто майрософтовских технологий типа OLE обстоят дела в питоне, но подозреваю, не так хорошо, как в дотнете.
Точно так же, как .net через Win32 API и COM. И точно так же это не будет работать в любой операционной системе, отличной от Windows, как в ,net, так и в Python. Попытки сделать COM кросcплатформенным оказались безрезультатны, так что, если уж очень хочется аналогии с OLE/COM, сейчас лучше смотреть в сторону D-Bus, чем "откапывать стюардессу".
это просто очень комфортный способ писать процедурный код
Никто не запрещает использовать .net (С# или F#) в качестве процедурного языка PostgreSQL. Но, на мой взгляд, Rust в этих целях более предсказуемый из-за отсутствия GC.
https://github.com/Brick-Abode/pldotnet
Теперь и в постгрес :)
я бы не сказал. в техническом плане оракл с его UNDO log заметно красивей. mssql пишет версии строк от версионности в tempdb, который и так узкое место. ну и кластер RAC/Exadata. в техническом плане оракл пока красивей, но цена делает их обоих мало кому интересными вне облаков.
PostgreSQL как раз очень сильный конкурент MS SQL. "Вы просто не умеете его готовить" (c).
Я тоже спец по MS SQL с более чем двадцатилетним стажем. У MS SQL множество преимуществ перед PostgreSQL. Но верно и обратное. Одни только массивы и композитные типы (включая массивы композитных типов) многого стоят. CLR в MS SQL - костыль, которым легко можно уложить весь сервер. Например, только при создании кастомных агрегатов я нарывался на это несколько раз. Вызов кода на других языках через sp_execute_external_script и launchpad - медленный и не эффективный. А все по той причине, что MS SQL - один процесс с множеством нитей на все соединения, тогда как PostgreSQL выполняет fork() для каждого соединения, порождая отдельный процесс. Что и позволяет безопасно в этом процессе выполнять код на любом из множества процедурных языков. При возникновении критической ошибки упадет только форкнутый процесс и одно соединение, а не вся СУБД.
Это даже не говоря о намного более широких возможностях расширения PostgreSQL, по сравнению с MS SQL. Я бы сказал, что между MS SQL и PostgreSQL +-паритет. Что то проще и/или эффективней делается на первом, что то на втором. Но мне ещё не встречались случаи, когда что то запрошенное заказчиком можно было реализовать на MS SQL, но нельзя на PostgreSQL. И наоборот.
Так ведь эта особенность c процессами под подключение жесткий такой косяк postgres, разве нет? Настолько, что сам по себе сервер мало лишь кто юзает, всегда в связке с пулером?
До сих пор страдаем с лимитом подключений кластера, пытаясь распределить их между юзерами. В mssql с его потоками даже не задумывались о такой проблеме, про пул воркеров вообще узнал, когда полез разбираться, почему там в этом плане все тип-топ)
P. S. А, и бэкап только кластера целиком та еще фича)
Это не косяк, а идеология, позволяющая моментально вызывать хранимые процедуры на python, R, Rust и т.п. напрямую, а не через отдельный сервис launchpad, как в MS SQL, медленно и печально.
При этом пулер позволяет решить проблему с большими издержками на создание процесса, вместо нити. А проблему медленного выполнения sp_execute_external_script в MS SQL можно решить, только отказавшись от него в пользу CLR. Но уже с риском уложить всю СУБД и только через разработку и тестирование.
До сих пор страдаем с лимитом подключений кластера
Там еще проблема в том, что они не стали заморачиваться глобальным кэшем для prepared statement, а сделали это на уровне сессии (процесса), сразу выделяя под него место на старте процесса. Мало того что это оверхэд на подготовку запроса, так еще оно и памяти немало ест когда сессий много. Возможно, если у вас так много сессий, поможет уменьшение размера этого кэша (хотя решение довольно спорное, надо смотреть по ситуации)
У сиквеля крах треда тоже не кладет сервер.
> Я бы сказал, что между MS SQL и PostgreSQL +-паритет. Что то проще и/или эффективней делается на первом, что то на втором.
Я начинал писать подобный вашему ответ и решил стереть, потому что не умею всё сразу и по полочкам. Со своей колокольни сравнивал бы как винду и линукс - всё те же непримиримые стороны, всё та же суть где одно лучше для одного, другое для другого и также стандартами от первого нельзя подходить ко второму и наоборот.
Тем не менее я согласен с тем кому вы отвечаете, что спецу с МССКЛ может быть тяжело переключиться на ПГ, если подходить к нему со своей привычкой. То есть проще его смотреть с нуля и опыт МС поможет в некоторых вещах. Администрирование абсолютно разное, а вот квери в более половины случаев совпадают (при изменении синтаксиса).
спецу с МССКЛ может быть тяжело переключиться на ПГ
Кому как. Мне, в свое время, переходить с IDMS на ADABAS, или с ADABAS на DB/2 было намного сложнее, чем потом с DB/2 на MS SQL и с MS SQL на PostgreSQL.
В IT подобные переходы - дело обычное и к ним нужно быть готовым.
Администрирование абсолютно разное
В IDMS многие административные операции требовали макроассемблер или COBOL. После этого администрирование PostgreSQL меня уже никак напугать не могло. С другой стороны и в MS SQL я подавляющее большинство административных операций выполнял средствами T-SQL, а не GUI.
Да, сложно. Во времена версий PostgreSQL 9.4 я себя буквально заставил. Ни разу не пожалел.
Главным толчком послужила попытка поставить на нетбук с Atom на борту, "лёгкий" SQL Server Express. После 4-х часов установки, я понял, что даже в случае успеха, перестановка и трабл шутинг могут занять бесконечное время. И попробовал поставить PostgreSQL. Установка заняла 3 минуты. Таких стендов предполагалось сотня, выбор был очевиден.
Оракл купил mysql, чтобы убить её развитие. Поэтому сейчас mysql хуже postgre.
Оракл купил mysql чтобы залезть в другую лигу. Postgresql во многом копирует oracle, и, хотя вечно в роли догоняющего, является прямым конкурентом на рынке условного enterprise.
/*дальше сугубо мое субъективное мнение поскольку с mssql знаком слабо*/ Mysql же когда-то была "простой базой для сайтов", с тех пор много чего поменялось, но все равно как мне кажется она позиционируется как более простая СУБД без этих вот всяких заморочек.
Простите, а когда мускул был лучше постгреса? Во времена MyISAM? :)
Чисто по фичам это СУБД разных классов. Для личных бложиков это может и неважно, но для чего-то более серьёзного, где там появляются хранимые процедуры и т.п. - мускул отваливается.
С другой стороны, для совсем топовых вещей, ну там банков, не знаю, уже и постгреса не хватает. В оракле вы можете начать транзакцию на боевой базе, накатить миграции схемы, запустить тесты, и если не понравилось - через полдня сделать rollback. Если, конечно, железо вытащит, но тем не менее.
Ах, ну да, oracle купил не мускул, а Sun, которой принадлежал мускул. Ну и "группа энтузиастов в красных шапках" сначала писала жалобы в ФАС и её аналоги в других странах, а потом резко сделала форк (MariaDB).
Автор свалил всё в кучу, пробежал верхами, местами ввёл в заблуждение и не сказал про действительно важные моменты. Прежде всего, СУРБД - это системы с очень широкой областью применения, они подойдут в большинстве задач. В то время, как остальные перечисленные системы - это более узкоспециализированные инструменты, которые обычно требуются для решения специфических проблем. Кроме того, PostgreSQL требует намного меньше ресурсов, чем например MongoDB или Datomic. Кстати про Datomic, это графовая СУБД, а не реляционная.
Сравнивать и не упоминать https://jepsen.io/analyses ?
у неё нет каких-то невероятных скрытых преимуществ
Для MySQL существуюет galeracluster и percona xtradb cluster. Они при помощи galera replication позволяют сделать multi-master. Что позволяет получить очень простую и эффективную отказоустойчивость с нулевым временем фейловера про потере одной из баз - достаточно просто воткнуть перед кластером балансер, типа хапрокси. И никаких проблем с улетевшими не на ту базу запросами - в мульти-мастере они просто спокойно выполнятся.
Для postgresql похожий уровень отказоустойчивости можно получить, только применяя всякие сложные сторонние решения. Для сопровождения которых, по моим ощущениям, потребуется отдельный DBA, глубоко разбирающийся в вопросе.
Я слышал, что на PostgreSQL жалуются девопсы, мол там сложнее её обслуживать чем Oracle или MsSQL. Хотелось бы конкретики, чем бесплатный PostgreSQL, хуже платных "ынтэрпрайз" решений, и если это незначительные вещи, тогда совет имеет смысл.
Если платные решения покупают, то значит это зачем-то нужно. Но вот хотелось бы понять, это из-за моды или же есть реальный случаи когда платные версии нужны.
Платные версии в первую очередь приобретают из-за технической поддержки
Я не девопс, но немного разобрался в этой теме. Поправьте если пишу ерунду.
Автовакум в постгресе иногда вызывает проблемы схожие со сборкой мусора, это в случае множества апдейтов рядов.
Насколько я понял чтобы обновить постгрес по сути база реимпортируется, там все завязано на бинарных форматах, без даунтайма почти никак. Может уже нерелевантно. В Мариадб просто ставишь новую версию и она работает со старым журналом.
В Постгресе надо коннекшены пулить, как и в Оракле. В Маридб и Мускуле это делать необязательно, те проблем с этим нет.
В мускуле и марии индексы создаются автоматически на форейн ки. В постгресе это надо делать руками.
Постгрес в моем бенче был на 40% быстрее при вставке чем Мария. Мария была на 10% быстрее в чтении. Возможно где-то это важно.
В Марии Мускуле нет схем и прочих пермишеннов, в каком-то смысле тикетов к девопсам с запросом прав будет меньше. Им проще.
В Постгресе есть 1 сторадж энжин. При супербольших таблицах в оракле марии и мускуле можно выключить транзакции и какие-то фичи и получить около 2.5х прибавку к производительности.
В мускуле и марии вроде бы как лучше тулинг для репликации по сравнению с пг. Уж то что он более старый и проверенный сомнений нет.
доступ к которой выполняется через границу сети.
может, пограничные сети?
Автор может ответить почему не ClickHouse
Если серьезно - там специфичный SQL, в котором например не работают correlated subquery. Но с другой стороны там работает много другого всего. Всему своё применение вобщем.
Я думаю, потому что речь была о OLTP, а не OLAP. Достаточно один раз сунуться в CH с INSERT/UPDATE по одной записи, чтобы понять, что для такого профиля нагрузки он совершенно не пригоден.
Ну и columnstore и join плохо совместимы по определению. Из-за чего в CH приходится дублировать массу полей в таблицах, что ставит жирный крест на консистентности.
При работе с реляционной базой данных можно перейти от получения всех домашних питомцев человека к получению всех владельцев домашнего питомца, добавив в таблицы один-два индекса.
Скорее не индекса, а внешних ключа.
Внешние ключи никак не влияют на производительность выборки данных. Это вид ограничения. А вот индексы действительно позволяют избежать сканирования данных. А иногда вообще исключить обращение к страницам данных.
Внешние ключи никак не влияют на производительность выборки данных
Я где-то утверждал обратное?
Смотрите внимательно: есть таблица, содержащая виды (типы) домашних питомцев. Всё верно? В оригинале:
можно перейти от получения всех домашних питомцев человека
Есть таблица владельцев домашних животных. В оригинале:
перейти ... к получению всех владельцев домашнего питомца
Таблица: Владельцы Таблица: Домашние Животные
+----+------------+ +----+------------+------------+
| ID | Имя | | ID | Вид | Владелец_ID|
+----+------------+ +----+------------+------------+
| 1 | Иван | | 1 | Кошка | 1 |
| 2 | Анна | | 2 | Собака | 1 |
| 3 | Ольга | | 3 | Хомяк | 2 |
| 4 | Павел | | 4 | Попугай | 3 |
+----+------------+ +----+------------+------------+
Получить владельцев домашних питомцев можно создав реляцию между этими двумя таблицами. И индексы здесь не при чём.
Нам нужно ключевое поле, на которое можно ссылаться и индекс по нему может быть вообще не построен. И внешний ключ. Строго говоря, внешний ключ тоже не обязателен. Но для целей самодокументирования и обеспечения ссылочной целостности — крайне желателен.
Вот и получается, что Вы не поняли о чём идёт речь, но минус влепили.
Всё верно?
Нет, так как фраза "к получению всех владельцев домашнего питомца" уже подразумевает, что у домашнего питомца могло быть несколько владельцев.
Получить владельцев домашних питомцев можно создав реляцию между этими двумя таблицами.
Между тремя. Таблицами. Вот только реляция далеко не всегда обозначает ограничение внешним ключом.
И индексы здесь не при чём.
Индексы позволяют избежать сканирования таблиц.
Теперь пример. Почти из жизни.
CREATE TABLE owners (
Id integer NOT NULL,
Valid daterange NOT NULL, -- период действия записи
Name varchar NOT NULL, -- человек может сменить имя
CONSTRAINT owners_PK_idx
EXCLUDE USING GIST (Id WITH =, Valid WITH &&)
);
CREATE TABLE pets (
Id integer NOT NULL,
Valid daterange NOT NULL, -- период действия записи
Name varchar NOT NULL, -- у питомца могут изменить кличку
CONSTRAINT pets_PK_idx
EXCLUDE USING GIST (Id WITH =, Valid WITH &&)
);
CREATE TABLE owners_to_pets (
Id integer NOT NULL,
Valid daterange NOT NULL,
own_id int NOT NULL,
pet_id int NOT NULL,
CONSTRAINT owners_to_pets_PK_idx
EXCLUDE USING GIST (pet_id WITH =, Valid WITH &&)
-- в конкретный период у питомца может быть только один владелец
);Никакие внешние ключи сюда не прикрутите, хотя консистентность можно контролировать триггерами.
Выборка всех владельцев питомца возможна по индексу owners_to_pets_PK_idx без сканирования таблицы owners_to_pets. А вот для того, чтобы наоборот, найти всех питомцов владельца на конкретный момент времени без сканирования таблицы, потребуется еще один индекс, например:
CREATE INDEX owners_to_pets_reverse_idx ON owners_to_pets
USING GIST (own_id, Valid) INCLUDE (pet_id);Нет, так как фраза "к получению всех владельцев домашнего питомца" уже подразумевает, что у домашнего питомца могло быть несколько владельцев.
Абсолютно согласен. Просто было лениво рисовать pivot таблицу. Но рад, что Вы наконец-то внимательно прочитали текст сообщения и даже потратили время на аргументацию. Жаль, что минус не отозвали.
Индексы позволяют избежать сканирования таблиц.
Каким образом это утверждение соотносится с утверждением автора:
При работе с реляционной базой данных можно перейти от получения всех домашних питомцев человека к получению всех владельцев домашнего питомца, добавив в таблицы один-два индекса.
Никакие внешние ключи сюда не прикрутите, хотя консистентность можно контролировать триггерами.
Да ладно?
CREATE TABLE owners_to_pets (
Id integer NOT NULL,
Valid daterange NOT NULL,
own_id int NOT NULL,
pet_id int NOT NULL,
CONSTRAINT owners_to_pets_PK_idx
EXCLUDE USING GIST (pet_id WITH =, Valid WITH &&),
-- в конкретный период у питомца может быть только один владелец
-- Внешние ключи
CONSTRAINT fk_owners
FOREIGN KEY (own_id)
REFERENCES owners(Id)
ON DELETE CASCADE,
CONSTRAINT fk_pets
FOREIGN KEY (pet_id)
REFERENCES pets(Id)
ON DELETE CASCADE
);Держите внешние ключи. Но будет и без них работать, как я и написал выше. Просто просторечно поля вида own_id и pet_id называют внешними ключами, хотя внешние ключи могут быть и не построены.
Выборка всех владельцев питомца возможна по индексу owners_to_pets_PK_idx без сканирования таблицы owners_to_pets. А вот для того, чтобы наоборот, найти всех питомцов владельца на конкретный момент времени без сканирования таблицы, потребуется еще один индекс …
Я откровенно не понимаю, почему Вы прицепились к сканированию таблицы?
Поясняю, даже без единого индекса, СУБД найдёт искомые записи, уж как оно будет это делать, эффективно или нет, неважно. НАЙДЁТ!
А вот если выкинуть поля own_id и pet_id — НЕ НАЙДЁТ!
В оригинале не идёт речь о том, что можно получить всех владельцев домашнего питомца без использования seq scan. А о том, что их можно получить, добавив...
Вот на мой взгляд, добавив ключевые поля. А никак не индексы.
Жаль, что минус не отозвали.
Как я могу отозвать то, что не ставил?
Да ладно?
Хотя бы сами попробовали бы такие внешние ключи создать, чтобы полюбоваться на сообщение "ERROR: there is no unique constraint matching given keys for referenced table "owners"".
Там же по таблицам видно, что смена имени у владельца вовсе не влияет на принадлежность ему питомца. Так же как смена имени у питомца не влияет на принадлежность его владельцу. А смена владельца у питомца может быть без изменения имени питомца и имен прежнего или нового владельцев.
Поэтому нет и не может быть однозначного соответствия между записями owners и pets. Оно однозначно только для момента времени, но не для всего периода в owners_to_pets.
Я откровенно не понимаю, почему Вы прицепились к сканированию таблицы?
Потому что это первое на что смотришь в плане запроса. У меня в практике еще не попадались проекты, где допускалось сканирование таблицы, если данные в ней занимают больше, чем несколько страниц и выбираются единицы или доли процентов от общего количества записей.
В оригинале не идёт речь о том, что можно получить всех владельцев домашнего питомца без использования seq scan. А о том, что их можно получить, добавив...
Ну это лично Ваше восприятие. У меня оно противоположное. Я чуть ли не каждую неделю леплю Decline на PR именно из-за SeqScan в плане запроса. Даже по крошечной таблице из тысячи строк.
Как я могу отозвать то, что не ставил?
Прошу искренне извинить. Из контекста на Вас подумалось.
Потому что это первое на что смотришь в плане запроса
Так я ведь с этим и не спорю. Все знают, что seq scan на больших объёмах данных это нехорошо.
Ну это лично Ваше восприятие
Ну да. Это моё восприятие. Мне показалось, что автор имеет ввиду, что для поиска соподчинённых данных нужно всего лишь добавить ключевые поля. Даже своё восприятие аргументировал. А тут мне минусы лепят и рассказывают очевидные вещи о пользе индексирования. Я уж весь мозг изломал, что же я такого крамольного написал.
Хотя бы сами попробовали
О, и верно. Был не прав.
Внешние ключи никак не влияют на производительность выборки данных
Ну, смотря насколько "умён" движок - он может проверить первичный ключ во внешней таблице и если там пусто по фильтру, то тупо не пойдёт в запрашиваемую таблицу.
Не, ну то, что автор первым делом начинает сравнивать сабж с SQLite, которая вообще не столько СУБД, сколько библиотека доступа к данным с SQL-синтаксисом - для многих читателей это будет сигналом закрыть статью и не открывать её больше. Да, ниже уже упоминаются Oracle и MS SQL Server, но до этого места надо ещё дойти. Понятно, что автор хотел упростить себе задачу (преимущества PostgreSQL перед SQLite, разумеется, показать, куда легче), но эффект, боюсь, для читателей, которые что-то знают про базы данных, получился прямо противоположным. Если бы сразу начать сравнение Postgre vs Oracle - статья бы выглядела более серьёзной. :)
Упоминание NoSQL-решений... Вообще, это крайне холиварная тема, но правильнее всего было бы упомянуть, что реляционные и нереляционные СУБД можно не противопоставлять, а сочетать. И в некоторых крупных проектах так и делают (если не ошибаюсь, про авито был такой доклад, и кажется, там PostgreSQL и упоминался, но это неточно). А так да, если человек не собирается делать миллионы транзакций в минуту и сразу тащит в рот NoSQL исключительно из соображений того, что он сэкономит время на проектировании структуры данных - он делает неправильно, тут соглашусь с автором.
Наконец, если кто-то затевает долгоиграющий проект с сильно более чем одним внедрением - ему стоит подумать над тем, чтобы сделать СУБД "сменной". У меня, например, есть опыт разработки проекта, где хранилище могло быть либо клиент-серверным (PostgreSQL), либо локальным (внутренняя СУБД - SQLite плюс выгрузка-загрузка в XML либо JSON, для пользователя локального варианта это выглядит просто как работа с XML-файлами), при этом по необходимости можно было легко перейти от одного варианта к другому, просто загрузив XML в PostgreSQL утилитой импорта. При этом порядка 90% SQL-запросов работали одинаково на обеих СУБД (кое-где таки пришлось сделать СУБД-специфичные ветки и то, главным образом, для оптимизации).
Так, а самый главный конкурент MariaDB почему не упомянут? Там обещают и совместимость с оракловскими процедурами и какие-то другие фишки и совместимость с MySql с лучшим перформансом
автор очень аккуратно не упомянул Firebird...
он же не некромант
ваша шутка оч. могучая, конечно. Впрочем, если автор оригинального текста живет в США, то там - да, Firebird не имеет популярности. А вот в Бразилии, России, и ряде стран Европы - очень даже.
Firebird активно развивается. Другое дело, что по функциональности ему до PostgreSQL очень далеко. Если поддержку JSON/XML хотя бы обещают, то, например, про секционирование таблиц в нем я вообще не слышал, так сначала надо туда параллельные планы запросов завезти. Так что они просто пока в разных весовых категориях.
Если вы видите, что студент или выпускник использует MongoDB, то остановите его. Ему нужна помощь. Его ввели в заблуждение.
Пожалуйста, ставьте плюсы статье и автору только за эту цитату, я вас прошу. Господи, наконец-то на хабре правда!
Ещё преимущество MSSQL, например, что T-SQL умеет бекапить в 1 файл и не было такого, что бэкап не полный или не работал. И он легко бэкапит на ходу. Но его подымет только текущая или любая последующая версия MSSQL. По мне хороший компромисс за предсказуемость и стабильность бекапов, про сравнению с PostgreSQL.
Смешались в кучу люди, кони...
Чем холодильник лучше микроволновки?
Полезная статья.
Действительно postgres сейчас развивается очень быстро и наверное совсем скоро навяжет конкуренцию oracle db.
Просто выберите Postgres