PostgreSQL — объектно-реляционная база данных

1. Что такое PostgreSQL

PostgreSQL (часто называют просто Postgres) — объектно-реляционная система управления базами данных с открытым исходным кодом. Она разрабатывается с 1996 года и считается самой функционально богатой open-source СУБД.

• Объектно-реляционная — поддерживает не только таблицы, но и пользовательские типы, наследование и сложные индексы.
• Стандартный порт — сервер по умолчанию слушает порт 5432.
• Расширяемость — можно добавлять свои типы, функции и операторы.
• Надёжность — строгое соблюдение ACID и полное соответствие стандарту SQL.

Где применяется

2. Установка PostgreSQL

Скачай установщик с официального сайта postgresql.org. Для Windows выбери дистрибутив EDB Installer.

• psql — терминальный клиент для работы с базами данных.
• pgAdmin — графический интерфейс для управления сервером.
• Stack Builder — утилита для установки дополнительных расширений и драйверов.

Проверь установку, запустив psql и показав версию сервера:

psql --version
# psql (PostgreSQL) 16.2

3. Подключение и первые команды

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

psql -U postgres

Откроется интерактивная оболочка. Базовые служебные команды начинаются с обратной косой черты:

\l              -- список всех баз данных
\c mydb         -- подключиться к базе mydb
\dt             -- список таблиц в базе
\q              -- выйти из psql

SQL-команды пишутся обычным текстом и обязательно заканчиваются точкой с запятой:

CREATE DATABASE shop;

4. Таблицы и типы данных

Таблица создаётся командой CREATE TABLE. Для автоматического первичного ключа используют GENERATED ALWAYS AS IDENTITY (современная замена SERIAL):

CREATE TABLE products (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    price       NUMERIC(10, 2),
    created_at  TIMESTAMPTZ DEFAULT now()
);

Основные типы данных PostgreSQL

5. Вставка и выборка данных

Вставка с возвратом сгенерированного ключа через RETURNING:

INSERT INTO products (name, price)
VALUES ('Ноутбук', 79990.00)
RETURNING id;

-- id
-- 1

Выборка данных с условиями и сортировкой:

SELECT name, price
FROM products
WHERE price > 10000
ORDER BY price DESC
LIMIT 10 OFFSET 0;

• WHERE — фильтрация строк по условию.
• ORDER BY — сортировка (ASC по возрастанию, DESC по убыванию).
• LIMIT — ограничить количество строк.
• OFFSET — пропустить первые строки (для пагинации).

6. Агрегация и JOIN

Агрегатные функции (COUNT, SUM, AVG) считают значения по группам:

SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

Соединение таблиц

Создадим вторую таблицу заказов и свяжем её с продуктами через JOIN:

CREATE TABLE orders (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id  INTEGER REFERENCES products(id),
    quantity    INTEGER NOT NULL
);

INSERT INTO orders (product_id, quantity) VALUES (1, 2), (1, 1), (2, 3);

INNER JOIN возвращает только строки, у которых есть совпадение в обеих таблицах:

SELECT p.name, o.quantity
FROM orders o
INNER JOIN products p ON p.id = o.product_id;

LEFT JOIN дополнительно сохраняет все строки левой таблицы, даже если совпадения нет (NULL в колонках правой):

SELECT p.name, o.quantity
FROM products p
LEFT JOIN orders o ON o.product_id = p.id;

7. Работа с JSONB

JSONB позволяет хранить полуструктурированные данные прямо в колонке и эффективно их фильтровать:

CREATE TABLE events (
    id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB NOT NULL
);

INSERT INTO events (payload) VALUES
    ('{"type": "click", "page": "/", "user_id": 1}'),
    ('{"type": "purchase", "amount": 1500, "user_id": 2}');

Оператор -> возвращает значение как JSON, а ->> — как текст:

SELECT payload->>'type' AS type,
       payload->'amount' AS amount
FROM events;

--  type     | amount
--  click    |
--  purchase | 1500

Фильтрация по вложенным полям JSONB:

SELECT payload->>'user_id' AS user_id
FROM events
WHERE payload->>'type' = 'purchase'
  AND (payload->>'amount')::NUMERIC > 1000;

8. Индексы и производительность

Индекс ускоряет поиск по часто используемым колонкам. Создаётся командой CREATE INDEX:

