Сравнение баз данных MY SQL, POSTGRESQL, SQL SERVER
Выбор между базами данных SQL и базами данных, отличными от SQL, обычно сводится к различиям в структуре. Однако, когда мы рассматриваем несколько решений SQL, критерии гораздо более искажены. Теперь рассмотрим аспекты более точно и проанализируем базовый функционал. Мы сделаем сравнение СУБД: MySQL, Postgresql и SQL server.
MySQL является одной из самых популярных баз данных. Это определенный лидер среди SQL-решений, используемых Google, LinkedIn, Amazon, Netflix, Twitter и другими. MySQL популярность сильно растет, потому что команды все чаще предпочитают решения с открытым исходным кодом вместо коммерческих.
Цена: решение базы данных разработано Oracle и имеет дополнительные платные инструменты; доступ к основной функциональности можно получить бесплатно.
Исходный код: открытый.
Операционная система: FreeBSD, Linux, OS X, Solaris, Windows.
Проверенная реляционная база данных, которая известна поддержкой большого количества типов данных, интуитивно понятным хранением бессхематических данных и богатой функциональностью. Некоторые разработчики заходят даже так далеко, что утверждают, что это самая передовая база данных с открытым исходным кодом на рынке. Мы не будем поддерживать или опровергать, но это, безусловно, очень универсальное решение.
Исходный код: открытый.
Операционная система: FreeBSD, Windows, HP-UX, Linux, NetBSD, OpenBSD, OS X, Solaris, Unix.
В отличие от Postgresql против MySQL, SQL Server является коммерческим решением. Его предпочитают компании, которые регулярно сталкиваются с большими нагрузками трафика. Она также считается одной из наиболее совместимых систем со службами Windows.
Инфраструктура SQL Server включает в себя множество дополнительных средств, таких как службы отчетов, системы интеграции и аналитика. Для компаний, которые управляют несколькими командами, эти инструменты имеют большое значение в повседневной работе.
Цена: база данных имеет бесплатную версию для разработчиков и малых предприятий, но поддерживает только 1 процессор, 1 ГБ максимальной памяти, используемой ядром СУБД, и максимальный размер базы данных 10 ГБ. За сервер пользователям нужно заплатить $931.
Операционная система: Linux, Windows.
В этом сравнении мы рассмотрим функциональность трех самых популярных баз данных SQL, рассмотрим их варианты использования, соответствующие преимущества и недостатки. Во-первых, мы начнем с изучения углубленной функциональности.
Здесь мы оцениваем легкость, с которой данные могут быть изменены, а база данных дефрагментирована. Ключевым приоритетом является гибкость, безопасность и удобство использования систем.
Этот критерий относится к алгоритмам, которые база данных использует для обновления своего содержимого, скорости и эффективности.
MySQL: решение автоматически обновляет данные в хранилище отката. Если что-то пойдет не так, разработчики всегда могут вернуться к предыдущей версии.
PostgreSQL: разработчики вставляют новый столбец и строку, чтобы обновить базу данных. Все обновленные строки имеют уникальные идентификаторы. Это умножает количество столбцов и строк и увеличивает размер базы данных, но, в свою очередь, разработчики выигрывают от более высокой удобочитаемости.
SQL Server: база данных имеет три ядра, которые отвечают за обновления строк. Хранилище строк обрабатывает информацию обо всех предыдущих обновлениях строк, идентификаторах и измененном содержимом. Механизм in-memory позволяет анализировать качество обновленной базы данных с помощью сборщика мусора. База данных хранилища столбцов позволяет хранить обновления в столбцах, например в базах данных, управляемых столбцами.
Среди этих трех SQL Server предлагает, пожалуй, наибольшую гибкость и эффективность, поскольку он позволяет отслеживать обновленные строки и столбцы, собирать ошибки и автоматизировать процесс. Разница между SQL Server и MySQL и Postgresql заключается главным образом в настройке позиций — SQL Server предлагает гораздо больше, чем другие.
Когда разработчики обновляют различные части базы данных SQL, изменения происходят в разных точках систем и могут быть трудными для чтения, отслеживания и управления. Поэтому обслуживание должно включать дефрагментацию — процесс унификации обновленной базы данных путем присвоения индексов, пересмотра структуры и создания новых страниц. База данных освобождает неправильно используемое дисковое пространство, чтобы база данных могла работать быстрее.
MySQL предлагает несколько подходов к дефрагментации – во время резервного копирования, создания индекса и с помощью Таблица OPTIMIZE команды. Наличие такого количества вариантов обслуживания столов удобно для разработчиков, и это экономит много времени.
PostgreSQL позволяет сканировать все таблицы уровня данных, чтобы найти пустые строки и удалить ненужные элементы. Таким образом, система освобождает дисковое пространство. Однако метод требует загружает процессор и может повлиять на производительность приложения.
SQL Server предлагает эффективный сборщик мусора, но это не дает более 15-20% накладных расходов. Технически разработчики могут даже запускать сборщик мусора на постоянной основе, потому что это так эффективно.
В целом, MySQL и SQL Server предлагают больше методов дефрагментации, чем Postgresql. Они меньше нагружают процессор и обеспечивают более гибкие настройки.
Здесь мы рассмотрим, как системы кэшируют и обрабатывают запросы пользователей, какие подходы они используют при хранении данных, и как разработчики могут управлять ими.
Некоторые системы вызывают буфер для извлечения кэша, но независимо от терминологии, наша цель состоит в том, чтобы обобщить алгоритмы, которые системы используют для обработки пользовательских запросов и поддержания соединений.
MySQL предлагает масштабируемый буферный пул — разработчики могут настроить размер кэша в соответствии с рабочей нагрузкой. Если цель состоит в том, чтобы сэкономить пространство ЦП и хранилища, разработчики могут установить строгие тесты производительности в своем буферном пуле. Кроме того, MySQL позволяет разделить кэш по сегментам для хранения различных типов данных и максимальной изоляции.
PostgreSQL изолирует процессы даже дальше, чем MySQL, рассматривая их как отдельный процесс ОС. Каждая база данных имеет отдельную память и запускает свой собственный процесс. С одной стороны, управление и мониторинг становятся намного проще, но с другой стороны, масштабирование нескольких баз данных занимает много времени и вычислительных ресурсов.
SQL Server также использует буферный пул, и так же, как и в MySQL, он может быть ограничен или увеличен в соответствии с потребностями обработки. Вся работа выполняется в одном пуле, без нескольких страниц, как в Postgresql.
Если вашим приоритетом является экономия вычислительных ресурсов и хранилищ, выбирайте гибкие решения: выбор будет между MySQL и SQL Server. Однако, если вы предпочитаете четкую организацию и долгосрочный порядок, Postgre, с его изолированным подходом, может быть более подходящим.
Временные таблицы позволяют хранить промежуточные результаты сложных процедур и разветвленной бизнес-логики. Если вам нужна какая-то информация только для питания следующего процесса, нет смысла хранить ее в обычной таблице. Временные таблицы улучшают производительность и организацию базы данных, отделяя промежуточные данные от основной информации.
MySQL предлагает ограниченные функциональные возможности для временных таблиц. Разработчики не могут задавать переменные или создавать глобальные шаблоны. Программное обеспечение даже ограничивает количество раз, когда временная таблица может быть упомянута — не более одного раза.
Postgresql предлагает гораздо больше функциональности, когда дело доходит до временного контента. Временные таблицы делятся на локальные и глобальные и настраиваются с помощью гибких переменных.
SQL Server также предлагает широкие функциональные возможности для временного управления таблицами. Можно создавать локальные и глобальные временные таблицы, а также контролировать и создавать переменные.
Временные таблицы необходимы для приложений со сложной бизнес-логикой. Если ваше программное обеспечение запускает много сложных процессов, вам нужно будет хранить несколько промежуточных результатов. Наличие богатых функциональных возможностей настройки часто будет необходимо на протяжении всего процесса разработки.
Способ обработки индексов базой данных имеет важное значение, поскольку они используются для поиска данных без поиска определенной строки. Индексы могут ссылаться на несколько строк и столбцов. Вы можете назначить один и тот же индекс файлам, расположенным в разных местах базы данных, и собрать все эти фрагменты одним поиском.
В этом сравнении мы оценили способ создания индексов в каждом решении, поддержку поиска с несколькими индексами и индексов с несколькими столбцами, а также частичных.
MySQL организовал индексы в таблицах и кластерах. Разработчики могут автоматически находить и обновлять индексы в своих базах данных. Поиск не очень гибкий — вы не можете искать несколько индексов в одном запросе. MySQL поддерживает индексы с несколькими столбцами, что позволяет добавлять до 16 столбцов.
Postgresql также поддерживает организацию таблиц на основе индексов, но ранние версии не включают автоматические обновления индексов. Решение также позволяет искать много индексов в одном поиске, а это значит, что вы можете найти много информации. Настройки с несколькими столбцами также более гибкие, чем в MySQL — разработчики могут включать до 32 столбцов.
SQL Server предлагает широкие автоматизированные функциональные возможности для управления индексами. Они могут объединяться в кластеры и поддерживать правильный порядок строк без ручного вмешательства. Решение также поддерживает поиск по нескольким индексам и частичные индексы. Наличие гибких настроек индекса позволяет быстрее искать информацию и организовывать несколько данных одновременно.
Оптимизированные для памяти таблицы в основном известны как Концепция SQL Server, но они также существуют в других решениях для управления базами данных. Такая таблица хранится в активной памяти и на дисковом пространстве упрощенным способом. Чтобы увеличить скорость транзакций, приложение может просто получить доступ к данным непосредственно на диске, не блокируя параллельные транзакции. Для процессов, которые происходят на регулярной основе и обычно требуют много времени, оптимизированная для памяти таблица может быть решением для повышения производительности базы данных.
MySQL поддерживает таблицу, хранящуюся в памяти, но не может участвовать в транзакциях, а ее безопасность очень уязвима. Такие таблицы используются только в целях чтения и могут упростить исключительно примитивные операции. На данный момент MySQL не приближается к тому, чтобы максимально использовать оптимизированные для памяти таблицы.
PostgreSQL не поддерживает создание базы данных в памяти.
SQL Server использует оптимистичную стратегию для обработки оптимизированных для памяти таблиц, что означает, что они могут участвовать в транзакциях наряду с обычными таблицами. Транзакции на основе памяти быстрее, чем обычные, и это позволяет резко увеличить скорость приложения.
Как и ожидалось, оптимизированные для памяти таблицы лучше всего настраивать в MySQL — это в основном их собственный подход. Это не важная функция базы данных, но все же хороший способ повысить производительность.
Использование файлов JSON позволяет разработчикам хранить нечисловые данные и достигать более высокой производительности. Документы JSON не нужно разбирать, что способствует гораздо более высокой скорости обработки. Они легко читаемы и доступны, поэтому поддержка JSON упрощает обслуживание. Файлы JSON в основном используются в нереляционных базах данных, но в последнее время SQL-решения также поддерживают этот формат.
MySQL поддерживает файлы JSON, но не позволяет индексировать их. В целом, функциональность JSON-файлов в MySQL очень ограничена, и разработчики в основном предпочитают использовать классические строки. Как и нереляционные базы данных, MySQL также позволяет работать с геопространственными данными, хотя обработка их не так интуитивно понятна.
Postgresql поддерживает JSON-файлы, а также их индексацию и частичные обновления. База данных поддерживает даже больше дополнительных данных, чем MySQL. Пользователи могут загружать определенные типы, геопространственные данные, создавать многомерные массивы и многое другое.
SQL Server также обеспечивает полную поддержку документов JSON, их обновлений, функциональности и обслуживания. Он имеет множество дополнительных функций для данных GPS, пользовательских типов, иерархической информации и т. Д.
В целом, все три решения довольно универсальны и предлагают множество функциональных возможностей для нестандартных типов данных. MySQL, однако, накладывает несколько ограничений на файлы JSON, но помимо этого, он очень совместим с расширенными данными.
Когда приложение растет, один сервер больше не может справиться с рабочей нагрузкой. Навигация по одному хранилищу усложняется, и разработчики предпочитают переходить на разные или, по крайней мере, создавать разделы. Процесс секционирования — это создание множества отсеков для данных в одном процессе.
Замена в базах данных NoSQL проще, поскольку они поддерживают горизонтальное масштабирование, а не вертикальное, увеличивая количество местоположений, а не размер одного. Тем не менее, можно распределить данные между различными отсеками даже в решениях SQL, даже если это немного менее эффективно.
MySQL позволяет секционировать базы данных с функциями хэширования для распределения данных между несколькими узлами. Разработчики могут создать определенный ключ секции, который определит расположение данных. Хэширование позволяет избежать узких мест и упростить обслуживание.
Postgresql позволяет создавать разделы LIST и RANGE, где индекс раздела создается вручную. Разработчикам необходимо определить дочерний и родительский столбцы, прежде чем назначать для них раздел.
SQL Server также предоставляет доступ к секционированию RANGE, где раздел назначается всем значениям, попадающим в определенный диапазон. Если данные находятся в пределах порогового значения, они будут перемещены в секцию.
Экосистема баз данных важна, поскольку она определяет частоту обновлений, доступность учебных ресурсов, спрос на рынке.
MySQL является частью экосистемы Oracle. Это самая большая база данных SQL на рынке с большим сообществом с открытым исходным кодом. Разработчики могут либо приобрести коммерческие дополнения, разработанные командой Oracle, либо использовать бесплатные установки. Вы легко найдете инструменты для управления базами данных, мониторинга, оптимизации и обучения. Сама база данных проста в установке — все, что вам нужно сделать, это в значительной степени загрузить установщик.
MySQL является надежным решением для баз данных в течение 25 лет, и статистика не указывает ни на какие признаки его упадка. Похоже, что MySQL продолжит удерживать лидирующие позиции не только среди SQL-инструментов, но и среди всех баз данных в целом.
Сообщество Postgresql предлагает множество инструментов для масштабирования и оптимизации программного обеспечения. Вы можете найти дополнения по вашей отрасли — взгляните на полный список на официальная страница. Интеграция позволяет разработчикам выполнять кластеризацию, интеграцию ИИ, совместную работу, отслеживание проблем, улучшение отображения объектов и охватывать многие другие важные функции.
Некоторые разработчики отмечают, что процесс установки Postgresql немного сложен — вы можете взглянуть на его официальный учебник. В отличие от MySQL, который может запускаться сразу, Postgresql требует дополнительных установок.
SQL Server обладает высокой совместимостью с Windows и всеми ОС и инструменты Майкрософт. Если вы работаете с Windows, SQL Server, безусловно, лучший вариант на рынке. Пользователи базы данных получают доступ ко многим дополнительным инструментам, которые охватывают мониторинг сервера, анализ данных, парсинг и программное обеспечение для управления безопасностью.
Экосистема SQL Server ориентирована на большие инфраструктуры. Это дороже, чем конкуренты с открытым исходным кодом, но в конце концов пользователи получают доступ к часто обновляемой официальной экосистеме и активной поддержке клиентов.
В чем разница между SQL и MySQL? MySQL — это база данных с открытым исходным кодом, в то время как SQL Server — коммерческая. MySQL более популярен, но SQL Server приближается к этому.
MySQL является самой востребованной базой данных на рынке, а это означает, что найти компетентные команды, учебные ресурсы, многоразовые библиотеки и готовые дополнения будет легко. Таким образом, если вы выбираете между SQL Server и MySQL с точки зрения рыночных тенденций, последний является лучшим выбором.
Компании, использующие MySQL:
- Google;
- Udemy;
- Netflix;
- Airbnb;
- Amazon;
- Pinterest.
MySQL широко используется крупными корпорациями и правительственными организациями. За последние 25 лет решение завоевало репутацию надежного решения для управления базами данных, и, как показывает время, оно действительно способно поддерживать долгосрочные проекты.
Компании, использующие PostgreSQL:
- Apple;
- Skype;
- Cisco;
- Etsy.
Postgre известен своей интуитивно понятной функциональностью и универсальными настройками безопасности. Вот почему его основными вариантами использования являются правительственные платформы, приложения для обмена сообщениями, видеочаты и платформы электронной коммерции.
Компании, использующие SQL Server:
- JPMorganChase;
- Bank of America;
- UPS;
- Houston Methodist.
SQL Server — это выбор для крупных предприятий, которые имеют обширную бизнес-логику и обрабатывают несколько приложений одновременно. Команды, которые отдают приоритет эффективности и надежности, а не масштабируемости и затратам, обычно выбирают эту базу данных. Это распространенный вариант для «традиционных» отраслей – финансов, безопасности, производства и других.
Какая база данных лучше из трех наиболее популярных баз данных в конечном итоге сводится к сравнению функциональности, вариантов использования и экосистемам. Компании, которые отдают приоритет гибкости, экономической эффективности и инновациям, обычно выбирают решения с открытым исходным кодом. Они могут быть интегрированы с несколькими бесплатными дополнениями, иметь активные сообщества пользователей и постоянно обновляться.
Для корпораций, которые предпочитают традиционные коммерческие решения, программное обеспечение, такое как SQL Server, резервное копирование крупной корпорации и совместимое с обширной инфраструктурой, является лучшим выбором. Они имеют доступ к постоянной технической поддержке, персонализированной помощи и профессиональным инструментам управления.
PostgreSQL vs MS SQL для 1С
Давайте признаемся честно, хоть 1С Предприятие и совместимо со многими СУБД, но по факту 99 процентов работают либо в MS SQL или в бесплатной PostgreSQL.
Другими словами эти две «субдешки» завоевали рынок клиент-серверной 1С.
И можно смело предполагать, что если компания не работает в MS SQL то, скорее всего, просто используют PostgreSQL.
Соответственно сравнивать «Постгрес» есть смысл разве что только с MS SQL.
Сегодня много пишут, как об MS SQL так и о PostgreSQL, но обычно объективно не сравнивают их.
В этой статье мы же разберем основные технические моменты бесплатной PostgreSQL, сравнивая ее c MS SQL.
Конечно, данная тема также подымается и на курсе: Администратор 1С!
Что позволит Вам в будущем сделать оптимальный выбор и быть готовым к разным «неожиданностям» или что будет более правильно «особенностям» работы в этой бесплатной СУБД.
Оценивать будем все как есть, не приплюсовуя Постгресу тех заслуг, которых у него нет и, не приукрашивая платный MS.
Сразу отвечу на вопрос, который волнует многих новичков!
ДА! MS SQL работает быстрее PostgreSQL, это факт! И на это есть ряд причин!
Возможно, я кого-то прямо сразу разочаровал, и возможно, Вы не согласны с таким утверждением, извиняюсь, но сама физика работы этой бесплатной СУБД не позволяет ему опередить MS SQL, особенно если мы говорим о такой связке как «Монстр 1С» и PostgreSQL.
Подобные доводы Вы не раз встретите на различных конференциях и семинарах посвященных этой СУБД. Никто ничего не скрывает и не отрицает, факт есть факт.
Тем не менее, производительности PostgreSQL вполне достаточно, для того, чтоб пользователи могли комфортно работать в 1С.
Будь это десяток пользователей или даже несколько сотен, одновременно работающих в 1С Предприятии.
Почему «Монстр 1С» ?
Собственно так 1С видит PostgreSQL без установленных специальных «патчей» и расширений.
Да, что называется из коробки, скачав дистрибутив PostgreSQL на оф. сайте Вы не сможете использовать его для работы в связке с 1С. 1С-ка будет жутко тормозить и просто останавливаться, отказываться работать.
Почему так происходит, и зачем «патчи»?
Дело в том что 1С Предприятие создает огромное количество временных таблиц в процессе своей работы, речь может идти о тысячах таблиц в секунду, а если взять, например регистр «Срез последних» — «ОстаткиИОбороты», там вполне могут и по миллиону строк быть.
Дело в том что по умолчанию (без «патчей») PostgreSQL не считает статистику по этим большим временным таблицам, другими словами оптимизатор запросов который руководствуется данными из статистики (а она как помним пуста, нечего считать) грубо говоря, делает выборку методом SELECT * что конечно будет работать очень и очень медленно!
Отсюда грандиозные тормоза в 1С!
Конечно это не все проблемы, которые нужно решить, чтоб PostgreSQL работал в паре с 1С нормально. Нужны будут и другие «патчи» и специальные расширения и после 15-20 пользователей, еще и доп. настройки в «конфиге»
Да, на самом деле в реалии все выглядит намного сложнее, чем я описал выше, но вот так если сильно упростить и будет выглядеть основная проблема медленной работы 1С с PostgreSQL.
Второе что мне сильно не нравится в PostgreSQL это отсутствие многопоточности в рамках одного запроса в сравнении с MS SQL.
(Начиная с версии 9.6 сделали первую попытку распараллеливания запросов, но пока работает плохо, иногда эффект обратный ). но за попытку 5! )
Что конечно влияет на производительность, чтоб Вы понимали простым языком —
PostgreSQL способен уложить Ваш 48-ми ядерный сервер, одним большим запросом!
Все просто, распараллеливания потоков в рамках одного запроса нет и один большой запрос «грузит» только одно ядро.
Да, если запросов много, тогда все ядра будут нагружены, и все будет работать хорошо.
И чуть не забыл, сравниваем мы PostgreSQL c MS SQL Standard не Express!
Express хоть и можно использовать в коммерческих целях, но целый ряд ограничений
таких как 10 Гб на базу, использование одного процесора, 1 Гб оперативной памяти,
делает использование такого продукта почти нереальным для работы в 1С Предприятии.
Разве что у вас очень маленькая база и всего пара пользователей, (да и то бывают тормоза 1 гб для СУБД очень мало).
Так что сравниваем PostgreSQL с популярной версией Standard.
СКРИПТЫ.
PostgreSQL это прежде всего скрипты в сравнении с MS SQL, большинство операций приходится делать руками, да можно установить конечно pgAdmin 4 и некоторые базовые вещи выполнять через интерфейс, но подчеркну, что базовые , а шаг влево шаг вправо и нужно писать скрипт, или БАШ на Линуксе или cmd, powershell наWindows.
Просмотр и анализ трассировок с помощью приложения SQL Server Profiler.
Всем известный SQL Server Profiler в PostgreSQL отсутствует, причем под словом «отсутствует» я имею, введу напрочь, увы, нет ничего подобного в PostgreSQL.
Есть, конечно, утилиты, которые позволяют, если успеть перехватить запрос или поставить точку останова 1С в отладчике и что-то получит и посмотреть, но в сравнении с Профайлером как говорится и близко не стояло.
Можно настроить лог и потом это все перебирать — но долго!
Вот пример:
Программист 1С пытается отладить какой-нибудь большой запрос, он долго выполняется, например 30 минут, так вот в PostgreSQL, чтоб данные попали в лог, этот запрос должен выполнится! Представляете, как долго можно отлаживать такой запрос?
В то время как в MS SQL можно прервать выполнение запроса и в Профайлере его разобрать, так как он там уже будет, но со статусом «failed».
По разновидности создания «бэкапов» Постгресу нет равных!
Здесь Вам и инкрементный «бекап» и полное резервное копирование и непрерывное WAL архивирование.
Как собственно есть и частичное резервное копирование и частичное восстановление данных.
Можно настроить непрерывное архивирование и восстановление на момент времени (Point-in-Time Recovery (PITR)).
Также репликация, доступна изначально в PostgreSQl без каких либо «патчей» утилит и дополнений!
- Каскадная репликация
- Потоковая репликация
- Синхронная репликация
- Непрерывное архивирование на резервном сервере
Все это есть, уже изначально в PostgreSQl и конечно нет в «экспрессе» и недоступно на версии MS SQL Standard.
Чтоб получить все выше перечисленное в MS SQL, нужно покупать очень дорогой MS SQL Enterprise, сейчас что-то около 15 000$ долларов.
Чего нет в сравнении с MS SQL ?
НЕТ диференциального «бэкапа»
Да в PostgreSQl нет дифференциального «бэкапа», но есть различные аналоги инкрементного создания «бэкапов».
Например, инкрементный «бэкап» на уровне блоков.
ЕСТЬ разделение TABLESPACE-ов, что уже по умолчанию поддерживает 1С!
Которого к слову нет в MS SQL!
Например, Вы можете настроить на каком диске у вас будут «индексы» и на каком диске будет находиться «таблица», очень удобно при планировании IТ инфраструктуры, когда речь идет о больших базах данных 1С.
PostgreSQl намного быстрее обновляет статистику (если точнее — считает ее):
Например тот же MS SQL в терабайтной базе может считать статистку час, а вот PostgreSQL минуты 2-3. )
Внимание!
MS SQL в «бэкап» помещает уже посчитанную статистику, чего не делает PostgreSQL!
И когда Вы восстановитесь из такого «бэкапа» будут тормоза, пока не сделаете ONLINE_ANALYZE, чтоб пересчитать статистику. Тоже самое касается файла *dt.
Выгрузили – загрузили, нужно считать статистику ONLINE_ANALYZE!
Используя PostgreSQl очень редко нужен REINDEX!
Фактически стоит использовать только когда, есть подозрения, что целостность базы данных повреждена.
Можно делать «бэкапы» с исключением таблиц!
Например, у вас в компании работают несколько программистов 1С, они гарантированно будут делать себе резервные копии создавать «бэкапы» для дальнейшей разработки.
В итоге страдают пользователи, база тормозит во время создания большого «бэкапа» особенно если в этой базе есть такие вещи как различные прикрепленные файлы, архивы, документы из писем. Такие таблицы с файлами запросто могут содержать сотни гигабайт. И их же можно исключить в PostgreSQl создавая «бэкап», тем самым малого размера, и со всем функционалом одновременно.
Так мы лишний раз не нагружаем сетевые устройства, не забиваем канал, тратим намного меньше времени на создание такого «бэкапа»
В итоге все в выигрыше! И пользователи, и программисты и админы спят спокойно.
В этой статье мы разобрали лишь базовые отличия PostgreSQl от MS SQL, (есть и другие) но определится с выбором в пользу той или иной СУБД, статья должна помочь!
P.S. Сейчас работаю над новым курсом «1С и PostgreSQL» (Уже на стадии записи, ждите, скоро!)
Если Вы хотите больше узнать о технической стороне 1С, тогда регистрируйтесь на первый бесплатный модуль курса: Администратор 1С >>>
Сравнение производительности 1С при использовании СУБД PostgreSQL и MS SQL
В связи со стремительной девальвацией рубля, покупать СУБД Microsoft SQL стало очень дорого, а для некоторых компаний стоимость этих лицензий стала совсем «неподъемной». В данный момент чтобы развернуть сервер Microsoft SQL для 20 пользователей необходимо купить такие лицензии:
1 лицензия на операционную систему (WinSvrStd 2012R2 )
20 лицензий на подключение к серверу (WinSvrCAL 2012)
1 лицензия на сервер СУБД (SQLSvrStd 2014)
20 лицензий на подключение к СУБД (SQLCAL 2014)
Ориентировочная стоимость такого пакета 275 000 руб., что для компании, в которой всего 20 человек достаточно дорого. Данных затрат можно избежать, если создать сервер СУБД на свободном ПО. Поставить операционную систему семейства Linux и бесплатную версию СУБД – PostgreSQL . На таком сервере без проблем можно развернуть сервер 1С предприятия, а также другие роли, которые потенциально могут быть совмещены c ролью баз данных, например WebServer или файловое хранилище.
Так как использовать свободное ПО очень привлекательно с финансовой точки зрения, было решено проверить, на сколько это хорошо с точки зрения производительности.
Тестирование производительности 1С:
Для выполнения теста было взято оборудование и программное обеспечение, указанное в таблице 1. Физический сервер для обоих стендов использовался один и тот же, менялось только ПО. Настройки обоих СУБД использовались по-умолчанию и в статье мы их подробно не расписываем. Дистрибутив PostGreSQL с соответсвующими патчами были взят с сайта компании 1С, версия — последняя из доступных на данном сайте.
Таблица 1. Тестовые стенды
Характеристики
Windows Server 2012R2
Microsoft SQL Server 2012R2
Intel Core i 5 3330 (3.0 Ghz )
24 GB DDD 3 1333 Ghz
SSD 240 Gb Intel
Для начала был выполнен «тест Гилева», который показал незначительное преимущество стенда номер 2, против стенда со свободным ПО.
Результаты смотрим ниже, разница в значениях получилась всего 3%.
Для информации: «тест Гилева» — популярный синтетический тест 1С, который выполняет ряд стандартных операций – чем быстрее тест выполняется, тем выше оценка. Оценка выполняется в условных единицах. Полученную оценку можно сравнить с прилагаемой к тесту шкале, которая покажет на сколько высока производительность текущей системы.
Рисунок 1. Результат теста Гилева. Стенд №2 СУБД MS SQL
Рисунок 2. Результат теста Гилева. Стенд №1 СУБД PostgreSQL
Далее решено было тестирование выполнить по методике APDEX . Суть метода заключается в измерении времени выполнения основных операций в 1С, замеры проводятся несколько раз на протяжении определенно периода времени. Далее полученный результат сравнивается с приемлемым временем выполнения той или иной операции.
Для этого взяли реальную рабочую базу одной из самых тяжелых конфигураций 1С, характеристики базы указаны в таблице №2.
Таблица 2. Характеристики тестовой базы
Конфигурация
Управление производственным предприятием
Замерялось время выполнения 7-ми стандартных операций с объектами в базе. Каждый тест выполнялся 10 раз и выводилось среднее значение. Замеры проводились с использованием толстого клиента через локальную сеть. Клиент устанавливался на рабочую станцию под управлением Windows 7. Тесты также пробовали запускать с клиента установленного на Ubuntu Linux , но он работал не стабильно и все тесты решено было выполнять только с клиента на Windows .
Таблица 3. Результаты APDEX
Ключевая операция
Время выполнения в секундах
Стенд №2 ( MSSQL )
(Свободное ПО)
Проведение нового документа
Документ объект: Заказ клиента
Анализ доходов расходов
Ведомость по партиям товаров
Ведомость по товарам на складах
Расчеты с клиентами
В среднем наша реальная база при использовании MSSQL работала на 45% быстрее, чем на стенде со свободным ПО. На некоторых тестах отрыв был очень значителен, а на таких как, например проведение нового документа составлял всего 11%.
Вывод:
1C на СУБД MSSQL работает примерно в 1,5 быстрее, чем на PostgreSQL. Соответственно, если есть возможность купить или арендовать лицензии MSSQL, лучше использовать его для более высокой производительности. Для небольших и ненагруженных баз можно попробовать использовать версию MSSQL Express. Тестов с ней мы не проводили, поэтому она может показать себя по производительности как лучше так и хуже PostgreSQL. Данная редакция ограничена использованием 1 процессора и 1 Гб ОЗУ, также не работает с базами более 10Гб. Если база дорастет до такого размера, то она остановиться и перестанет работать полностью, но как показывает практика, если в базе работает 15-20 пользователей, то комфортно можно работать при размере базы 4-5ГБ, далее база начинает сильно тормозить.
Оценка «тестом Гилева» показывает крайне незначительное превосходство MSSQL, что позволяет сделать предположение о том, что другие базы 1С могут работать на PostgreSQL так же хорошо, как и на MSSQL, а возможно и быстрее. Перед выбором СУБД рекомендуем провести тесты на своей конкретной базе и сравнить полученные результаты.
Использование СУБД PostgreSQL для развертывания на нем 1С является приемлемым решением в условиях ограниченного бюджета. База будет работать не так быстро как на MSSQL , но зато не нужно платить за лицензии.
В конце 2017 года мы провели новые тесты и опубликовали их в очередной статье.
Ещё одним способом сэкономить при использовании 1С является решение — взять сервер 1С в аренду.
Карманный справочник: сравнение синтаксиса MS SQL Server и PostgreSQL
Я занимаюсь переводом кода из MS SQL Server в PostgreSQL с начала 2019 года и сегодня продолжу сравнение этих СУБД.
В прошлой публикации мы рассматривали отличия в быстродействии MS SQL Server и PostgreSQL для «1C».
В Ozon есть решения и на MS SQL Server, и на PostgreSQL: первая используется в логистике и системах внутренних сервисов, вторая — в mission critical-подсистемах, от которых напрямую зависит бизнес компании (склад, корзина, оплата картами, платежи, информация о товарах на сайте и др.).
Периодически появляются задачи перевода решений из огромных монолитных баз из MS SQL Server в PostgreSQL. Поэтому давайте сравним основные конструкции синтаксиса этих СУБД для правильного чтения кода, а также для того, чтобы быстро изменять код из MS SQL Server для PostgreSQL и наоборот.
Начнём с сопоставления типов.
Сопоставление типов
DOUBLE PRECISION, FLOAT8
INT, INTEGER, INT4
TIMESTAMP(n) WITH TIME ZONE, TIMESTAMPTZ
Примечание. Типы CHAR и VARCHAR лучше не использовать. Причины подробно описаны здесь.
Более подробно о типах данных:
Теперь перейдём к сопоставлению синтаксиса MS SQL Server и PostgreSQL.
Сопоставление синтаксиса MS SQL Server и PostgreSQL
I. Регистрозависимое обращение к схемам, таблицам (представлениям) и их полям и другим объектам базы данных
В MS SQL Server при обращениях к объектам можно использовать квадратные скобки (они обязательны, только если в названии объекта или его поля присутствуют недопустимые символы):
В PostgreSQL для этого используются двойные кавычки (они обязательны, только если в названии объекта присутствуют заглавные буквы или есть недопустимые символы в названии объекта или его поля):
II. Выборка заданных N данных
В MS SQL Server используется TOP:
В PostgreSQL используется LIMIT:
SELECT . LIMIT N;
III. Постраничная загрузка данных (скользящее окно)
Задача: извлечь 100 строк начиная с 202-й строки включительно по возрастанию даты рождения:
SELECT *
FROM tbl
ORDER BY BirthDate ASC
OFFSET 201 ROW FETCH
NEXT 100 ROWS ONLY;
select *
from tbl
order by BirthDate asc
[—offset 201 row fetch
next 100 rows only;]
LIMIT 100 OFFSET 200
Примечание. Вместо row можно использовать rows в любом месте запроса, а вместо next можно использовать first в обеих СУБД.
IV. Выборка первого непустого значения
V. Тернарный оператор IIF
CASE WHEN <условие> THEN <выражение_если_условие_истинно> ELSE <выражение_если_условие_ложно> END
case when <условие> then <выражение_если_условие_истинно> else <выражение_если_условие_ложно> end
VI. Создание псевдонима
VII. Выражения CASE
VIII. Работа с переменными
Объявление переменной
Примечание. В MS SQL Server при объявлении переменных используется знак @ перед именем, а в PostgreSQL — нет. Также, помимо PL/pgSQL, в PostgreSQL можно встраивать и другие языки, такие как PL/Python и PL/Perl.
Присвоение переменной значения
SET @переменная = значение;
Примечание. В PostgreSQL используется := для PL/pgSQL и просто = для PL/Python и PL/Perl.
Вывод значения на консоль
RAISERROR(@переменная, 1, 1) WITH NOWAIT;
RAISE NOTICE ‘%’, ‘строка’;
RAISE NOTICE ‘%’, <переменная>;
IX. Управление выполнением кода
Выполнение скрипта
В MS SQL Server:
Пример (вывод информации):
Пример (передача значения клиенту):
В DBeaver (бобре) нужно нажать CTRL+SHIFT+O при отсутствии окна вывода, а в pgAdmin вывод происходит автоматически.
В psql и так всё работает.
Цикл WHILE
Логическое ветвление
Более подробно про управление выполнением кода:
X. Функции для работы со строками
Определение длины строки (количество символов в строке)
Примечание. В MS SQL Server исключаются конечные пробелы. Если нужно учитывать и их, то необходимо воспользоваться функцией DATALENGTH (<строка>), которая возвращает суммарное количество байтов в символах строки.
Возвращение символа по его коду:
Конкатенация строк
Нахождение позиции вхождения подстроки
В MS SQL Server:
strpos(substring(<где_ищем>, <с_какой_позиции_ищем_начиная_с_1>, length(<где_ищем>)- <с_какой_позиции_ищем_начиная_с_1>+1), <что_ищем>)
Примечание. Точного соответствия не будет, если производить поиск не с начала строки.
Регистронезависимое сравнение и поиск данных
В MS SQL Server:
2. lower(a) = lower(b) или upper(a)=upper(b)
3. lower(a) <> lower(b) или upper(a)<>upper(b)
4. lower(a) in (lower(b1), . ) или upper(a) in (upper(b1), . )
Примечание. В PostgreSQL рекомендуется произвести оптимизацию через создание функционального индекса:
Более подробно про команду ANALYZE.
Слияние строк по запросу в одну строку по заданному разделителю
В MS SQL Server можно использовать функцию STUFF следующим образом:
Также начиная с версии 2017 доступна функция STRING_AGG.
В PostgreSQL для этого можно использовать функцию string_agg таким образом:
Более подробно про функции для работы со строками:
XI. Функции для работы с датой и временем
Получение текущей даты и времени (локальное время)
Получение текущей даты
CAST(GetDate() as DATE)
Пример преобразования формата даты и времени из строки public_date:
В MS SQL Server:
Приращение даты/времени
В MS SQL Server:
DateAdd(datepart, count, dt);
dt + (count * interval ‘1 datepart’);
или
dt + interval ‘count datepart’;
Более подробно про функции для работы с датой и временем:
XII. Получение количества строк, затронутых при выполнении последней команды
XIII. Выполнение динамического SQL-кода
XIV. Проверка и приведение типов
Проверка строки на то, что она является числом
В MS SQL Server:
Безопасное приведение типа
В MS SQL Server:
Примечание. try_cast в MS SQL Server возвращает NULL, если значение невозможно привести к заданному типу, в других случаях — работает как оператор CAST.
В PostgreSQL есть два способа:
1) через обработку ошибок:
2) через реализацию функции:
Функция в итоге не возвращает преобразованное в заданный тип значение.
Функция на вход принимает текст и возвращает текст.
Если значение невозможно привести к заданному типу, то возвращается NULL.
Пример использования (чтобы было как в MS SQL Server):
XV. DML-команды
Обновление данных
Пример в MS SQL Server:
Обновление поля Name в таблице Production.ScrapReason для тех строк, для которых есть соответствующие записи в таблице Production.WorkOrder по равенству ScrapReasonID и у которых значение ScrappedQty больше 300:
Ключевое слово OUTPUT позволяет получить данные об обновлении.
Пример в PostgreSQL:
Обновление поля Name в таблице production.scrapreason для тех строк, для которых есть соответствующие записи в таблице production.workorder по равенству scrapreasonid и у которых значение scrappedqty больше 300:
Ключевое слово returning позволяет получить данные об обновлении.
Более подробно о команде UPDATE:
Удаление данных
Пример в MS SQL Server:
Удаление из таблицы Sales.SalesPersonQuotaHistory тех записей, для которых есть соответствующие записи в таблице Sales.SalesPerson по равенству BusinessEntityID и у которых значение SalesYTD больше 2500000.00:
Ключевое слово OUTPUT позволяет получить данные об удалении.
Пример в PostgreSQL:
Удаление из таблицы sales.salespersonquotahistory тех записей, для которых есть соответствующие записи в таблице sales.salesperson по равенству businessentitid и у которых значение salesytd больше 2500000.00:
Ключевое слово returning позволяет получить данные об удалении.
Более подробно о команде DELETE:
Получение изменённых записей
В MS SQL Server:
insert/update/delete таблица
Output deleted/inserted.<столбец>
into [@/#] <таблица>
Values|From <запрос>
insert/update/delete таблица
values()|from <запрос>|using <запрос>
returning *, столбец/столбцы
В update есть доступ только к inserted.
Примечание. В PostgreSQL не нужна промежуточная таблица для получения изменённых записей.
Удаление дубликатов (дублирующих строк):
В MS SQL Server:
или более сложный вариант:
Примечание. Оптимальный вариант — внести в таблицу уникальный ключ, так как работа с метаданными увеличивает нагрузку на систему.
При наличии уникального ключа удалять дубликаты в PostgreSQL можно следующим образом:
XVI. DDL-команды для работы с таблицами
Удаление таблицы с предварительной проверкой
В MS SQL Server:
Для основной таблицы:
Для локальной временной таблицы:
Для глобальной временной таблицы:
#<table> — локальная временная таблица, которая видна только в текущей сессии
##<table> — глобальная временная таблица, которая видна всем пока она существует
Все временные таблицы живут, либо пока активна сессия, во время которой они были созданы, либо пока их явно не удалят.
Для основной таблицы:
Для временной таблицы:
Более детально про удаление таблиц:
Создание таблицы через выборку
В MS SQL Server:
Для основной таблицы:
Для временной таблицы:
Для основной таблицы:
Для временной таблицы:
Более детально про создание таблиц через выборку:
Создание/изменение и удаление значения по умолчанию для колонки таблицы
В MS SQL Server:
Выборка всех значений по умолчанию:
Изменение происходит через удаление и добавление.
Создание и изменение:
Выборка всех значений по умолчанию:
Изменение типа колонки таблицы
В MS SQL Server:
ALTER TABLE
<схема>.<таблица>
ALTER COLUMN <поле>
<новый_тип> [NULL|NOT NULL];
alter table
<схема>.<таблица>
alter column <поле>
type <новый_тип>;
Примечание. Если у изменяемого столбца есть какие-либо констрейнты или значения по умолчанию, то сначала нужно их сохранить, а затем удалить. Вернуть их можно будет после изменения типа.
Перенос автоинкрементных полей
В MS SQL Server делаем запрос вида:
Полученные скрипты применяем на стороне PostgreSQL.
Создание автоинкрементных полей
В MS SQL Server:
Более детально про создание таблиц:
Более детально про изменение таблиц:
XVII. Создание и изменение представления
В MS SQL Server:
CREATE OR ALTER VIEW
create or replace view
Примечание. В PostgreSQL лучше сначала удалять представление, а потом заново его создавать, если набор полей меняется или меняются названия выходных полей, иначе можно получить ошибку при обращении к изменённому представлению.
Более подробно про создание и изменение представлений:
XVIII. Построчная обработка строк в наборе
В MS SQL Server:
XIX. Системные информационные функции безопасности
Текущий пользователь
В MS SQL Server используется функция CURRENT_USER().
session_user — под каким пользователем открыта сессия
current_user (или просто user) — под каким контекстом (ролью) идёт выполнение (session_user переключается для выполнения — здесь важно, под каким правом делается переключение)
Получение имени экземпляра и IP-адреса сервера СУБД
В MS SQL Server:
Получить информацию об IP-адресе сервера СУБД:
Получить название экземпляра СУБД:
Получить IP-адрес сервера СУБД:
Получение названия экземпляра СУБД пока не реализовано.
Более подробно про системные информационные функции безопасности:
XX. Определение и вызов хранимой процедуры
Определение хранимой процедуры
Вызов хранимой процедуры
В MS SQL Server:
XXI. Создание скалярной функции
XXII. Передача табличного значения (вывод таблицы)
В MS SQL Server:
XXIII. DML-триггеры
Пример в MS SQL Server:
Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text после обновления данных, который для обновляемых строк проставляет текущие дату, время и пользователя соответственно.
Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text
Пример в PostgreSQL:
Здесь создаётся функция dbo.update_mod(), которая заполняет два поля текущими датой, временем и пользователем соответственно.
Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text до обновления данных, который для каждой строки вызывает выполнение функции dbo.update_mod().
Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text.
Важно! В триггере используйте ключевое слово before, когда хотите нашкодничать в той же таблице, для которой создаётся триггер, и after — для логирования в другую таблицу.
Более подробно про DML-триггеры:
И в качестве бонуса кратко рассмотрим сопоставление основных системных представлений и приведём ссылки для мониторинга.
Немного о сопоставлении системных представлений и мониторинге
Сопоставление системных представлений
MS SQL Server
PostgreSQL
Описание
Предоставляет статистику по выполненным запросам.
В MS SQL Server содержит только то, что в кеше, а в PostgreSQL — всю статистику.
CREATE EXTENSION pg_stat_statements;
Предоставляет статистику по вызовам пользовательских функций.
Предоставляет статистику по использованию всех пользовательских индексов.
Предоставляет статистические данные по каждой БД.
Системные представления MS SQL Server:
Мониторинг работы СУБД
Заключение
Мы рассмотрели сопоставление типов и основные конструкции синтаксиса MS SQL Server и PostgreSQL, что позволит быстрее адаптировать решения из одной СУБД под другую.
Также мы пробежались по сопоставлению системных представлений и ссылкам на документацию, в том числе о мониторинге, что позволит анализировать производительность реализованного или перенесённого кода.