По модели данных¶

По степени распределённости¶

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

По способу доступа к БД¶

Стратегии работы с внешней памятью¶

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

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

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

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

Таблица¶

Таблица - это базовая единица данных. В реляционной алгебре она называется «отношение» (relation). Состоит из столбцов (columns), которые определяют конкретные типы данных. Данные в таблице организованы в строки (rows), которые содержат множества значений столбцов.

Язык описания данных (DDL)¶

В SQL для создания таблицы используется оператор CREATE TABLE. Этот оператор является примером языка описания данных (DDL). DDL служит для описания структуры базы данных. Пример использования:

CREATE TABLE employee (
  emp_name VARCHAR(30),
  dep_id INTEGER
)

Первичные ключи¶

При создании таблицы могут быть использованы различные «ограничения» (constraints), которые содержат правила, указывающие, какие данные представлены в ней. Одним из самых используемых ограничений является первичный ключ (primary key constraint), который гарантирует, что каждая строка таблицы должна содержать уникальное значение. Первичный ключ может состоять из одного или нескольких столбцов. Первичные ключ, состоящие из нескольких столбцов, также называются «составными» (composite).

Правильным считается наличие первичного ключа во всех таблицах базы данных. При этом существует два варианта первичных ключей: искусственный (surrogate primary key) и естественный (natural primary key).

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

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

Пример создания первичного ключа:

CREATE TABLE employee (
  emp_id INTEGER,
  emp_name VARCHAR(30),
  dep_id INTEGER,
  PRIMARY KEY (emp_id)
)

Внешние ключи¶

В то время как одна таблица имеет первичный ключ, другая таблица может иметь ограничение, описывающее, что её строки ссылаются на гарантированно существующие строки в первой таблице. Это реализуется через создание в «удалённой» таблице («потомке») столбца (может быть и несколько), значениями которого являются значения первичного ключа из «локальной» таблицы («родителя»). Вместе наборы этих столбцов составляют внешний ключ (foreign key constraint), который является механизмом базы данных, гарантирующим что значения в «удалённых» столбцах присутствуют как первичные ключи в «локальных». Это ограничение контролирует все операции на этих таблицах: добавление / изменение данных в «удалённой» таблице; удаление / изменение данных в «родительской» таблице. Внешний ключ проверяет, чтобы данные корректно присутствовали в обоих таблицах. Иначе операции будут отменены.

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

В примере представлена таблица «department», которая связана с таблицей «employee» через отношение столбцов «employee.dep_id» и «department.dep_id»:

Представленная на рисунке связь может быть описана через DDL следующим образом:

CREATE TABLE department (
  dep_id INTEGER,
  dep_name VARCHAR(30),
  PRIMARY KEY (dep_id)
)

CREATE TABLE employee (
  emp_id INTEGER,
  emp_name VARCHAR(30),
  dep_id INTEGER,
  PRIMARY KEY (emp_id),
  FOREIGN KEY (dep_id)
    REFERENCES department(dep_id)
)

Нормализация¶

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

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

Классический пример денормализованных данных:

Строки в этой таблице могут быть уникально идентифицированы по столбцам «name» и «language», которые являются потенциальным ключом. По теории нормализации таблица из примера нарушает вторую нормальную форму. Потому как неосновной атрибут «department» логически связан только со столбцом «name». Правильная нормализация в данном случае выглядит следующим образом:

Теперь наглядно видно, как вторая форма улучшила структуру данных. Изначально пример содержал повторы связок полей «name» и «department» так часто, как часто встречался уникальный для данного имени «язык». Улучшенный же вариант сделал связки «name/department» и «name/language» независимыми друг от друга.

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

• «Employee Department -> name» - первичный ключ;
• «Employee Language -> name, language» - составной первичный ключ;
• «Employee Language -> name», в свою очередь, - внешний ключ, на поле «Employee Department -> name».

Если таблицу удаётся сходу свернуть в отношения ключей, то это, зачастую, значит, что она не нормализована.

Вставка (insert)¶

Новые строки добавляются с помощью команды INSERT. Эта команда содержит часть VALUES, в которой прописаны данные для каждой добавляемой строки:

