Пост грейт sql что это

Чем PostgreSQL лучше других SQL баз данных с открытым исходным кодом. Часть 1

Возможно, вы спрашиваете себя: «Почему PostgreSQL?» Ведь есть и другие варианты реляционных баз данных с открытым исходным кодом (в рамках этой статьи мы рассматривали MySQL, MariaDB и Firebird), так что же Постгрес может предложить такого, чего нет у них? В слогане PostgreSQL заявляется, что это «Самая продвинутая база данных с открытым исходным кодом в мире». Мы приведем несколько причин, почему Постгрес делает такие заявления.

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

Модель данных

PostgreSQL не просто реляционная, а объектно-реляционная СУБД. Это даёт ему некоторые преимущества над другими SQL базами данных с открытым исходным кодом, такими как MySQL, MariaDB и Firebird.

Фундаментальная характеристика объектно-реляционной базы данных — это поддержка пользовательских объектов и их поведения, включая типы данных, функции, операции, домены и индексы. Это делает Постгрес невероятно гибким и надежным. Среди прочего, он умеет создавать, хранить и извлекать сложные структуры данных. В некоторых примерах ниже вы увидите вложенные и составные конструкции, которые не поддерживаются стандартными РСУБД.

Структуры и типы данных

Существует обширный список типов данных, которые поддерживает Постгрес. Кроме числовых, с плавающей точкой, текстовых, булевых и других ожидаемых типов данных (а также множества их вариаций), PostgreSQL может похвастаться поддержкой uuid, денежного, перечисляемого, геометрического, бинарного типов, сетевых адресов, битовых строк, текстового поиска, xml, json, массивов, композитных типов и диапазонов, а также некоторых внутренних типов для идентификации объектов и местоположения логов. Справедливости ради стоит сказать, что MySQL, MariaDB и Firebird тоже имеют некоторые из этих типов данных, но только Постгрес поддерживает их все.

Давайте рассмотрим подробнее некоторые из них:

Сетевые адреса

• 192.168.100.128/25
• 10.1.2.3/32
• 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
• ::ffff:1.2.3.0/128

У MySQL и MariaDB тоже есть INET функции для конвертации сетевых адресов, но они не предоставляют типы данных для внутреннего хранения сетевых адресов. У Firebird тоже нет типов для хранения сетевых адресов.

Многомерные массивы

Поскольку Постгрес — это объектно-реляционная база данных, массивы значений могут храниться для большинства существующих типов данных. Сделать это можно путём добавления квадратных скобок к спецификации типа данных для столбца или с помощью выражения ARRAY. Размер массива может быть задан, но это необязательно. Давайте рассмотрим меню праздничного пикника для демонстрации использования массивов:

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

Геометрические данные

Геоданные быстро становятся основным требованием для многих приложений. PostgreSQL уже давно поддерживает множество геометрических типов данных, таких как точки, линии, круги и многоугольники. Один из этих типов – PATH, он состоит из множества последовательно расположенных точек и может быть открытым (начальная и конечная точки не связаны) или закрытым (начальная и конечная точки связаны). Давайте рассмотрим в качестве примера туристическую тропу. В данном случае туристическая тропа — это петля, поэтому начальная и конечная точки связаны, и, значит, мой путь является закрытым. Круглые скобки вокруг набора координат указывают на закрытый путь, а квадратные — на открытый.

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

Заметьте, что в MySQL 5.7.8 и в MariaDB, начиная с версии 5.3.3, были добавлены расширения типов данных для поддержки стандарта географической информации OpenGIS. Эта версия MySQL и последующие версии MariaDB предлагают хранение типов данных, аналогичное штатным геоданным Постгреса. Тем не менее, в MySQL и MariaDB значения данных сначала должны быть сконвертированы в геометрический формат простыми командами перед тем, как будут вставлены в таблицу. Firebird на данный момент не поддерживает геометрические типы данных.

Поддержка JSON

Поддержка JSON в PostgreSQL позволяет вам перейти к хранению schema-less данных в SQL базе данных. Это может быть полезно, когда структура данных требует определённой гибкости: например, если в процессе разработки структура всё ещё меняется или неизвестно, какие поля будет содержать объект данных.

Тип данных JSON обеспечивает проверку корректности JSON, который позволяет использовать специализированные JSON операторы и функции, встроенные в Постгрес для выполнения запросов и манипулирования данными. Также доступен тип JSONB — двоичная разновидность формата JSON, у которой пробелы удаляются, сортировка объектов не сохраняется, вместо этого они хранятся наиболее оптимальным образом, и сохраняется только последнее значение для ключей-дубликатов. JSONB обычно является предпочтительным форматом, поскольку требует меньше места для объектов, может быть проиндексирован и обрабатывается быстрее, так как не требует повторного синтаксического анализа.

В MySQL 5.7.8 и MariaDB 10.0.1 была добавлена поддержка встроенных объектов JSON. Но, хотя существует множество функций и операторов для JSON, которые теперь доступны в этих базах данных, они не индексируются так, как JSONB в PostgreSQL. Firebird пока что не присоединился к тренду и поддерживает объекты JSON только в виде текста.

Создание нового типа

Если вдруг так случится, что обширного списка типов данных Постгреса вам окажется недостаточно, вы можете использовать команду CREATE TYPE, чтобы создать новые типы данных, такие как составной, перечисляемый, диапазон и базовый. Рассмотрим пример создания и отправки запросов нового составного типа:

Поскольку они не являются объектно-реляционными, MySQL, MariaDB и Firebird не предоставляют такую мощную функциональность.

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

PostgreSQL может обрабатывать много данных. Текущие опубликованные ограничения перечислены ниже:

