Кодировки удалось решить такую проблему. Проблемы для кодирования национальных алфавитов

SQL ,

Разработка веб-сайтов

Перевод

Сегодня давайте поговорим о преимуществах Postgres перед другими системами с открытым кодом. Эту тему мы обязательно раскроем более подробно на PG Day"16 Russia, до которой осталось всего два месяца.

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

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

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

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

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

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

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

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

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

PostgreSQL обеспечивает хранение разных типов сетевых адресов. Тип данных CIDR (бесклассовая маршрутизация интернет домена, Classless Internet Domain Routing) следует соглашению для сетевых адресов IPv4 и IPv6. Вот несколько примеров:

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

Также для хранения сетевых адресов доступен тип данных INET, используемый для IPv4 и IPv6 хостов, где подсети являются необязательными. Тип данных MACADDR может использоваться для хранения MAC-адресов для идентификации оборудования, таких как 08-00-2b-01-02-03.

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

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

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

Создаем таблицу, у которой значения являются массивами CREATE TABLE holiday_picnic (holiday varchar(50) -- строковое значение sandwich text, -- массив side text , -- многомерный массив dessert text ARRAY, -- массив beverage text ARRAY -- массив из 4-х элементов); -- вставляем значения массивов в таблицу INSERT INTO holiday_picnic VALUES ("Labor Day", "{"roast beef","veggie","turkey"}", "{ {"potato salad","green salad","macaroni salad"}, {"chips","crackers"} }", "{"fruit cocktail","berry pie","ice cream"}", "{"soda","juice","beer","water"}");
MySQL, MariaDB, и Firebird так не умеют. Чтобы хранить такие массивы значений в традиционных реляционных базах данных, придется использовать обходной путь и создавать отдельную таблицу со строками для каждого из значений массива.

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

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

Создаем таблицу для хранения троп CREATE TABLE trails (trail_name varchar(250), trail_path path); -- вставляем тропу в таблицу, -- для которой маршрут определяется координатами в формате широта-долгота INSERT INTO trails VALUES ("Dool Trail - Creeping Forest Trail Loop", ((37.172,-122.22261666667), (37.171616666667,-122.22385), (37.1735,-122.2236), (37.175416666667,-122.223), (37.1758,-122.22378333333), (37.179466666667,-122.22866666667), (37.18395,-122.22675), (37.180783333333,-122.22466666667), (37.176116666667,-122.2222), (37.1753,-122.22293333333), (37.173116666667,-122.22281666667)));
Расширение 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, чтобы создать новые типы данных, такие как составной, перечисляемый, диапазон и базовый. Рассмотрим пример создания и отправки запросов нового составного типа:

Создаем новый составной тип "wine" CREATE TYPE wine AS (wine_vineyard varchar(50), wine_type varchar(50), wine_year int); -- создаем таблицу, которая использует составной тип "wine" CREATE TABLE pairings (menu_entree varchar(50), wine_pairing wine); -- вставляем данные в таблицу при помощи выражения ROW INSERT INTO pairings VALUES ("Lobster Tail",ROW("Stag""s Leap","Chardonnay", 2012)), ("Elk Medallions",ROW("Rombauer","Cabernet Sauvignon",2012)); /* выборка из таблицы с использованием имени колонки (используйте скобки, отделяемые точкой от имени поля в составном типе) */ SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type FROM pairings WHERE menu_entree = "Elk Medallions";
Поскольку они не являются объектно-реляционными, MySQL, MariaDB и Firebird не предоставляют такую мощную функциональность.

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

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

В 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 считаются:

Исторический очерк

PostgreSQL ведет свою «родословную» от некоммерческой СУБД Postgres, разработанной, как и многие проекты, в Калифорнийском университете в Беркли . К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер , руководитель более раннего проекта Ingres , на тот момент уже приобретённого компанией Computer Associates. Само название «Postgres » расшифровывалось как «Post Ingres », соответственно, при создании Postgres были применены многие уже ранее сделанные наработки.

Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет, с по 1994 год . За этот период в синтаксис были введены процедуры, правила, пользовательские типы и многие другие компоненты. Работа не прошла даром - в 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres - Postgres95, в которой язык запросов POSTQUEL - наследие Ingres - был заменен на SQL.