INSERT INTO employee (emp_id, emp_name, dep_id)
    VALUES (1, 'dilbert', 1);

INSERT INTO employee (emp_id, emp_name, dep_id)
    VALUES (2, 'wally', 1);

INSERT INTO employee (emp_name, dep_id)
    VALUES ('dilbert', 1);

INSERT INTO employee (emp_name, dep_id)
    VALUES ('wally', 1);

INSERT INTO employee (emp_name, dep_id)
    VALUES ('dilbert', 1) RETURNING emp_id;

Обновление (Update)¶

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

UPDATE employee SET dep_id=7 WHERE emp_name='dilbert'

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

Удаление (Delete)¶

Команда DELETE служит для удаления строк. Также как и UPDATE использует параметр WHERE для выборки нужных строк:

DELETE FROM employee WHERE dep_id=1

Сортировка¶

К команде SELECT можно добавить параметр ORDER BY задающий по какому полю сортировать результаты:

SELECT emp_id, emp_name FROM employee WHERE dep_id=12 ORDER BY emp_name

Объединения (Joins)¶

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

Параметр JOIN помещается внутри блока FROM, между именами объединяемых таблиц. Он, в свою очередь, в себе содержит параметр ON, который отвечает за критерий объединения строк из разных таблиц.

JOIN создаёт промежуточную структуру табличного вида. Она содержит в себе объединенные данные из обоих таблиц.

Используя примеры с таблицами Department и Employee, выберем сотрудников вместе с названиями их отделов:

SELECT e.emp_id, e.emp_name, d.dep_name
  FROM employee AS e
    JOIN department AS d
    ON e.dep_id=d.dep_id
  WHERE d.dep_name = 'Software Artistry'

SELECT d.dep_name, e.emp_name
  FROM department AS d
  LEFT OUTER JOIN employee AS e
  ON d.dep_id=e.dep_id

Агрегация¶

Функция агрегации принимает на вход множество значений, выдавая на выходе одно. Наиболее часто используемая функция агрегации - это``count()``. Она получает набор строк и возвращает их количество.

В качестве параметра может использоваться любое SQL выражение. Наиболее часто используется шаблон *, означающий «все столбцы». В отличии от большинства функций агрегации count() не вычисляет значение своего аргумента, а просто считает сколько раз он был вызван:

SELECT count(*) FROM employee

Другая функция агрегации может вернуть нам среднее количество сотрудников в офисах. Для этого нам также потребуется использовать конструкцию GROUP BY в подзапросе:

SELECT avg(emp_count) FROM
  (SELECT count(*) AS emp_count
    FROM employee GROUP BY dep_id) AS emp_counts

Группировка¶

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

На изображении выше строки разделены на 3 подгруппы по некоему полю «a». Затем применена функция SUM() к полю «b» в каждой из этих групп.

В качестве примера совместного использования агрегации и конструкции GROUP BY рассчитаем количество сотрудников в каждом отделе:

SELECT count(*) FROM employee GROUP BY dep_id

Having¶

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

SELECT count(*) as emp_count FROM employee GROUP BY dep_id HAVING emp_count > 7

Выборка. Резюме¶

Рассмотрим, как команда SELECT ведёт себя при комбинации всех вышеописанных конструкций.

Для примера возьмём следующий набор строк:

К которому будет применён вот этот код:

SELECT count(emp_id) as emp_count, dep_id
  FROM employee
  WHERE dep_id=1 OR dep_id=3 OR dep_id=4
  GROUP BY dep_id
  HAVING emp_count > 1
  ORDER BY emp_count, dep_id

1. Конструкция FROM определяет таблицы, из которых будут выбраны строки. В нашем примере это таблица employee:

   ... FROM employee ...

   emp_id	emp_name	dep_id
   1	wally	1
   2	dilbert	1
   3	jack	2
   4	ed	3
   5	wendy	1
   6	dogbert	4
   7	boss	3

2. Каждая строка проверяется на соответствие условию, описанному в блоке WHERE. Только строки, прошедшие эту проверку, подвергаются дальнейшей обработке:

   ... WHERE dep_id=1 OR dep_id=3 OR dep_id=4 ...

   emp_id	emp_name	dep_id
   1	wally	1
   2	dilbert	1
   4	ed	3
   5	wendy	1
   6	dogbert	4
   7	boss	3