CREATE INDEX idx_products_name ON products (name);
CREATE INDEX idx_events_type ON events ((payload->>'type'));

Чтобы понять, как выполняется запрос, используй EXPLAIN ANALYZE — он выполняет запрос и показывает план выполнения с реальным временем:

EXPLAIN ANALYZE
SELECT name FROM products WHERE name = 'Ноутбук';

Частичный индекс

Частичный (partial) индекс индексирует только подмножество строк и занимает меньше места:

CREATE INDEX idx_orders_active ON orders (product_id)
WHERE quantity > 0;

9. Сравнение MySQL и PostgreSQL

10. PostgreSQL из Python

Для работы с PostgreSQL в Python используют библиотеку psycopg2. Установи её через pip:

pip install psycopg2-binary

Подключение и чтение данных:

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    dbname="shop",
    user="postgres",
    password="your_password",
)
cur = conn.cursor()
cur.execute("SELECT name, price FROM products ORDER BY price DESC")
for name, price in cur.fetchall():
    print(name, price)
cur.close()
conn.close()

Вставка данных с параметрами через плейсхолдеры %s (защита от SQL-инъекций):

import psycopg2

conn = psycopg2.connect(
    dbname="shop", user="postgres",
    password="your_password", host="localhost",
)
cur = conn.cursor()
cur.execute(
    "INSERT INTO products (name, price) VALUES (%s, %s) RETURNING id",
    ("Мышь", 1990.00),
)
new_id = cur.fetchone()[0]
conn.commit()
print("Добавлен продукт с id:", new_id)
cur.close()
conn.close()

11. Транзакции и ACID

Транзакция объединяет несколько операций в одну атомарную единицу. Либо выполняются все, либо ни одна:

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;
-- при ошибке: ROLLBACK;

• Atomicity — все изменения коммитятся вместе;
• Consistency — данные всегда в допустимом состоянии;
• Isolation — параллельные транзакции не мешают друг другу;
• Durability — после COMMIT данные переживают сбой.

Уровни изоляции задаются командой SET TRANSACTION ISOLATION LEVEL: по умолчанию Read Committed, для строгих подсчётов — Repeatable Read.

12. Представления, функции и триггеры

Представление (view) — сохранённый запрос, который читается как таблица:

CREATE VIEW active_products AS
SELECT name, price FROM products WHERE price > 0;

Хранимая функция на PL/pgSQL инкапсулирует логику:

CREATE FUNCTION add_product(p_name TEXT, p_price NUMERIC)
RETURNS INTEGER AS $$
DECLARE new_id INTEGER;
BEGIN
  INSERT INTO products (name, price) VALUES (p_name, p_price)
  RETURNING id INTO new_id;
  RETURN new_id;
END;
$$ LANGUAGE plpgsql;

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

13. Резервное копирование

Классический бэкап — дамп через pg_dump:

pg_dump -U postgres shop > shop.sql
-- восстановление
psql -U postgres -d shop_new < shop.sql

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

14. Профилирование и автонастройка

Для медленных запросов смотри план выполнения:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE created_at > now() - interval '1 day';

• autovacuum отвечает за очистку мёртвых строк — не отключай его;
• pg_stat_statements показывает частоту и время запросов;
• настройки в postgresql.conf: shared_buffers, work_mem, max_connections;
• партиционирование больших таблиц по времени ускоряет выборки и чистку.

15. Оконные функции

Оконные функции считают значения по «окну» строк без сжатия результата — в отличие от GROUP BY каждая строка остаётся в выводе. Это то, чего не хватает для рангов, «сравнение с прошлым», накопленных итогов:

SELECT name, price,
       rank()   OVER (ORDER BY price DESC) AS price_rank,
       lag(price, 1) OVER (ORDER BY id)    AS prev_price,
       sum(price)  OVER (PARTITION BY category) AS category_total
FROM products;

• ROW_NUMBER() — нумерация строк, удобна для дедупликации «первая запись по ключу»;
• LAG / LEAD — доступ к предыдущей/следующей строке;
• рамка ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW задаёт, что попадает в окно;
• фильтрация по значению окна — через подзапрос: в WHERE оконные функции не работают.

16. Расширения: pg_trgm, PostGIS, uuid и FTS

PostgreSQL расширяется модулями. Частая тройка «на каждый день»:

CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- нечёткий поиск
CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- функции шифрования
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- генерация UUID

17. Схемы, search_path и миграции

Схема — пространство имён для таблиц. По умолчанию всё живёт в public, но для модулей и сред удобно разделять:

CREATE SCHEMA app;
CREATE TABLE app.products (id int primary key, name text);

SHOW search_path;                 -- "$user", public
SET search_path TO app, public;   -- теперь app.products ищется без префикса

Миграции — скрипты изменения схемы, исполняемые по очереди. Держи их в папке проекта (например, db/migrations/001_init.sql) и применяй в CI перед тестами:

psql -U postgres -d shop -f db/migrations/001_init.sql

• одна миграция — одно логическое изменение, обратимое там, где возможно;
• не редактируй применённые миграции — добавляй новые;
• search_path фиксируй в конфиге подключения, чтобы не зависеть от сессии.

18. NULL, COALESCE и подводные камни агрегатов

NULL — не «ноль»: сравнения с NULL дают NULL, а агрегаты его игнорируют. Классика ошибок:

SELECT * FROM orders WHERE price <> 100;   -- строки с NULL не попадут
SELECT price, coalesce(price, 0) FROM orders;  -- подмена значения
SELECT count(price) FROM orders;   -- считает НЕ-NULL значения
SELECT count(*) FROM orders;       -- все строки

19. Конкурентность: MVCC и блокировки

PostgreSQL не замораживает таблицы при чтении: MVCC даёт каждой транзакции собственный снимок данных. Читатели не ждут писателей, писатели не ждут читателей — но блокировки строк при одновременных UPDATE всё же возникают, и с ними нужно работать осознанно.

SELECT pid, state, wait_event_type, query
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL;

• UPDATE одной строки двумя транзакциями: вторая ждёт COMMIT первой — не держи транзакции открытыми долго;
• deadlock случается при пересекающемся порядке обновления строк — Postgres сам откатывает одну транзакцию;
• SELECT ... FOR UPDATE нужен лишь там, где «проверил и поменял» должно быть атомарно;
• длинные SELECT не блокируют запись, но раздувают индексы и замедляют autovacuum.

20. Безопасность: роли, права и сеть

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

-- отдельная роль для приложения (не суперпользователь!)
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE shop TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_user;

• в pg_hba.conf разрешай только нужные подсети и включай scram-sha-256 вместо trust;
• SSL: ssl = on в postgresql.conf — пароли не ходят по сети открытым текстом;
• секреты — в переменных окружения или .env, а не в коде и не в коммитах;
• не давай приложению права на DROP и изменение схемы — миграции запускаются отдельной ролью в CI.

21. Быстрая пагинация: OFFSET против ключа

LIMIT/OFFSET на больших таблицах тормозит: Postgres каждый раз сканирует и выбрасывает первые N строк. Keyset-пагинация по стабильному ключу не имеет этой проблемы и не «плывёт» при вставках:

-- OFFSET: сканирует и отбрасывает 10 000 строк
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- keyset: мгновенный переход по индексу
SELECT * FROM orders
WHERE id > 10000
ORDER BY id LIMIT 20;

• ключ должен быть уникальным и монотонным: id или пара (created_at, id);
• для составного ключа заведи составной индекс (created_at, id);
• новые строки не «сдвигают» страницы — порядок стабилен;
• OFFSET оставь для маленьких таблиц и админ-интерфейсов с прыжком на страницу N.

22. Кейс: схема интернет-магазина

Соберём типовую схему из курса и разберём спорные места проектирования:

CREATE TABLE users (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE products (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

• цены — NUMERIC, а не float: ошибки округления при суммах недопустимы;
• для статуса заказа ENUM не заводи: добавить значение позже — тяжёлая миграция. Лучше TEXT + CHECK;
• гибкие атрибуты товара — отдельная таблица, а не «колонка на всё»;
• created_at задавать через DEFAULT now(), updated_at — триггером или в одном месте приложения.

Что дальше

Ты освоил основы PostgreSQL: установку, работу в psql, создание таблиц, запросы, JOIN, JSONB и подключение из Python. Рекомендуемый следующий шаг — изучить SQL для детального разбора языка запросов и FastAPI, чтобы построить веб-приложение с базой данных.

← Вернуться к курсам