Чем PostgreSQL лучше других СУБД


Оглавление (нажмите, чтобы открыть):

MySQL и PostgreSQL — описание, сравнение.

24 октября 2013, 10:25

В данной статье мы рассмотрим две системы управления базами данных (СУБД) — MySQL и PostgreSQL. Эти СУБД используются достаточно широко и разрабатывались для разных целей, а также являясь бесплатными программными продуктами, составляют серьезную альтернативу коммерческим СУБД, например MicrosoftSQL. Нужно учитывать, что обе системы продолжают бурное развитие и возможно в будущем те сравнительные характеристики, которые мы будем рассматривать, будут иметь более сильное различие. При разработке этих СУБД, разработчики пытались обеспечить полную совместимость со стандартами ANSI SQL, но не смотря на это для решения некоторых задач лучше подходит MySQL, а для других — PostgreSQL. Поэтому, если нужно выбрать СУБД, следует проверить соответствие возможностей СУБД требованиям, которые предъявляет определенная задача. Например, если важна скорость, то MySQL Server. Сможет ее обеспечить в достатке, а некоторые же специфические возможности присущи только PostgreSQL.

Рассмотрим некоторые особенности принципов разработки этих СУБД. Разработчики MySQL, работающие в MySQL AB, старались писать оптимальный код, который не нужно будет менять в корне при следующих версиях, а также заботятся о максимальной скорости работы своей СУБД, нежели о более расширенных возможностях. Также каждая новая версия тщательно тестируется перед релизом. СУБД PostgreSQL имеет свою основу на ядре, написанном множеством разработчиков, некоторые из которых даже совершенно не знакомы с теми частями программы, которые создавались другими. Разработчики PostgreSQL постарались наделить свое детище большим количеством возможностей, пускай даже не оптимизировав их на максимум, так как позже можно будет подправить отдельные участки программы в более идеальную форму.

Теперь поговорим о самих возможностях этих двух систем управления БД. Рассмотрим преимущества MySQL. Как уже было сказано, что MySQL превосходит по скорости работы, это также реализуется благодаря использованию кэша в запросах. Ведь зачастую запросы на чтение повторяются, например к страницам веб-сайта и кэш играет свою роль. Также у MySQL больше пользователей, чем у PostgreSQL, например, MySQL очень часто выбирают для производственных предприятий. От большого числа пользователей увеличивается и ответственность у разработчиков и тестеров, ведь MySQL предоставляет пользователям поддержку на коммерческой основе. А у PostgreSQL поддержка появилась только в последнее время и не настолько развита. Также MySQL имеет превосходство над PostgreSQL в Windows-окружении, MySQL работает как нативное Windows-приложение, а PostgreSQL запускается из эмуляции Unix-среды — окружения Cygwin.

Отсюда и большая стабильность MySQL на Windows-машинах. Еще MySQL обладает большим количеством различных API-интерфейсов для разных языков. MySQL работает круглосуточно на промышленных серверах, обычно не требуется производить какие-либо «чистки». В случае PostgreSQL иногда приходиться использовать команду VACUUM для освобождения занятого пространства после отработки команд UPDATE и DELETE, а также проводить анализ для увеличения производительности. Причем VACUUM блокирует работу с базой данных, а также трабатывает долго при больших объемах данных. В поставку MySQL входят дополнительные пакеты для тестов и замера производительности, а также MySQL поддерживает больше функций ODBC, чем PostgreSQL. Стоит вспомнить, что на тему MySQL существует больше книг, которые очень помогают начинающим и уже более опытным администраторам. Можно перечислить много преимуществ MySQL, некоторые из них будут сугубо технические. Это и многопоточность, и создание таблиц без транзакций, система привилегий, полнотекстовый поиск, хорошая реализация ALTER TABLE, работа с обработчиками таблиц InnoDB и BerkeleyDB, а также многие другие вещи.

Несмотря на все плюсы MySQL, стоит отметить и недостатки по сравнению с PostgreSQL. Например, в PostgreSQL лучше проверена система транзакций. Использование потоков в работе MySQL не всегда отрабатывает безошибочно. Также бывают случаи с MySQL, когда в некоторых случаях один длинный запрос может блокировать надолго заблокировать таблицу. В PostgreSQL проще расширить возможности и дополнить новыми SQL-функциями, а также проще организованы обновления нескольких таблиц сразу. Также бывают случаи, в которых PostgreSQL более соответствует ANSI SQL. Еще одним преимуществом PostgreSQL является ускорение кода, за счет хранения его в виде процедур, а также лучше реализовано хранение географических данных. Вообще, разработчиков серверного кода PostgreSQL больше чем разработчиков MySQL, поэтому неизвестно что ждет в будущем эти две СУБД.

Выявление преимуществ использования СУБД PostgreSQL над MySQL

Секция: Технические науки

XLV Студенческая международная заочная научно-практическая конференция «Молодежный научный форум: технические и математические науки»

Выявление преимуществ использования СУБД PostgreSQL над MySQL

Данная статья посвящена сравнительному анализу двух систем управления базами данных (СУБД): реляционной MySQL и объектно-реляционной PostgreSQL.

Исследование и сравнение свободно распространяемого программного обеспечения (ПО) является актуальным, поскольку финансовый аспект играет немаловажную роль в деятельности организаций. Приобретение платной лицензии СУБД для коммерческого использования становится довольно затратным мероприятием, особенно это затруднительно для организаций, находящихся на начальных стадиях развития, когда финансовое состояние может не предусматривать расходы на приобретение ПО. Так, например, для инсталляции коммерческой редакции MySQL Standard Edition Subscription в компании, имеющей около 20 пользователей, потребуется купить годовую лицензию на сервер стоимостью около 250 000 рублей. Таким образом, использование сервера СУБД на свободном программном обеспечении (вместо приобретения платной коммерческой лицензии MySQL) позволит организациям с небольшим числом сотрудников решить задачу автоматизации без финансовых затрат.

Рассмотрим один из возможных подходов решения данной задачи. Например, на компьютер с операционной системой семейства Linux можно установить распространяемую в свободном доступе версию объектно-реляционной СУБД PostgreSQL с открытым исходным кодом. Данного вида сервер позволяет беспрепятственно организовать доступ к стороннему установленному серверу или совместить с ролью базы данных иные роли (облачное хранилище, Web Server и пр.).

Кроме того, при выборе СУБД не менее остро стоит вопрос функциональности выбранной версии и производительности сервера при нормальных нагрузках с допустимым количеством одновременно работающих пользователей. Интерес пользователей к такого рода характеристикам систем управления базами данных вполне оправдан и является более значимым по сравнению со стоимостной характеристикой продукта, Но в том, уместно ли такое решение в данном случае, при выборе между свободно распространяемыми версиями СУБД MySQL и PostgreSQL, необходимо разобраться в дальнейшем.

Многие пользователи, близкие к области аудита информационных технологий, либо затрудняются в выборе между свободно распространяемыми версиями СУБД MySQL и PostgreSQL, либо выбирают коммерческие лицензии, как платный продукт, вызывающий большее доверие. Необходимо выявить критерии оптимальности, позволяющие определить преимущества и недостатки сравниваемых СУБД.

СУБД PostgreSQL, созданная на основе некоммерческой Postgres, также имеет коммерческие решения от компании EnterpriseDB, ориентированные на наличие коммерческой поддержки и интеграции с Oracle Database. Однако в данной статье будет рассмотрен функционал версии MySQL ветки 5.7 для сравнения с возможностями именно свободно распространяемой версии PostgreSQL 9.6 по следующим критериям:

· общая производительность в нагрузке;

· эффективность механизмов репликации;

· наличие документации и соответствие стандартам;

Первым критерием сравнения СУБД в статье были выбраны механизмы репликации. В реализации репликации MySQL имеет место множество проблемных ситуаций – некорректность и нестабильность функционирования, часто невысокая производительность. При этом указанные ситуации являются не совсем связанными между собой или даже вовсе разносторонними. Обратившись к описаниям хронологически более ранних версий, можно сказать, что проблемы осуществления репликации в MySQL связаны не с неполноценностью работы самого механизма, а, без сомнения, с введением поддержки подключаемых движков (storage engine): MyISAM, InnoDB, HEAP, NDB и др. Таким образом, разработчики MySQL не решили уделить достаточное внимание вопросам синхронизации появившихся движков с журналом, а также способу их участия в репликации. В то же время, в начале 2000-х годов, попытавшись найти «storage engine» в исходном коде PostgreSQL, движков в нем невозможно было обнаружить, потому что там они на тот момент и не планировались создаваться. Свой выбор разработчики прокомментировали тем, что понимают высокую вероятность возможных угроз возникновения проблем с репликацией и транзакциями между движками.

На данный момент, спустя более 10 лет, ситуация такова, что в MySQL наблюдается некорректная работа транзакций между таблицами на разных движках, а также возникают проблемы с репликацией. За это время в PostgreSQL были реализованы подключаемые типы данных и индексы, появился механизм репликации. Этим преимущество MySQL над PostgreSQL, можно сказать, было ликвидировано, а проблемы первой СУБД остались не менее актуальными. В версии MySQL 5.7, анонсированной 23 апреля 2013 года, была проведена попытка увеличить эффективность репликации путем распараллеливания операций. Исходя из того, что любой масштабный проект, реализуемый на практике, сильно зависит от уровня производительности операций по репликации, заинтересованными лицами был проведен ряд тестов на производительность репликации в СУБД MySQL версий 5.5 и 5.7. В результате тестирования было выявлено, что в большинстве случаев однопоточная репликация версии MySQL 5.5 работает быстрее параллельной, реализованной в версии 5.7, т.е. для «нагруженных» проектов репликация последней версии является абсолютно неэффективной.

Также возможные проблемы, ведущие к неэффективности операций репликации в MySQL и в PostgreSQL, различаются своим характером: в первой СУБД они логические, а во второй – физические, соответственно используемым механизмам репликации. Логическая репликация, безусловно, имеет свои преимущества (независимость от формата хранения данных, доступность для чтения с каждого узла, частичная репликация отдельных таблиц и схем и др.). Однако физическая репликация PostgreSQL в силу большей оптимизации перекрывает превосходства MySQL, т.е. практически все то, чего нет в первой, уже можно реализовать сейчас или в скором времени.

Разница в производительности между различными уровнями репликации двух СУБД возникает в большей степени из-за того, что MySQL использует два или четыре журнала (для запросов и для репликации), а PostgreSQL достаточно одного. При этом механизм репликации в PostgreSQL, при котором запросы и операции репликации попадают в один журнал, является стабильным и оптимизированным, а дублирование транзакций между журналами в MySQL определенно негативно сказывается на производительности.

В PostgreSQL есть также триггерная репликация (Tungsten): по исполнению созданных триггеров данные передаются в таблицы или создаются файлы, которые затем читаются на реплике. Репликация MySQL же исполняется так, как предписывает движок, без возможности свободного изменения. Поэтому обозримое преимущество логической репликации MySQL на данный момент заключается только в большем возможном размере журнала.

Следующий критерий для сравнения СУБД – реализация документации. В PostgreSQL она в большей степени понятна и полезна, нежели в MySQL, где документация и вовсе не дает никакой информации об отдельных опциях, либо дает очень поверхностно.

На сегодняшний день имеет место критика при сравнении документации PostgreSQL с Oracle, в то время как MySQL с Oracle сравнивать никто не пытался. Сложившаяся ситуация позволяет предположить, что СУБД PosgreSQL по своим возможностям и производительности может конкурировать с мощной СУБД Oracle. Среди реализованных на PostgreSQL довольно крупных проектов выделяется 1C. Рассматриваемая СУБД частично может заменить мощную MS SQL.

Кроме того, PostgreSQL соответствует стандартам SQL-92, SQL-98, SQL-2003 и поддерживает многие возможности SQL-2011, когда MySQL не поддерживает даже SQL-92 и все еще не имеет эффективного оптимизатора. Очевидно, разработчики MySQL опустили то, что стандарты – это не мелкие изменения между версиями, а также новые функциональные возможности.

Если сравнивать простоту администрирования двух СУБД, то выигрыш будет на стороне MySQL, но только по причине того, что она является более скудной по возможностям в плане администрирования.

По производительности СУБД MySQL менее эффективна, чем PostgreSQL, и при разработке требует большего количества временных затрат. PostrgreSQL же имеет возможность строить гистограммы, а также хороший оптимизатор, значительно повышающий эффективность и быстроту выполнения запросов. Однако при использовании PostrgreSQL могут возникать ситуации, которые необходимо распознавать и выбирать для них оптимальные параметры посредством специальных настроек, которые необходимо знать. Несмотря на это, по корректности и удобству использования PostgreSQL можно назвать более предпочтительным по сравнению с MySQL.

При выборе СУБД для конкретного проекта, несмотря на описанное преимущество PostgreSQL над MySQL во многих аспектах, важно учесть наличие опыта работы команды с СУБД. То есть, для реализации проекта, не требующего большого количества затрат и не имеющего обозримых перспектив на развитие, разработчик которого имеет опыт работы с MySQL, лучше использовать данную СУБД. В другой ситуации, когда проект предполагается масштабным, со сложной логикой и связями между таблицами, то для него определенно подходит PostgreSQL. Данная СУБД облегчит процесс разработки, сократит затраты и позволит при желании развивать проект.

Oracle vs PostgreSQL: какую СУБД выбрать?

В то время как в государственном секторе повсеместно и в обязательном порядке происходит переход на свободное программное обеспечение, все больше коммерческих компаний задумываются о добровольной миграции с Oracle на свободную систему управления базами данных PostgreSQL.

Что приобретают и теряют компании при таком переходе? Ответ на этот вопрос искали участники конференции PgConf, которая прошла в начале февраля в Москве.

На ней начальник отдела технического консалтинга по серверным технологиям российского представительства Oracle Марк Ривкин предложил сравнивать СУБД по следующим критериям: производительность, безопасность, масштабируемость, обновляемость, уровень техподдержки, работа с большими объемами данных и цена владения. С таким подходом согласился и эксперт компании PostgreSQL-Consulting.com, специалист по базам данных PostgreSQL, DB2 и Oracle, Илья Космодемьянский.

Хорошо, давайте сравним. И начнем с основного, на наш взгляд, критерия.

Совокупная стоимость владения (Total Cost of Ownership или TCO).