3. Затем производится фоновая группировка по какому-либо критерия с помощью конструкции GROUP BY. Дальнейшая обработка производится уже над этими группами. Для текущего примера это выглядит следующим образом:

   ... GROUP BY dep_id ...

   «group»	emp_id	emp_name	dep_id
   dep_id=1	1	wally	1
   	2	dilbert	1
   	5	wendy	1
   dep_id=3	4	ed	3
   	7	boss	3
   dep_id=4	6	dogbert	4

4. Затем функции агрегации применяются к каждой группе строк. В примере функция count() применяется к полю emp_id. Это значит, что для группы «1» она получит «1», «2» и «5», в качестве значений. Функции count() нет дела до того, какие значения в неё были переданы. И можно для упрощения передать *, что означает «все столбцы». Тем не менее, большинству функций агрегации важно, что именно передаётся в них в качестве параметров. Потому хорошей практикой считается определение тех столбцов, которые передаются в качестве параметров. Результат нашего примера выглядит примерно так:

   ... count(emp_id) AS emp_count ...

   emp_count	dep_id
   3	1
   2	3
   1	4

5. Конструкция HAVING работает как WHERE, но только для агрегированных значений. В текущем примере она осуществляет фильтрацию групп, которые имеют более одного сотрудника:

   ... HAVING emp_count > 1 ...

   emp_count	dep_id
   3	1
   2	3

6. В конце применяется конструкция ORDER BY. Важно помнить, что реляционная математика, заложенная в основу SQL, базируется на понятии множеств. Которые являются по определению неупорядоченными. В обычном случае выборка, агрегация и фильтрация применяются к неупорядоченным строкам. И только в конце, перед отдачей результата пользователю, производится упорядочивание по какому-либо признаку:

   ... ORDER BY emp_count, dep_id

   emp_count	dep_id
   2	3
   3	1

Атомарность (Atomicity)¶

Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной. Поскольку на практике невозможно одновременно и атомарно выполнить всю последовательность операций внутри транзакции, вводится понятие «отката» (rollback): если транзакцию не удаётся полностью завершить, результаты всех её до сих пор произведённых действий будут отменены и система вернётся во «внешне исходное» состояние - со стороны будет казаться, что транзакции и не было. Сигнал завершения транзакции называется «фиксация» (commit).

Согласованность (Consistency)¶

Транзакция достигающая своего нормального завершения (EOT - end of transaction, завершение транзакции) и, тем самым, фиксирующая свои результаты, сохраняет согласованность базы данных. Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты. Это условие является необходимым для поддержки четвёртого свойства.

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

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

Изолированность (Isolation)¶

Во время выполнения транзакции параллельные транзакции не должны оказывать влияние на её результат. Изолированность - требование дорогое, поэтому в реальных БД существуют различные уровни изоляции.

• Read uncommitted. Низший (нулевой) уровень изоляции. Он гарантирует только отсутствие потерянных обновлений Если несколько параллельных транзакций пытаются изменять одну и ту же строку таблицы, то в окончательном варианте строка будет иметь значение, определенное последней успешно выполненной транзакцией. При этом возможно считывание не только логически несогласованных данных, но и данных, изменения которых ещё не зафиксированы.
• Read committed. На этом уровне обеспечивается защита от чернового, «грязного» чтения, тем не менее, в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных.
• Repeatable Read. Уровень, при котором читающая транзакция «не видит» данные, которые были изменены но еще не зафиксированы другой транзакцией. При этом никакая другая транзакция не может изменять данные читаемые текущей транзакцией, пока та не окончена.
• Serializable. Самый высокий уровень изолированности; транзакции полностью изолируются друг от друга, каждая выполняется так, как будто параллельных транзакций не существует. Только на этом уровне параллельные транзакции не подвержены эффекту «фантомного чтения» (ситуация, когда при повторном чтении в рамках одной транзакции одна и та же выборка дает разные множества строк).

Надежность (Durability)¶

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

Создание структуры каталога¶

В этом примере мы рассмотрим как создать простую струкртуру каталога товаров.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE category (name TEXT NOT NULL);