В этот момент разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. С этого момента СУБД получила имя, под которым она известна и развивается в текущий момент - PostgreSQL.

Основные возможности

Функции

Функции являются блоками кода, исполняемыми на сервере, а не на клиенте БД. Хотя они могут писаться на чистом 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++ , Java (через модуль PL/Java);
Статистический язык (через модуль PL/R).

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

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

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

Триггеры

Пользовательские объекты

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

Преобразования типов
Типы данных
Домены (пользовательские типы с изначально наложенными ограничениями)
Функции (включая агрегатные)
Индексы
Операторы (включая переопределение уже существующих)
Процедурные языки

Наследование

Таблицы могут наследовать характеристики и наборы полей от других таблиц (родительских). При этом данные, добавленные в порождённую таблицу, автоматически будут участвовать (если это не указано отдельно) в запросах к родительской таблице.

Данная функциональность в текущее время не является полностью завершённой. Однако она достаточна для практического использования.

Прочие возможности

Соблюдение принципов ACID .
Соответствие стандартам ANSI SQL-92 и SQL-99.
Поддержка запросов с OUTER JOIN , UNION , UNION ALL , EXCEPT , INTERSECT и подзапросов.
Последовательности.
Поддержка Юникода (UTF-8).
Поддержка регулярных выражений в стиле Perl .
Встроенная поддержка SSL ,SELinux и Kerberos .
Протокол разделяемых блокировок.
Подгружаемые расширения, поддерживающие SHA1 , MD5 , XML ,

расширения для написания сложных выборок, отчётов и другую функциональность (API открыт).

Средства для генерации совместимого с другими системами SQL-кода и импорта из других систем.
Автономные блоки на доступных языках, а не только SQL

Надёжность

Согласно результатам автоматизированного исследования различного ПО на предмет ошибок, в исходном коде PostgreSQL было найдено 20 проблемных мест на 775 000 строк исходного кода (в среднем, одна ошибка на 39 000 строк кода) . Для сравнения: MySQL - 97 проблем, одна ошибка на 4 000 строк кода; FreeBSD (целиком) - 306 проблем, одна ошибка на 4 000 строк кода; Linux (только ядро) - 950 проблем, одна ошибка на 10 000 строк кода.

Коммерческие расширения

На базе PostgreSQL компанией EnterpriseDB созданы более мощные варианты этой СУБД, являющиеся платными для коммерческого использования - Postgres Plus (состоит целиком только из продуктов с открытыми исходными кодами; плата требуется только при необходимости приобретения коммерческой поддержки продукта) и Postgres Plus Advanced Server (расширение PostgreSQL специальными возможностями для обеспечения совместимости с Oracle Database) . В комплекте поставки данных продуктов содержится большой набор ПО для разработчиков и DBA:

Postgres Studio - более мощный аналог pgAdmin;
Postgres Plus Debugger - отладчик для кода на PL/pgSQL, интегрированный с предыдущим пакетом;
Migration Studio - инструмент для автоматического преобразования баз данных из MySQL/Oracle в PostgreSQL

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

Основные возможности СУБД по мере выхода новых версий

8.3

Интегрированный полнотекстовый поиск
Базовая поддержка для XML
Новый тип - перечисление
Массивы составных типов(структур в понимании С)
Новый тип - UUID(уникальный глобальный идентификатор)

8.4

Аналитические функции
Общие табличные выражения и рекурсивные запросы
Сопоставление локали(COLLATION) отдельно для каждой базы данных
Права доступа на колонку

9.0