Как известно, основными составляющими ТСО в мире софта являются цена приобретения и стоимость поддержки продукта. Определенное значение имеют и затраты на администраторов баз данных (DBA), но, согласно результатам исследований HeadHunter, эти расходы различаются в пределах 10%.

Стоит признать: цена приобретения Oracle высока, так же как и стоимость поддержки. Кроме того, каждую дополнительную функцию приходится приобретать отдельно, причем за немалые деньги. Об этом верно сказал Илья Космодемьянский: «Лицензии придумывают неглупые люди: Express и Standard-one выпущены для того, чтобы вы купили Enterprise и RAC».

Для примера приведем стоимость лицензии Oracle Enterprise Edition для одного 4-х ядерного CPU и цену сопровождения на один год — около 7,5 миллионов рублей.

Стоимость для ваших решений можно прикинуть в различных калькуляторах:

Как вы уже догадываетесь, именно TCO является ударным аргументом в пользу PostgreSQL, поскольку в случае выбора open-source СУБД цена приобретения является нулевой, аналогичная ситуация и со стоимостью сопровождения. Впрочем, об этом критерии стоит поговорить отдельно.

Сопровождение

Если вы думаете, что «бесплатно» – это синоним слова «плохо», то глубоко заблуждаетесь. Да, в случае с PostgreSQL возникшие проблемы будет решать «комьюнити», а не высокооплачиваемая (вами!) группа профессионалов. И потому вполне вероятно, что это будет сделано с некоторой задержкой, но она, как правило, не слишком критична, а значит, не приведет к серьезным последствиям. Для тех же компаний, кого такой подход не устраивает, есть выход: организации, которые занимаются профессиональной поддержкой PostgreSQL на высоком уровне. Их услуги, конечно, не бесплатны, но стоимость и условия более чем демократичны: нет годовых лицензий, штрафов за пропуск оплат и прочих «приятных» сюрпризов, которыми славится поддержка Oracle.

Кстати, а вы в курсе, что стоимость сопровождения Oracle в год составляет почти четверть стоимости лицензии? Причем эта сумма ежегодно возрастает на 3%-5%. В долларах, конечно. С более подробной информацией можно ознакомиться вот здесь:

Производительность

Илья Космодемьянский признает, что оспаривать техническое первенство Oracle глупо. В общем случае эта СУБД обеспечивает больше транзакций в секунду (TPS), чем PostgreSQL. Во сколько раз? А вот на этот вопрос точно ответить пока никто не смог. Для того чтобы сравнить производительность Oracle и PostgreSQL, необходимо провести их тестирование в идентичных условиях: на одинаковом «железе» с равной нагрузкой, используя оптимальные операционные и файловые системы, а также осуществив сопоставимый по уровню «тюнинг» СУБД.

Сложность соблюдений этих условий, их высокая трудоемкость, отсутствие квалифицированных DBA ограничивают подобные исследования.

Тем не менее, на сайте pgconf представлены некоторые цифры по производительности PostgreSQL, правда, без указания ссылок на источники:

  • сайт Госдумы РФ – 100 ГБ данных, 400 000 хитов в день;
  • «Яндекс» – 500 млн транзакций в сутки, 5+ ТБ данных на четыре шарда;
  • Coub.com – 400 млн просмотров в сутки, 10 000 запросов в секунду.

Безопасность

О какой безопасности упоминал Марк Ривкин? Наверное, об опциях типа Oracle advanced security или Label security, которые входят только в самый дорогой пакет Enterprise edition. Это действительно «крутые «фичи», но они не столь актуальны в условиях ожидания новых санкций, которые могут привести к полному отказу в технической поддержке продукта или, что еще более катастрофично, к отключению наших облачных БД.

Масштабируемость

Теперь давайте посмотрим, как обстоят дела с масштабируемостью в Oracle. Начиная с версии Standard edition, предоставляется всем известный RAC (четыре сокета). Однако при работе с highload-проектами, вам, скорее всего, придется купить Enterprise edition, что влетит в копеечку.

В то же время сообщество PostgreSQL бесплатно предоставляет и расширения наподобие PL/Proxy от компании Skype, которое позволяет шардировать информацию по кластеру БД, и отдельные кластерные решения, базирующиеся на PostgreSQL – Postgres-XC и Postgres-XL.

Обновляемость

Oracle не гонится за частотой релизов. Как правило, новый релиз выходит раз в два-три года и отражает качественные изменения в соответствии с потребностями рынка. Проследим хронологию:

1998 год – выпущена версия 8i Release 1 (8.1.5), «i» в названии обозначает «internet», символизируя поддержку интернета.

2001 год – выпущена версия 9i Release 1 (9.0.1), поддержка XML, появление RAC.

2004 год – выпущена версия 10g Release 1 (10.1.0), «g» в названии обозначает «grid» («сеть»), символизируя поддержку грид-вычислений.

2007 год – выпущена версия 11g Release 1 (11.1.0.6).

2013 год – выпущена версия 12c (12.1.0.1), «с» в названии обозначает «cloud» («облако»). Основное новшество релиза – поддержка подключаемых баз данных, которая обеспечивает свойства мультиарендности и живой миграции БД.

Кроме того, ежеквартально выпускаются критические патчи.

Еще более нетороплив при смене номера версии PostgreSQL: релиз выходит примерно раз в пять лет. Но, в отличие от Oracle, добавление новых функций и обновление существующих происходит, по сути, постоянно. Так что, если вы готовы рискнуть стабильностью ради функциональности, обновлять СУБД можно хоть каждый день.

Объем изменений в межрелизных обновлениях можно косвенно оценить из того факта, что их краткое описание занимает несколько экранных страниц.

Ведущий разработчик PostgreSQL Олег Бартунов считает, что спектр возможностей этой СУБД расширяется намного быстрее, чем у Oracle. Поэтому вполне возможно, что в самое ближайшее время PostgreSQL займет лидирующие позиции в данном направлении.

Работа с очень большими данными

Так же как и в случае с производительностью, сравнивать СУБД по этому критерию достаточно сложно. С одной стороны, Enterprise версия Oracle при прочих равных условиях должна быть производительнее PostgreSQL по крайней мере за счет in-memory технологии, но для получения конкретных цифр необходимо сравнивать результаты на рабочих запросах. С другой – PostgreSQL не стоит на месте: в версии 9.4 появились huge pages, что дает прирост производительности от 10% до 30% на машинах с большим объемом памяти.

Для тех, кто хочет иметь некоторые опорные цифры, мы собрали статистику:

  • Сайт объявлений «Авито» (250 млн просмотров, 7 млн посетителей в сутки), количество серверов БД – около 30. Размер мастер-базы – 1.5 ТБ, более 3 000 запросов в секунду на просмотр информации и около 1 500 запросов в секунду на изменение.
  • Облачный сервис «Мой склад» с относительно «тяжелыми» запросами к БД: 6 серверов Intel обеспечивают одновременную работу 2 000 пользователей, генерирующих до 1 400 транзакций в секунду на БД размером 700 ГБ.
  • Компания «Яндекс» мигрировала с Oracle в части почтовой системы: размер БД – 2 ТБ (15+ млрд строк), производительность 40 000 запросов в секунду. Более подробную информацию можно найти здесь: https://www.sl >Итак, какой же совет мы можем дать тем, кто стоит перед выбором СУБД? PostgreSQL представляется достаточно зрелой системой, способной обеспечить потребности средних, а иногда и крупных корпораций (за примерами далеко ходить не надо – «Яндекс», «Авито», Skype). Поэтому, если ваш проект еще не выведен в «продуктив» или существует безболезненный вариант попробовать различные СУБД, то это стоит сделать. Как в таком случае минимизировать риски? На этот вопрос существует только один ответ: необходимо рассчитать, затем эмулировать боевую нагрузку и, в конце концов, оценить результат. Кстати, «Перфоманс Лаб» это умеет.

Если же перед вами стоит острая проблема «импортозамещения» уже работающей системы, то не стоит рисковать и, помолясь индийскому богу Ганеше, запускать скрипт миграции из Oracle в PostgreSQL (а такой скрипт есть). Как советует технический директор 404 Group Роман Друзягин, сначала нужно протестировать систему, зафиксировать все проблемы, найти пути их решения, провести несколько тестовых миграций и только затем, хорошенько выспавшись, устраивать час «Ч».

Сравнение PostgreSQL, Oracle и MySQL на боевой нагрузке

Если вы хотите подробнее ознакомиться с возможностями обеих СУБД, мы подобрали для вас ссылки:

PostgreSQL СУБД

Разработчики: PostgreSQL Global Development Group
Дата последнего релиза: 2020/05/21
Технологии: СУБД

Содержание

Свободная объектно-реляционная система управления базами данных (СУБД), является свободной альтернативой коммерческим СУБД (таким как Oracle Database, Microsoft SQL Server, IBM DB2, Informix и СУБД производства Sybase) вместе с другими свободными СУБД (такими как MySQL и Firebird). Существует в реализациях для следующих платформ: Linux, Solaris/OpenSolaris, Win32, Mac OS X, FreeBSD, QNX 4.25, QNX 6.

Большой вклад в развитие PostgreSQL внесли российские разработчики. Среди них можно выделить Вадима Михеева, Олега Бартунова, Федора Сигаева, Александра Короткова.

Крупнейшим вендором, оказывающим услуги доработки и внедрения Postgres, является американская компания EnterpriseDB. В России по аналогичной модели с января 2015 года развивается компания «Постгрес профессиональный (Postgres Professional)», предоставляющая услуги по поддержке, обучению, разработке, консалтингу.

Функции являются блоками кода, исполняемыми на сервере, а не на клиенте БД. Хотя они могут писаться на чистом SQL, реализация дополнительной логики, например, условных переходов и циклов, выходит за рамки собственно SQL и требует использования некоторых языковых расширений. Функции могут писаться с использованием одного из следующих языков:

  • Встроенный процедурный язык PL/pgSQL, во многом аналогичный языку PL/SQL, используемому в СУБД Oracle;
  • Скриптовые языки — PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl и PL/Scheme;
  • Классические языки — C, C++, Java (через модуль PL/Java);
  • Статистический язык R (через модуль PL/R).

PostgreSQL допускает использование функций, возвращающих набор записей, который далее можно использовать так же, как и результат выполнения обычного запроса.

Функции могут выполняться как с правами их создателя, так и с правами текущего пользователя. Иногда функции отождествляются с хранимыми процедурами, однако между этими понятиями есть различие.

Триггеры определяются как функции, инициируемые DML—операциями. Например, операция INSERT может запускать триггер, проверяющий добавленную запись на соответствия определённым условиям. При написании функций для триггеров могут использоваться различные языки программирования.

Триггеры ассоциируются с таблицами. Множественные триггеры выполняются в алфавитном порядке.

Правила и представления

Механизм правил (англ. rules) представляет собой механизм создания пользовательских обработчиков не только DML-операций, но и операции выборки. Основное отличие от механизма триггеров заключается в том, что правила срабатывают на этапе разбора запроса, до выбора оптимального плана выполнения и самого процесса выполнения. Правила позволяют переопределять поведение системы при выполнении SQL-операции к таблице. Хорошим примером является реализация механизма представлений (англ. views): при создании представления создается правило, которое определяет, что вместо выполнения операции выборки к представлению система должна выполнять операцию выборки к базовой таблице/таблицам с учетом условий выборки, лежащих в основе определения представления. Для создания представлений, поддерживающих операции обновления, правила для операций вставки, изменения и удаления строк должны быть определены пользователем.

В PostgreSQL имеется поддержка индексов следующих типов: B-дерево, хэш, R-дерево, GiST, GIN. При необходимости можно создавать новые типы индексов, хотя это далеко не тривиальный процесс. Индексы в PostgreSQL обладают следующими свойствами:

  • возможен просмотр индекса не только в прямом, но и в обратном порядке — создание отдельного индекса для работы конструкции ORDER BY . DESC не нужно;
  • возможно создание индекса над несколькими столбцами таблицы, в том числе над столбцами различных типов данных;
  • индексы могут быть функциональными, то есть строиться не на базе набора значений некоего столбца/столбцов, а на базе набора значений функции от набора значений;
  • индексы могут быть частичными, то есть строиться только по части таблицы (по некоторой её проекции); в некоторых случаях это помогает создавать намного более компактные индексы или достигать улучшения производительности за счёт использования разных типов индексов для разных (например, с точки зрения частоты обновления) частей таблицы;
  • планировщик запросов может использовать несколько индексов одновременно для выполнения сложных запросов.

PostgreSQL поддерживает одновременную модификацию БД несколькими пользователями с помощью механизма Multiversion Concurrency Control (MVCC). Благодаря этому соблюдаются требования ACID и практически отпадает нужда в блокировках чтения.

PostgreSQL обладает встроенной системой полнотекстового поиска, позволяющей искать внутри базы данных документы и сортировать их в заданном порядке. Основными преимуществами использования встроенного полнотекстового поиска являются: тесная интеграция с СУБД(транзакционность, одновременный доступ, восстановление после сбоев), масштабируемость, широкие возможности настройки (словари, парсеры, и т.д.).

PostGIS — расширение СУБД PostgreSQL предназначенное для хранения в базе географических данных. PostGIS включает поддержку пространственных индексов R-Tree/GiST и функции обработки геоданных.

Включение в продукты DeviceLock

22 октября 2020 года стало известно, что DeviceLock включил в свои продукты поддержку Postgres Pro и PostgreSQL. Подробнее здесь.

Запуск программы сертификации специалистов по СУБД PostgreSQL

21 мая 2020 года Postgres Professional сообщил о запуске программы сертификации специалистов по СУБД PostgreSQL.

Программа сертификации предусматривает три уровня с возрастающей квалификацией:

Для получения сертификата необходимо пройти тестирование в офисе компании Postgres Professional и набрать проходной балл. Материалом для подготовки могут служить авторские курсы Postgres Professional, доступные на сайте, а также регулярно читаемые в сертифицированных учебных центрах. Ежегодно слушателями курсов становятся более 500 человек.

Тест для первого уровня — «Профессионал» — включает в себя 50 вопросов по основам администрирования PostgreSQL и длится 75 минут. Поскольку для каждого релиза PostgreSQL характерны свои особенности администрирования, сертификация соотносится с конкретной версией СУБД. Например, на май 2020 года доступен тест для 10-ой версии PostgreSQL DBA1-10. Для прошедших тестирование на знание PostgreSQL 10 и желающих в будущем подтвердить свои навыки для 11-ой версии достаточно будет пройти короткое дополнительное тестирование, сфокусированное на отличиях продуктов.