INSERT INTO category (name) VALUES
    ('Тапки'),
    ('Самолёты'),
    ('Ноутбуки');


CREATE TABLE product (
    name TEXT NOT NULL,
    price NUMERIC NOT NULL,
    category REFERENCES category(rowid)
);

INSERT INTO product (name, price, category) VALUES
    ('Босоножки', 1.17, 1),
    ('Вьетнамки', 2.36, 1),
    ('Макасины', 4.99, 1),
    ('ИЛ-2', 556000, 2),
    ('Суперджет 100', 1500000, 2),
    ('Ту-160', 25000000, 2),
    ('Dell', 590, 3),
    ('Lenovo', 200, 3),
    ('Sony', 437, 3);

Расширение структуры каталога¶

В этом примере мы рассмотрим, как расширить существующую структуру под новые требования.

Требования будут следующими:

• Для категории «Тапки» необходимо добавить поле «Размер».
• Для категории «Самолёты» необходимо добавить поле «Вместимость».
• Для категории «Ноутбуки» необходимо добавить поле «Прцессор».

1
2
3
4
5

ALTER TABLE product ADD size INTEGER;

ALTER TABLE product ADD capacity INTEGER;

ALTER TABLE product ADD processor TEXT;

Сущетсвенным недостатком данного подхода является тот факт, что поля одних категорий товаров являются обязательными для других категорий. Т.е., например, поле «размер», которое добавлено для категории «Тапки», становится обязательным и для товаров из категории «Самолёты» и «Ноутбуки».

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

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

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

Настройка окружения¶

Установим необходимые зависимости для проекта.

pip install zodb

Реализация задачи¶

Для начала создадим классы для реализации задачи без сохранения.

Настройка окружения¶

Установим необходимые пакеты:

wget http://download.redis.io/redis-stable.tar.gz
tar xzf redis-stable.tar.gz
cd redis-stable
make

Запустим сервер:

src/redis-server  --loglevel=DEBUG

Подключимся с помощью клиента к запущенному серверу:

src/redis-cli

Строки¶

Установка / получение значения:

> set mykey somevalue
OK
> get mykey
"somevalue"

Через команду SET можно выполнять условную установку значения для ключа (например, присвоить значение только в том случае, если ключ не / существует), а также задать время «жизни» ключа.

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

> set counter 100
OK
> incr counter
(integer) 101
> incrby counter 50
(integer) 151
> decr counter
(integer) 150
> decrby counter 50
(integer) 100

Операции де / инкремента атомарны. Т.е. при чтении двумя клиентов некоторого ключа со значением 10 и последующего его инкремента, финальное значение этого ключа будет 12.

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

> mset a 10 b 20 c 30
OK
> mget a b c
1) "10"
2) "20"
3) "30"

Списки¶

Для построения списка необходимо добавить в него элементы. Добавить можно как с головы списка (слева), так и с конца (справа):

> lpush mylist A first
(integer) 2
> rpush mylist B
(integer) 3
> lrange mylist 0 -1
1) "first"
2) "A"
3) "B"

Для получения крайних элементов слева / справа служат соответствующие команды:

> lpop mylist
"first"
> rpop mylist
"B"

Существуют блокирующие реализации этих команды BLPOP и BRPOP. Они вернут результат выполнения только после того как будет добавлен элемент или по истечении времени ожидания.

Хеш-таблицы¶

Могут служить для хранения каких-либо объектов:

> hmset user:1000 username antirez birthyear 1977 verified 1
OK
> hget user:1000 username
"antirez"
> hget user:1000 birthyear
"1977"
> hgetall user:1000
1) "username"
2) "antirez"
3) "birthyear"
4) "1977"
5) "verified"
6) "1"

Аналогично команде HMSET, которая позволяет записать сразу несколько значений в хеш-таблицу, существует команда HMGET, позволяющая получить массив значений из желаемых полей:

> hmget user:1000 username birthyear no-such-field
1) "antirez"
2) "1977"
3) (nil)

Присутствует возможность инкремента целочисленных значений в хеш-таблице:

> hincrby user:1000 birthyear 10
(integer) 1987

Множества¶