Автономные блоки(использование любых разрешённых процедурных языков без создания самих процедур)
Асинхронная встроенная репликация с «тёплым резервом» посредством журнала транзакций
Поддержка 64 разрядных версий ОС WINDOWS
Облегчено администрирование прав на множества объектов
Триггеры могут быть ориентированы на отдельные столбцы и их комбинации
Отложенные до конца транзакции проверки уникальности
Ограничения типа Exclusion
Сверх-быстрый механизм извещений между сессиями(LISTEN/NOTIFY)

9.1

Синхронная репликация
Очень быстрые нежурналируемые таблицы
Сопоставление локали(COLLATION) вплоть до каждого столбца таблицы отдельно
Расширение возможностей общетабличных выражений для INSERT/UPDATE/DELETE
Доступ на чтение ко внешним источникам данных как к таблицам
Введён механизм пакетов расширений(extension)
Введён каноничный уровень изоляции serializable
Расширение функционала SELinux

9.2

Введён механизм index-only scan для планировщика
Новый тип данных - диапазоны(range data types)
Новый тип данных - JSON
Многоуровневая потоковая репликация

Дальнейшие планы

Дальнейшие планы по развитию СУБД находятся по адресу http://wiki.postgresql.org/wiki/Todo

Примечания

Ссылки

Официальный сайт PostgreSQL (англ.)
Search PostgreSQL sites (англ.) . Специализированный поиск по ресурсам PostgreSQL
PostgreSQL . Серия из шести статей, опубликованных в журнале Linux Format (2006-2007)

Базы данных
Концепции
Объекты
Ключи

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

Числовые типы

smallint

короткое 2-х байтовое целое

integer

обычное 4-х байтовое целое

bigint

большое 8-байтовое целое

decimal

numeric

дробное с фиксированной точкой

real

дробное с плавающей точкой

double precision

дробное с плавающей точкой двойной точности

serial

целое с автоувеличением

bigserial

большое целое с автоувеличением

Денежные типы

money

для хранения денежных значений

Символьные типы

character varying(n), varchar(n)

строка переменной длины с ограничением

character(n), char(n)

строка фиксированной длины

text

строка переменной неограниченной длины

Бинарные (двоичные) типы

bytea

бинарная строка переменной длины

Дата и время

timestamp [ (p) ] [ без часового пояса ]

дата и время

timestamp [ (p) ] с часовым поясом

дата и время с часовым поясом

interval [ (p) ]

интервал времени

date

только дата

time [ (p) ] [ без часового пояса ]

только время

time [ (p) ] с часовым поясом

только время с часовым поясом

Логические типы

boolean

TRUE или FALSE

Геометрические типы

point

Точка на плоскости (x,y)

line

Невидимая линия (не полностью реализовано)

lseg

Видимый отрезок ((x1,y1),(x2,y2))

box

Четырёхугольник ((x1,y1),(x2,y2))

path

Замкнутый многоугольник (похож на полигон) ((x1,y1),…)

path

Ломаная линия [(x1,y1),…]

polygon

Полигон (похож на замкнутый многоугольник) ((x1,y1),…)

circle

Круг (x,y),r (центр и радиус)

Типы для адресов компьютерных сетей

cidr

IPv4 или IPv6 сеть

inet

IPv4 или IPv6 хост и сеть

macaddr

MAC адрес

Битовые строки

bit [ (n) ]

битовая строка фиксированной длины

bit varying [ (n) ]

битовая строка переменной длины

Типы для поиска текста

tsquery

запрос на поиск текста

tsvector

список для поиска текста

UUID тип

uuid

универсальный уникальный идентификатор

XML типы

xml

данные XML

Кроме этого набора типов, PostgreSQL предоставляет возможность создания списков (тип ENUM), массивов типов, составных типов наподобие структур в языке C, а также имеет типы для уникальной идентификации объектов (OID) и псевдотипы для хранимых процедур.

Типы данных, создаваемые пользователем

С помощью команды CREATE TYPE пользователи могут создавать новые типы данных для своих нужд.

Локализация