Для получения сертификата уровня «Эксперт» понадобится успешно пройти уже три теста:

  1. DBA2-10 (настройка и мониторинг PostgreSQL)
  2. DBA3-10 (резервное копирование и репликация PostgreSQL)
  3. QPT-10 (оптимизация запросов)

А переход на уровень «Мастер» предполагает выполнение практических заданий по работе с PostgreSQL. В дальнейших планах компании Postgres Professional – запуск программы сертификации для разработчиков приложений на PostgreSQL.

Иван Панченко прокомментировал запуск программы сертификации:

Специалисты по Postgres становятся все более востребованными на российском рынке, что подтверждают данные кадровых агентств. В такой ситуации необходимы единые стандарты и критерии для оценки уровня знаний. Во многом наша программа сертификации стала ответом на запросы заказчиков и партнеров, заинтересованных в независимом инструменте оценки и повышения квалификации своих сотрудников.

Совместимость с Live Universal Interface

15 апреля 2020 года компания ФОРС Телеком сообщила о появлении в экосистеме программно-инструментальных средств, совместимых с открытой платформой Postgres Pro/PostgreSQL конструктора пользовательских веб-интерфейсов к базам данных — Live Universal Interface (LUI). Подробнее здесь.

Совместимость с TerraLink xDE

12 марта 2020 года TerraLink сообщил, что TerraLink xDE поддерживает OC семейства Linux и СУБД PostgreSQL. Подробнее здесь.

Включение в список коммитеров СУБД PostgreSQL сооснователя Postgres Professional Александра Короткова

В июне 2020 года список коммитеров (разработчиков, вносящих вклад в развитие кода) СУБД PostgreSQL пополнился третьим россиянином. В список основных коммитеров ядра PostgreSQL был включен Александр Коротков, сооснователь и руководитель разработки российской компании Postgres Professional.

Документация версии 10 локализована для России

11 октября 2020 года компания Postgres Professional сообщила о переводе документации по PostgreSQL 10.0 на русский язык. Материалы доступны в форматах Html, epub и pdf. Общий объем текста составляет почти 2 600 страниц.

Помимо документации на русском языке, российским пользователям PostgreSQL также доступна техническая поддержка в режиме 24/7, помощь в миграции с других СУБД на PostgreSQL, обучающие курсы и технические конференции.

В ближайшее время Postgres Professional планирует выпуск обновленной версии российской СУБД Postgres Pro Standard на основе PostgreSQL 10.0.

PostgreSQL 10

5 октября 2020 года состоялся релиз PostgreSQL версии 10. В целом с каждой версией, выходящей раз в год, PostrgeSQL получает возможности, расширяющие область эффективного применения СУБД.

  • Логическая репликация: отдельные части этого механизма были добавлены в PostgreSQL уже довольно давно, а в этой версии логическая репликация стала полностью доступна для пользователей. С ее помощью можно выборочно реплицировать отдельные таблицы на другой сервер, который при этом может выполнять как читающие, так и пишущие запросы. Серверы, участвующие в репликации, могут работать под управлением разных версий PostgreSQL, что позволяет проводить обновление кластера с минимальным временем простоя.
  • Декларативное секционирование избавляет администратора от необходимости вручную определять иерархию таблиц, создавать триггеры и ограничения целостности.
  • Параллельное выполнение запросов стало возможным для сканирования битовых карт и индексов, для соединения слиянием и подзапросов в дополнение к тем возможностям, которые появились в предыдущей версии.
  • Синхронная репликация с учетом кворума позволяет фиксировать изменения, если их подтвердило необходимое число произвольных реплик.
  • SCRAM-аутентификация является более криптостойким вариантом используемой ранее MD5-аутентификации.

Всего, по словам разработчиков, в версию 10 вошло более 100 изменений и улучшений, часть из которых выполнена в компании Postgres Professional.

Интеграция с Ethereum

14 сентября 2020 года российская компания Postgres Professional объявила о создании прототипа расширения Posthereum для интеграции полнофункциональной СУБД PostgreSQL с блокчейн-платформой Ethereum, предназначенной для регистрации сделок с любыми видами активов на основе системы «умных контрактов». По замыслу компании, крупные российские банки, корпорации и госструктуры, работающие с СУБД PostgreSQL, с помощью данной разработки смогут объединить базы данных с блокчейн-приложениями на основе Ethereum. Подробнее здесь.

PostgreSQL 9.6

29 сентября 2020 года сообщество разработчиков представило стабильную ветку СУБД PostgreSQL 9.6. Обновления для нее 9.6 будут выходить в течение пяти лет, до сентября 2021 года.

  • поддержка распараллеливания операций последовательного сканирования записей (Sequential Scan), слияния запросов (join) и агрегирования данных. При распараллеливании операция разбивается на части и каждая часть разбирается отдельным обработчиком, после чего результаты работы каждого обработчика объединяются, что позволяет существенно увеличить скорость обработки запроса на системах с большим числом процессорных ядер. Выигрыш заметен для ресурсоёмких запросов, таких как сопоставление по регулярным выражениям. Например, в текстах при включении распараллеливания некоторые запросы выполняются до 32 раз быстрее [1] .
  • режим синхронной репликации «synchronous_commit = remote_apply», при котором основной узел перед закрытием транзакции ожидает подтверждения применения данных на standby-узле, что позволяет обеспечить непротиворечивость операций чтения из БД в любой части кластера;
  • возможность создания кластерных конфигураций, включающих несколько запасных узлов, реплицируемых в синхронном режиме. Данная возможность может применяться для создания нескольких полностью идентичных узлов для балансировки нагрузки;
  • в модуль postgres_fdw, позволяющий логически объединить содержимое БД с нескольких серверов, добавлена поддержка операций слияния (join) и сортировки запросов, а также выполнения операций UPDATE и DELETE на внешнем сервере. В том числе модуль может применяться для первичной обработки запросов в PostgreSQL с трансляцией запросов на внешние БД, которые могут обслуживаться другими СУБД;
  • API для создания «горячих» бэкапов, в котором метка резервной копии не записывается в директорию с данными, а возвращается как результат выполнения функции pg_stop_backup(), что позволяет защититься от проблем в случае краха во время бэкапа;
  • снижено негативное влияние на работу больших таблиц операции «autovacuum», благодаря исключению операций повторной заморозки («refreezing») старых данных;
  • реализована подсистема для отображения прогресса выполнения операций, например, организовано информирование о времени до завершения VACUUM;
  • в систему полнотекстового поиска добавлены средства поиска фраз, реализованные через новый оператор ‹-› или ‹DISTANCE›, определяющий расстояние между словами (например, можно осуществить выборку фраз, в которых слово «А» отделено от «B» заданным числом слов). Совместно с новыми опциями гибкой настройки поиска, новая возможность может применяться для создания гибридных систем поиска, объединяющих поиск по реляционным данным, JSON и полнотекстовым индексам;
  • добавлены системные представления и функции: pg_stat_wal_receiver, pg_visibility, pg_config, pg_blocking_pids, g_notification_queue_usage;
  • при установке дополнений теперь поддерживается каскадная установка зависимостей;
  • в модуле pg_basebackup появилась поддержка распараллеливания операций и слотов репликации;
  • в утилиту psql добавлены команды \ev и \sv для редактирования представлений. Реализована возможность указания нескольких экземпляров опций «-c» и «-f». Добавлен режим \crosstabview для отображения результатов запроса в виде сетки, по аналогии с электронными таблицами;
  • добавлен отладочный модуль pg_visibility, предоставляющий средства для изучения карты видимости (Visibility Map, отслеживает какие элементы видны для всех активных транзакций);
  • поддержка выражения «ALTER TABLE ADD COLUMN . IF NOT EXISTS» для добавления столбца только при его отсутствии в таблице;
  • возможность создавать GIN-индексы с любым значением maintenance_work_mem;
  • системное представление pg_config, отражающее параметры сборки СУБД;

Сравнение Tibero и PostgreSQL

Корректирующий выпуск всех веток

11 февраля 2020 года сообщество разработчиков PostgreSQL сообщило о выпуске корректирующих обновлений для всех поддерживаемых веток PostgreSQL: 9.5.1, 9.4.6, 9.3.11, 9.2.15 и 9.1.20, в которых устранены две уязвимости, представлена порция исправлений ошибок, добавлена поддержка Python 3.5 в PL/Python и обеспечена возможность совместного использования Python2 и Python3 в одной БД [2] .

Поддержка ветки 9.0.x прекращена. Выпуск обновлений для ветки:

  • 9.1 продлен до сентября 2020 года.
  • 9.2 продлен до сентября 2020 года,
  • 9.3 продлен до сентября 2020 года,
  • 9.4 продлен до декабря 2020 года,
  • 9.5 продлен до января 2021 года.

Первая из уязвимостей (CVE-2020-0773) проявляется в движке обработки регулярных выражений и может привести к краху бэкенда при разборе регулярных выражений с символами вне диапазона Unicode (проблеме подвержены системы, в которых пользовательский ввод применяется для генерации регулярного выражения).

Вторая уязвимость (CVE-2020-0766) присутствует в движке PL/Java и позволяет повысить свои привилегии при работе с БД.

PostgreSQL 9.5

7 января 2020 года стало известно о выходе стабильной ветки СУБД PostgreSQL 9.5. Выпуск обновлений для ветки 9.5 будет поддерживаться до января 2021 года [3] .

  • Функциональность «UPSERT» (добавить-или-модифицировать), реализованная через новое выражение «INSERT . ON CONFLICT DO NOTHING/UPDATE», позволяющая обработать ситуацию невозможности добавления данных через «INSERT», например, из-за нарушения условий уникальности или недопустимости значения одного из полей. Вместо вывода ошибки теперь можно игнорировать выполнение оператора или изменить связанные с ключевым полем данные (т.е. если запись уже существует, вместо INSERT выполнить UPDATE);
  • Ограничение доступа на уровне строк (Row-Level Security, RLS). Доступ пользователей к данным в таблице теперь можно разграничивать на уровне отдельных строк, например, можно запретить определённой категории пользователей просмотр строк, в которых хранятся данные, добавленные другим пользователем. Для активации RLS следует использовать директиву «ALTER TABLE tablename ENABLE ROW LEVEL SECURITY», после чего следует задать правила доступа при помощи выражения «CREATE POLICY»;
  • BRIN-индексы («индексы блоковых зон», Block Range Index), позволяющие сверхкомпактно индексировать очень большие таблицы, без применения традиционных B-деревьев. Суть BRIN-индексов сводится к разбиению общего индекса на блоки, каждый из которых содержит данные индекса только для определённого диапазона значений. В тесте подобный метод оказался примерно в два раза медленнее b-деревьев при осуществлении операций выборки данных, но в 3-4 раза быстрее при создании и обновлении индекса, а также занял значительно меньше места на диске (64 Кб против 28 Мб);
  • Новые функции и операторы для типа данных JSONB. Для изменения значений в документе JSONB теперь можно обойтись без извлечения и переопределения всего документа, благодаря появлению функции jsonb_set(). Также добавлены функции json_strip_nulls (удаление атрибутов, содержащих значения NULL) и jsonb_pretty (вывод в отформатированном JSON). Добавлен оператор «||» для соединения двух значений JSONB;
  • Инструмент pg_rewind, позволяющий существенно упростить процесс восстановления отказоустойчивых конфигураций после переключения на резервный сервер. После возвращения в строй основного сервера возникает задача синхронизации его состояния с продолжившим работу запасным сервером, который успел накопить свою порцию изменений. Утилита pg_rewind пытается восстановить состояние первичного сервера по WAL-логу транзакций, перебирая их начиная с момента незадолго до сбоя, определяя изменённые данные и перенося только изменившиеся блоки, что позволяет обойтись без восстановления полной копии с работающего резервного сервера.
  • Значительно оптимизированы скорости сортировки и хэширования в памяти. Благодаря применению нового метода сортировки строковых значений и чисел, удалось до 20 раз увеличить скорость создания индексов, а время выполнения запросов требующих сортировки больших объёмов данных, сократить в 2-12 раз;
  • Добавлена поддержка выражения TABLESAMPLE, позволяющего сформировать выборку над неполным объёмом данных из больших таблиц, без выполнения ресурсоёмких операций сортировки над всей таблицей. Например, запрос «SELECT * FROM test TABLESAMPLE SYSTEM(10)» сформирует вывод, охватив только 10% от таблицы test. Доступно несколько алгоритмов отсеивания значений в процессе неполной выборки;
  • Улучшено масштабирование на системах с большим количеством процессорных ядер и оперативной памяти. Например, на системе с 24 ядрами CPU и 496 Гб ОЗУ в тесте EnterpriseDB при нагрузке в 64 одновременных соединения PostgreSQL 9.5 показал прирост производительности в 96% по сравнению с PostgreSQL 9.4;
  • Автоматизировано управление размером лога транзакций. Возможность исключения отражения таблиц в логе транзакций (ALTER TABLE . SET LOGGED / UNLOGGED);
  • Аналитические возможности «GROUPING SETS», «CUBE» и «ROLLUP», позволяющие формировать вывод с группировкой по набору полей и рассчитывать число комбинаций различных категорий;
  • Улучшена репликация и средства повышения отказоустойчивости. Добавлен механизм отслеживания состояния выполнения репликации, в том числе реализованы методы для определения причины возникновения отдельных изменений в процессе выполнения логической репликации;
  • Произведены множественные улучшения в механизме Foreign Data Wrappers, включая выражение «IMPORT FOREIGN SCHEMA», которое позволяет автоматизировать импорт всех связанных внешних таблиц для существующих таблиц с выбранной меткой сервера. Кроме того, обеспечена возможность наследования внешних таблиц в локальных таблицах и наоборот, например, «CREATE local_customers () inherits (remote.customers);»
  • В утилиту vacuumdb добавлена опция «-j», позволяющая запускать VACUUM в несколько одновременно выполняемых потоков.

Инфраструктура параллельных вычислений в PostgreSQL