Максимальный размер базы данных	Неограничен
Максимальный размер таблицы	32 TB
Максимальный размер строки	1.6 TB
Максимальный размер поля	1 GB
Максимальное количество строк в таблице	Неограничено
Максимальное количество столбцов в таблице	250-1600 в зависимости от типа столбца
Максимальное количество индексов в таблице	Неограничено

В Compose [прим. пер.: организация, в которой трудится автор оригинальной статьи] мы автоматически масштабируем вашу инсталляцию, чтобы вам не приходилось волноваться о росте количества данных. Но, как известно любому администратору баз данных, стоит с опаской относиться к слишком большим и неограниченным возможностям. Мы советуем руководствоваться здравым смыслом при создании таблиц и добавлении индексов.

Для сравнения, MySQL и MariaDB печально известны ограничением размера строк в 65 535 байт. Firebird также предлагает всего лишь 64Кб в качестве максимального размера строки. Обычно объём данных ограничивается максимальным размером файлов операционной системы. Поскольку PostgreSQL умеет хранить табличные данные в множестве файлов меньшего размера, он может обойти это ограничение. Но стоит отметить, что слишком большое количество файлов может негативно сказаться на производительности. MySQL и MariaDB поддерживают большее количество столбцов в таблице (до 4,096 в зависимости от типа данных) и большие индивидуальные размеры таблицы, чем PostgreSQL, но необходимость превысить существующие ограничения Постгреса возникает лишь в крайне редких случаях.

Целостность данных

Постгрес стремится соответствовать стандарту ANSI-SQL:2008, отвечает требованиям ACID (атомарность, согласованность, изолированность и надежность) и известен своей ссылочной и транзакционной целостностью. Первичные ключи, ограничивающие и каскадные внешние ключи, уникальные ограничения, ограничения NOT NULL, проверочные ограничения и другие функции обеспечения целостности данных дают уверенность, что только корректные данные будут сохранены.

MySQL и MariaDB больше работают на то, чтобы соответствовать стандарту SQL с движками таблиц InnoDB/XtraDB. Теперь они предлагают опцию STRICT с использованием режимов SQL, которая устанавливает проверки корректности используемых данных. Несмотря на это, в зависимости от того, какой режим вы используете, недостоверные и даже урезанные без вашего ведома данные могут быть вставлены или созданы при обновлении. Ни одна из этих баз данных сейчас не поддерживает CHECK ограничения. Кроме того, у них существует множество особенностей в отношении ограничений ссылочной целостности по внешним ключам. В дополнение к вышесказанному, целостность данных может существенно пострадать в зависимости от выбранного движка хранения. MySQL (и fork MariaDB) не делают секрета из того, что променяли целостность и соответствие стандартам на скорость и эффективность.

Подводя итоги

У Постгреса множество возможностей. Созданный с использованием объектно-реляционной модели, он поддерживает сложные структуры и широкий спектр встроенных и определяемых пользователем типов данных. Он обеспечивает расширенную ёмкость данных и заслужил доверие бережным отношением к целостности данных. Возможно, вам не понадобятся все те продвинутые функции хранения данных, которые мы исследовали в этой статье, но, поскольку потребности могут быстро возрасти, есть несомненное преимущество в том, чтобы иметь всё это под рукой.

Если вам кажется, что PostgreSQL не соответствует вашим потребностям, или вы предпочитаете “стрелять от бедра”, тогда вам стоит обратить внимание на NoSQL базы данных, которые мы предлагаем в Compose, или подумать о других SQL базах данных, которые мы упоминали. У каждой из них есть свои преимущества. Compose твёрдо уверен, что очень важно выбрать правильную базу данных для конкретной задачи… иногда это означает, что нужно выбрать несколько баз данных!

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

Пост грейт sql что это

Краткую историю PostgreSQL можно прочитать в документации, распространяемой с дистрибутивом или на сайте. Также, есть перевод на русский язык. Из нее следует, что современный проект PostgreSQL ведет происхождение из проекта POSTGRES, который разрабатывался под руководством Майкла Стоунбрейкера (Michael Stonebraker), профессора Калифорнийского университета в Беркли (UCB). Мне захотелось несколько подробнее показать взаимосвязи родословных баз данных, чтобы лучше понять место PostgreSQL среди основных игроков современного рынка баз данных.

Я попытался графически ( большая версия картинки откроется в новом окне) отобразить все наиболее заметные RDBMS и связи между ними и приблизительно привел даты их создания и конца. Пересечение объектов означает поглощение, при этом поглощаемый объект более бледен и не окантован. Знак доллара означает, что база данных является коммерческой. При этом, я основывался на информации, доступной в интернете, в частности в Wikipedia, в научных статьях, которые я читал и комментариях непосредственных пользователей БД, которые я получил после публикации этой картинки в интернете.

Надо сказать, что несмотря на то, что вся история реляционных баз данных насчитывает менее 4 десятков лет, многие факты из истории создания трактуются по-разному, даты не согласуются, а сами участники событий зачастую просто вольно трактуют прошлое.Здесь надо принимать во внимание тот факт, что базы данных — это большой бизнес, в котором развитие одних БД часто связано с концом других. Кроме того, БД в то время были предметом научных исследований, поэтому приоритетность работ является не последним аргументом при написании воспоминаний и интервью. Наверное, учитывая такую запутанность, премия ACM Software System Award #6 была присуждена одновременно двум соперничающим группам исследователей из IBM за работу над «System R» и Беркли — за INGRES, хотя Стоунбрейкер получил награду от ACM SIGMOD (сейчас это премия названа в честь Теда Кодда — автора реляционной теории баз данных) #1 в 1992 г., а Грей (James Gray, Microsoft) — #2 в 1993 году.