База PostgreSQL работает с локализацией, установленной в операционной системе и отвечающей стандарту POSIX. На практике это означает возможность работы с несколькими десятками языков, в том числе и с русским языком во всех возможных кодировках: koi8-r, cp1251, iso8859-5 и UTF-8. Возможность корректной работы PostgreSQL с конкретной кодировкой зависит от корректной поддержки этой кодировки средствами самой операционной системы.
Благодаря использованию библиотеки gettext, сообщения об ошибках и в утилитах переведены на многие языки, в том числе и на русский.

Языки

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

Python
C/C++
PL/pgSQL

Функции и операторы

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

Индексы
PostgreSQL предлагает 4-ре типа индексов: B-tree, Hash, GiST и GIN. Каждый тип индекса имеет свой алгоритм реализации, что позволяет существенно увеличить быстродействие, если для определённого вида данных выборать определённый типа индекса.
PostgreSQL позволяет создавать индексы с использованием выражений, например: CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
PostgreSQL позволяет создавать частичные (partial) индексы, используя выражение WHERE, например: CREATE INDEX orders_unbilled_index ON orders (order_nr) WHERE billed IS NOT true;.

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

Многоверсионный контроль конкурентых транзакций и изоляция транзакций
В PostgreSQL реализован (Multiversion Concurrency Control, MVCC) — многоверсионный контроль конкурентных транзакций, который управляет конкурентным доступом к данным на многоверсионной основе. На практике это означает, что при запросе к БД каждая транзакция видит как бы снимок данных (версию) на момент этого снимка, а не текущее состояние данных. Таким образом транзакции защищаются от просмотра нецелостных данных, которые могут ещё только формироваться другими конкурентными транзакциями в тех же самых строках таблицы. Этим же достигается изоляция транзакций для каждой сессии к БД. MMVC позволяет избегать методов явной блокировки, которые применяются в традиционных СУБД и таким образом, минимизирует блокировки данных и позволяет увеличить производительность в многопользовательской работе. Основное преимущество MMVC состоит в том, что чтение данных никогда не блокирует запись, а запись никогда не блокирует чтение.
Также в PostgreSQL реализованы традиционные схемы явных блокировок данных, применяющихся для изоляции транзаций, такие как:

блокировка на уровне таблицы
блокировка на уровне записи в таблице (строки)
advisory блокировки (для собственных блокировок на уровне приложений)

Также реализовано отслеживание deadlocks (взаимных блокировок)

Журналы (логи) опережающей записи (WAL)
PostgreSQL реализует механизм WAL (журналов опережающей записи), что даёт такие преимущества как:

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

Табличные пространства (tablespaces)
Табличные пространства в PostgreSQL позволяют задать место хранения объектов БД в файловой системе. Сперва создаётся табличное пространство с определённым именем. Далее, это имя может быть использовано при создании таблиц, чтобы разместить эти таблицы именно в данном табличном пространстве

Гибкая настройка сервера
Основной конфигурационный файл postgresql.conf включает более настраиваемых 150 параметров по разделам:

Файлы и пути к ним
Сетевые соединения
Авторизация и безопасность
Выделение ресурсов
WAL — логи обратной записи
Планирование запросов
Ошибки и протоколирование
Статистика запросов
Оптимизация данных через VACUUM
Управление блокировками
Совместимость версий и платформ
Настройки клиента по умолчанию

Дополнительный конфигурационный файл pg_hba.conf включает в себя настройки доступа к отдельным БД, такие как указание конкретных IP адресов и(или) сетей, с которых разрешён доступ, а также метод(ы) авторизации для доступа к БД и возможность включения безопасных (зашифрованных соединений) через SSL.

Ограничения целостности
Поддерживаются следующие ограничения целостности:

NOT NULL — не NULL
UNIQUE — уникальность
PRIMARY KEY — первичный ключ
FOREIGN KEY/REFERENCES — внешний ключ, ссылки
CHECK — проверка

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