4 мая 2015 года стало известно о принятии изменений в дерево исходных текстов СУБД PostgreSQL с реализацией инфраструктуры для параллельных вычислений [4] .

  • Удобные процедуры для координирования запуска и завершения работы параллельно выполняемых рабочих процессов;
  • Синхронизация различных внутренних состояний (GUCs, комбинированный маппинг CID, снапшоты транзакций) между лидером группы параллельных работ и непосредственно распараллеленными рабочими процессами;
  • Ограничение вызова различных операций, которые могут привести к внесению некорректных изменений в условиях активного распараллеливания;
  • Доставка уведомлений клиенту через сообщения ErrorResponse, NoticeResponse и NotifyResponse от работающих в параллельном режиме обработчиков.

Postgres-XL на EcoServer — альтернатива для ЦОД

13 августа 2015 года стало известно о завершении испытаний системы управления базами данных Postgres-XL на серверах линейки EcoServer.

Postgres-XL – СУБД с открытым кодом для анализа большого объема данных, используемое в банковской сфере и бизнес-аналитике. Запуск СУБД Postgres-XL на серверах «Рикор», по мнению руководства компании Рикор, дает возможность российским заказчикам — использовать программное решение обработки информации на доступной отечественной аппаратной платформе и тем снизить зависимость от импортных ИТ-решений.

«Адаптация свободного ПО к нуждам российских компаний – это та ниша, в которой могут успешно трудиться отечественные ИТ-разработчики, – отметил Борис Иванов, вице-президент «Рикор». – Наше программно-аппаратное решение, состоящее из энергоэффективного сервера EcoServer и СУБД Postgres-XL является отличной альтернативой для использования различными отечественными организациями, в том числе в банковской сфере, которые ранее использовали дорогостоящее ПО для управления базами данных от зарубежных вендоров».

СУБД Postgres-XL ориентирована на создание кластерных систем обработки транзакций в реальном времени, работы с крупными базами для анализа больших наборов данных. Код Postgres-XL распространяется по свободной лицензии Mozilla. Оптимизация процессов в Postgres-XL происходит путем горизонтального масштабирования и массовых параллельных вычислений.

PostgreSQL 9.5 Анонс бета-версии

8 октября 2015 года сообщество разработчиков СУБД PostgreSQL представило бета-версию СУБД PostgreSQL 9.5.

Среди изменений в данной версии:

  • Функциональность «UPSERT» (добавить-или-модифицировать), реализованная через новое выражение «INSERT . ON CONFLICT DO NOTHING/UPDATE», позволяющая обработать ситуацию невозможности добавления данных через «INSERT», например, из-за нарушения условий уникальности или недопустимости значения одного из полей. Вместо вывода ошибки теперь можно игнорировать выполнение оператора или изменить связанные с ключевым полем данные (т.е. если запись уже существует, вместо INSERT выполнить UPDATE);
  • BRIN-индексы («индексы блоковых зон», Block Range Index), позволяющие сверхкомпактно индексировать очень большие таблицы, без применения традиционных B-деревьев. Суть BRIN-индексов сводится к разбиению общего индекса на блоки, каждый из которых содержит данные индекса только для определённого диапазона значений. В тесте подобный метод оказался примерно в два раза медленнее b-деревьев при осуществлении операций выборки данных, но в 3-4 раза быстрее при создании и обновлении индекса, а также занял значительно меньше места на диске (64 Кб против 28 Мб);
  • Ограничение доступа на уровне строк (Row-Level Security, RLS). Доступ пользователей к данным в таблице теперь можно разграничивать на уровне отдельных строк, например, можно запретить определённой категории пользователей просмотр строк, в которых хранятся данные, добавленные другим пользователем. Для активации RLS следует использовать директиву «ALTER TABLE tablename ENABLE ROW LEVEL SECURITY», после чего следует задать правила доступа при помощи выражения «CREATE POLICY»;
  • Новые функции и операторы для типа данных JSONB. Для изменения значений в документе JSONB теперь можно обойтись без извлечения и переопределения всего документа, благодаря появлению функции jsonb_set(). Также добавлены функции json_strip_nulls (удаление атрибутов, содержащих значения NULL) и jsonb_pretty (вывод в отформатированном JSON). Добавлен оператор «||» для соединения двух значений JSONB;
  • Инструмент pg_rewind, позволяющий существенно упростить процесс восстановления отказоустойчивых конфигураций после переключения на резервный сервер. После возвращения в строй основного сервера возникает задача синхронизации его состояния с продолжившим работу запасным сервером, который успел накопить свою порцию изменений. Утилита pg_rewind пытается восстановить состояние первичного сервера по WAL-логу транзакций, перебирая их начиная с момента незадолго до сбоя, определяя изменённые данные и перенося только изменившиеся блоки, что позволяет обойтись без восстановления полной копии с работающего резервного сервера.
  • Существенные оптимизации скорости сортировки и хэширования в памяти. Благодаря применению нового метода сортировки строковых значений и чисел, удалось увеличить скорость индексации в три раза;
  • Автоматизированное управление размером лога транзакций. Возможность исключения отражения таблиц в логе транзакций (ALTER TABLE . SET LOGGED / UNLOGGED);
  • Аналитические возможности «GROUPING SETS», «CUBE» и «ROLLUP», позволяющие формировать вывод с группировкой по набору полей и рассчитывать число комбинаций различных категорий;
  • Улучшения репликации и средств повышения отказоустойчивости;
  • Множественные улучшения в механизме Foreign Data Wrappers, включая выражение «IMPORT FOREIGN SCHEMA», которое позволяет автоматизировать импорт всех связанных внешних таблиц для уже существующих таблиц с выбранной меткой сервера. Кроме того, обеспечена возможность наследования внешних таблиц в локальных таблицах и наоборот, например, «CREATE local_customers () inherits (remote.customers);»
  • В утилиту vacuumdb добавлена опция «-j», позволяющая запускать VACUUM в несколько одновременно выполняемых потоков;
  • Сделаны улучшения масштабирования на системах с большим количеством процессорных ядер и оперативной памяти.

PostgreSQL поддерживает распараллеливание запросов

12 ноября 2015 года сообщество разработчиков сообщило о принятии в экспериментальной ветке, на базе которой будет формироваться релиз PostgreSQL 9.6 изменений, с реализацией распараллеливания операций последовательного сканирования записей (Sequential Scan), используемых для перебора значений в случае выборки по не индексированным полям или при манипуляциях с содержимым полей [5] .

Перебор в несколько параллельных потоков позволит существенно увеличить скорость перебора данных на системах с большим числом процессорных ядер. Выигрыш особенно заметен для ресурсоёмких запросов, таких как сопоставление по регулярным выражениям.

Выполнение тестового запроса «select * from pgbench_accounts where filler like ‘%a%'» в обычных условиях занимает 743 мс, в то время как распараллеливание в четыре потока дает результат — 213 мс.

При распараллеливании операция сканирования разбивается на части и каждая часть разбирается отдельным обработчиком, после чего результаты работы каждого обработчика объединяются.

Сравнение производительности с лицензионными СУБД

18 мая 2015 года «Индиго ИТ» сообщила о результатах сравнительного тестирования производительности наиболее востребованных на рынке СУП СУБД — в отдельных тестовых наборах СУБД с открытыми кодами PostgreSQL была в три раза лучше MS SQL и Oracle DataBase.

Тестирование проводилось с целью мониторинга новых технологий и реализации плана технологического развития на 2015 год.

Андрей Черногоров, генеральный директор «Индиго ИТ», отметил: «Сегодня на рынке ИТ наиболее востребованными являются СУБД MS SQL и Oracle DataBase. Вместе с тем, по ряду ключевых возможностей им ни чем не уступает, а кое-где и превосходит, СУБД с открытыми кодами PostgreSQL, что открывает перед ней широкие перспективы для использования в рамках программы импортозамещения».

Для тестирования специалисты компании подготовили идентичные для всех СУБД тестовые наборы данных. Объектом испытаний стала база данных объемом 1 ТБ, состоящая из 1 млн. бизнес-объектов. Продолжительность тестирования для каждой СУБД — 10 часов.

В нем участвовали последние версии наиболее востребованных заказчиками «Индиго ИТ» СУБД:

Всего проведено 5 наборов тестов:

  • создание сложно структурированных документов,
  • обновление сложно структурированных документов,
  • поиск документов,
  • запись файла в базу данных,
  • получение файла из базы данных.

Результаты тестирования, 2015

Под временем, затраченным в каждом из наборов тестов указанных в таблице, имеется ввиду усредненное по всем наборам значение (мс). Тестирование проводилось на серверах с процессорами Intel Xeon Е5 v3 с 128 Гб ОЗУ.

В результате нагрузочного тестирования на двух из пяти наборов тестов (создание сложно структурированных документов, обновление сложно структурированных документов) PostgreSQL 9.4 показала результаты почти в три раза лучше, чем у конкурентов. В остальных тестах (поиск документов, запись и получение файлов из БД) участники тестирования показали почти одинаковые результаты.

Андрей Черногоров заявил о намерении компании — рекомендовать заказчикам, для решений компании, PostgreSQL не только потому, что эта СУБД распространяется бесплатно, но потому, что демонстрирует более высокие показатели производительности на решениях Индиго ИТ. Он выразил уверенность, что это станет аргументом в пользу перехода на отечественные и открытые решения в рамках программы импортозамещения, объявленной президентом России.

PostgreSQL 9.4

Поддержка данной версией СУБД с открытым кодом PostgreSQL широко распространенного формата обмена данными JSON нацелена на растущий рынок нереляционных хранилищ данных NoSQL и особенно на популярную СУБД MongoDB.

В первой бета-версии PostgreSQL 9.4 появился ряд новых функций, ориентированных на стремительно расширяющийся рынок веб-приложений, многим из которых требуются быстрые хранилища и выборка больших объемов пользовательских данных.

Версия PostgreSQL 9.4 поддерживает формат JSON (JavaScript Simple Object Notation), который быстро завоевал популярность при организации обмена данными между различными системами, в том числе и с применением протокола REST (Representational State Transfer). Успех документальной СУБД MongoDB во многом обусловлен как раз растущей популярностью JSON.

Структурированный формат PostgreSQL для сохранения данных в соответствии со спецификациями JSON (JSONB) исключает необходимость реструктуризации документа перед его занесением в базу данных. В результате PostgreSQL проглатывает документы так же быстро, как и MongoDB, продолжая при этом удовлетворять требованиям ACID (atomicity, consistency, isolation, durability — атомарность, согласованность, изолированность и надежность), которые предъявляются к хранению информации в базах данных. Кроме того, PostgreSQL поддерживает полный набор индексных сервисов, функций и операторов для эффективного манипулирования данными JSON.

Предыдущие версии PostgreSQL также поддерживали JSON, но при этом документы JSON сохранялись в текстовом формате, вследствие чего операции их записи и выборки выполнялись значительно дольше.

PostgreSQL получила ряд новых функций:

  • Новый интерфейс API для декодирования данных из потока репликации открывает независимым разработчикам программного обеспечения путь к созданию более быстрых реплицирующих систем.
  • Новая функция Materialized Views, называемая «одновременным обновлением», позволяет на лету обновлять итоговые отчеты.
  • Функция Alter System Set поможет администраторам изменять файл конфигурации PostgreSQL непосредственно из командной строки SQL.

Добавлен ряд функций и возможностей, среди которых динамические фоновые исполнители (Dynamic Background Workers), манипуляции с массивами и табличные функции, увеличена общая производительность.

PostgreSQL 9.3

В PostgreSQL 9.3 реализован ряд механизмов, позволяющих обмениваться информацией с другими базами и хранилищами данных. Модули Foreign Data Wrapper, которые появились еще в версии 9.1 и раньше позволяли только считывать данные из других систем, теперь предоставляют и возможность записи. Поддерживается работа как с реляционными таблицами, так и с полуструктурированной информацией из систем NoSQL. Для СУБД также создан драйвер, который позволяет связать с друг другом две разных копии самой PostgreSQL и обеспечивает ускоренное выполнение транзакций между ними.

Среди других особенностей — расширенная поддержка JSON и возможность создания произвольных фоновых серверных модулей с неограниченным доступом к данным PostgreSQL. Пример — модуль Mongres, автоматически переводящий запросы MongoDB в формат PostgreSQL.

Реализовано автоматическое обновление представлений и добавлена утилита, позволяющая в параллельном режиме выполнять резервное копирование больших баз. Приняты меры по повышению надежности СУБД. Функция Fast Failover позволяет переключить работу с мастер-базы на копию меньше чем за секунду. Появилась возможность проверки контрольных сумм страниц, помогающая диагностировать сбои жестких дисков.

PostgreSQL 9.2

Разработчики СУБД с открытым кодом PostgreSQL продолжают оптимизировать ее для корпоративного применения: новая версия может исполняться на серверах, имеющих до 64 процессорных ядер, тогда как в предыдущей максимум был 16. PostgreSQL 9.2 также поддерживает запросы в формате JSON, благодаря чему разработчики веб-приложений могут обращаться к СУБД как к NoSQL-хранилищу «ключ-значение» с помощью привычных инструментов наподобие jQuery.

Что касается быстродействия, система в новой версии способна обрабатывать до 350 тыс. запросов на считывание в секунду, вчетверо больше, чем Postgres 9.1. А максимальная скорость записи выросла впятеро, достигнув 14 тыс. операций в секунду.

Среди других особенностей — механизм исключительного поиска по индексу, ускоряющий выполнение некоторых запросов в 20 раз, и возможность хранить и опрашивать диапазоны данных заданного типа; как объясняют разработчики, эта особенность дает большую гибкость, чем другие реляционные РСУБД, где подобные задачи решаются с использованием двух столбцов.

В Postgres улучшен механизм управления блокировками с расчетом на ускорение выполнения больших рабочих нагрузок. Сборка Postgres 9.2 выпущена в том числе для ОС HP-UX, работающей на серверах с процессорами Itanium.

PostgreSQL 9.1

PostgreSQL Global Development Group представила новую версию сервера баз данных PostgreSQL 9.1.0. Основные изменения в новой версии:

  • синхронная репликация в распределенной БД;
  • поддержка внешних таблиц, т. е. чтения файлов вне БД как таблиц (пока запись не поддерживается);
  • поддержка предложения COLLATE для выбора символьной сортировки (collation);
  • поддержка расширений для сервера;
  • полноценная изоляция последовательных (serializable) транзакций (в прежних версиях могли происходить некоторые аномалии, которые теперь устранены); алгоритм изоляции транзакций теперь соответствует уровню Repeatable read;
  • возможность создавать непротоколируемые таблицы с помощью опции UNLOGGED в команде CREATE TABLE;
  • возможность изменения данных (INSERT/UPDATE/DELETE) в предложении WITH;
  • индексы GiST теперь обеспечивают быстрый поиск ближайших соседей;
  • добавлена поддержка SELinux и команды SECURITY LABEL;
  • добавлен ряд новых возможностей программирования сервера с помощью PL/Python.

