Объяснение мема Postgres - Уровень 0: Зона неба

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

Типы данных

PostgreSQL поддерживает огромное количество различных типов данных, начиная от числовых, денежных, json и xml и заканчивая такими типами, как геометрические, сетевые адреса и составные типы. Представляю Вашему вниманию длиннющий список типов данных, поддерживаемых PostgreSQL.

Этот запрос отражает типы, которые интересны разработчику приложений. Его результат - 87 различных типов данных на PostgreSQL версии 14.1:

select typname, typlen, nspname
from pg_type t
join pg_namespace n
on t.typnamespace = n.oid
where nspname = 'pg_catalog'
and typname !~ '(^_|^pg_|^reg|_handlers$)'
order by nspname, typname;

Например, если Вы хотите хранить журналы аудита действий пользователей/администраторов и Вам необходимо хранить их IP-адреса, Вы можете использовать в PostgreSQL тип inet, а не хранить их в виде текста. Это позволит хранить данные более эффективно, кроме того, их будет легче проверить (по сравнению с системой, не поддерживающей такой тип, например, Sqlite).

CREATE TABLE

Язык SQL (Structured Query Language или язык структурированных запросов) состоит из нескольких областей, и каждая из них имеет свой подъязык.

Один из таких подъязыков называется DDL, что означает "язык определения данных". Он состоит из операторов типа CREATE, ALTER и DROP, которые используются для определения структур данных на диске.

Приведем пример запроса на создание таблицы:

create table "audit_log" (
            id serial primary key,
            ip inet,
            action text,
            actor text,
            description text,
            created_at timestamp default NOW()
)

В результате будет создана таблица audit_log с такими столбцами, как id, ip, action и т.д.

SELECT, INSERT, UPDATE, DELETE

DML - это еще один из подъязыков SQL, который расшифровывается как язык манипулирования данными. Он включает в себя операторов insert, update и delete, которые используются для ввода данных в систему баз данных.

Select также помогает извлекать данные из базы данных. Это, пожалуй, один из самых простых запросов в SQL:

select 0;

Вот некоторые примеры таких DML-запросов:

insert into "audit_log" (ip, action, actor, description) values (
            '127.0.0.1',
            'delete user',
            'admin',
            'admin deleted the user x'
)



update "audit_log"
set ip = '192.168.1.1'
where id = 1;

delete from "audit_log"
where id = 1;

select action, actor, description
from "audit_log"
where ip = '127.0.0.1';

Команда table_name может также использоваться для выбора всей таблицы. Вот эта команда sql:

table users;

она полностью эквивалентна

select * from users;

ORDER BY

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

select *
from "audit_log"
order by created_at desc;

select "name", "location", "country" from "circuits" order by point(lng,lat) <-> point(2.349014, 48.864716) limit 10;

LIMIT и OFFSET

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

select *
from "audit_log"
offset 100
limit 10;

Осторожно: Этот метод пагинации может быть слишком медленным!

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

GROUP BY

Предложение group by вводит в PostgreSQL агрегаты (они же Map/Reduce), которые позволяют распределить строки по различным группам и затем свести полученный набор результатов к одному значению.

Предположим, что у нас есть таблица Student со столбцами id, class_no и grade, и мы можем найти среднюю оценку по каждому классу с помощью следующего запроса:

select class_no, avg(grade) as class_avg
from student
group by class_no;

определение таблицы

create table student(
    id serial primary key,
    class_no integer,
    grade integer
)

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

NULL

В PostgreSQL NULL означает неопределенное значение или просто незнание значения, а не его отсутствие. Именно поэтому проверки true = NULL, false = NULL и NULL = NULL приводят к результату NULL.

select
            true = NULL as a,
            false = NULL as b,
            NULL = NULL as c;

-- result
-- a: NULL
-- b: NULL
-- c: NULL

Теперь, когда вы знаете значение NULL, вам следует быть более осторожным с его семантикой. Следующий запрос теперь возвращает строки:

select x
from generate_series(1, 100) as t(x) -- generate_series(1, 100) creates rows 1,2,3,...,99,100
where x not in (1, 2, 3, null)

-- total rows: 0

Индексы

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

Ниже приведен простой запрос на создание индекса по столбцу last_name таблицы student с использованием метода GiST.

create index on student using gist(last_name);

Почему первичный ключ и ограничение UNIQUE должны быть подкреплены индексами?

Ввиду MVCC (Multiversion Concurrency Control).

PostgreSQL предоставляет разработчикам богатый набор инструментов для управления параллельным доступом к данным. Внутри PostgreSQL согласованность данных поддерживается за счет использования Multiversion Concurrency Control или MVCC. Это означает, что каждый оператор SQL видит снимок данных (версию базы данных), каким он был некоторое время назад, независимо от текущего состояния базовых данных. Это предотвращает просмотр операторами противоречивых данных, создаваемых параллельными транзакциями, выполняющими обновление одних и тех же строк данных, обеспечивая изоляцию транзакций для каждого сеанса работы с базой данных. MVCCминимизирует количество блокировок, что позволяет добиться высокого уровня производительности в условиях работы сразу нескольких пользователей.