Триггеры
Триггеры предназначены для автоматического выполнения отдельных процедур в зависимости от операции, для которой они были назначены. Триггеры могут быть назначены до или после операций INSERT, UPDATE или DELETE как для случаев изменения записи в таблице так и для случая выполнения оператора SQL. Если произошло событие, на которое был назначен триггер, то вызывается закреплённая за этим триггером процедура.

Система правил
Система правил (более правильно говорить: система правил изменения запросов) позволяет изменять запросы согласно заданным правилам и затем передаёт изменённый запрос планировщику запросов для планирования и выполнения. Система правил является очень мощным инструментом и может быть использована во многих случаях таких как хранимые процедуры, представления (views) и версии.

Схемы

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

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

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

Резервное копирование и восстановление
PostgreSQL предлагает несколько режимов резервного копирования и восстановления БД. Поскольку БД располагаются в файловой системе, вполне нормальным методом является резервное копирование на уровне файлов, т.е. самого каталога где размещаются файлы БД. Единственное условие такого режима — полный останов сервера PostgreSQL . Однако, для систем высокой готовности такой режим резервного копирования недопустим, поэтому PostgreSQL позволяет выполнять резервное копирование при запущенном сервере, не прерывая его обычной работы. Наиболее простой режим — это получение дампа БД в текстовом виде (в форме операторов SQL) на стандартный вывод. Для экономии дискового пространства можно сразу же перенаправлять такой дамп на стандартный ввод утилите сжатия (например gzip). Также существует возможность создания дампа БД в двоичной форме, а также возможность задавать специальные параметры для большего удобства в получении резервной копии и её последующего восстановления.
PostgreSQL также предоставляет возможность резервного копирования и за счёт этого, восстановление БД на конкретный момент времени, а также инкрементальное резервное копирование.

Базы данных - это логически смоделированные хранилища любых типов данных. Каждая база данных, не являющаяся бессхемной, следует модели, которая задаёт определённую структуру обработки данных. СУБД - это приложения (или библиотеки), управляющие базами данных различных форм, размеров и типов.

Чтобы лучше разобраться в СУБД, ознакомьтесь с .

Реляционные системы управления базами данных

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

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

Отношения и типы данных

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

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

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

SQLite

SQLite - это изумительная библиотека, встраиваемая в приложение, которое её использует. Будучи файловой БД, она предоставляет отличный набор инструментов для более простой (в сравнении с серверными БД) обработки любых видов данных.

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

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

NULL: NULL-значение.

INTEGER: целое со знаком, хранящееся в 1, 2, 3, 4, 6, или 8 байтах.

REAL: число с плавающей запятой, хранящееся в 8-байтовом формате IEEE.

TEXT: текстовая строка с кодировкойUTF-8, UTF-16BE или UTF-16LE.

BLOB: тип данных, хранящийся точно в таком же виде, в каком и был получен.

Note: для получения более подробной информации ознакомьтесь с документацией .

Преимущества

Файловая: вся база данных хранится в одном файле, что облегчает перемещение.

Стандартизированная: SQLite использует SQL; некоторые функции опущены (RIGHT OUTER JOIN или FOR EACH STATEMENT), однако, есть и некоторые новые.

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

Недостатки

Отсутствие пользовательского управления: продвинутые БД предоставляют пользователям возможность управлять связями в таблицах в соответствии с привилегиями, но у SQLite такой функции нет.

Невозможность дополнительной настройки: опять-таки, SQLite нельзя сделать более производительной, поковырявшись в настройках - так уж она устроена.

Когда стоит использовать SQLite

Встроенные приложения: все портируемые не предназначенные для масштабирования приложения - например, локальные однопользовательские приложения, мобильные приложения или игры.

Система доступа к дисковой памяти: в большинстве случаев приложения, часто производящие прямые операции чтения/записи на диск, можно перевести на SQLite для повышения производительности.

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

Когда не стоит использовать SQLite

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

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

MySQL

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

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