С момента выхода версии 9.0 прошел без малого год, с момента последнего обновления в ветке 9.* (9.0.4) — около 4 месяцев, а с момента выхода первого и последнего релиз-кандидата (9.1 RC1) — 20 дней. Сервер распространяется под собственной лицензией, похожей на лицензию BSD и одобренной Open Source Initiative.

PostgreSQL 9.0

Разработчики открытой системы управления базами данных PostgreSQL выпустили в сентябре 2010 года первый релиз-кандидат системы Postrgesql 9.0, в котором реализованы все функции, заготовленные к выходу в девятой версии этой популярной СУБД. В свободном доступе на данный момент доступна бинарная версия предварительной сборки Postgresql 9.0 и все желающие могут протестировать новые возможности этой разработки перед тем, как переводить на нее производственные серверы, работающие с реальной информацией.

Также в девятой версии появилась возможность репликации информации из бинарных логов, соответствующая механизму Hot Stanby Databases в Oracle Database. Не обошли вниманием разработчики и набирающие популярность облачные или SaaS-системы. Теперь СУБД оптимизирована для работы в среде виртуальных машин, поддерживает механизм быстрого клонирования данных, а также возможность репликации информации с единого мастер-сервера на большое количество (более сотни) подчиненных серверов. Также новый релиз полностью поддерживает возможности адресации памяти в 64-битных вариантах Windows.

PostgreSQL: вчера, сегодня, завтра

К сегодняшнему дню вокруг полнофункциональной СУБД с открытым кодом PostgreSQL сформировалась экосистема создания и развертывания высокопроизводительных решений, позволяющая рассматривать эту СУБД как реальную альтернативу коммерческим продуктам.

Полнофункциональная СУБД с открытым кодом PostgreSQL образовала вокруг себя экосистему построения и эксплуатации высокопроизводительных решений и сегодня эту систему можно рассматривать как альтернативу коммерческим продуктам.

Корни PostgreSQL уходят в проект POSTGRES Майкла Стоунбрейкера, профессора Калифорнийского университета в Беркли, получивший развитие как одна из трех ветвей реляционных баз данных. Первая выросла из System R, продвигаемой IBM в начале 70-х, вторая — это проект Ingres Стоунбрейкера и третья — Oracle. СУБД Ingres развивалась в духе Беркли как открытая база, коды которой распространялись на лентах по цене почтовых отправлений. Система разрабатывалась для операционной системы UNIX PDP 11, что и предопределило ее популярность, а либеральная лицензия BSD и харизма Стоунбрейкера способствовали как развитию Ingres, так и появлению большого количества реляционных СУБД.

Проект Postgres стал результатом осмысления опыта Ingres и желания преодолеть ограниченность типов данных за счет возможности определения новых типов. Работа над проектом началась в 1985 году; в период с 1985 по 1988 год появились описание модели данных, язык запросов POSTQUEL и хранилище, однако уже тогда отмечалась ограниченность реляционной модели, вытекающая из ее простоты. Первая версия постреляционной СУБД Postgres вышла в 1989 году, причем коды Ingres и Postgres не имели ничего общего. После выпуска в 1993 году версии 4.2 проект был закрыт, однако открытый код и лицензия BSD подвигли выпускников Беркли Эндрю Ю и Джолли Чена в 1994 году взяться за его дальнейшее развитие. После замены языка запросов POSTQUEL на стандартный SQL проект, получивший название Postgres95, сразу привлек к себе множество последователей.

В 1996 году проект получил название PostgreSQL, чтобы подчеркнуть связь с оригинальным проектом POSTGRES и SQL, а управление им взяла на себя инициативная группа пользователей и разработчиков PGDG (PostgreSQL Global Development Group). Все решения о планах развития и выпусках новых версий принимаются управляющим комитетом (Core team), состоящим из шести человек. Помимо этого, выделяется группа основных (major) разработчиков (около 20 человек, из которых трое из России), внесших существенный вклад в развитие PostgreSQL, а также просто разработчиков.

Разработка и поддержка

Цикл работы над очередной «мажорной» версией PostgreSQL обычно составляет около года, в течение которого любой желающий может отправить на рассмотрение свои рекомендации (патчи). Для их обсуждения используется список рассылки pgsql-hackers, и если патч прошел обязательную процедуру проверки другими разработчиками, то он включается в новый релиз (на сайте commitfest.postgresql.org организована процедура отслеживания статуса предложенных рекомендаций). В ходе подготовки релиза появляются бета-версии, выпуск которых обычно совмещается с проведением конференций PGDG.

В некоторый момент объявляется этап замораживания кода (code freeze), в течение которого рекомендации с новой функциональностью не принимаются, а допускается только исправление или улучшение кода. Иногда в процессе работы над новой версией вскрываются или исправляются ошибки предыдущих версий (backporting), и по мере накопления таких исправлений принимается решение о выпуске новой стабильной версии, совместимой со старой. Например, 9.4.4 — это исправленная версия (bugfix) стабильной версии 9.4. Ближе к концу цикла выпускается Release Candidate, а затем выходит и новая мажорная версия PostgreSQL.

Через списки рассылки PGDG выполняет поддержку мажорных версий на протяжении пяти лет с момента ее выпуска, причем корректно оформленное сообщение об ошибке имеет все шансы на скорейшее рассмотрение и нередки случаи, когда исправления выпускаются в течение суток. Помимо поддержки сообществом разработчиков, ведется и коммерческая поддержка PostgreSQL, которую осуществляют ряд компаний: EnterpriseDB в Северной Америке, 2ndQuadrant, Dalibo и другие в Европе и «Постгрес Профессиональный» в России.

Российский след PostgreSQL

Одним из первых разработчиков PostgreSQL (1996 год) был Вадим Михеев из Красноярска. Он автор таких частей СУБД, как: многоверсионное управление одновременным доступом (multiversion concurrency control, MVCC), на которой в современном PostgreSQL базируются управление транзакциями и поддержка целостности данных; система очистки (Vacuum); журнал транзакций (WAL); вложенные запросы и триггеры. Сегодня среди основных разработчиков проекта PostgreSQL три представителя из России: научный сотрудник ГАИШ МГУ Олег Бартунов, выпускник физфака МГУ Федор Сигаев и Александр Коротков (МИФИ). Ими выполнена локализация PostgreSQL (поддержка национальных кодировок, включая Unicode), создана система полнотекстового поиска и работы со слабоструктурированными данными (hstore, json, jsonb), а также предложены новые методы индексации (GiST, GIN, SP-GiST).

Бартунов и Сигаев входили в команду разработчиков портала «Рамблер» (лидера Рунета начала 2000-х), для которого потребовалось создать систему управления контентом и платформу для разработки контентных проектов, сочетающую высокую производительность и гибкость. Именно тогда возникла идея организовать средствами СУБД быстрый поиск по массивам, однако на тот момент в PostgreSQL поддерживалась работа с индексами типов B-tree и R-tree, что плохо подходило для данной задачи, поэтому разработчики обратили внимание на инфраструктуру обобщенных индексных деревьев Generalized Search Tree (GiST).

Первоначально система GiST была исследовательским проектом — обобщением над R-tree и его вариациями (RD-tree, signature-tree и т. д.), а реализация GiST для PostgreSQL, предложенная авторами GiST, имела много ограничений (ключи только фиксированного размера, отсутствие поддержки восстановления и т. д.), не позволяющих говорить о промышленном использовании. Бартунов и Сигаев модернизировали GiST, которая стала полноценным компонентом PostgreSQL, — на ее базе были разработаны индексы для быстрого поиска по массивам, система полнотекстового поиска OpenFTS и индексы для поиска по деревьям и графам ltree. Реализация R-tree с помощью GiST заменила отдельную реализацию R-tree в PostgreSQL.