Основное преимущество использования MVCC вместо блокировок заключается в том, что в MVCC блокировки, полученные для чтения данных, не конфликтуют с блокировками, полученными для записи данных, поэтому чтение никогда не блокирует запись, а запись не блокирует чтение. PostgreSQL сохраняет эту гарантию даже при обеспечении самого строгого уровня изоляции транзакций за счет использования инновационного уровня Serializable Snapshot Isolation (SSI).

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

Что такое Gist?

GiST - это индексный метод доступа. Методы доступа - это алгоритмы, используемые для поиска и доступа к любым данным. PostgreSQL предоставляет следующие методы доступа:

B-tree: индекс сгруппированный по листьям сбалансированного дерева. Сбалансированные индексы используются наиболее часто, они максимально эффективны и оптимизированы для одновременного чтения и записи. Однако они не могут корректно индексировать типы данных без общего порядка (например, геометрические точки и диапазоны).

Hash: работают только с простыми условиями равенства.

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

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

GIN: представляют собой «инвертированные индексы», в которых могут содержаться значения с несколькими ключами, например массивы. Инвертированный индекс содержит отдельный элемент для значения каждого компонента, и может эффективно работать в запросах, проверяющих присутствие определённых значений компонентов. GIN является основой для поддержки полнотекстового поиска в PostgreSQL.

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

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

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

Список всех поддерживаемых методов доступа можно получить, обратившись к таблице pg_am:

table pg_am;

JOIN

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

Мы также можем рассматривать join как способ создания новых отношений из пары существующих. Отношение в PostgreSQL - это набор данных, обладающих общими свойствами.

Приведенный ниже запрос позволяет получить пользователя admin с указанием его роли:

select u.username, u.email, r.role_name
from "user" as u
join "role" as r
on u.role_id = r.role_id -- equivalent: using(role_id)
where u.username = 'admin';

Существует множество видов join, включая следующие:

Внутренние соединения (inner join): Сохраняются только те строки, которые удовлетворяют условию объединения для обеих сторон задействованных отношений (левой и правой);

Левое/правое/полное внешнее соединение (left/right/full outer join): Получение всех записей из таблицы, даже тех, которые не имеют совпадающих значений ни в левой, ни в правой, ни в обеих сторонах отношения;

Перекрестное соединение (cross join): Декартово произведение левого и правого отношений, дающее все возможные комбинации из строк левой таблицы, объединенных со строками правой таблицы.

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

Внутренние соединения (inner join): Сохраняются только те строки, которые удовлетворяют условию объединения для обеих сторон задействованных отношений (левой и правой);
Левое/правое/полное внешнее соединение (left/right/full outer join): Получение всех записей из таблицы, даже тех, которые не имеют совпадающих значений ни в левой, ни в правой, ни в обеих сторонах отношения;
Перекрестное соединение (cross join): Декартово произведение левого и правого отношений, дающее все возможные комбинации из строк левой таблицы, объединенных со строками правой таблицы.

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

Что такое natural join?

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

select name, title
from artist natural join album
where artist.artist_id = 1;

Внешние ключи (Foreign Keys)

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

create table author (
            name text primary key
);

create table book (
            name text primary key,
            author text references author(name)
)

insert into author values ('George Orwell');

insert into book values ('Animal Farm', 'George Orwell'); -- OK

insert into book values ('Anna Karenina', 'Leo Tolstoy'); -- NOT OK
-- ERROR:  insert or update on table "book" violates foreign key constraint "book_author_fkey"
-- DETAIL:  Key (author)=(Leo Tolstoy) is not present in table "author".

ORM

Объектно-реляционное отображение (ORM, O/RM, а также O/R Mapping tool) - технология программирования, которая связывает базы данных с концепциями объектно-ориентированных языков программирования, создавая «виртуальную объектную базу данных». ORM помогают программисту взаимодействовать и изменять данные в базе данных, используя языковые конструкции, определенные в объектно-ориентированном языке программирования. Другими словами, ORM выступает в роли моста между объектно-ориентированным миром и математическим реляционным миром.

Java

// Java, Hibernate ORM
@Entity
@Table(name = "Person")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private int age;
}

Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
SessionFactory sessionFactory = configuration.buildSessionFactory();

try (Session session = sessionFactory.openSession()) {
	List persons = session.createQuery("FROM Person", Person.class).list();
} catch (Exception e) {
	e.printStackTrace();
}

// C#, EntityFramework ORM
public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }

    public virtual List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public virtual Blog Blog { get; set; }
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

using (var db = new BloggingContext())
{
var blog = new Blog { Name = "My Blog" };
db.Blogs.Add(blog);
db.SaveChanges();
}

Python

# Python, SQLAlchemy ORM
from sqlalchemy import create_engine
engine = create_engine('postgresql://usr:pass@localhost:5432/sqlalchemy')

class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer, primary_key=True)
    comments = relationship("Comment")

class Comment(Base):
    __tablename__ = 'comments'
    id = Column(Integer, primary_key=True)
    article_id = Column(Integer, ForeignKey('articles.id'))

stmt = select(Article).where(Article.id.in_([1, 2]))

Далее: Уровень 1: Зона поверхности: ACID, outer joins, нормальные формы, ...