TINYINT: очень маленькое целое.

SMALLINT: маленькое целое.

MEDIUMINT: целое среднего размера.

INT или INTEGER: целое нормального размера.

BIGINT: большое целое.

FLOAT: знаковое число с плавающей запятой одинарной точности.

DOUBLE, DOUBLE PRECISION, REAL: знаковое число с плавающей запятой двойной точности.

DECIMAL, NUMERIC: знаковое число с плавающей запятой.

DATE: дата.

DATETIME: комбинация даты и времени.

TIMESTAMP: отметка времени.

TIME: время.

YEAR: год в формате YY или YYYY.

CHAR: строка фиксированного размера, дополняемая справа пробелами до максимальной длины.

VARCHAR: строка переменной длины.

TINYBLOB, TINYTEXT: BLOB- или TEXT-столбец длиной максимум 255 (2^8 — 1) символов.

BLOB, TEXT: BLOB- или TEXT-столбец длиной максимум 65535 (2^16 — 1) символов.

MEDIUMBLOB, MEDIUMTEXT: BLOB- или TEXT-столбец длиной максимум 16777215 (2^24 — 1) символов.

LONGBLOB, LONGTEXT: BLOB- или TEXT-столбец длиной максимум 4294967295 (2^32 — 1) символов.

ENUM: перечисление.

SET: множества.

Преимущества

Простота: MySQL легко устанавливается. Существует много сторонних инструментов, включая визуальные, облегчающих начало работы с БД.

Много функций: MySQL поддерживает большую часть функционала SQL.

Безопасность: в MySQL встроено много функций безопасности.

Мощность и масштабируемость: MySQL может работать с действительно большими объёмами данных, и неплохо походит для масштабируемых приложений.

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

Недостатки

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

Вопросы надёжности: некоторые операции реализованы менее надёжно, чем в других РСУБД.

Застой в разработке: хотя MySQL и является open-source продуктом, работа над ней сильно заторможена. Тем не менее, существует несколько БД, полностью основанных на MySQL (например, MariaDB). Кстати, подробнее о родстве MariaDB и MySQL можно из нашего с создателем обеих РСУБД - Джеймсом Боттомли.

Когда стоит использовать MySQL

Распределённые операции: когда вам нужен функционал бо́льший, чем может предоставить SQLite, стоит использовать MySQL.

Высокая безопасность: функции безопасности MySQL предоставляют надёжную защиту доступа и использования данных.

Веб-сайты и приложения: большая часть веб-ресурсов вполне может работать с MySQL, несмотря на ограничения. Этот инструмент весьма гибок и прост в обращении, что только на руку в длительной перспективе.

Кастомные решения: если вы работаете над очень специфичным продуктом, MySQL подстроится под ваши потребности благодаря широкому спектру настроек и режимов работы.

Когда не стоит использовать MySQL

SQL-совместимость: поскольку MySQL не пытается полностью реализовать стандарты SQL, она не является полностью совместимой с SQL. Из-за этого могут возникнуть проблемы при интеграции с другими РСУБД.

Конкурентность: хотя MySQL неплохо справляется с операциями чтения, одновременные операции чтения-записи могут вызвать проблемы.

Недостаток функций: в зависимости от выбора движка MySQL может недоставать некоторых функций.

PostgreSQL

PostgreSQL - это самая продвинутая РСУБД, ориентирующаяся в первую очередь на полное соответствие стандартам и расширяемость. PostgreSQL, или Postgres, пытается полностью соответствовать SQL-стандартам ANSI/ISO.

PostgreSQL отличается от других РСУБД тем, что обладает объектно-ориентированным функционалом, в том числе полной поддержкой концепта ACID (Atomicity, Consistency, Isolation, Durability).

Будучи основанным на мощной технологии Postgres отлично справляется с одновременной обработкой нескольких заданий. Поддержка конкурентности реализована с использованием MVCC (Multiversion Concurrency Control), что также обеспечивает совместимость с ACID.