Итак, как следует из рисунка, видно две ветви развития баз данных — одна следует из «System R», которая разрабатывалась в IBM в начале 70-х, и другая из проекта «INGRES», которым руководил Стоунбрейкер приблизительно в тоже время. Эти два проекта начались как необходимость практического использования реляционной модели баз данных, разработанной Тедом Коддом (Ted Codd) из IBM в 1969,1970 годах. Надо помнить, что в то время имелось две альтернативные модели баз данных — сетевая и иерархическая, причем за ними стояли мощные силы — CODASYL Data Base Task Group (сетевая) и сама IBM с ее базой IMS (Information Management System с иерархической моделью данных). Немного в стороне стоит «Oracle», взлет которой во многом связан с коммерческим талантом Эллисона быть в нужном месте и в нужное время, как сказал Стоунбрейкер в своем интервью, хотя она вместе с IBM сыграла большую роль в создании и продвижении SQL.

«System R» сыграла большую роль в развитии реляционных баз данных, создании языка SQL (изначально SEQUEL, но из-за проблем с уже существующей торговой маркой пришлось выкинуть все гласные буквы). Из «System R» развилась SQL/DS и DB2. На самом деле, в IBM было еще несколько проектов, но они были чисто внутренними. Подробнее об этой ветви можно прочитать в весьма поучительном документе «The 1995 SQL Reunion: People, Projects, and Politics», русский перевод которого доступен по адресу www.citforum.ru/database/digest/sql1.shtml.

INGRES (или Ingres89), в отличие от «System R», вполне в духе Беркли развивалась как открытая база данных, коды которой распространялись на лентах практически бесплатно (оплачивались почтовые расходы и стоимость ленты). К 1980 году было распространено порядка 1000 копий. Название расшифровывается как «INteractive Graphics Retrieval System» и совершенно случайно связано с французским художником Jean Auguste Dominique Ingres. Отличительной особенностью этой системы являлось то, что она разрабатывалась для операционной системы UNIX, которая работала на распространенных тогда PDP 11, что и предопределило ее популярность, в то время как «System R» работала только на больших и дорогих mainframe. Был разработан язык запросов QUEL, который, как писал Стоунбрейкер, похож на SEQUEL в том отношении, что программист свободен от знания о структуре данных и алгоритмах, что способствует значительной степени независимости от данных. Доступность INGRES и очень либеральная лицензия BSD, а также творческая деятельность, способствовали появлению большого количества реляционных баз данных, как показано на рисунке.

Стоунбрейкер лично способствовал их появлению, так он конце 70-х он организовал компанию Ingres Corporation (как он сам объясняет, ему пришлось на это пойти, так как Аризонский университет, потребовал поддержки), которая выпустила коммерческую версию Ingres, в 1994 году она была куплена CA (Computer Associates) и которая в 2004 году стала открытой как Ingres r3.

«NonStop SQL» компании Tandem Computers являлась модифицированной версией Ingres, которая эффективно работала на параллельных компьютерах и с распределенными данными. Она умела выполнять запросы параллельно и масштабировалась почти линейно с количеством процессоров. Ее авторами были выпускники из Беркли. Впоследствии, Tandem Computers была куплена компанией Compaq (2000 г.), а затем компанией HP.

Компания Sybase тоже была организована человеком из Беркли (Роберт Эпстейн) и на основе Ingres. Известно, что база данных компании Мaйкрософт «SQL Server» — это не что иное как база данных Sybase, которая была лицензирована для Windows NT. С 1993 года пути Sybase и Mirosoft разошлись и уже в 1995 году Sybase переименовывает свою базу данных в ASE (Adaptive Server Enterprise), а Microsoft стала продолжать развивать MS SQL.

Informix тоже возник из Ingres, но на это раз людьми не из Беркли, хотя Стоунбрейкер все-таки поработал в ней CEO после того, как Informix купила в 1995 году компанию Ilustra, чтобы прибавить себе объектно-реляционности и расширяемости (DataBlade), которую организовал все тот же Майкл Стоунбрейкер как результат коммерциализации Postgres в 1992 году. В 2001 году она была куплена IBM, которая приобретала немалое количество пользователей Informix и технологию. Таким образом, DB2 также приобрела немного объектно-реляционности.

Проект Postgres возник как результат осмысления ошибок Ingres и желания преодолеть ограниченность типов данных, за счет возможности определения новых типов данных. Работа над проектом началась в 1985 и в период 1985-1988 было опубликовано несколько статей, описывающих модель данных, язык запросов POSTQUEL, и хранилище Postgres.

Еще при проектировании оригинальной версии POSTGRES основное внимание было уделено расширяемости и объектно-ориентированным возможностям. Уже тогда было ясна необходимость расширения функциональности DMBS от управления данными (data management) в сторону управления объектами (object management) и знаниями (knowledge management). При этом объектная функциональность позволит эффективно хранить и манипулировать нетрадиционными типами данных, а управление знаниями позволяет хранить и обеспечивать выполнения коллекции правил (rules), которые несут семантику приложения. Стоунбрейкер так и определил основную задачу POSTGRES как «обеспечить поддержку приложений, которые требуют службы управления данными, объектами и знаниями«.

Одним из фундаментальным понятием POSTGRES является class. Class есть именованная коллекция экземпляров (instances) объектов. Каждый экземпляр имеет коллекцию именованных атрибутов и каждый атрибут имеет определенный тип. Классы могут быть трех типов — это основной класс, чьи экземпляры хранятся в базе данных, виртуальный (view), чьи экземпляры материализуются только при запросе (они поддерживаются системой управления правилами), и может быть версией другого (parent) класса.