В 2011 году Александр Коротков, будучи аспирантом МИФИ, в рамках программы Google Summer of Code разработал реализацию алгоритма построения GiST на дисковом пространстве и представил ее на конференции PGConf.EU 2011 (https://wiki.postgresql.org/images/0/07/Fast_GiST_index_build.pdf). Затем он предложил новый алгоритм разделения узла для R-tree, который был использован в различных применениях GiST: для встроенных геометрических типов данных, диапазонов, pgSphere, типа geometry в PostGIS.

Система полнотекстового поиска PostgreSQL является одним из главных достоинств этой СУБД: возможность включать полнотекстовые критерии поиска в произвольные SQL-запросы выгодно отличает поиск в PostgreSQL от специализированных поисковых движков типа Solr или Sphynx. Сигаев и Коротков разработали систему нечеткого поиска по текстам, действующую на основе разложения на триграммы, — модуль pg_trgm, добавивший возможность индексного поиска по условиям LIKE/ILIKE, а также по регулярным выражениям. Индексный поиск по регулярным выражениям pg_trgm был представлен на международной конференции PGCon 2012 (https://www.pgcon.org/2012/schedule/attachments/248_Alexander%20Korotkov%20-%20Index%20support%20for%20regular%20expression%20search.pdf). Однако для эффективного полнотекстового поиска и поиска по масcивам производительности GiST-индексов не хватало — требовался обратный индекс. По аналогии с GiST такой индекс был реализован: Generalized Inverted iNdex (GIN) позволяет осуществлять индексирование сложных объектов с произвольным разбиением на ключи. GIN был представлен на PostgreSQL Anniversary Summit в Торонто в 2006 году (https://www.sai.msu.su/

megera/postgres/talks/Gin-toronto-2006.pdf). В результате СУБД PostgreSQL может сегодня конкурировать со специализированными системами полнотекстового поиска. Дальнейшим развитием GiST стала технология поиска ближайших соседей (KNN), позволяющая организовывать эффективный поиск как ближайших геометрических объектов, так и похожих изображений и других сложных массивов данных.

Одно из самых популярных расширений PostgreSQL — модуль PostGIS, поддерживающий стандарт OpenGIS и все ГИС-проекции для работы с геометрическими данными в пространствах от двух до пяти измерений. В PostGIS включен разработанный Коротковым алгоритм разделения узла для типа geometry, что увеличило скорость поиска от трех до десяти раз.

Начиная с версии 8.2 (2006 год) в PostgreSQL появилось расширение Hstore, реализующее тип данных для хранения набора пар «ключ — значение», и с ростом востребованности документоориентированных СУБД возникла идея добавить в Hstore поддержку вложенности, типов и массивов. Прототип был представлен Бартуновым и Сигаевым на конференции PGCon 2013. Впоследствии на основе этой работы был создан тип данных jsonb, реализующий эффективное бинарное хранение json-объектов, что стало одной из ключевых особенностей версии PostgreSQL 9.4.

Современная СУБД PostgreSQL

За более чем 20-летнюю историю своего развития PostgreSQL из академической разработки превратилась в полноценную СУБД корпоративного уровня, составляющую реальную альтернативу коммерческим базам. Лицензия PostgreSQL разрешает ее неограниченное использование, модификацию кода, а также включение в состав других продуктов, в том числе закрытых и коммерческих.

Надежность и безопасность

Вопросы обеспечения надежности особенно важны в приложениях уровня предприятия при работе с критически важными данными. СУБД PostgreSQL дает возможность настраивать горячее резервирование и восстановление на заданный момент времени в прошлом, а также поддерживает различные виды репликации (синхронную, асинхронную и каскадную). Все это позволяет строить отказоустойчивые системы с «теплым» или «горячим» резервированием, а также создавать надежные кластерные решения.

Особое внимание в PostgreSQL уделено обеспечению безопасности — СУБД предоставляет различные методы аутентификации: по паролю в открытом или зашифрованном (md5) виде, с помощью серверов LDAP, RADIUS или подключаемых модулей (PAM); по внешней аутентификации (ident, peer, cert — сертификатSSL, gss — Kerberos по протоколу GSSAPI, sspi — Kerberos/NTLM для Windows). При управлении пользователями и доступом к объектам базы данных имеется возможность выделять отдельных пользователей и роли, которые могут быть вложенными; доступ к объектам базы (grant/revoke) может осуществляться как напрямую пользователями, так и косвенно через роли; в версии 9.5 появится разделение доступа на уровне столбцов и строк (Row Level Security); реализована поддержка SELinux через встроенную функциональность SE-PostgreSQL (мандатный доступ).

По мере развития стандарта ANSI SQL его поддержка осуществлялась и в PostgreSQL: SQL-92, SQL:1999, SQL:2003, SQL:2008 и SQL:2011. Версия PostgreSQL 9.4 поддерживает 160 из 179 обязательных возможностей SQL:2011.

СУБД PostgreSQL обеспечивает полную поддержку свойств ACID и гарантирует изоляцию транзакций благодаря механизму многоверсионного управления одновременным доступом — транзакции на чтение никогда не блокируют транзакции на запись, и наоборот. Это справедливо и для самого строгого уровня изоляции SERIALIZABLE, который использует инновационную систему SSI (SERIALIZABLE SNAPSHOT ISOLATION) и обеспечивает полную изоляцию транзакций, гарантирующую, что результат работы одновременных транзакций будет такой же, как и при их последовательном исполнении.

Возможности для разработчиков

Разработчики получают в свое распоряжение инструментарий, позволяющий создавать приложения любого типа. В его состав входят:

  • интерфейсы для Tcl, Perl, C, C++, PHP, Json, ODBC, JDBC, Embedded SQL in C, Python, Ruby, Java;
  • представления, последовательности, наследование, ограничения целостности, внешнее соединение, вложенные запросы, window-функции, CTE (запросы WITH), хранимые процедуры, функции, триггеры;
  • встроенная гибкая система полнотекстового поиска с поддержкой русского и всех европейских языков;
  • поддержка NoSQL: слабоструктурированные данные (xml, json, jsonb);
  • подключение внешних источников в качестве таблиц всех основных баз данных с возможностью записи через Foreign Data Wrappers.

Расширяемость и применение

Расширяемость — одно из фундаментальных свойств системы, лежащее в основе ее архитектуры. Пользователи могут самостоятельно добавлять функции, типы данных, операторы для работы с новыми типами, использовать индексные методы доступа (Btree, Hash, GiST, GIN, SP-GiST) и языки программирования (pl/pgsql, pl/perl, pl/python, pl/tcl, pl/R, pl/java, pl/v8. .). Подключение к внешним источникам (Foreign Data Wrappers) осуществляется через интерфейсы практически ко всем СУБД, а загружаемые расширения позволяют, например, поддерживать геоинформационные данные PostGIS, осуществлять нечеткий поиск с помощью триграмм, работу с массивами и др.

Среди крупнейших пользователей PostgreSQL такие компании, как Microsoft, Yahoo, Instagram, BASF и Afilias. Эта СУБД применяется и в государственном секторе: например, во Франции на базе PostgreSQL работают национальная метеослужба и информационная система национального фонда семейных пособий (CNAF), хранящая данные о 30 млн человек. В России PostgreSQL используется, в частности, компаниями «Яндекс», Avito, а также в ряде государственных структур и на промышленных предприятиях.

PostgreSQL поддерживает все клоны Unix, включая Linux, FreeBSD, Solaris, HPUX, Mac OS X, а также Windows.

Характеристики PostgreSQL

В СУБД PostgreSQL учитываются особенности архитектуры многоядерных процессоров, поэтому производительность растет почти линейно с увеличением количества ядер, но тем не менее в системе имеются некоторые пределы (см. таблицу).

В PostgreSQL используется планировщик запросов, позволяющий оптимизировать сложные запросы. Способность планировщика исключать просмотр дочерних таблиц на основе анализа условия запроса и имеющихся ограничений целостности (constraint exclusion) позволяет реализовать в PostgreSQL секционирование (partitioning), что особенно актуально для крупных хранилищ данных.

При индексировании, помимо традиционного B-дерева, также доступны: Hash, GIN (Generalized INverted index — обобщенный обратный индекс), GiST (Generalized Search Tree — обобщенное поисковое дерево), SP-GiST (Space-Partitioned GiST — пространственный индекс) — причем индексы могут строиться по выражениям (функциональные), а при необходимости создаются индексы только для определенных строк в таблице (частичные индексы).


Отечественная экосистема PostgreSQL

Преодоление технологической зависимости невозможно в закрытой среде [1, 2], поэтому целесообразно внедрять открытое ПО, интегрируя российское сообщество программистов, в частности, в экосистему разработки СУБД PostgreSQL, а также создавать в стране центры компетенции и развивать систему подготовки специалистов. Наличие полного комплекта исходного кода, процедур сборки, а главное, техническая поддержка силами отечественных разработчиков внутри страны являются основой успеха такой интеграции. Действительно, работоспособность СУБД в значительной степени зависит от мощной системы технической поддержки в режиме 24x7x365 — это задача промышленного уровня [3], которую для PostgreSQL решает в России компания «Постгрес Профессиональный».

В России cегодня развивается экосистема PostgreSQL, включая все больше компаний и институтов. Совместная работа участников альянса предполагает не только развитие СУБД PostgreSQL и ее расширений, но и тесную интеграцию с ОС, разработку прикладных решений, инструментов миграции с других СУБД и др. Среди наиболее значимых работ по развитию PostgreSQL можно выделить следующие.

Масштабируемый кластер shared-nothing высокой доступности. Кластер позволит масштабировать запись и обработку данных в системах OLAP и OLTP при обеспечении высокого уровня доступности, что востребовано в высоконагруженных промышленных системах. Дистрибутив отказоустойчивой конфигурации PostgreSQL позволяет относительно просто развернуть кластер с полным дублированием для работы в динамически изменяющихся вычислительных средах (например, облачных). Кроме того, можно создавать кластер MultiMaster высокой доступности с масштабированием по чтению и записи при увеличении числа узлов, причем за счет шардинга данных общая емкость хранения может превышать доступную на каждом из узлов в отдельности.

Подключаемые хранилища. Механизм foreign data wrapper (fdw) для работы со специализированными хранилищами данных (хранение по строкам или колонкам, работа с диском или хранение в оперативной памяти) позволит ускорить выполнение как OLTP-, так и OLAP-запросов.

Система автоматической адаптивной оптимизации исполнения запросов. Современные методы машинного обучения открывают новые перспективы для развития СУБД — такие задачи, как балансировка нагрузки, расчет плана выполнения запросов, построение эффективных индексов и пр., могут иметь оптимальное решение для конкретных наборов данных, запросов и режимов нагрузки. Кроме того, машинное обучение позволяет адаптивно перестраивать алгоритмы обработки в реальном времени. Разработанные совместно со специалистами из МГУ и НИУ ВШЭ инструменты машинного обучения, встроенные в стандартный функционал СУБД, способны расширить привычную область применения СУБД — в частности, позволят эффективно и с минимальной потерей точности в условиях реального времени выполнять запросы на больших объемах данных. Также появится возможность гибко реагировать на изменения распределения данных и запросов, что особенно важно для СУБД эпохи Интернета вещей.

Расширенная функциональность слабоструктурированных данных. Благодаря технологии, позволяющей работать с данными в формате JSON и JSONB, PostgreSQL сочетает в себе такие преимущества традиционных СУБД, как транзакционность, атомарность изменений и целостность данных, с гибкостью NoSQL без потери производительности. Язык запросов к слабоструктурированным данным дает возможность формулировать на SQL сложные запросы, повышая производительность за счет упрощения структур данных, переноса сложной фильтрации данных из приложений на сторону СУБД и эффективного использования индексов.

Усовершенствованная система мониторинга и трассировки выполнения запросов. Для промышленных СУБД необходима возможность мониторинга и трассировки выполнения запросов с отслеживанием ресурсов для разных стадий их обработки — это позволяет оптимизировать работу приложений и конфигурацию сервера. PostgreSQL предоставляет возможность сбора различных метрик функционирования и средства их сопоставления с метриками ОС.

Перевод документации и обучение. Начат процесс по переводу на русский язык технической документации, планируется также ее своевременная актуализация. Кроме того, в России разворачивается система подготовки специалистов по таким направлениям, как современные технологии и разработка СУБД, промышленная эксплуатация СУБД и разработка прикладных информационных систем на базе СУБД с учетом PostgreSQL. Создаются курсы повышения квалификации администраторов и разработчиков, а на их основе выстраивается система сертификации.

Будущее PostgreSQL

В июле 2015 года вышла альфа-версия PostgreSQL 9.5, в которой серьезное внимание уделено реализации новых функций, характерных для решений корпоративного уровня и направленных прежде всего на повышение надежности и быстродействия СУБД.

Функция Row level security позволяет организовать доступ не к таблице целиком, а к ее отдельным строкам. Эта возможность также известна как Virtual Private Database или Fine-grained access control и дополняет набор существующих в PostgreSQL механизмов для управления доступом к данным. Благодаря функции pgaudit можно выполнять детальный аудит операций в базе данных, что особенно полезно для автоматизации контроля функционирования прикладных систем, например для регистрации аудиторского следа. Кроме этого, в новой версии получили развитие средства работы с Большими Данными — в частности, появились индексы Block Range (BRIN) с методом доступа по диапазонам страниц (они занимают меньше пространства и требуют меньше ресурсов при обновлении, хотя и менее эффективны при выборке данных, чем B-tree). Для повышения надежности было включено расширение pg_rewind, которое при использовании репликации «ведущий-ведомый» позволяет быстро синхронизировать сбойный ведущий сервер с ведомым.

Сегодня PostgreSQL — это полнофункциональная СУБД с открытым кодом, позволяющая решать широкий круг задач. За время существования PostgreSQL вокруг нее сформировалась экосистема, включающая разработчиков, аналитиков и пользователей, благодаря чему имеется возможность расширять функционал этой СУБД в зависимости от требований рынка.

Чем PostgreSQL лучше других СУБД

You are using an outdated browser. Please upgrade your browser.

By signing up, you agree to our Terms of Service and Privacy Policy.

На протяжении уже длительного периода реляционные базы данных пользуются популярностью. MySQL существует уже достаточно долго, примерно так же как и PostgreSQL. Но Postgre имеет намного больше интересных и полезных функций, благодаря этому и набирает стремительно свою популярность. В данной статье сделаем сравнение функций MySQL и PostgreSQL, чтобы понять какая из них наиболее подходит для вашего проекта.

Для чего предназначены системы управления базами данных? Базы данных созданы для структурированного хранения и быстрого доступа к различным данным. Каждая база данных, кроме самих данных, должна иметь определенную модель работы, с помощью которой выполняться обработка данных. Для управления базами данных разработаны СУБД: MySQL и Postgresql.

Какие особенности реляционных системы управления базами данных: с помощью них можно размещать данные в таблицах, связывая строки из разных таблиц. Для начала работы перед сохранением данных, необходимо создать таблицы определенного размера и указать тип данных для каждого столбца. Столбы – это поля данных, а сами данные хранятся в строках. MySQL vs Postgresql относятся к реляционным.

История разработки MySQL и PostgreSQL.

MySQL начал создаваться еще в 90-х. Внутренний выпуск произошел в 1995 году. Тогда разработкой MySQL занимались несколько компаний. Начиная с 2010 года компания Oracle владеет проектом MySQL и разрабатывает новые версии.

PostgreSQL немного ранее в 1986 году начал разрабатываться в Калифорнийском университете. Над проектом работали более 8 лет, но потом был разделен на коммерческую БД IIlustra и свободный проект Postrgesql.

Особенности хранения данных.

В MySQL для хранения данных в таблицах используются различные движки. Движок не имеет влияния на синтаксис запросов и их выполнение. Имеется поддержка MyISAM, InnoDB, MEMORY, Berkeley DB. Их основное отличие в способе записи данных на диск и методов считывания. Как удалить все таблицы в базе данных MS SQL? PostgreSQL работает только на движке storage engine. Таблицы организованы в виде обьектов, а действия выполняются с помощью объективно ориентированных функций.

SQL – это стандартизированный язык выполнения запросов, который используется и MySQL и PostgreSQL. Этот стандарт имеет несколько версий и был разработан еще в 1986 году.

MySQL имеет поддержку не всех функций и возможностей SQL. Это сделано для того, чтобы работать с MySQL было просто и удобно. Но если для проекта необходимо какое-то расширение, разработчики его могут добавить не в ущерб стандарту.

PostgreSQL поддерживает все новые стандарты SQL, из-за этого данный проект довольно сложный и не настолько популярный как MySQL.

Возможности обработки данных.

MySQL при исполнении запроса делает загрузку всего ответа сервера в память клиента. В случае больших обьемов это не всегда удобно. По функциям Postgresql более широкий чем Mysql. Например, в Postgresql при помощи курсора можно перемещать полученные данные. Вам предоставляется только указатель, а весь ответ хранится в памяти сервера баз данных. Данный указатель можно хранить между сеансами. Postgresql имеет поддержку регулярных выражений в запросах, рекурсивных запросов и наследования таблиц.

Производительность MySQL и Postgresql.

MySQL всегда был ориентирован на большую производительность, в то время как Postgresql был нацелен на большое количество настроек и стандартов. Но со временем эта ситуация поменялась и Postgre стал более производительным.

Для организации работы с базой данных в MySQL используется таблица InnoDB. А это значит, что MySQL будет значительно быстрее Postgre в случае использовании первичного ключа.

По поводу Postgresql, вся заголовочная информация таблиц размещается в оперативной памяти. Можно применять несколько индексов к одной таблице для большего удобства. В общем PostgreSQL работает быстрее, кроме ситуаций с использованием первичных ключей.

Поддерживаемые типы данных.

MySQL и Postgresql имеют похожий набор, который, конечно же, имеет свои отличия. В Postgre типы более разнообразны и есть свои типы полей для определенных видов данных, которых, например, нет в MySQL.

Будущее MySQL и Postgresql.

Эти проекты имеют открытый исходный код, но развиваются совсем по-разному. MySQL под руководством компании Oracle тормозит в развитии. Postgresql развивается группой программистов и несколькими компаниями. Новые версии выходят достаточно часто и имеют новые функции.

Также к Вашему вниманию обзор систем управления базами данных – MariaDB и MongoDB и обзор популярных и актуальных систем управления базами данных.

Почему СУБД PostgreSQL востребована и сегодня

12:56 28.01.2020 | Мэтт Эсай | InfoWorld, США

Рубрика Технологии | 3824 прочтения

Эта СУБД стара, как мир, но в последние пять лет она словно переживает второе рождение. MongoDB провела IPO на миллиард долларов, а в Amazon Web Services запустила умопомрачительный сервис Aurora Serverless, но именно PostgreSQL в глазах профессиональных разработчиков программных систем продолжает набирать очки – снова и снова, и снова.

Четвертой в мире по популярности, согласно рейтингу DB-Engines, СУБД предстоит сделать еще немало, прежде чем ей удастся опередить Oracle, MySQL и Microsoft SQL Server. Но при нынешних темпах есть все основания полагать, что это вполне может случиться.

Естественно, возникает вопрос, в чем причина такого успеха? Почему PostgreSQL активно привлекает сегодня не только старожилов мира СУБД, но и хипстеров?

Postgres. PostgreSQL. Postgres Pro

PostgreSQL создана на основе некоммерческой СУБД Postgres, разработанной в Калифорнийском университете в Беркли. К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение легендарный Майкл Стоунбрейкер, который вместе со своими студентами работал над ней в течение восьми лет. Новую версию передали команде энтузиастов; СУБД получила новое имя — PostgreSQL; ключевой вклад в развитие ядра системы в течение продолжительного вносят российские разработчики.

На базе PostgreSQL создано несколько вариантов СУБД, в том числе, и коммерческих. Одна из них — СУБД Postgres Pro отечественной компании «Постгрес Профессиональный», включенная в единый реестр российского ПО. Значительные расширения, предложенные в «Постгрес Профессиональный» и адресованные корпоративным пользователям СУБД, сделали Postgres Pro одним из самых заметных компонентов многочисленных инициатив последних лет, связанных с импортозамещением и миграцией баз данных.

Ключевым нововведением в PostgreSQL в последние годы стала встроенная поддержка JSON, появившаяся в версии PostgreSQL 9.2. Изначально она была реализована не слишком удачно, но уже в версии 9.4 поддержка JSONB придает почтенной СУБД значительное ускорение, подкрепленное сверхмощным индексированием.

«От NoSQL и больших данных уже успела накопиться некоторая усталость, – заметил аналитик компании Redmonk Джеймс Гавернор. – И вот внезапно разработчики обнаружили, что испытанная PostgreSQL является вполне жизнеспособной альтернативой MongoDB и Apache Cassandra».

Золотое дно? Да, практически. Все привыкли к тому, что реляционные СУБД не слишком хорошо справляются с масштабируемостью. Конечно, можно заложить свой дом и получить вертикальную масштабируемость Oracle начального уровня, но так сегодня в мире никто уже не работает. Опыт Google, Facebook и других доказывает, что на самом деле имеет значение масштабируемость горизонтальная – но и в этом отношении PostgreSQL в прошлом подрезали крылья.

Однако ключевое слово здесь – «в прошлом». Благодаря расширениям PostgreSQL (одно из таких расширений — Citus) теперь СУБД легко можно масштабировать на несколько узлов, придавая интеллектуальной распределенной обработке транзакций и SQL-запросов массовый параллелизм. PostgreSQL получает больше вычислительных мощностей, памяти и дискового пространства. Как указал лидер Citus Крейг Керстинс, стартапы, предлагающие сервисы SaaS, и уже зрелые предприятия, начинающие использовать PostgreSQL, могут делать это, не опасаясь, что позже для масштабирования им придется полностью перестраивать архитектуру для NoSQL. Благодаря Citus базы данных PostgreSQL получают достаточную масштабируемость.

Помимо новомодной масштабируемости и функциональности NoSQL разработчики PostgreSQL всегда совершенствовали и традиционные технологии СУБД. Речь идет прежде всего о надежном (без потерь) хранении данных. Когда я спросил, почему PostgreSQL испытывает такой ренессанс, большинство респондентов сосредоточились на впечатляющих (и довольно скучных) аспектах: целостности данных и их корректности, расширении функционала СУБД путем различных примочек, а также возможности обращения к другим системам из PostgreSQL с использованием внешних оболочек данных.

Как заметил директор компании Joyent по проектированию решений Элайджа Зупанчич, PostgreSQL предоставляет разработчикам весьма обширные права. «С точки зрения разработчика, эту СУБД приятно использовать, – подчеркнул он. – Документация прекрасна, типы данных отражают те типы, с которыми имеют дело разработчики, и неожиданностей практически нет. В отличие от всех прочих СУБД из первой десятки, PostgreSQL не зависит от какого-то одного поставщика. Это действительно нейтральное по отношению к конкретным поставщикам сообщество разработчиков программного обеспечения с открытым кодом».

Последнее говорит само за себя. Вполне возможно, что со временем PostgreSQL вытеснит MySQL. Почему? Потому что основной вклад в MySQL вносит единственный участник: Oracle. По словам технического директора OpsCompass Джона Гренджа, клиенты отдают предпочтение PostgreSQL в значительной мере именно потому, что Oracle не имеет к ней никакого отношения. И в этом отношении клиенты OpsCompass не одиноки.

Конечно, ни MySQL, ни Oracle не уступят в ближайшее время свои позиции ни PostgreSQL, ни кому бы то ни было еще. Тенденции, однако, благоприятствуют PostgreSQL. В определенной степени это происходит потому, что ветераны ценят ее устойчивость. Однако и хипстеры успели оценить скучную добродетель PostgreSQL. Как заявил один из них в свой публикации в Hacker News, «PostgreSQL требует определенной настройки, но, проделав это, вы получаете надежную рабочую лошадку, умеющую делать все, что вам нужно».

Национальная библиотека им. Н. Э. Баумана
Bauman National Library

Персональные инструменты

Сравнение производительности MySQL vs PostgreSQL

Реляционные базы данных использовались на протяжении длительного времени. Они стали популярными благодаря системам управления, которые реализуют реляционную модель настолько хорошо, что она является наилучшим способом работы с данными, особенно для критически важных приложений и служб.

MySQL существует достаточно давно и зарекомендовала себя как отличное решение, Postgresql пришла на рынок приблизительно в то же самое время, но предоставляет достаточно много интересных функций и возможностей, благодаря чему стремительно набирает популярность. В этой статье мы попытаемся выполнить сравнение MySQL vs Postgresql, сравним основные отличия этих систем и проведем несколько тестов для сравнения производительности этих баз данных.

Содержание

Основные характеристики баз данных

MySQL — свободная реляционная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems. [1]

PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (ORDBMS) (по-русски ОРСУБД или просто СУБД) основанная на POSTGRES, версии 4.2, которая была разработана в Научном Компьютерном Департаменте Беркли Калифорнийского Университета. [2]

Сравнение MySQL и PostgreSQL [3]

Нет ограничений

Максимальный размер таблицы

Максимальный размер записи

1,6 ТБайт

Максимальный размер поля

Максимум записей в таблице

Ограничено размером таблицы

Максимум полей в таблице

250—1600, в зависимости от типов полей

Максимум индексов в таблице

Нет ограничений

Параметры MySQL PostgreSQL
Краткое описание Широко используемая свободная реляционная система управления базами данных Широко используемая свободная реляционная система управления базами данных
Основная модель хранения данных Реляционная база данных Реляционная база данных
Дополнительная модель хранения данных База данных типа Key/Value, документно-ориентированная база данных База данных типа Key/Value, документно-ориентированная база данных
Вебсайт www.mysql.com www.postgresql.org
Документация dev.mysql.com/­doc www.postgresql.org/­docs/­manuals
Разработчик Oracle PostgreSQL Global Development Group
Дата релиза 1995 1996
Текущая версия 8.0.12, Июль 2020 10.5, Август 2020
Лицензия Открытое программное обеспечение Открытое программное обеспечение
Облачное Нет
Язык реализации С++, C C
Поддерживаемые операционные системы сервера FreeBSD, Linux, Solaris, OS X, Windows FreeBSD, Linux, Solaris, OS X, Windows, NetBSD, OpenBSD, HP-UX, Unix
Схема данных Да
Типизация Да
Поддержка XML Да
Поддержка вторичных индексов Да
SQL Да
API и другие методы доступа Проприентарное нативное API, ADO.NET, JDBC, ODBC Нативная С библиотека, потоковое API для больших объектов, ADO.NET, JDBC, ODBC
Поддерживаемые языки программирования Ada, C, C#, С++, D, Delphi, Eiffel, Erlang, Haskell, Java, JavaScript (Node.js), Objective-C, OCaml, Perl, PHP, Python, Ruby, Scheme, Tcl .Net, C, С++, Delphi, Java, Perl, PHP, Python, Tcl
Язык написания скриптов на стороне сервера Да Нет Да Да Критерии сравнения

Мы сравним скорость выборки из одной таблицы, обновления одной таблицы, скорость сортировки (ORDER BY) и группировки (GROUP BY) при выборке данных из одной таблицы, а также скорость вставки в таблицу и внутреннего объединения двух таблиц.

Выбор структуры таблиц и обоснование

Мы создадим две таблицы: first_table(rand_num int, some_data varchar(40)) и second_table(rand_num int, rand_group int, some_data varchar(40)). Атрибут some_data нам нужен просто для того, чтобы увеличить объем записи на диск, так что в него можно записывать любой текст. Атрибуты rand_num обеих таблиц будут содержать в себе случайные значения, так же как и атрибут rand_group второй таблицы. Тесты будут проводиться следующим образом:

1) сначала мы будем просто добавлять строки (до 400000 строк) в первую таблицу и замерять время добавления.