Хотя эта РСУБД не так популярна, как MySQL, существует много сторонних инструментов и библиотек для облегчения работы с PostgreSQL.

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

bigint: знаковое 8-байтное целое.

bigserial: автоматически инкрементируемое 8-битное целое.

bit [(n)]: битовая строка фиксированной длины.

bit varying [(n)]: битовая строка переменной длины.

boolean: булевская величина.

box: прямоугольник на плоскости.

bytea: бинарные данные.

character varying [(n)]: строка символов фиксированной длины.

character [(n)]:

cidr: сетевой адрес IPv4 или IPv6.

circle: круг на плоскости.

date: календарная дата.

double precision: число с плавающей запятой двойной точности.

inet: адрес хоста IPv4 или IPv6.

integer: знаковое 4-байтное целое.

interval [(p)]: временной промежуток.

line: бесконечная прямая на плоскости.

lseg: отрезок на плоскости.

macaddr: MAC-адрес.

money: денежная величина.

path: геометрический путь на плоскости.

point: геометрическая точка на плоскости.

polygon: многоугольник на плоскости.

real: число с плавающей запятой одинарной точности.

smallint: знаковое 2-байтное целое.

serial: автоматически инкрементируемое 4-битное целое.

text: строка символов переменной длины.

time [(p)] : время суток (без часового пояса).

time [(p)] with time zone: время суток (с часовым поясом).

timestamp [(p)] : дата ивремя (без часового пояса).

timestamp [(p)] with time zone: дата и время (с часовым поясом).

tsquery: запрос текстового поиска.

tsvector: документ текстового поиска.

txid_snapshot: снэпшот ID пользовательской транзакции.

uuid: уникальный идентификатор.

xml: XML-данные.

Преимущества

Полная SQL-совместимость .

Сообщество: PostgreSQL поддерживается опытным сообществом 24/7.

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

Расширяемость: PostgreSQL можно программно расширить за счёт хранимых процедур.

Объектно-ориентированность: PostgreSQL - не только реляционная, но и объектно-ориентированная СУБД.

Недостатки

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

Популярность: из-за своей сложности инструмент не очень популярен.

Хостинг: из-за вышеперечисленных факторов проблематично найти подходящего провайдера.

Когда стоит использовать PostgreSQL

Целостность данных: если приоритет стоит на надёжность и целостность данных, PostgreSQL - лучший выбор.

Сложные процедуры: если ваша БД должна выполнять сложные процедуры, стоит выбрать PostgreSQL в силу её расширяемости.

Интеграция: если в будущем вам предстоит перемещать всю базу на другое решение, меньше всего проблем возникнет с PostgreSQL.

Когда не стоит использовать PostgreSQL

Скорость: если всё, что нужно - это быстрые операции чтения, не стоит использовать PostgreSQL.

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

Перевод

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

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

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

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

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

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

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

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

Поддержка JSON

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

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

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

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

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

Хотите больше Постгреса?

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

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

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

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

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

Поддержка JSON

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

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

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

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

Исторический очерк

Основные возможности

Функции

Триггеры

Пользовательские объекты

Наследование

Прочие возможности

Надёжность

Коммерческие расширения

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

8.3

8.4

9.0

9.1

9.2

Дальнейшие планы

Примечания

Ссылки

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

Типы данных, создаваемые пользователем

Локализация

Языки

Функции и операторы

Схемы

Реляционные системы управления базами данных

Отношения и типы данных

Популярные РСУБД

SQLite

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

Преимущества

Недостатки

Когда стоит использовать SQLite

Когда не стоит использовать SQLite

MySQL

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

Преимущества

Недостатки

Когда стоит использовать MySQL

Когда не стоит использовать MySQL

PostgreSQL

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

Преимущества

Недостатки

Когда стоит использовать PostgreSQL

Когда не стоит использовать PostgreSQL

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

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

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

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

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

Поддержка JSON

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

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

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

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