Первая версия была выпущена в 1989 году, затем последовало еще несколько переписываний системы правил (rule system). Отметим, что коды Ingres и Postgres не имели ничего общего ! В 1992 году была образована компания Illustra, а сам проект был закрыт в 1993 году выпуcком версии 4.2. Однако, несмотря на официальное закрытие проекта, открытый код и BSD лицензия сподвигли выпускников Беркли Andrew Yu и Jolly Chen в 1994 году взяться за его дальнейшее развитие. В 1995 году они заменили язык запросов POSTQUEL на общепринятый SQL, проект получил название Postgres95, изменилась нумерация версий, был создан веб сайт проекта и появились много новых пользователей (среди которых был и автор).

К 1996 году стало ясно, что название «Postgres95» не выдержит испытанием временем и было выбрано новое имя — «PostgreSQL», которое отражает связь с оригинальным проектом POSTGRES и приобретением SQL. Также, вернули старую нумерацию версий, таким образом новая версия стартовала как 6.0. В 1997 был предложен слон в качестве логотипа, сохранилось письмо в архивах рассылки -hackers за 3 марта 1997 года и последующая дискуссия. Слон был предложен Дэвидом Янгом в честь романа Агаты Кристи «Elephants can remember» (Слоны могут вспоминать). До этого, логотипом был бегущий леопард (ягуар). Проект стал большой и управление на себя взяла небольшая вначале группа инициативных пользователей и разработчиков, которая и получила название PGDG (PostgreSQL Global Development Group). Дальнейшее развитие проекта полностью документировано в документации и отражено в архивах списка рассылки -hackers.

Что есть PostgreSQL сегодня ?

На сегодняшний день выпущена версия PostgreSQL v8 (19 января 2005 года), которая является значительным событием в мире баз данных, так как количество новых возможностей добавленных в этой версии, позволяет говорить о возникновении интереса крупного бизнеса как в использовании, так и его продвижении. Так, крупнейшая компания в мире, Fujitsu поддержала работы над версией 8, выпустила коммерческий модуль Extended Storage Management. Либеральная BSD-лицензия позволяет коммерческим компаниям выпускать свои версии PostgreSQL под своим именем и осуществлять коммерческую поддержку. Например, компания Pervasive объявила о выпуске Pervasive Postgres.

PostgreSQL поддерживается на всех современных Unix системах (34 платформы), включая наиболее распространенные, такие как Linux, FreeBSD, NetBSD, OpenBSD, SunOS, Solaris, DUX, а также под Mac OS X. Начиная с версии 8.X PostgreSQL работает в «native» режиме под MS Windows NT, Win2000, WinXP, Win2003. Известно, что есть успешные попытки работать с PostgreSQL под Novell Netware 6 и OS2.

Основные возможности и функциональность