Это классические математические множества:

> sadd myset 1 2 3 2
(integer) 3
> smembers myset
1. 3
2. 1
3. 2

Есть возможность проверки множества на наличие в нём элемента:

> sismember myset 3
(integer) 1
> sismember myset 30
(integer) 0

Для данных этого типа доступны операции над множествами: пересечение, объединение, разность и т.п. Для получения элемента служит команда SPOP.

Упорядоченные множества¶

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

> zadd hackers 1940 "Alan Kay"
(integer) 1
> zadd hackers 1957 "Sophie Wilson"
(integer 1)
> zadd hackers 1953 "Richard Stallman"
(integer) 1
> zadd hackers 1949 "Anita Borg"
(integer) 1
> zadd hackers 1965 "Yukihiro Matsumoto"
(integer) 1
> zadd hackers 1914 "Hedy Lamarr"
(integer) 1
> zadd hackers 1916 "Claude Shannon"
(integer) 1
> zadd hackers 1969 "Linus Torvalds"
(integer) 1
> zadd hackers 1912 "Alan Turing"
(integer) 1
> zrange hackers 0 -1 withscores
1) "Alan Turing"
2) "1912"
3) "Hedy Lamarr"
4) "1914"
5) "Claude Shannon"
6) "1916"
7) "Alan Kay"
8) "1940"
9) "Anita Borg"
10) "1949"
11) "Richard Stallman"
12) "1953"
13) "Sophie Wilson"
14) "1957"
15) "Yukihiro Matsumoto"
16) "1965"
17) "Linus Torvalds"
18) "1969"

Присутствуют возможности выборки определённых наборов элементов из упорядоченного множества по полю score (команда ZRANGEBYSCORE) и определение позиции элемента в множестве (команда ZRANK):

> zrangebyscore hackers -inf 1950
1) "Alan Turing"
2) "Hedy Lamarr"
3) "Claude Shannon"
4) "Alan Kay"
5) "Anita Borg"
> zrank hackers "Anita Borg"
(integer) 4

В Redis имеется возможность лексической (по алфавиту) сортировки упорядоченных множеств строковых значений:

> zadd hackers 0 "Alan Kay" 0 "Sophie Wilson" 0 "Richard Stallman" 0 "Anita Borg" 0 "Yukihiro Matsumoto" 0 "Hedy Lamarr" 0 "Claude Shannon" 0 "Linus Torvalds" 0 "Alan Turing"
(integer) 9
> zrange hackers 0 -1
1) "Alan Kay"
2) "Alan Turing"
3) "Anita Borg"
4) "Claude Shannon"
5) "Hedy Lamarr"
6) "Linus Torvalds"
7) "Richard Stallman"
8) "Sophie Wilson"
9) "Yukihiro Matsumoto"

Для таких множеств также присутствует возможность выборки наборов элементов:

> zrangebylex hackers [B [P
1) "Claude Shannon"
2) "Hedy Lamarr"
3) "Linus Torvalds"

Битовые массивы и HyperLogLog¶

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

Функции¶

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

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

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

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

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

Триггеры¶

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

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

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

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

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

Индексы¶

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

Индексы в PostgreSQL обладают следующими свойствами:

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

Многоверсионность (MVCC)¶

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

Типы данных¶

PostgreSQL поддерживает большой набор встроенных типов данных:

• Численные типы
  • Целые
  • С фиксированной точкой
  • С плавающей точкой
  • Денежный тип (отличается специальным форматом вывода, а в остальном аналогичен числам с фиксированной точкой с двумя знаками после запятой)
• Символьные типы произвольной длины
• Двоичные типы (включая BLOB)
• Типы «дата/время» (полностью поддерживающие различные форматы, точность, форматы вывода, включая последние изменения в часовых поясах)
• Булев тип
• Перечисление
• Геометрические примитивы
• Сетевые типы
  • IP и IPv6-адреса
  • CIDR-формат
  • MAC-адрес
• UUID-идентификатор
• XML-данные
• Массивы
• JSON
• Идентификаторы объектов БД
• Псевдотипы
• Типы для текстового поиска
• Диапазонные типы

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

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

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

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

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

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

Настройка окружения¶