Когда же мы будем заполнять вторую таблицу (до 400000 строк), то каждый раз после добавления фиксированного числа строк (40000) мы будем проводить следующие измерения: 1) скорость внутреннего объединения первой и второй таблицы по атрибуту rand_num

2) скорость выборки всех данных из второй таблицы, сортируя их по атрибуту rand_num

3) скорость подсчета числа строк второй таблицы, принадлежащих одной группе (rand_group)

4) скорость выборки тех строк из первой таблицы, атрибут rand_num которых совпадает с атрибутом rand_num какой-либо строки второй таблицы

5) Скорость обновления атрибута some_data в строках первой таблицы. Но обновлять мы будем не все строки, а только те, которые удовлетворяют условию, описанному в п.4.

Все тесты мы проведем дважды: с индексированием атрибутов rand_num обеих таблиц и без индексирования. Отметим, что в данной серии тестов нас интересует производительность «из коробки», то есть сразу после установки обеих СУБД.

Система управления базами данных PostgreSQL

История развития MySQL и PostgreSQL

История MySQL начинается в 1979 г., у ее истоков стояла небольшая компания во главе с Monty Widenius. В 1996 г. появился первый релиз 3.11 под солярис с публичной лицензией. Затем MySQL была портирована под другие операционные системы, появилась специальная коммерческая лицензия. В 2000 г., после добавления интерфейса, аналогичного Berkeley DB, база стала транзакционной. Примерно тогда же была добавлена репликация. В 2001 г. в версии 4.0 был добавлен движок InnoDB к уже имеющемуся MyISAM, в результате чего появилось кеширование и возросла производительность. В 2004 г. вышла версия 4.1, в которой появились подзапросы, парциальная индексация для MyISAM, юникод. В версии 5.0 в 2005 г. появились хранимые процедуры, курсоры, триггеры, представления (views). В MySQL развиваются коммерческие тенденции: в 2009 г. MySQL стала торговой маркой компании Oracle.

История постгрес началась в 1977 г. с базы данных Ingress.

В 1986 г. в университете Беркли, Калифорния, она была переименована в PostgreSQL.

В 1995 г. постгрес стала открытой базой данных. Появился интерактивный psql.

В 1996 г. Postgres95 была переименована в PostgreSQL версии 6.0.

У постгреса несколько сотен разработчиков по всему миру.

Архитектура MySQL и PostgreSQL

PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.

Для каждого клиента на сервере создается новый процесс (не поток !). Для работы с такими клиентскими процессами сервер использует семафоры.

Клиентский запрос проходит следующие стадии.

  1. Коннект.
  2. Парсинг: проверяется корректность запроса и создается дерево запроса (query tree). В основу парсера положены базовые юниксовые утилиты yacc и lex.
  3. Rewrite: берется дерево запросов и проверяется наличие в нем правил (rules), которые лежат в системных каталогах. Всякий раз пользовательский запрос переписывается на запрос, получающий доступ к таблицам базы данных.
  4. Оптимизатор: на каждый запрос создается план запроса – query plan, который передается исполнителю – executor. Смысл плана в том, что в нем перебираются все возможные варианты получения результата (использовать ли индексы, джойны и т.д.), и выбирается самый быстрый вариант.
  5. Выполнение запроса: исполнитель рекурсивно проходит по дереву и получает результат, используя при этом сортировку, джойны и т.д., и возвращает строки. Постгрес – обьектно-реляционная база данных, каждая таблица в ней представляет класс, между таблицами реализовано наследование. Реализованы стандарты SQL92 и SQL99.

Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.

MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.

Реализован стандарт SQL92.

С модульной точки зрения код MySQL можно разделить на следующие модули.

  1. Инициализация сервера.
  2. Менеджер коннектов.
  3. Менеджер потоков.
  4. Обработчик команд.
  5. Аутентификация.
  6. Парсер.
  7. Кеш.
  8. Оптимизатор.
  9. Табличный менеджер.
  10. Движки (MyISAM, InnoDB, MEMORY, Berkeley DB).
  11. Логирование.
  12. Репликация.
  13. Сетевое API.
  14. API ядра.

Порядок работы модулей следующий: сначала загружается первый модуль, который читает опции командной строки, конфиг-файлы, выделяет память, инициализирует глобальные структуры, загружает системные таблицы и передает управление менеджеру коннектов.

Когда клиент подсоединяется к базе, управление передается менеджеру потоков, который создает поток (не процесс!) для клиента, и проверяется его аутентификация.

Клиентские запросы в зависимости от их типа на верхнем уровне обрабатываются четвертым модулем (dispatcher). Запросы будут залогированы 11-м модулем. Команда передается парсеру, проверяется кеш. Далее запрос может попасть в оптимизатор, табличный модуль, модуль репликации, и т.д. В результате данные возвращаются клиенту через менеджер потоков.

Организация СУБД PostgreSQL

Как и любая СУБД PostgreSQL должна обслуживать запросы sql и возвращать результаты клиентским приложениям. Для этого механизм СУБД оптимизатор запросов принимает решение об оптимальном использовании ресурсов и строит план запроса. При этом он может опереться на используемых механизмы ускорения работы (индексов, кэшей в памяти, ресурсам процессора, данные статистики и т.д.).
Результаты запросов возвращаются клиентским подключениям

В Windows управляет всем файл службы pg_ctl.exe (останавливает, запускает, перезапускает).

Сам движок базы – это postgres.exe.

Оптимизатор запросов мало подвержен управлению со стороны разработчиков, поэтому к нему вернемся лишь косвенно в разделе анализа быстродействия.
Зато управление дисковым пространством имеет весомое значение. Как и в любой СУБД, для баз данных PostgreSQL лучше использовать RAID 10 для баз данных и отдельный дисковый массив под логии. Применение STORAGE систем может также положительно сказаться на всем быстродействии. Еще одна фича, ускоряющая работу за счет расположения данных на разных дисках, это табличные пространства (tablespaces).

Критически важные для администраторов многогигабайтных хранилищ данных, табличные пространства позволяют размещать большие таблицы и индексы на отдельных дисках или массивах, что повышает производительность запросов.

У PostgreSQL есть особенность в реализации хранения – это так называемый “кластер”. В данном случаи речь идет месте расположения каталога для баз данных. Плюс PostgreSQL организует единую файловую структуру, в которой отдельные файлы не соответствуют непосредственно таблицам или другим объектам базы данных.

При установке вы указываете “кластер” (читай каталог).

При установке PostgreSQL создает системную базу postgres и базу template1 как шаблон настроек для всех новых баз. Обычно в Linux-среде в каталоге /var/postgres/data находится некоторое количество служебных файлов для PostgreSQL, а в каталоге /var/postgres/data/base размещаются базы данных, каждая в своем отдельном каталоге.

К сожалению, реализованный в PostgreSQL способ хранения данных требует дополнительного обслуживания — периодической дефрагментации базы данных командой VACUUM.Примечание. Материализованные запросы (представления пользователя) view платформой не используются.

Размеры базы данных