• Надежность PostgreSQL является проверенным и доказанным фактом и обеспечивается следующими возможностями:
  • полное соответствие принципам ACID — атомарность, непротиворечивость, изолированность, сохранность данных.
    • Atomicity — транзакция рассматривается как единая логическая единица, все ее изменения или сохраняются целиком, или полностью откатываются.
    • Consistency — транзакция переводит базу данных из одного непротиворечивого состояния (на момент старта транзакции) в другое непротиворечивое состояние (на момент завершения транзакции). Непротиворечивым считается состояние базы, когда выполняются все ограничения физической и логической целостности базы данных, при этом допускается нарушение ограничений целостности в течение транзакции, но на момент завершения все ограничения целостности, как физические, так и логические, должны быть соблюдены.
    • Isolation — изменения данных при конкурентных транзакциях изолированы друг от друга на основе системы версионности
    • Durability — PostgreSQL заботится о том, что результаты успешных транзакций гарантировано сохраняются на жесткий диск вне зависимости от сбоев аппаратуры.

    • Поддержка индексов
      • Стандартные индексы — B-tree, hash, R-tree, GiST (обобщенное поисковое дерево)
      • Частичные индексы (partial indices)
      • Функциональные индексы

      На рисунке приведена ER диаграмма системного каталога PostgreSQL, в котором заложены все сведения об объектах системы, операторах и методах доступа к ним. При инициализации PostgreSQL кластера (команда initdb) создаются две базы данных — template0 и template1, которые содержат предопределенный по умолчанию набор функциональностей. Любая другая база данных наследует template1, таким образом, часто используемые объекты и методы можно добавить в системный каталог template1.

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

      • Написать функции ввода/вывода и зарегистрировать их в системном каталоге с помощью CREATE FUNCTION
      • Определить тип в системном каталоге с помощью CREATE TYPE
      • Создать операторы для этого типа данных с помощью CREATE OPERATOR
      • Написать функции сравнения и зарегистрировать их в системном каталоге с помощью CREATE FUNCTION
      • Создать оператор по умолчанию, который будет использоваться для создания индекса по primary key — CREATE OPERATOR CLASS
      • модуль полнотекстового поиска tsearch2
      • модуль для работы с иерархическими данными (tree-like) ltree
      • модуль для работы с массивами целых чисел intarray

      Дистрибутив PostgreSQL в поддиректории contrib/ содержит большое количество (около 80) так называемых контриб-модулей, реализующих разнообразную дополнительную функциональность, такую как, полнотекстовый поиск, работа с xml, функции математической статистики, поиск с ошибками, криптографические модули и т.д. Также, есть утилиты, облегчающие миграцию с mysql, oracle, для административных работ.

      • Очень высокий уровень соответствия ANSI SQL 92, ANSI SQL 99 и ANSI SQL 2003. Подробнее можно прочитать в документации.
      • Схемы, которые обеспечивают пространство имен на уровне SQL. Схемы содержат таблицы, в них можно определять типы данных, функции и операторы. Используя полное имя объекта можно одновременно работать с несколькими схемами. Схемы позволяют организовать базы данных совокупность нескольких логических частей, каждая их которых имеет свою политику доступа, типы данных. Для приложений, которые создают новые объекты в базе данных удобно и безопасно создавать отдельную схему (и включать ее в SEARCH_PATH) с тем, чтобы избежать возможной коллизии с именами объектов и удобством обновления приложения.
      • Subqueries — подзапросы (subselects), полная поддержка SQL92. Подзапросы делают язык SQL более гибким и зачастую более эффективным.
      • Outer Joins — внешние связки (LEFT,RIGHT, FULL)
      • Rules — правила, согласно которым модифицируется исходный запрос. Главное отличие от триггеров состоит в том, что rule работает на уровне запроса и перед исполнением запроса, а триггер — это реакция системы на изменение данных, т.е. триггер запускается в процессе исполнения запроса для каждой измененной записи (PER ROW). Правила используются для указания системе, какие действия надо произвести при попытке обновления view.
      • Views — представления, виртуальные таблицы. Реальных экземпляров этих таблиц не существуют, они материализуются только при запросе. Одним из основных предназначений ‘view’ является разделение прав доступа к родительским таблицам и к ‘view, а также обеспечение постоянства пользовательского интерфейса при изменении родительских таблиц. Обновление ‘view’ (материализация) возможно в PostgreSQL с помощью pl/pgsql.
      • Cursors — курсоры, позволяют уменьшить трафик между клиентом и сервером, а также память на клиенте, если требуется получить не весь результат запроса, а только его часть.
      • Table Inheritance — наследование таблиц, позволяющее создавать объекты, которые наследуют структуру родительского объекта и добавлять свои специфические атрибуты. При этом наследуются значения атрибутов по умолчанию (DEFAULTS) и ограничение целостности (CONSTRAINTS). Поиск по родительской таблице автоматически включает поиск по дочерним объектам, при этом сохраняется возможность поиска только по ней (only). Наследование можно использовать для работы с очень большими таблицами для эмуляции partitioning.
      • Prepared Statements (преподготовленные запросы) — это объекты, живущие на стороне сервера, которые представляют собой оригинальный запрос после команды PREPARE, который уже прошел стадии разбора запроса (parser), модификации запроса (rewriting rules) и создания плана выполнения запроса (planner), в результате чего, можно использовать команду EXECUTE, которая уже не требует прохождения этих стадий. Для сложных запросов это может быть большим выигрышем.
      • Stored Procedures — серверные (хранимые) процедуры позволяют реализовывать бизнес логику приложения на стороне сервера. Кроме того, они позволяют сильно уменьшить трафик между клиентом и сервером.
      • Savepoints(nested transactions) — в отличие от «плоских транзакций», которые не имеют промежуточных точек фиксации, использование savepoints позволяет отменять работу части транзакции, например вследствии ошибочно введенной команды, без влияния на оставшуюся часть транзакции. Это бывает очень полезно для транзакций, которые работают с большими объемами данных.
      • Права доступа к объектам системы на основе системы привилегий. Владелец объекта или суперюзер может как разрешать доступ (GRANT), так и отменять (REVOKE).
      • Система обмена сообщениями между процессами — LISTEN и NOTIFY позволяют организовывать событийную модель взаимодействия между клиентом и сервером (клиенту передается название события, назначенное командой notify и PID процесса).
      • Триггеры позволяют управлять реакцией системы на изменение данных (INSERT,UPDATE,DELETE), как перед самой операцией (BEFORE), так и после (AFTER). Во время выполнения триггера доступны специальные переменные NEW (запись, которая будет вставлена или обновлена) и OLD (запись перед обновлением).
      • Cluster table — упорядочивание записей таблицы на диске согласно индексу, что иногда за счет уменьшения доступа к диску ускоряет выполнение запроса.

      • Символьные типы (character(n)) как определено в стандарте SQL и тип text с практически неограниченной длиной.
      • Numeric тип поддерживает произвольную точность, очень востребованную в научных и финансовых приложениях.
      • Массивы согласно стандарту SQL:2003
      • Большие объекты (Large Objects) позволяют хранить в базе данных бинарные данные размером до 2Gb
      • Геометрические типы (point, line, circle,polygon, box. ) позволяют работать с пространственными данными на плоскости.
      • ГИС (GIS) типы в PostgreSQL являются доказательством расширяемости PostgreSQL и позволяют эффективно работать с трехмерными данными. Подробности можно найти на сайте проекта PostGis.
      • Сетевые типы (Network types) поддерживают типы данных inet для IPV4, IPV6, а также cidr (Classless Internet Domain Routing) блоки и macaddr
      • Композитные типы (composite types) объединяют один или несколько элементарных типов и позволяют пользователям манипулировать со сложными объектами.
      • Временные типы (timestamp, interval, date, time) реализованы с очень большой точностью
      • Псевдотипы serial и bigserial позволяют организовать упорядоченную последовательность целых чисел (AUTO_INCREMENT у некоторых СУБД).

      • PostgreSQL нельзя запустить под привилегированным пользователем — системный контекст
      • SSL,SSH шифрование трафика между клиентом и сервером — сетевой контекст
      • сложная система аутентификации на уровне хоста или IP адреса/подсети. Система аутентификации поддерживает пароли, шифрованные пароли, Kerberos, IDENT и прочие системы, которые могут подключаться используя механизм подключаемых аутентификационных модулей.
      • Детализированная система прав доступа ко всем объектам базы данных, которая совместно со схемой, обеспечивающая изоляцию названий объектов для каждого пользователя, PostgreSQL предоставляет богатую и гибкую инфраструктуру.

      Некоторые ограничения PostgreSQL

      Название	Значение
      Максимальный размер БД	Unlimited
      Максимальный размер таблицы	32 TB
      Максимальная длина записи	1.6 TB
      Максимальный длина атрибута	1 GB
      Максимальное количество записей в таблице	Unlimited
      Максимальное количество атрибутов в таблице	250 — 1600 в зависимости от типа атрибута
      Максимальное количество индексов на таблицу	Unlimited

      Сводная таблица основных реляционных баз данных

      За основу взяты данные из Wikipedia

      Название	ASE	DB2	FireBird	InterBase	MS SQL	MySQL	Oracle	PostgreSQL
      ACID	Yes	Yes	Yes	Yes	Yes	Depends 1	Yes	Yes
      Referential integrity	Yes	Yes	Yes	Yes	Yes	Depends 1	Yes	Yes
      Transaction	Yes	Yes	Yes	Yes	Yes	Depends 1	Yes	Yes
      Unicode	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
      Schema	Yes	Yes	Yes	Yes	Yes	No	Yes	Yes
      Temporary table	No	Yes	No	Yes	Yes	Yes	Yes	Yes
      View	Yes	Yes	Yes	Yes	Yes	No	Yes	Yes
      Materialized view	No	Yes	No	No	No	No	Yes	No 3
      Expression index	No	No	No	No	No	No	Yes	Yes
      Partial index	No	No	No	No	No	No	No	Yes
      Inverted index	No	No	No	No	No	Yes	No	No
      Bitmap index	No	Yes	No	No	No	No	Yes	No
      Domain	No	No	Yes	Yes	No	No	Yes	Yes
      Cursor	Yes	Yes	Yes	Yes	Yes	No	Yes	Yes
      User Defined Functions	Yes	Yes	Yes	Yes	Yes	No 4	Yes	Yes
      Trigger	Yes	Yes	Yes	Yes	Yes	No 4	Yes	Yes
      Stored procedure	Yes	Yes	Yes	Yes	Yes	No 4	Yes	Yes
      Tablespace	Yes	Yes	No	?	Yes	No 1	Yes	Yes
      Название	ASE	DB2	FireBird	InterBase	MS SQL	MySQL	Oracle	PostgreSQL

      • 1 — для поддержки транзакций и ссылочной целостности требуется InnoDB (не является типом таблицы по умолчанию)
      • 3 — Materialized view (обновляемые представления) могут быть эмулированы на PL/pgSQL
      • 4 — только в MySQL 5.0, которая является экспериментальной версией

      Что ожидается в будущих версиях

      • интегрирование autovacuum в серверный процесс
      • Two phase commit JDBC driver
      • поддержка IN,OUT,INOUT параметров для pl/pgsql
      • увеличение предела максимального количества аргументов у функции (100 по умолчанию)

      PGDG — PostgreSQL Global Development Group

      PostgreSQL развивается силами международной группы разработчиков (PGDG), в которую входят как непосредственно программисты, так и те, кто отвечают за продвижение PostgreSQL (Public Relation), за поддержание серверов и сервисов, написание и перевод документации, всего на 2005 год насчитывается около 200 человек. Другими словами, PGDG — это сложившийся коллектив, который полностью самодостаточен и устойчив. Проект развивается по общепринятой среди открытых проектов схеме, когда приоритеты определяются реальными нуждами и возможностями. При этом, практикуется публичное обсуждение всех вопросов в списке рассылке, что практически исключает возможность неправильных и несогласованных решений.

      Это относится и к тем предложениям, которые уже имеют или рассчитывают на финансовую поддержку коммерческих компаний.

      Цикл разработки

      • Обсуждение предложений в списке -hackers. На собственном опыте могу заверить, что это очень непростой процесс и плохо подготовленный proposal не пройдет. Учитываются много факторов — алгоритмы, структуры данных, совместимость с существующей архитектурой, совместимость с SQL и так далее.
      • После принятия решения о работе над новой версией в CVS открывается новая ветка и с этого момента все изменения, касающиеся новых возможностей, вносятся туда. Также, анализируются патчи, которые присылаются в список -patches. Все изменения протоколируются и доступны любому для рассмотрения (anonymous CVS, -commiters лист рассылки или через веб-интерфейс к CVS). Иногда, в процессе работы над новой версией вскрываются или исправляются старые ошибки, в этом случае, наиболее критические исправляются и в предыдущих версиях (backporting). По мере накопления таких исправлений принимается решение о выпуске новой стабильной версии, которая совместима со старой и не требует обновления хранилища. Например, 7.4.7 — является bugfix-ом стабильной версии 7.4.
      • В некоторый момент объявляется этап code freeze(замораживания кода), после которого в CVS не допускается новая функциональность, а только исправление или улучшение кода. Граница между новой функциональностью и улучшением кода не описана и иногда возникают разногласия на этот счет, к документации и расширениям (contribution modules в поддиректории contrib/) обычно относятся более либерально. Замечу, что все это время все CVS версия проходит непрерывное тестирование на большом количестве машин, под разными архитектурами, операционными системами и компиляторами. Все это стало возможно благодаря проекту pgbuildfarm, который является распределенной системой тестирования, объединяющая добровольцев, которые предоставляют свои машины для тестирования. Проверяется не только корректность сборки, но и, благодаря обширному набору тестов (regression test), и правильность работы. Время от времени, проект OSDB помогает в обнаружении систематических изменений производительности (в обе стороны), иногда такие обнаружения приводят к необходимости «размораживания кода».
      • После внутреннего тестирования «собирается» дистрибутив и объявляется выход бета версии, на тестирование и исправление ошибок отводится 1-3 месяца. Бета версия не рекомендуется для использования в продакшн проектах (production), но практика показала хорошее качество таких версий и многие начинают ее использовать ради апробирования новой функциональности. Как правило, окончательная версия совместима с бета-версией и не требует обновления хранилища. По мере исправления замеченных ошибок выпускаются новые бета-версии.
      • После исправления всех замеченных ошибок, выпускается релиз-кандидат, который уже практически ничем не отличается от окончательной версии, разве что не хватает документации и списка изменений.
      • В течении месяца выходит окончательная версия, которая анонсируется на главном веб-сайте проекта и его зеркалах, мэйлинг листах. Также, PR группа, которая к этому моменту подготовила анонсы на разных языках, распространяет их по всем ведущим сайтам и СМИ. Они принимают участие в конференциях, семинарах и прочих общественных мероприятиях.

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

      Структура

      • Управляющий комитет (6 человек).
        Принимает решение о планах развития и выпусках новых версий.
      • Заслуженные разработчики ( 2 человека ).
        Бывшие члены управляющего комитета, которые отошли от участия в проекте.
      • Основные разработчики (23).
      • Разработчики (22)
      • принимают на работу членов PGDG
      • оплачивают разработку каких-либо новых возможностей
      • предоставляют услуги в виде хостинга или оплаты трафика
      • поддерживают публичные мероприятия PGDG

      Где используется

      Сообщество

      Поддержка

      • Основной источник актуальной информации о PostgreSQL является его официальный сайтwww.postgresql.org, который имеет зеркала по всему миру. На нем публикуются сведения о всех событиях (анонсы релизов, семинаров, конференций), поддерживается список ресурсов, относящихся к PostgreSQL.
      • Основная поддержка осуществляется через почтовую рассылку, архивы которой доступны через Web по адресам:
        • archives.postgresql.org
          Архив pgsql-ru-general — русскоязычного списка рассылки,как подписаться.
        • www.pgsql.ru/db/mw

        Небольшая статистика списков рассылок PostgreSQL по данным www.pgsql.ru на 1 апреля 2005 года.

        Разработка

        Заключение

        Благодарности

        Текст написан Олегом Бартуновым в 2005 году, поправки и комментарии приветствуются.

        Олег Бартунов (основная специальность астроном, работает в ГАИШ МГУ) является членом PGDG (основной разработчик) с 1996 года, был в числе основателей компании «GreatBridge», является членом «The PostgreSQL Foundation». Помимо использования PostgreSQL в проектах (самые известные — этo портал Рамблер, Научная Сеть, Астронет), он в 1996 году добавил поддержку locale в PostgreSQL, затем совместно с Федором Сигаевым (компания Delta-Soft) занимался поддержкой и разработкой GiST в PostgreSQL, на основе которого были разработаны такие популярные модули как полнотекстовый поиск, работа с массивами, поиск с ошибкам, поддержка иерархических данных. Соавтор свободного полнотекстового поиска для PostgreSQL OpenFTS. Является автором и создателем (совместно с Федором Сигаевым) сайта pgsql.ru. Занимается продвижением PostgreSQL для использования в астрономии, в частности, для работы с очень большими астрономическими каталогами, проект pgSphere — хранение данных со сферическими координатами и индексные методы доступа к ним.

        About

        PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 35 years of active development on the core platform.

        PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organisations.

        Getting started with using PostgreSQL has never been easier — pick a project you want to build, and let PostgreSQL safely and robustly store your data.

        Why use PostgreSQL?

        PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset. In addition to being free and open source, PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database!

        PostgreSQL tries to conform with the SQL standard where such conformance does not contradict traditional features or could lead to poor architectural decisions. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time. As of the version 15 release in October 2022, PostgreSQL conforms to at least 170 of the 179 mandatory features for SQL:2016 Core conformance. As of this writing, no relational database meets full conformance with this standard.

        Below is an inexhaustive list of various features found in PostgreSQL, with more being added in every major release:

        • Data Types
          • Primitives: Integer, Numeric, String, Boolean
          • Structured: Date/Time, Array, Range / Multirange, UUID
          • Document: JSON/JSONB, XML, Key-value (Hstore)
          • Geometry: Point, Line, Circle, Polygon
          • Customizations: Composite, Custom Types
          • UNIQUE, NOT NULL
          • Primary Keys
          • Foreign Keys
          • Exclusion Constraints
          • Explicit Locks, Advisory Locks
          • Indexing: B-tree, Multicolumn, Expressions, Partial
          • Advanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, Bloom filters
          • Sophisticated query planner / optimizer, index-only scans, multicolumn statistics
          • Transactions, Nested Transactions (via savepoints)
          • Multi-Version concurrency Control (MVCC)
          • Parallelization of read queries and building B-tree indexes
          • Table partitioning
          • All transaction isolation levels defined in the SQL standard, including Serializable
          • Just-in-time (JIT) compilation of expressions
          • Write-ahead Logging (WAL)
          • Replication: Asynchronous, Synchronous, Logical
          • Point-in-time-recovery (PITR), active standbys
          • Tablespaces
          • Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more
          • Robust access-control system
          • Column and row-level security
          • Multi-factor authentication with certificates and an additional method
          • Stored functions and procedures
          • Procedural Languages: PL/pgSQL, Perl, Python, and Tcl. There are other languages available through extensions, e.g. Java, JavaScript (V8), R, Lua, and Rust
          • SQL/JSON path expressions
          • Foreign data wrappers: connect to other databases or streams with a standard SQL interface
          • Customizable storage interface for tables
          • Many extensions that provide additional functionality, including PostGIS
          • Support for international character sets, e.g. through ICU collations
          • Case-insensitive and accent-insensitive collations
          • Full-text search

          There are many more features that you can discover in the PostgreSQL documentation. Additionally, PostgreSQL is highly extensible: many features, such as indexes, have defined APIs so that you can build out with PostgreSQL to solve your challenges.

          PostgreSQL has been proven to be highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL clusters in production environments that manage many terabytes of data, and specialized systems that manage petabytes.

          Any questions?

          The first place to go to for any questions on PostgreSQL is its world-renowned documentation which discusses how to use PostgreSQL in-depth.

          We also have many mailing lists where you can connect and participate in the community. There are also many events and local user groups where you can connect with other PostgreSQL users.

          Our users us

          We have been heavily using PostgreSQL since 9.3 and are very excited about version 10 since it brings basis for long-awaited partitioning and built-in logical replication. It will allow us to use PostgreSQL in even more services,

          Etsy is the sum total of its data, so when it came to choosing where to store our critical information there was only one sound choice. PostgreSQL's strong emphasis on quality, stability, and data integrity contribute to making it the premier open source database.

          The Etsy Development Team, Etsy.com

          "I can't say enough good things about PostgreSQL. From backups to application development to administration, PostgreSQL has been a joy to work with."

          As the chief database architect for Synthetic Genomics, I was utterly amazed at the power, flexibility and richness of programming interfaces for Postgres.

          Good software with good services makes us very satisfied with our choice of PostgreSQL and free software.

          Intro to PostgreSQL

          

          There’s a number of options available when it comes to choosing a database when starting on a new project. You could use JavaScript-based MongoDB which stores data as documents. While you can link related data to documents in MongoDB, using a relationship-based database could be a better option if you’re looking to run complex queries.

          Postgres uses SQL which stands for Structured Query Language. This is a human-readable language that has the ability to get very specific results. Postgres, MySQL, SQLite, and Oracle are all SQL databases that store information in a table-like format.

          One advantage of Postgres is that you can use JSON as a datatype. This is helpful when you want to be able to have a column that can store a variety of content types like videos, polls, images, etc. Another reason you may choose Postgres is for its scalability and compatibility with applications running on NodeJS.

          Create Database and Tables

          After following the steps to download and install the Postgres, you can enter the CLI by typing ‘psql’ in your terminal. Here’s a list of common commands that are used in the Postgres CLI that will allow you to view existing tables and changes made to the database.

          As I mentioned earlier, SQL is very human-readable. Creating a database is as simple as typing ‘CREATE DATABASE’. Although you don’t have to capitalize all keywords, it is conventional to do so.

          The code above will create a database and a table called users. Each user will have a unique id that is an integer. This id will act as a primary key that is automatically generated upon creation. If you have worked with MongoDB a primary key is equivalent to the _id. It’s used for creating relationships between separate tables of data. VARCHAR is a data type that refers to text that we can put a maximum value on. If we don’t need to limit this data, we can use the TEXT type instead. By including UNIQUE NOT NULL, we are explicitly saying that if a username is not unique an error will be thrown and it will not be saved.

          To add a user to our newly created table, we can use the INSERT INTO keywords followed by the table name, columns, and values for those columns. The values should be in single-quotes — never double-quotes.

          Select and Query Clauses

          Every query that we run will begin with the keyword SELECT. If you want to select all of the data from a table, you can run something like this…

          To get more specific data you can use clauses. Clauses are additional keywords like WHERE and ORDER BY that allow us to drill down into the table data further and reduce it to return only what we need at the moment. Here’s an example.

          This will return the primary key and username of the first ten results from the users table sorted by username in descending order. The default order of results is ascending, so that’s what you’ll get if you don’t include the DESC clause.

          The order of clauses is important. Although you’ll likely not need to use all of these clauses at once, here’s the acceptable order of operations when querying.

          1. FROM, including JOINs
          2. WHERE
          3. GROUP BY
          4. HAVING
          5. WINDOW functions
          6. SELECT
          7. DISTINCT
          8. UNION
          9. ORDER BY
          10. LIMIT and OFFSET

          You can read more about each of these clauses and their use cases here.

          Update and Delete

          Changing the values within our tables is as simple as defining what we want to set as the new values and querying for the data we want to change.

          Deleting is just as easy.

          Foreign Keys

          Remember that the beauty of SQL databases is that they are relational and we can build connections. In our users table example, we have a primary key that is always generated on the creation of a user and will always be unique. We can map that primary key to another table to refer to that user instance. This is called a foreign key.

          In the new comments table, the user_id is defined as an INT REFERENCE data type that acts as a foreign key to link the user_id to a specific comment instance. ON DELETE CASCADE tells Postgres to delete all the comments that are associated with a specific user if that user has been deleted. This helps us from keeping data that is no longer useful.

          Join Tables

          Now that we have a foreign key on the comment table, we can implement a join table. Join tables allow us to display related data. They help us to follow the coding principle of a single source of truth by pulling data from one table into another, instead of adding it manually which could lead to conflicts.

          The code above would create a table with a column for comment id, user id, and username. It’s pulling this information from the comments table and the users table. They are connected by the comment’s foreign key (comments.user_id) that matches the user’s primary key (users.user_id).

          INNER JOIN is just one of several join tables. The most commonly used join tables are inner, left, and right. Choosing the right join table will depend on where the information you need is stored. Here’s a brief description of the types of join tables available to use.

          I found this diagram to also be useful for understanding the differences between each type of join table.

          Additional Resources

          We’ve only dipped our toes into the basic functionality and features of Postgres. I hope that this has piqued your curiosity about SQL databases. Here are some helpful resources to help you get more acclimated to this fantastic tool.

          Похожие публикации:

          1. Drop table sql что это
          2. Left outer join sql описание чем отличается от left join
          3. Microsoft sql server как пользоваться
          4. Ms sql set nocount on что это