На данный момент (версия 9.1.2), в PostgreSQL имеются следующие ограничения:

Максимальный размер базы данных

Сильными сторонами PostgreSQL считаются:

  • поддержка БД практически неограниченного размера;
  • мощные и надёжные механизмы транзакций и репликации ;
  • расширяемая система встроенных языков программирования: в стандартной поставке поддерживаются PL/pgSQL , PL/Perl , PL/Python и PL/Tcl ; дополнительно можно использовать PL/Java , PL/PHP , PL/Py , PL/R , PL/Ruby , PL/Scheme и PL/sh , а также имеется поддержка загрузки C -совместимых модулей [4] ;
  • наследование ;
  • легкая расширяемость.

Утилита администрирования баз данных PostgreSQL

pgAdmin — графическая оболочка проектирования и административная СУБД PostgreSQL для *nix и системы Windows.

pgAdmin пишется на языке программирования C и использует превосходный пакет разработчика платформы wxWidgets (когда-то wxWindows).

pgAdmin распространяется по лицензии PostgreSQL, т.е. также является свободным ПО.

PosgreSQL в 1С 8.0

1С:Предприятие 8 имеет некоторые особенности работы с СУБД PostgreSQL, связанные с использованием транзакционных блокировок:

режиме автоматического управления блокировками в транзакции используются табличные блокировки СУБД;

в режиме управляемых блокировок в транзакции используются блокировки записей и полей СУБД.

Управление соединениями с других компьютеров

По умолчанию, PostgreSQL разрешает только соединения на локальной машине через сокеты домена Unix или TCP/IP соединения. Для того, чтобы другие машины смогли подключиться к базе вы должны изменить в postgresql.conf , разрешить host-авторизация в файле $PGDATA/pg_hba.conf и перестартовать сервер.

Сравнительный Анализ PostgreSQL с MySQL

Заинтересовал меня СУБД PostgreSQL тремя моментами:

  • что-то новенькое (до этого в вебе все мои проекты бегали под mysql), интересно было пощупать что за зверь
  • более богатая возможность работы с R-деревьями (на практике — богатый набор возможностей работы с географическими данными)
  • ярые поклонники постгреса, заявляли что он стабильнее и надежнее (чем mysql)

Ниже приводится таблица сравнения основных возможностей этих СУБД. Критерии сравнения выбирались сугубо из моих интересов/опыта и обзоров в инете.

MySQL

PostgreSQL

Ответственный за код

компания MySQL AB

разные разработчики

Сжатие данных при передаче

Поддержка модели ACID

+/- (InnoDB, Falcon)

Поддержка SQL команд: insert ignore / replace

Поддержка внешних ключей

+/- (InnoDB)

Репликации

+ (говорят в 8.4 стало все хорошо)

Под запросы

Полнотекстовые индексы

+ (MyISAM)

Частичное индексирование

Чистка после работы команд UPDATE и DELETE

не нуждается

VACUUM

Система привилегий

+/- (проще чем в MySQL)

Хранение таблиц в файлах

Хранение/обработка географических данных

Лицензирование

GNU GPL

ACID-стандарт


Производительность (performance)

Базы данных часто оптимизируются в зависимости от окружения, в котором они работают. Обе базы имеют различные технологии для улучшения производительности. Исторически так сложилось, что MySQL начинала разрабатываться с прицелом на скорость, а PostgreSQL с самого начала разрабатывалась как база с большим числом настроек и соответствием стандарту. PostgreSQL имеет ряд настроек, которые повышают скорость доступа:

  • парциальные индексы;
  • компрессия данных;
  • выделение памяти;
  • улучшенный кеш.

MySQL имеет частичную поддержку парциальных индексов в InnoDB. Если взять MySQL-ский движок ISAM, он оказывается быстрее на плоских запросах, при этом нет блокировок на инсерты, нет поддержки транзакций, foreign key.

Компрессия

PostgreSQL лучше сжимает и разжимает данные, позволяя сохранить больше данных на дисковом пространстве. При этом компрессионные данные читаются быстрее с диска.

MySQL-компрессия для разных движков частично поддерживается, частично нет, и это зависит от конкретной версии конкретного движка.

На мульти-процессорности PostgreSQL имеет преимущество над MySQL. Даже сами разработчики MySQL признают, что их движок в этом плане не так хорош.

Типы данных

MySQL: для хранения бинарных данных использует типы TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, которые отличаются размером (до 4 ГБ).

Character: четыре типа – TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

PostgreSQL: поддерживает механизм пользовательских данных с помощью команды CREATE TYPE, тип BOOLEAN, геометрические типы.

Character: TEXT (ограничение – max row size).

Для хранения бинарных данных есть тип BLOB, который хранится в файловой системе. Столбцы таблицы могут быть определены как многомерный массив переменной длины. Обьектно-реляционное расширение: структура таблицы может быть унаследована от другой таблицы.

Хранимые процедуры

И PostgreSQL , и MySQL поддерживают хранимые процедуры. PostgreSQL придерживается стандарта Oracle PL/SQL, MySQL – IBM DB2. MySQL поддерживает extend SQL для написания функций на языке C/C++ с версии 5.1. PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C для написания хранимых процедур.

Ключи

И PostgreSQL , и MySQL поддерживают уникальность Primary Key и Foreign Key. MySQL не поддерживает check constraint плюс вторичные ключи реализованы частично. PostgreSQL: полная реализация плюс поддержка ON DELETE CASCADE и ON UPDATE CASCADE.

Триггеры

MySQL: рудиментарная поддержка. PostgreSQL: декларативные триггеры: SELECT, INSERT, DELETE, UPDATE, INSTEAD OF; процедурные триггеры: CONSTRAINT TRIGGER. События: BEFORE или AFTER на IN SERT, DELETE , UPDATE.

Автоинкремент

MySQL: в таблице может быть только один такой столбец, который должен быть проиндексирован. PostgreSQL: SERIAL data type.

Репликации

Поддерживаются и в MySQL, и в PostgreSQL. PostgreSQL имеет модульную архитектуру, и репликация входит в отдельные модули:

Репликация в PostgreSQL основана на триггерах и более медленная, чем в MySQL. В ядро репликацию планируется добавить, начиная с версии 8.4.

В MySQL репликация входит в ядро и имеет две разновидности, начиная с версии 5.1:

  • SBR – statement based replication;
  • RBR – row based replication.

Первый тип основан на логировании записей в бинарный лог, второй – на логировании изменений. Начиная с версии 5.5, в MySQL поддерживается так называемая полусинхронная репликация, при которой основной сервер (master) делает сброс данных на другой сервер (slave) при каждом коммите. Движок NDB делает полную синхронную двухфазную репликацию.

Транзакции

MySQL: только для для InnoDB. Поддержка SAVEPOINT, ROLLBACK TO SAVE POINT. Уровни блокировки: table level (MyISAM). PostgreSQL: поддерживается плюс read committed и уровни изоляции. Поддержка ROLLBACK, ROLLBACK TO SAVEPOINT. Уровни блокировки: row level, table level.

Уровни привилегий

PostgreSQL: для пользователя или группы пользователей могут быть назначены привилегии.

Экспорт-импорт данных

MySQL: набор утилит для экспорта: mysqldump, mysqlhotcopy, mysqlsnapshot. Импорт из текстовых файлов, html, dbf. PostgreSQL: экспорт – утилита pg_dump. Импорт между базами данных и файловой системой.

Вложенные запросы

Есть и в MySQL, и в PostgreSQL, но в MySQL могут работать непроизводительно.

Индексация

Хэширование индексов: в MySQL– частичное, в PostgreSQL – полное. Полнотекстовый поиск: в MySQL– частичный, в PostgreSQL – полный. Парциальные индексы: в MySQL не поддерживаются, в PostgreSQL поддерживаются. Многостолбцовые индексы: в MySQL ограничение 16 столбцов, в PostgreSQL – 32. Expression-индексы: в MySQL– эмуляция, в PostgreSQL – полное. Неблокирующий create index: в MySQL – частичное, в PostgreSQL – полное.

Партиционирование (Partitioning)

MySQL поддерживает горизонтальное партиционирование: range, list, hash, key, композитное партиционирование. PostgreSQL поддерживает RANGE и LIST. Автоматическое партиционирование для таблиц и индексов.

Автоматическое восстановление после сбоев

MySQL: частичное для InnoDB – нужно вручную сделать backup. PostgreSQL: Write Ahead Logging (WAL).

Data Storage Engines

PostgreSQL поддерживает один движок – Postgres Storage System. В MySQL 5.1 их несколько:

  • MyISAM – используется для хранения системных таблиц;
  • InnoDB – максимальное соответствие AC >
  • NDB Cluster – движок, ориентированный на работу с памятью, кластерная архитектура, использующая синхронную репликацию;
  • ARCHIVE – поддерживает компрессию, не использует индексы;
  • а также: MERGE, MEMORY (HEAP), CSV.

InnoDB разрабатывается компанией InnoBase, являющейся дочерней компанией Oracle. В 6-й версии должны появиться два движка – Maria и Falcon. Falcon – движок, основанный на ACID-транзакциях.

Лицензирование

PostgreSQL: BSD (Berkeley Software Distribution) open source. MySQL: GPL (Gnu General Public License ) или Commercial. MySQL – это open-source продукт. Postgres – это open-source проект.

Подводя итоги, можно сказать следующее: MySQL и PostgreSQL – две наиболее популярные open-source базы данных в мире. Каждая база имеет свои особенности и отличия. Если вам нужно быстрое хранилище для простых запросов с минимальной настройкой, я бы порекомендовал MySQL. Если вам нужно надежное хранилище для большого объема данных с возможностью расширения, репликации, полностью соответствующее современным стандартам языка SQL, я бы предложил использовать PostgreSQL.

Россияне в два раза ускорили СУБД PostgreSQL

Новый релиз Postgres Pro Enterprise

Российская компания Postgres Professional («Постгрес профессиональный») объявила о выпуске новой версии промышленной системы управления базами данных (СУБД) Postgres Pro Enterprise, предназначенной для высоконагруженных систем.

СУБД Postgres Pro Enterprise 11, по заявлению представителей компании, способна выдержать нагрузку до 10 тыс. одновременно работающих пользователей и 1 млн транзакций в секунду при размере базы данных до 150 ТБ, при этом максимальная задержка при восстановлении работоспособности кластера составит около 5 секунд.

Postgres Pro Enterprise, базирующаяся на свободной СУБД PostgreSQL, входит в Единый реестр отечественного ПО Минкомсвязи. Продукт нашел применение в информационных системах ФНС, Минфина, «Газпромнефти». В марте 2020 г. CNews рассказывал о планах Федеральной таможенной службы отказаться от СУБД Oracle в пользу продукта Postgres Professional.

Ключевые изменения

В новую версию Postgres Pro Enterprise 11 добавлен экспериментальный встроенный пулер соединений, позволяющий, как утверждают разработчики, на порядок увеличить число одновременно работающих с базой данных пользователей. Ускорено создание индексов и индексный поиск по JSON, добавлены покрывающие индексы. Кроме того, ряд улучшений коснулся оптимизации SQL-запросов, секционирования таблиц и параллельного исполнения запросов.

Кроме того, расширены возможности оконных функций до полного покрытия стандарта SQL:2011. Добавлена поддержка управления транзакциями во встроенных процедурных языках.

В число особых возможностей Postgres Pro Enterprise входят встроенный планировщик заданий, поддержка автономных транзакций и мультимастер-кластер. По сравнению со стандартным кластером PostgreSQL конструкции ведущий-ведомый в мультимастер-кластере все узлы являются ведущими. Такой подход упрощает построение и эксплуатацию отказоустойчивой конфигурации, а также позволяет проводить обновление версий СУБД без остановки системы. В Postgres Pro Enterprise 11 модуль мультимастера обновлен с целью повышения надежности и сокращения системных требований.

Postgres Pro Enterprise 11 включает поддержку расширенного управления доступом, в том числе на уровне отдельных записей. Также добавлена поддержка семейства криптографических функций SHA2 (sha224, sha256, sha384 и sha512) с целью повышения надежности хранения хешей паролей.

Краткая история Postgres Pro и PostgreSQL

Postgres Pro Enterprise – это российская коммерческая система управления базами данных для крупных клиентов, базирующаяся на свободной СУБД PostgreSQL. Возможности СУБД расширены за счет доработки ядра и включают компрессию данных на уровне блоков, 64-битный счетчик транзакций и прочие функции.

СУБД сертифицирована ФСТЭК, поэтому может применяться для защиты информации, в ГИС и АСУ до первого класса защищенности, а также обеспечения до первого уровня защищенности персональных данных в ИС, для которых к актуальным отнесены угрозы первого, второго или третьего типа.

Российский продукт разработан компанией Postgres Professional, созданной в начале 2015 г. Учредителями выступили ведущие на тот момент разработчики PostgreSQL Олег Бартунов, Федор Сигаев, Александр Коротков, разработчик Иван Панченко, а также инвестор проекта Антон Сушкевич, основатель интегратора «Энвижн груп».

Основа отечественной Postgres Pro Enterprise – PostgreSQL — реляционная СУБД, написанная одноименным сообществом разработчиков. Разработка PostgreSQL восходит к проекту POSTGRES, который стартовал в Калифорнийском университете в Беркли, США, в 1986 г. Проект спонсировали американские госведомства, в том числе Министерство обороны США. Разработкой руководил Майкл Стоунбрейкер (Michael Stonebraker), создатель таких СУБД как Ingres, Informix и VoltDB. Первая рабочая версия POSTGRES была выпущена в 1989 г.

POSTGRES использовалась в области анализа финансовых данных и работы с реактивными двигателями. На нем работали базы данных наблюдений за астероидами и медицинской информации, а также ряд геоинформационных систем. В 1993 г. разработку системы прекратили, последней версией стала 4.2.

В 1994 г. Эндрю Ю (Andrew Yu) и Джолли Чен (Jolly Chen) модифицировали POSTGRES, добавив в него интерпретатор языка SQL. Система получила название Postgres95 и стала распространяться с открытым кодом.

В 1996 г. проект был переименован в PostgreSQL. В этот же период была восстановлена нумерация версий, восходящая к POSTGRES. Если Postgres95 фокусировался на поиске проблем в серверном коде, то PostgreSQL — на расширении функциональности.

Мастер Йода рекомендует:  Раскрутка сайта из собственного опыта
Добавить комментарий