Skip to content

PostgreSQL Шпаргалка

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

01

С чего начать

Подключение через psql

psql — клиент командной строки PostgreSQL. Мета-команды начинаются с обратного слэша (\). \l перечисляет базы, \d описывает таблицы, \? показывает справку. Строки подключения (postgresql://) удобны и поддерживаются большинством драйверов. \i filename.sql выполняет SQL-файл. Задайте переменную окружения PGPASSWORD или файл ~/.pgpass, чтобы не вводить пароли.

postgresql
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database

# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"

# common psql meta-commands
\l              # list databases
\c mydb         # connect to mydb
\dt             # list tables
\d users        # describe table
\dn             # list schemas
\df             # list functions
\du             # list roles/users
\q              # quit
\?              # help on meta-commands
\timing         # toggle query timing

Создание и удаление баз

CREATE DATABASE создаёт новую базу; можно указать владельца, кодировку, локаль и шаблон. template0 — чистый шаблон без данных, зависящих от локали; template1 (по умолчанию) можно настраивать. DROP DATABASE требует отсутствия активных соединений — WITH (FORCE) (13+) сначала отключает их. Нельзя удалить базу, к которой вы подключены. ALTER DATABASE RENAME меняет имя, но не каталог на диске.

postgresql
# create a database
CREATE DATABASE mydb
  WITH OWNER alice
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0
  CONNECTION LIMIT 100;

# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;

# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;

# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);

# rename a database
ALTER DATABASE mydb RENAME TO newdb;

Схемы и search_path

Схемы — пространства имён для объектов базы, как папки для таблиц. Схема по умолчанию — 'public'. search_path определяет, какие схемы просматриваются при обращении к неквалифицированным именам. Используйте схемы для организации мультитенантных приложений, разделения ответственности или управления версиями. CASCADE удаляет зависимые объекты; RESTRICT (по умолчанию) отказывает, если объекты существуют. Схемы гибче баз для мультитенантности в рамках одного соединения.

postgresql
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;

# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;

# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);

# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;

# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';

# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;

Конфигурация и настройки

Настройки PostgreSQL живут в postgresql.conf. SHOW читает текущее значение; SET меняет его для сессии; SET LOCAL — только для текущей транзакции; ALTER DATABASE/ROLE задаёт сохраняемые значения по умолчанию. Некоторые настройки (shared_buffers, max_connections) требуют перезапуска сервера; другие (work_mem, statement_timeout) применяются сразу. pg_reload_conf() применяет изменения конфигурационного файла без перезапуска. Следите за log_min_duration_statement для медленных запросов.

postgresql
# view a setting
SHOW shared_buffers;
SHOW max_connections;

# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';

# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT;  -- reverts to previous value

# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;

# reload config without restart
SELECT pg_reload_conf();

# view config file location
SHOW config_file;

Скрипты и переменные psql

psql поддерживает скрипты с переменными (\set), включением файлов (\i), перенаправлением вывода (\o) и выполнением команд оболочки (\!). Переменные подставляются через :varname. Используйте \set для циклов и условий в скриптах. Флаг -f выполняет файл и выходит — удобно для пакетных задач и миграций. Для сложных сценариев рассмотрите функции PL/pgSQL или внешние инструменты вроде sqitch или Flyway для управления миграциями.

postgresql
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql

# inside psql
\i /path/to/script.sql

# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;

# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;

# output to file
\o results.txt
SELECT * FROM users;
\o

# execute shell command
\! date
02

Таблицы и ограничения

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

serial автоматически создаёт последовательность для автоинкрементных целых; для новых таблиц используйте GENERATED ALWAYS AS IDENTITY (стандарт SQL, предпочтителен с PG10). Ограничения: PRIMARY KEY (уникальность + not null), UNIQUE, NOT NULL, CHECK, REFERENCES (внешний ключ). ON DELETE CASCADE удаляет зависимые строки при удалении родителя; SET NULL обнуляет FK. TEMP-таблицы исчезают после сессии — отлично для промежуточных результатов.

postgresql
CREATE TABLE users (
  id          serial PRIMARY KEY,
  username    varchar(50) UNIQUE NOT NULL,
  email       varchar(255) UNIQUE NOT NULL,
  password    text NOT NULL,
  age         integer CHECK (age >= 0 AND age <= 150),
  role        varchar(20) NOT NULL DEFAULT 'user',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

# create with a foreign key
CREATE TABLE posts (
  id          serial PRIMARY KEY,
  user_id     integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  body        text,
  published   boolean DEFAULT false
);

# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
  SELECT user_id, count(*) FROM posts GROUP BY user_id;

Identity-колонки (PG10+)

GENERATED ALWAYS AS IDENTITY — стандартная замена serial в SQL: последовательность привязана к колонке, поэтому удаление колонки удаляет и последовательность (serial оставляет осиротевшие последовательности). ALWAYS запрещает ручную вставку ID (для принудительной используйте OVERRIDING SYSTEM VALUE). BY DEFAULT ведёт себя как serial (разрешает ручную вставку). Для новых таблиц берите identity-колонки; serial допустим для существующих. Оба варианта под капотом используют последовательности.

postgresql
# preferred over serial (SQL standard)
CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# allow manual override (like serial behavior)
CREATE TABLE products (
  id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;

# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';

Изменение таблиц

ALTER TABLE — способ развивать схему без пересоздания таблиц. Добавление колонки со значением по умолчанию (не волатильным) быстрое начиная с PG11 (без полной перезаписи таблицы). Смена типа колонки может потребовать полной перезаписи и предложения USING для преобразования. CASCADE удаляет зависимые объекты (представления, внешние ключи) — используйте осторожно. Задавайте ограничениям имена для удобного управления в будущем. Применяйте IF EXISTS/IF NOT EXISTS, чтобы скрипты были идемпотентными.

postgresql
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;

# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE;  -- drop dependent objects

# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;

# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;

# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
  CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');

Ограничения

Ограничения обеспечивают целостность данных на уровне базы — всегда предпочитайте их проверкам в приложении. Составные ключи работают для связующих таблиц. ON UPDATE CASCADE распространяет изменения PK на FK; ON DELETE SET NULL обнуляет FK (колонка FK должна допускать NULL). DEFERRABLE-ограничения проверяются при коммите (а не на каждом операторе), полезны для циклических ссылок. Давайте ограничениям имена для удобного управления — автогенерируемые имена уродливы и различаются между базами.

postgresql
# named constraint for easy management
ALTER TABLE users
  ADD CONSTRAINT positive_age CHECK (age >= 0);

# composite primary key
CREATE TABLE enrollments (
  student_id integer REFERENCES students(id),
  course_id  integer REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
  UNIQUE (email, domain);

# foreign key with actions
ALTER TABLE posts
  ADD FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE SET NULL;

# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;

# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;

Наследование таблиц

Наследование таблиц в PostgreSQL уникально: дочерние таблицы наследуют колонки родителей. Запрос к родителю возвращает строки всех детей, если не указать ONLY. Это полезно для секционирования (до декларативного секционирования) и моделирования таксономий. Однако у наследования есть особенности: внешние ключи не распространяются, а UNIQUE-ограничения действуют только в пределах таблицы. Для новых задач секционирования используйте декларативное секционирование (PARTITION BY) — оно надёжнее и лучше поддерживается.

postgresql
# PostgreSQL supports table inheritance
CREATE TABLE people (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE employees (
  salary numeric,
  dept   text
) INHERITS (people);

CREATE TABLE customers (
  credit_limit numeric
) INHERITS (people);

# query parent sees all children
SELECT * FROM people;          -- includes employees and customers
SELECT * FROM ONLY people;     -- only people, not children

# insert into a child
INSERT INTO employees (name, salary, dept)
  VALUES ('Alice', 50000, 'Engineering');
03

Типы данных

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

Выбирайте целочисленные типы по размеру данных: smallint для малых диапазонов, bigint для больших. numeric/decimal точны (необходимы для денег), но медленнее real/double. Никогда не используйте float для денег: ошибки округления накапливаются. serial устарел; предпочитайте GENERATED AS IDENTITY. Тип money зависит от локали и негибок — используйте numeric(10,2). В автоинкременте последовательности могут пропускать числа при откате или сбое (так задумано ради производительности).

postgresql
# integer types (use the smallest that fits)
smallint    -- 2 bytes, -32768 to 32767
integer     -- 4 bytes, -2B to 2B
bigint      -- 8 bytes, very large

# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY

# decimal/numeric (exact)
numeric(precision, scale)   -- e.g. numeric(10,2) for currency
decimal                     -- alias for numeric

# floating point (inexact, faster)
real        -- 4 bytes, 6 decimal digits
double precision  -- 8 bytes, 15 decimal digits

# special
serial / bigserial   -- legacy auto-increment (prefer identity)
money                -- currency (prefer numeric)

# examples
CREATE TABLE products (
  price numeric(10,2),        -- $99999999.99
  weight real,                -- 1.5 (kg)
  quantity integer DEFAULT 0
);

Текстовые типы

В PostgreSQL varchar, char и text имеют одинаковую производительность: text предпочтителен для неограниченного текста, varchar(n) — когда нужна проверка длины. char(n) дополняет пробелами и нужен редко. Изменение длины varchar — операция только с метаданными (быстро). LIKE использует шаблоны % и _, ILIKE не учитывает регистр, ~ — POSIX regex. Для полнотекстового поиска используйте tsvector/tsquery (ниже). Всегда храните текст как text/varchar, а не как bytea.

postgresql
# three text types (all variable-length)
varchar(n)   -- varchar with max length
char(n)      -- fixed-length, padded with spaces
text         -- unlimited length (preferred)

# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)

CREATE TABLE articles (
  title    varchar(200) NOT NULL,
  slug     varchar(100) UNIQUE,
  body     text,
  summary  text DEFAULT ''
);

# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;

# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);

# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$';   -- regex

Типы даты и времени

Всегда используйте timestamptz (а не timestamp) для меток времени: он хранит UTC и при выводе конвертирует в часовой пояс сессии. timestamp (без tz) наивен и вызывает баги в приложениях с несколькими часовыми поясами. interval мощно работает в арифметике ('1 day', '2 hours', '3 months'). date_trunc округляет вниз до единицы. to_char/to_timestamp дают гибкие форматы. Задавайте timezone на сессию или роль — данные всё равно хранятся в UTC.

postgresql
# date/time types
date              -- date only (4 bytes)
time              -- time only (8 bytes)
timetz            -- time with time zone (12 bytes)
timestamp         -- timestamp without tz (8 bytes)
timestamptz       -- timestamp WITH time zone (8 bytes, preferred)
interval          -- time span (16 bytes)

# always use timestamptz for timestamps
CREATE TABLE events (
  id         serial PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  scheduled  timestamptz,
  duration   interval
);

# common operations
SELECT now();                               -- current timestamp
SELECT current_date;                         -- today
SELECT created_at + interval '1 hour';       -- add time
SELECT age('2025-01-01', '2024-01-01');       -- interval between
SELECT date_trunc('month', created_at);       -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);

# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';

JSON и JSONB

jsonb — двоичный JSON: быстрее в запросах и индексируется, но чуть медленнее при вставке и не сохраняет порядок ключей. Используйте jsonb для всей новой работы. -> возвращает jsonb, ->> возвращает text. @> (вложенность) — самый мощный JSON-оператор и отлично работает с GIN-индексами. jsonb_set меняет путь, || объединяет объекты, - удаляет ключи. Для частых запросов к JSON-полю добавьте GIN-индекс или индекс по выражению извлечённого значения. JSONB идеален для гибких схем внутри реляционной базы.

postgresql
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
  id    serial PRIMARY KEY,
  data  jsonb
);

# insert JSON
INSERT INTO products (data) VALUES
  ('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');

# query JSON fields
SELECT data->'name'        FROM products;  -- returns jsonb
SELECT data->>'name'       FROM products;  -- returns text
SELECT data->'tags'->0     FROM products;  -- first tag (jsonb)

# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';  -- containment

# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}';        -- merge
UPDATE products SET data = data - 'sale';                    -- remove key

# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));

Массивы и свои типы

Массивы PostgreSQL индексируются с 1 (в отличие от большинства языков). ANY и @> — основные операторы запросов; unnest() разворачивает массивы в строки (отлично для соединений). Массивы мощны, но используйте их обдуманно: если вы часто запрашиваете и фильтруете элементы массива, нормализованная таблица может быть лучше. Enum упорядочены и проверяются при вставке. Составные типы позволяют структурированные колонки. Для сложных вложенных данных рассмотрите JSONB вместо массивов составных типов.

postgresql
# array columns
CREATE TABLE teams (
  id      serial PRIMARY KEY,
  name    text,
  members text[]           -- array of text
);

INSERT INTO teams (name, members) VALUES
  ('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
  ('Sales', '{"Dave", "Eve"}');

# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice'];  -- contains
SELECT members[1] FROM teams;            -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1;  -- expand to rows

# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');

# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);
04

CRUD-операции

INSERT

RETURNING — возможность PostgreSQL, возвращающая вставленные, обновлённые или удалённые строки, что избавляет от отдельного SELECT после вставки. ON CONFLICT (UPSERT) мощен: DO UPDATE использует EXCLUDED для ссылки на предлагаемую строку, DO NOTHING молча пропускает. Многострочная вставка гораздо быстрее одиночных. INSERT ... SELECT копирует данные между таблицами. Для массовых загрузок используйте COPY (ещё быстрее): он обходит накладные расходы на разбор SQL.

postgresql
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

# multi-row insert
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;

# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;

# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;

UPDATE

Всегда включайте WHERE, если не собираетесь обновлять все строки. UPDATE ... FROM позволяет соединения в обновлениях (нестандартно, но мощно). RETURNING показывает, что изменилось, — необходимо для аудита. Выражения CASE позволяют условные обновления одним оператором. Обновления, не меняющие значения, ничего не делают (нет WAL, нет срабатывания построчных триггеров). Большие обновления раздувают таблицу — делайте их пачками и затем VACUUM.

postgresql
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;

# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;

# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';

# conditional update with CASE
UPDATE products
SET price = CASE
  WHEN price < 10 THEN price * 1.2
  WHEN price < 50 THEN price * 1.1
  ELSE price
END;

# update all rows (be careful!)
UPDATE users SET status = 'active';

DELETE и TRUNCATE

DELETE удаляет строки по одной (вызывает триггеры, виден в транзакциях). TRUNCATE удаляет все строки мгновенно (DDL, не транзакционен в том же смысле, сбрасывает последовательности при RESTART IDENTITY). Для очистки целых таблиц используйте TRUNCATE — он на порядки быстрее. CASCADE очищает таблицы с внешними ключами, ссылающимися на эту. DELETE с RETURNING полезен для журналов аудита. Всегда используйте WHERE с DELETE: без него вы удалите всё (безопасно внутри транзакции, катастрофично после коммита).

postgresql
# basic delete
DELETE FROM users WHERE id = 1;

# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;

# delete based on a join
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE role = 'deleted'
);

# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';

# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments;          -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY;   -- reset sequences
TRUNCATE TABLE posts CASCADE;            -- also truncates FK refs

# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;

Основы SELECT

DISTINCT ON — расширение PostgreSQL, возвращающее первую строку по группе; мощно для запросов «последний в категории» (сначала нужно ORDER BY по колонке DISTINCT ON). LIMIT/OFFSET — простая пагинация, но медленная на больших смещениях; используйте keyset-пагинацию (WHERE id > last_id) ради производительности. Всегда перечисляйте колонки явно в продакшене (не SELECT *), чтобы избежать сюрпризов при изменении схемы. Для синтаксиса стандарта SQL используйте FETCH FIRST n ROWS ONLY.

postgresql
# basic select
SELECT id, username, email FROM users;

# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';

# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10;  -- pagination (or use FETCH)

# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC;  -- latest post per user

# column aliases and expressions
SELECT
  username AS name,
  EXTRACT(YEAR FROM created_at) AS join_year,
  age * 365 AS age_in_days
FROM users;

MERGE (PG15+)

MERGE (стандарт SQL, PG15+) мощнее ON CONFLICT: он умеет вставлять, обновлять И удалять одним оператором по условиям совпадения. Используйте его для сложных операций синхронизации (ETL, слияния хранилищ данных). Для простых upsert ON CONFLICT короче и шире поддерживается. MERGE требует источник (таблицу или запрос) и условие совпадения. Секции WHEN MATCHED/NOT MATCHED задают действия. В каждой ветви могут быть дополнительные условия AND для тонкого контроля.

postgresql
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
  UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);

# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;
05

Запросы и фильтрация

WHERE и условия

ILIKE — LIKE без учёта регистра (расширение PostgreSQL). ~ — POSIX regex (с учётом регистра), ~* без учёта регистра, !~ отрицание. IS DISTINCT FROM трактует NULL как сравнимое значение (NULL = NULL даёт NULL, а не true). ANY/ALL работают с массивами и подзапросами. Для сложного сопоставления текста рассмотрите полнотекстовый поиск (tsvector) или триграммные индексы (pg_trgm) ради производительности. Всегда индексируйте колонки из WHERE в больших таблицах.

postgresql
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0;        -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';

# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
   OR (price < 10 AND category = 'books');

# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%';     -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';

# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;

# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);

# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]';       -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget';       -- case-insensitive
SELECT * FROM products WHERE name !~ 'test';         -- does not match

ORDER BY и пагинация

Keyset-пагинация (WHERE id > last_id) значительно быстрее OFFSET на больших данных: OFFSET сканирует и отбрасывает строки. ORDER BY random() медленен на больших таблицах; для статистической выборки используйте TABLESAMPLE. NULLS FIRST/LAST управляет местом NULL при сортировке (по умолчанию зависит от ASC/DESC). Всегда указывайте ORDER BY при пагинации: без него порядок строк не определён. Для стабильной пагинации сортируйте по уникальной колонке.

postgresql
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;

# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- page 2

# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;

# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

# random rows
SELECT * FROM products ORDER BY random() LIMIT 5;  -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1);    -- 1% sample

GROUP BY и агрегаты

GROUP BY схлопывает строки по колонкам группировки; агрегаты (count, sum, avg, min, max) считаются по группе. HAVING фильтрует группы (WHERE фильтрует строки до группировки). ROLLUP добавляет подытоги на каждом уровне; CUBE — все комбинации; GROUPING SETS позволяет точно указать нужные группировки — всё это мощно для отчётности. count(*) считает строки; count(column) считает не-NULL значения. Для уникальных подсчётов используйте count(DISTINCT col).

postgresql
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;

# multiple aggregates
SELECT
  category,
  count(*)           AS total,
  avg(price)         AS avg_price,
  min(price)         AS min_price,
  max(price)         AS max_price,
  sum(price)         AS total_value
FROM products
GROUP BY category;

# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;

# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;

# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status);  -- adds subtotals and grand total

# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

DISTINCT и операции над множествами

UNION объединяет результаты и удаляет дубликаты (медленно); UNION ALL сохраняет дубликаты (быстро, предпочтительно, когда дубликатов нет или они не важны). INTERSECT возвращает общие строки; EXCEPT — разность. Операции над множествами требуют совместимых типов и числа колонок. DISTINCT ON — жемчужина PostgreSQL: он возвращает первую строку по группе согласно ORDER BY — идеально для запросов «последний/самый дорогой в категории». Все операции можно сцеплять, но управляйте приоритетом скобками.

postgresql
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;

# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC;  -- most expensive per category

# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;

# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;

# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;

CASE и условная логика

Выражения CASE добавляют логику if/then/else в SQL. Простой CASE сравнивает значение; поисковый CASE вычисляет условия. Секция FILTER (PostgreSQL) чище, чем CASE внутри агрегатов, для условного подсчёта. CASE может появляться в SELECT, WHERE, ORDER BY и даже GROUP BY. Используйте его для сводных таблиц, условного форматирования и бизнес-логики, которая должна жить в базе. Для сложной многоветвевой логики рассмотрите функцию или представление.

postgresql
# simple CASE
SELECT
  name,
  CASE category
    WHEN 'book' THEN 'Media'
    WHEN 'laptop' THEN 'Electronics'
    ELSE 'Other'
  END AS category_group
FROM products;

# searched CASE (more flexible)
SELECT
  name,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    WHEN price < 100 THEN 'Expensive'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

# CASE in aggregate (pivot-like)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active_count,
  count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

# CASE for conditional sorting
SELECT * FROM products
ORDER BY
  CASE WHEN featured THEN 0 ELSE 1 END,
  name;
06

Соединения (JOIN)

INNER и OUTER JOIN

INNER JOIN возвращает только совпавшие строки. LEFT JOIN сохраняет все левые строки (правые колонки NULL при отсутствии совпадения) — самое частое соединение для «включить связанные данные, если они есть». RIGHT JOIN используется редко (перепишите как LEFT). FULL OUTER JOIN сохраняет всё. Условие WHERE p.id IS NULL после LEFT JOIN — паттерн анти-join для поиска строк без совпадений. Всегда квалифицируйте имена колонок псевдонимами таблиц (u.id, p.user_id) во избежание неоднозначности.

postgresql
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- users with no posts

Многотабличные соединения

PostgreSQL умеет соединять много таблиц, но каждое соединение добавляет стоимость — убедитесь, что они используют индексы. Self-join полезен для иерархических данных (но для глубоких иерархий рассмотрите рекурсивные CTE). При соединении с агрегацией LEFT JOIN + count(p.id) (а не count(*)) даёт 0 для пользователей без постов. Оптимизатор выбирает порядок соединений, но ясные запросы ему помогают. Для сложных многотабличных запросов рассмотрите создание представления.

postgresql
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

# join with different conditions
SELECT
  u.username,
  p.title,
  l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;

# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

CROSS JOIN и LATERAL

CROSS JOIN даёт декартово произведение (каждая строка A с каждой строкой B) — редко то, что нужно, но полезно с generate_series для генерации данных. LATERAL мощен: он позволяет подзапросу ссылаться на колонки внешнего запроса, как функция на строку. Идеален для запросов «топ N на группу» (гораздо чище оконных функций в простых случаях). LEFT JOIN LATERAL ... ON true включает строки без совпадений.

postgresql
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;

# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);

# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

Стратегии соединения (EXPLAIN)

PostgreSQL выбирает стратегии соединения по размеру таблиц, индексам и статистике. Nested Loop имеет сложность O(N*M) — годится для малых или индексированных выборок. Hash Join строит хеш-таблицу по меньшему входу, затем проверяет по ней — отлично для больших неотсортированных соединений. Merge Join требует сортировки обоих входов — хорош, когда индексы дают порядок. Не форсируйте стратегии в продакшене: дайте решить оптимизатору. Используйте EXPLAIN (ANALYZE), чтобы увидеть реальные тайминги и при необходимости поправить индексы и статистику.

postgresql
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join strategies you'll see:
#   Nested Loop:   for each left row, scan right (good for small/indexed)
#   Hash Join:     hash one table, probe with other (good for large unsorted)
#   Merge Join:    both sorted, merge (good for sorted/indexed large tables)

# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;

Советы по производительности соединений

Самая действенная оптимизация соединений — индексирование колонок внешних ключей: PostgreSQL НЕ создаёт индексы для FK автоматически. Держите статистику свежей через ANALYZE (autovacuum делает это, но ручной ANALYZE после массовых загрузок помогает). Не оборачивайте колонки соединения в функции (это мешает использованию индекса). Выбирайте только нужные колонки. Для огромных таблиц рассмотрите секционирование, чтобы оптимизатор отсекал секции. Следите за медленными соединениями через pg_stat_statements.

postgresql
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id);  -- PK already indexed

# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;

# avoid joining on expressions (prevents index use)
-- BAD:  JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)

# limit columns selected (reduces I/O)
SELECT u.username, p.title  -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;

# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';  -- prunes partitions
07

Агрегации и оконные функции

Агрегатные функции

В PostgreSQL богатые агрегаты помимо count/sum/avg. string_agg и array_agg объединяют значения в строку или массив — отлично для избежания запросов N+1. bool_or/bool_and агрегируют булевы значения. percentile_cont вычисляет точные перцентили (медиана = 0.5) — очень полезно для аналитики. mode() возвращает самое частое значение. Секция FILTER (count(*) FILTER (WHERE...)) чище CASE для условной агрегации. ORDER BY внутри агрегатов управляет порядком вывода.

postgresql
# standard aggregates
SELECT
  count(*)              AS total_rows,
  count(distinct category) AS unique_categories,
  sum(price)            AS total_value,
  avg(price)            AS avg_price,
  min(price)            AS min_price,
  max(price)            AS max_price,
  stddev(price)         AS std_dev,
  variance(price)       AS variance
FROM products;

# string aggregation
SELECT
  user_id,
  string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;

# array aggregation
SELECT
  user_id,
  array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;

# boolean aggregation
SELECT
  bool_or(published)    AS any_published,
  bool_and(published)   AS all_published
FROM posts;

# statistical aggregates
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
  mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;

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

Оконные функции вычисляются по набору строк, связанных с текущей, и не схлопывают строки, как GROUP BY. PARTITION BY задаёт группу окна; ORDER BY — порядок внутри. ROW_NUMBER всегда уникален; RANK пропускает номера после ничьих; DENSE_RANK не пропускает. LAG/LEAD обращаются к другим строкам — идеально для анализа временных рядов. Секции рамки (ROWS BETWEEN ...) определяют, какие строки попадают в окно. Оконные функции вычисляются после WHERE/GROUP BY/HAVING.

postgresql
# ROW_NUMBER: unique sequential number
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

# RANK and DENSE_RANK (handle ties)
SELECT
  title,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

# LAG and LEAD (compare to previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;

# running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

Рамки окна и NTILE

Рамки окна определяют, какие строки видит оконная функция. Рамка по умолчанию для агрегатов — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (накопительная сумма). Для LAST_VALUE нужно расширить рамку до UNBOUNDED FOLLOWING, иначе она вернёт текущую строку (рамка по умолчанию заканчивается на текущей). NTILE делит строки на N корзин — полезно для квартилей и децилей. FIRST_VALUE/LAST_VALUE возвращают значения с границ рамки. Понимание рамок — ключ к оконным функциям.

postgresql
# frame types
#   ROWS:   exact row count
#   RANGE:  logical range (e.g., same value)
#   GROUPS: groups of peer rows

SELECT
  date,
  revenue,
  # running sum from start to current
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

  # 7-day moving average
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,

  # sum of all rows (no frame = entire partition)
  SUM(revenue) OVER () AS grand_total
FROM daily_sales;

# NTILE: divide rows into N equal buckets
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;

# FIRST_VALUE / LAST_VALUE
SELECT
  name,
  category,
  price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS least_expensive
FROM products;

Секция FILTER

Секция FILTER — расширение PostgreSQL, более чистое и часто более быстрое, чем CASE внутри агрегатов. Особенно полезна для отчётов в стиле сводных таблиц, где нужно несколько условных агрегатов. Запрос сканирует таблицу один раз и вычисляет все агрегаты за один проход — гораздо эффективнее нескольких подзапросов. FILTER входит в стандарт SQL (в отличие от многих расширений PG) и поддерживается другими базами.

postgresql
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status = 'banned') AS banned,
  sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;

# equivalent with CASE (more verbose)
SELECT
  count(*) AS total,
  count(CASE WHEN status = 'active' THEN 1 END) AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;

# FILTER with multiple aggregates in one pass (efficient)
SELECT
  category,
  count(*) FILTER (WHERE price > 100) AS expensive_count,
  avg(price) FILTER (WHERE price > 100) AS expensive_avg,
  count(*) FILTER (WHERE price <= 100) AS cheap_count,
  avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;

Общие табличные выражения (CTE)

CTE (секция WITH) улучшают читаемость запросов и позволяют переиспользовать подзапросы. PostgreSQL 12+ по умолчанию вливает нерекурсивные CTE (производительность как у подзапросов); до 12 CTE были барьером оптимизации. Рекурсивные CTE мощны для иерархических данных: якорный запрос задаёт старт, рекурсивная часть растёт. CTE с RETURNING позволяют сложное перемещение данных (удалить из одной таблицы, заархивировать в другую) одним оператором. Используйте CTE, чтобы разбивать сложные запросы на читаемые шаги.

postgresql
# basic CTE (WITH clause)
WITH active_users AS (
  SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

# multiple CTEs (can reference each other)
WITH user_stats AS (
  SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
  SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;

# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;

# CTE for DML (data modification)
WITH deleted AS (
  DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;
08

Индексы

B-Tree индексы

B-tree — тип индекса по умолчанию и самый распространённый: поддерживает равенство, диапазоны и сортировку. Составные индексы ищутся слева направо, поэтому сначала ставьте колонки равенства, затем диапазона и сортировки. Частичные индексы (с WHERE) экономят место и быстрее — используйте их для частых фильтрованных запросов (например, WHERE active = true). Индексы по выражениям позволяют индексировать результаты функций. CONCURRENTLY строит без блокировки записи (медленнее, но безопасно для продакшена). Всегда индексируйте внешние ключи!

postgresql
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

# composite index (order matters!)
CREATE INDEX idx_posts_user_created
  ON posts(user_id, created_at DESC);

# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
  WHERE active = true;

# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
#   SELECT * FROM users WHERE lower(email) = '[email protected]'

# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock, slower

GIN и GiST индексы

GIN-индексы идеальны, когда у каждой строки несколько индексируемых значений (ключи JSONB, элементы массивов, токены текста). Они поддерживают операторы @> (содержит), ? (ключ существует) и полнотекстовый @@. GIN-индексы крупнее, но эффективно обрабатывают такие запросы. GiST — для геометрических данных (точки, полигоны) и диапазонных типов: поддерживает перекрытие (&&), содержит (@>), содержится (<@). Строить и обновлять GIN и GiST медленнее, чем B-tree, но они дают запросы, недоступные B-tree. Используйте GIN для JSONB и полнотекста, GiST — для пространственных и диапазонных запросов.

postgresql
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search

CREATE INDEX idx_products_data ON products USING GIN (data);        -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);              -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text

# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');

# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location);  -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during);       -- tsrange

# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';

# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');

BRIN и другие типы индексов

BRIN (Block Range INdex) чрезвычайно компактен (килобайты, а не гигабайты): он хранит min/max для диапазона блоков. Он эффективен, когда физический порядок строк соответствует порядку запросов (например, логи временных рядов, куда новые данные дописываются). BRIN даёт «достаточную» фильтрацию, отсекающую большинство блоков; затем PostgreSQL проверяет оставшиеся строки. Для огромных append-only таблиц BRIN меняет правила игры. Hash-индексы поддерживают только равенство (не диапазоны), но быстры для точечных выборок. SP-GiST подходит для несбалансированных данных вроде таблиц маршрутизации IP.

postgresql
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!

# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)

# compare sizes:
#   B-tree on 1B rows: ~20GB
#   BRIN on 1B rows:   ~2MB (10000x smaller)

# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix);  -- e.g., IP routing

# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting

# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';

Обслуживание и анализ индексов

Следите за использованием индексов через pg_stat_user_indexes: неиспользуемые индексы тратят место и замедляют запись. Удаляйте дубликаты (те же колонки, та же таблица). REINDEX пересобирает раздутые индексы (появляются при интенсивных обновлениях и удалениях); CONCURRENTLY (12+) избегает блокировок. Раздувание индексов нормально — autovacuum поддерживает индексы, но иногда нужен ручной REINDEX. Для очень больших индексов рассмотрите REINDEX CONCURRENTLY или pg_repack (сторонний инструмент, пересобирающий без блокировок). Всегда тестируйте изменения индексов на staging.

postgresql
# find unused indexes (candidates for removal)
SELECT
  schemaname, relname, indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx::text))[1] AS indexes
FROM (
  SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
  FROM pg_index
  GROUP BY indrelid, indkey
  HAVING count(*) > 1
) sub;

# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;  -- PG12+, no lock

# check index bloat
SELECT
  relname, pg_size_pretty(pg_relation_size(relid)) AS size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

Стратегия индексирования

Начните с PK (индексируется автоматически) и FK (вручную). Добавляйте индексы под горячие пути запросов. Составные индексы следуют правилу левого префикса: упорядочивайте колонки по селективности и шаблонам запросов. INCLUDE (покрывающий индекс) позволяет удовлетворить запрос только индексом (index-only scan) без обращения к строке — огромный выигрыш для нагрузок с преобладанием чтения. Не индексируйте всё: каждый индекс добавляет накладные расходы на запись. Регулярно пересматривайте и чистите через pg_stat_user_indexes. Всегда проверяйте использование индекса через EXPLAIN.

postgresql
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);

# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);

# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
  WHERE status = 'active';

# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
  INCLUDE (name);  -- name available without heap fetch

# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)

# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
09

Транзакции и конкурентность

Основы транзакций

Транзакции группируют операторы в атомарную единицу: всё успешно или всё падает. BEGIN начинает, COMMIT сохраняет, ROLLBACK отменяет. Точки сохранения позволяют частичный откат внутри транзакции — полезно для восстановления после ошибок в сложных операциях. По умолчанию PostgreSQL автоматически коммитит каждый оператор. Для многооператорных действий (переводы, многострочные вставки) всегда используйте явные транзакции. Держите транзакции короткими, чтобы уменьшить конкуренцию за блокировки и дедлоки.

postgresql
# explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

# savepoints (partial rollback)
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  SAVEPOINT order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- oops, wrong product
  ROLLBACK TO order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;

# transaction control
BEGIN;
  -- statements
SAVEPOINT my_savepoint;
  -- more statements
ROLLBACK TO my_savepoint;  -- undo back to savepoint
RELEASE my_savepoint;      -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything

# auto-commit (default — each statement is its own transaction)
SET autocommit = on;  -- default

Уровни изоляции

Уровень PostgreSQL по умолчанию (READ COMMITTED) хорош для большинства приложений: грязных чтений нет, но повторное чтение в той же транзакции может увидеть новые данные. REPEATABLE READ в PostgreSQL сильнее стандарта SQL: он предотвращает и фантомы (изоляция снимком). SERIALIZABLE самый строгий, но может прерывать конфликтующие транзакции — приложение должно повторять при SQLSTATE 40001. Для большинства веб-приложений READ COMMITTED подходит. SERIALIZABLE берите для критичной корректности (финансовые регистры) с логикой повторов.

postgresql
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;   -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

# or set it for the session
SET default_transaction_isolation = 'serializable';

# READ COMMITTED (default):
#   - each query sees committed data at query start
#   - no dirty reads, but non-repeatable reads and phantoms possible

# REPEATABLE READ:
#   - each query sees snapshot from transaction start
#   - no non-repeatable reads, but serialization anomalies possible
#   - PostgreSQL's RR is stronger than SQL standard (no phantoms!)

# SERIALIZABLE:
#   - strongest isolation, behaves as if transactions ran one at a time
#   - may abort with serialization failure (retry needed)
#   - uses SSI (Serializable Snapshot Isolation)

# check current level
SHOW transaction_isolation;

# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- work
COMMIT;  -- may fail with SQLSTATE 40001 -> retry the transaction

Блокировки и дедлоки

SELECT FOR UPDATE блокирует строки для обновления (предотвращает параллельные изменения) — полезно для паттернов «прочитать, проверить, обновить». Advisory-блокировки — блокировки уровня приложения по целочисленным ключам, отлично для координации распределённых процессов. Дедлоки возникают, когда две транзакции ждут друг друга; PostgreSQL обнаруживает их и прерывает одну (ловите и повторяйте). Чтобы избежать дедлоков, захватывайте блокировки в едином порядке во всех транзакциях. Держите транзакции короткими. Следите за зависшими сессиями через pg_locks.

postgresql
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- allow reads, block writes

# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345);          -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345);      -- non-blocking
SELECT pg_advisory_xact_lock(12345);     -- transaction-level (auto-released)

# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

# terminate a blocked session
SELECT pg_cancel_backend(pid);    -- cancel query
SELECT pg_terminate_backend(pid); -- kill session

# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detected

MVCC и изоляция снимком

MVCC в PostgreSQL означает, что читатели никогда не блокируют писателей и наоборот: каждая транзакция видит снимок. Обновления создают новые версии строк; старые остаются до удаления VACUUM. Долгие транзакции мешают VACUUM очищать старые версии, вызывая раздувание. Держите транзакции короткими! Следите за сессиями 'idle in transaction': они держат снимки и блокируют очистку. Переполнение идентификаторов транзакций — серьёзная проблема (autovacuum предотвращает его, но следите, если autovacuum не справляется).

postgresql
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data

# visible transactions
SELECT txid_current();           -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());

# see long-running transactions (can prevent vacuum)
SELECT
  pid,
  age(clock_timestamp(), xact_start) AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
  pid,
  age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

Паттерны блокировок

FOR UPDATE блокирует строки пессимистично — для критичной согласованности (переводы). Оптимистичная блокировка (колонка версии) лучше при низкой конкуренции: без блокировок, просто повтор при конфликте. SKIP LOCKED идеален для очередей задач: несколько воркеров берут разные задачи, не блокируя друг друга. NOWAIT падает быстро при блокировке (для «попробуй обновить, не жди»). Advisory-блокировки координируют операции уровня приложения (миграции, cron-задачи). Выбирайте пессимистичный подход при высокой конкуренции, оптимистичный — при низкой.

postgresql
# pessimistic locking (lock before work)
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  -- now safe to modify
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 0 rows = conflict, retry

# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)

# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked

# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));
10

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

Создание представлений

Представления — сохранённые запросы, ведущие себя как таблицы. Простые представления (одна таблица, без агрегатов) автоматически обновляемы: вставки и обновления проходят в нижележащую таблицу. WITH CHECK OPTION запрещает вставки и обновления, которые сделали бы строку невидимой через представление. Используйте представления, чтобы упрощать сложные запросы, обеспечивать безопасность (доступ на уровне колонок) и давать стабильный API. CASCADE удаляет зависимые объекты. Представления не хранят данные — запрос выполняется каждый раз.

postgresql
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;

# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# view with computed columns
CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  count(p.id) AS post_count,
  max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]');  -- works!

# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails

# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;

Материализованные представления

Материализованные представления хранят результат запроса — читать гораздо быстрее, но данные устаревают до обновления. Используйте их для дорогих агрегатов (дашборды, отчёты). CONCURRENTLY обновляет без блокировки представления (читатели продолжают работу), но требует уникального индекса. Планируйте обновления через pg_cron или внешние инструменты. Для почти реального времени обновляйте каждые несколько минут; для исторической аналитики — ежедневно. Компромисс — свежесть против скорости запроса; материализованные представления — ключ к быстрой аналитике на больших таблицах.

postgresql
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  date_trunc('day', created_at) AS day,
  product_id,
  count(*) AS order_count,
  sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA;  -- or WITH NO DATA to populate later

# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- needs unique index

# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);

# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';

Представления для безопасности

Представления — мощный инструмент безопасности: показывайте только те колонки и строки, которые пользователь должен видеть, не давая доступ к таблице. Выдавайте SELECT на представление, а не на нижележащую таблицу. Для построчной безопасности рассмотрите встроенные политики RLS (Row Level Security) вместо представлений: они надёжнее и работают при прямом доступе к таблице. Представления с переменными сессии (current_setting) позволяют фильтрацию под пользователя. Используйте представления для стабильного API-слоя, скрывающего сложность схемы от приложений.

postgresql
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden

# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;

-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts;  -- only sees user 42's posts

# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.created_at,
  u.username,
  p.name AS product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;

Управление представлениями

CREATE OR REPLACE VIEW имеет ограничения: можно добавлять колонки в конец, но нельзя удалять или переупорядочивать их. Для значительных изменений удаляйте и пересоздавайте (но сначала проверьте зависимости). Представления могут зависеть от других представлений; удаление базового требует CASCADE. Используйте pg_views для просмотра определений. DO-блок для обновления всех материализованных представлений удобен для скриптов обслуживания, но где возможно используйте CONCURRENTLY, чтобы избежать блокировок.

postgresql
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

# get view definition
\d+ view_name  -- in psql

# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder

# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;

# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';

# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
  END LOOP;
END $$;

Рекурсивные представления

Рекурсивные представления (CREATE RECURSIVE VIEW) оборачивают рекурсивный CTE — отлично для обхода деревьев и графов. Всегда включайте ограничение глубины или расстояния в рекурсивной части, чтобы предотвратить бесконечные циклы (циклы в данных). Колонка path (конкатенация текста) — простой способ отслеживать и отображать путь обхода. Для очень глубоких иерархий рассмотрите таблицу замыканий или расширение ltree ради лучшей производительности. Рекурсивные представления удобны, но могут быть медленными на больших данных — добавьте индексы на колонки соединения.

postgresql
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
  id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
  e.id, e.name, e.manager_id, et.level + 1,
  et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

# query the tree
SELECT * FROM employee_tree ORDER BY path;

# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;

# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
  fc.person1, f.person2, fc.distance + 1,
  fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6;  -- limit depth to prevent infinite loops
11

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

Строковые функции

В PostgreSQL обширный набор строковых функций. position() и strpos() ищут подстроки. substring() с FROM/FOR — стандарт SQL; substr() компактнее. split_part() удобен для данных с разделителями. initcap() делает заглавной первую букву каждого слова. lpad/rpad дополняют строки до фиксированной ширины (полезно для форматирования). string_to_array/array_to_string конвертируют между строками и массивами. Для сопоставления с шаблоном LIKE/ILIKE (шаблоны), ~ (regex) и SIMILAR TO (шаблон SQL) покрывают большинство нужд.

postgresql
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello');          -- 4
SELECT strpos('hello', 'lo');               -- 4

# substring and slicing
SELECT substring('hello' from 2 for 3);     -- 'ell'
SELECT substring('hello' from 2);           -- 'ello'
SELECT substr('hello', 2, 3);               -- 'ell'
SELECT left('hello', 3);                    -- 'hel'
SELECT right('hello', 3);                   -- 'llo'

# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim('  hello  '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x');             -- trim both sides

# split and join
SELECT split_part('a,b,c', ',', 2);         -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ',');       -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', ');  -- 'a, b, c'

# replace and pad
SELECT replace('hello', 'l', 'L');          -- 'heLLo'
SELECT lpad('5', 3, '0');                   -- '005'
SELECT rpad('5', 3, '0');                   -- '500'
SELECT repeat('ab', 3);                     -- 'ababab'
SELECT reverse('hello');                    -- 'olleh'

Функции даты и времени

now() и current_timestamp эквивалентны (время начала транзакции). EXTRACT/date_part получают компоненты. date_trunc округляет вниз до единицы — необходимо для разбиения данных по времени. age() возвращает интервалы (человекочитаемо). to_char/to_date/to_timestamp обрабатывают форматирование и разбор. generate_series с датами невероятно полезен для заполнения разрывов во временных рядах (LEFT JOIN к нему, чтобы показать дни с нулём). Всегда используйте timestamptz; арифметика дат учитывает часовой пояс сессии.

postgresql
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;

# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now());     -- day of week (0=Sunday)
SELECT date_part('epoch', now());   -- Unix timestamp

# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01');                    -- interval since date
SELECT age('2025-01-01', '2024-01-01');      -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)

# truncation and rounding
SELECT date_trunc('month', now());           -- first day of month, 00:00
SELECT date_trunc('hour', now());            -- current hour, 00 minutes

# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000);             -- Unix epoch

# generate series (date ranges)
SELECT generate_series(
  '2025-01-01'::date,
  '2025-01-31'::date,
  '1 day'::interval
);

Числовые и математические функции

round() с двумя аргументами работает с numeric (не с double — сначала приведите). Тригонометрические функции используют радианы (для преобразования radians()/degrees()). random() возвращает 0–1; комбинируйте с математикой для диапазонов. generate_series — рабочая лошадка PostgreSQL: он генерирует строки на лету для последовательностей, диапазонов дат и заполнения разрывов. gcd/lcm (13+) удобны. Для финансовых расчётов всегда используйте numeric (точный): функции с плавающей точкой вроде round на double могут иметь проблемы точности.

postgresql
# rounding
SELECT round(3.14159, 2);    -- 3.14
SELECT ceil(3.1), ceiling(3.1);  -- 4
SELECT floor(3.9);           -- 3
SELECT trunc(3.14159, 2);    -- 3.14 (truncate, no rounding)

# power and roots
SELECT power(2, 10);         -- 1024
SELECT sqrt(16);             -- 4
SELECT cbrt(27);             -- 3
SELECT exp(1);               -- 2.718... (e^x)
SELECT ln(10), log(100);     -- natural log, base-10 log

# trigonometry (radians!)
SELECT sin(radians(90));     -- 1 (convert degrees to radians)
SELECT degrees(1.5708);      -- ~90
SELECT pi();                 -- 3.14159...

# random
SELECT random();             -- 0 to 1
SELECT floor(random() * 100)::int;  -- 0 to 99
SELECT setseed(0.5);         -- seed for reproducibility

# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2);   -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1);  -- 0.0, 0.1, ..., 1.0

# absolute, sign, gcd
SELECT abs(-5), sign(-5);    -- 5, -1
SELECT gcd(12, 8);           -- 4 (PG 13+)
SELECT lcm(12, 8);           -- 24 (PG 13+)

Условные функции и NULL

COALESCE возвращает первое не-NULL значение — необходимо для значений по умолчанию. NULLIF возвращает NULL при совпадении значений — чище CASE для защиты от деления на ноль. GREATEST/LEAST сравнивают значения (NULL распространяется в GREATEST, но не всегда в LEAST). CASE — общее условие. ISNULL/NOTNULL — сокращения PG. Сравнение строк (ROW(a,b) < ROW(c,d)) сравнивает лексикографически — полезно для пагинации по составному ключу. Понимание поведения NULL (NULL = NULL даёт NULL, а не true) критично в PostgreSQL.

postgresql
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;

# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users;  -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats;  -- NULL instead of error

# GREATEST / LEAST
SELECT GREATEST(1, 2, 3);        -- 3
SELECT LEAST(NULL, 1, 2);        -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;

# CASE expressions
SELECT
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age >= 65 THEN 'senior'
    ELSE 'adult'
  END AS age_group
FROM users;

# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL;    -- IS NULL
SELECT * FROM users WHERE email NOTNULL;   -- IS NOT NULL

# row comparison
SELECT ROW(1, 2) < ROW(1, 3);  -- true (compares column by column)

Функции для массивов и JSON

Функции массивов позволяют мощную обработку данных внутри базы: unnest разворачивает массивы в строки (отлично для соединений), array_agg — обратная операция. Операторы JSONB: -> и #> возвращают jsonb, ->> и #>> возвращают text. @> (содержит) — важнейший оператор JSONB, использует GIN-индексы. ? проверяет наличие ключа. jsonb_build_object строит JSON из колонок. jsonb_set/||/- изменяют JSON. Эти функции делают JSONB полноценным типом данных: гибким, но доступным для запросов.

postgresql
# array functions
SELECT array_length(ARRAY[1,2,3], 1);    -- 3
SELECT array_append(ARRAY[1,2], 3);       -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);      -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2);     -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]);              -- 3 rows
SELECT array_agg(name) FROM users;        -- aggregate to array

# JSONB functions
SELECT data->'name'           -- jsonb (key access)
SELECT data->>'name'          -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products;  -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products;  -- expand array

# JSONB predicates
SELECT data @> '{"active": true}'     -- contains
SELECT data ? 'name'                   -- key exists
SELECT data ?| ARRAY['name','email']   -- any key exists
SELECT data ?& ARRAY['name','email']   -- all keys exist

# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}'         -- merge
SELECT data - 'old_key'                -- remove key
SELECT data #- '{nested,old_key}'      -- remove nested key
12

Триггеры

Триггерные функции

Триггерные функции должны возвращать trigger и пишутся на PL/pgSQL (или других языках). TG_OP, TG_TABLE_NAME, OLD, NEW — специальные переменные внутри триггеров. BEFORE-триггеры могут менять NEW (и пропускать операцию, возвращая NULL). AFTER-триггеры — для побочных эффектов (журналы аудита, уведомления). FOR EACH ROW срабатывает на строку; FOR EACH STATEMENT — один раз. Триггеры мощны, но создают скрытые побочные эффекты — используйте их для сквозных задач (аудит, метки времени), а не для бизнес-логики.

postgresql
# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# attach the trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically

# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
  VALUES (
    TG_TABLE_NAME, TG_OP, current_user, now(),
    to_jsonb(OLD)  -- or NEW for insert
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_log();

Триггеры на оператор и на строку

FOR EACH ROW-триггеры срабатывают на каждую строку (могут быть медленными при массовых операциях). FOR EACH STATEMENT срабатывает один раз (не имеет доступа к отдельным OLD/NEW, но может использовать переходные таблицы для массовых операций). BEFORE-триггеры могут менять или пропускать строки; AFTER — для побочных эффектов. Секция WHEN фильтрует, когда триггер срабатывает (вычисляется на строку). Переходные таблицы (REFERENCING NEW TABLE/OLD TABLE) дают операторным триггерам доступ ко всем изменённым строкам — отлично для аудита массовых операций без построчных накладных расходов.

postgresql
# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_sales_summary();

# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)

# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.amount > 10000)
  EXECUTE FUNCTION require_approval();

# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_insert();

Событийные триггеры (DDL)

Событийные триггеры срабатывают на DDL-команды (CREATE/ALTER/DROP) — полезны для аудита изменений схемы или предотвращения опасных операций в продакшене. Они используют tg_event и tg_tag для определения команды. Типичные случаи: запрет DROP TABLE, логирование всех DDL для соответствия требованиям, автогенерация скриптов миграции. Событийные триггеры срабатывают AFTER команды (ddl_command_end) или могут перехватывать её (ddl_command_start). Они уровня базы, а не таблицы. Используйте умеренно: они добавляют накладные расходы к каждой DDL-операции.

postgresql
# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
  IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'Dropping tables is not allowed in production';
  END IF;
END;
$$ LANGUAGE plpgsql;

# register the event trigger
CREATE EVENT TRIGGER protect_tables
  ON ddl_command_end
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop_table();

# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  INSERT INTO ddl_log (event, tag, user_name, object_identity)
  VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_logger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl();

# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;

Управление триггерами

Временно отключайте триггеры для массовых загрузок (намного быстрее), но не забудьте включить обратно! session_replication_role = 'replica' отключает все триггеры и правила — для массовых импортов или репликации. ALTER TABLE ... DISABLE TRIGGER точечнее. Всегда включайте триггеры после массовых загрузок. Следите за использованием триггеров: они могут вызывать скрытые проблемы производительности (каждый триггер добавляет расходы на строку). Information_schema.triggers перечисляет все триггеры; \d+ в psql быстрее для просмотра.

postgresql
# list triggers
SELECT
  event_object_table AS table_name,
  trigger_name,
  action_timing,       -- BEFORE/AFTER/INSTEAD OF
  event_manipulation,  -- INSERT/UPDATE/DELETE
  action_statement     -- function call
FROM information_schema.triggers;

# or use psql
\d+ users  -- shows triggers on the table

# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL;    -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;

# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin';  -- re-enable

# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;

Частые паттерны триггеров

Частые паттерны триггеров: авто-метки времени (updated_at), журналы аудита (отслеживание всех изменений для соответствия требованиям), соблюдение бизнес-правил (межтабличная валидация) и синхронизация денормализованных данных (поддержание счётчиков). Каждый паттерн решает реальную задачу, но добавляет сложность и накладные расходы. Рассмотрите альтернативы: значения по умолчанию для меток времени, аудит на уровне приложения, CHECK-ограничения для простых правил, материализованные представления для денормализации. Используйте триггеры, когда логика должна жить в базе (данные используют несколько приложений, или правило не подлежит обсуждению).

postgresql
# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    current_user, now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
  ) THEN
    RAISE EXCEPTION 'Amount exceeds department budget';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
  UPDATE users SET post_count = (
    SELECT count(*) FROM posts WHERE user_id = NEW.user_id
  ) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
13

Хранимые процедуры и PL/pgSQL

Функции PL/pgSQL

PL/pgSQL — процедурный язык PostgreSQL: добавляет переменные, условия, циклы и обработку ошибок к SQL. Функции возвращают значение или таблицу. SELECT INTO присваивает результаты запроса переменным. RETURN QUERY возвращает строки из запроса. Параметры могут иметь значения по умолчанию. Используйте функции для инкапсуляции переиспользуемой логики, сложных запросов и бизнес-правил. Держите функции простыми и хорошо названными. Для производительности рассмотрите SQL-функции (LANGUAGE sql) для простых запросов: оптимизатор может их встраивать.

postgresql
# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
  post_count integer;
BEGIN
  SELECT count(*) INTO post_count
  FROM posts WHERE posts.user_id = get_user_post_count.user_id;

  IF post_count IS NULL THEN
    RETURN 0;
  END IF;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
  RETURN QUERY
  SELECT posts.id, posts.title, posts.created_at
  FROM posts
  ORDER BY posts.created_at DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);

Управляющие конструкции

В PL/pgSQL есть полный набор управляющих конструкций: IF/ELSIF/ELSE, CASE, LOOP/EXIT/CONTINUE, WHILE, FOR (целочисленные диапазоны) и FOREACH (итерация по массивам). Используйте EXIT WHEN для выхода из циклов. FOR i IN 1..n перебирает диапазоны (включая границы). FOREACH перебирает массивы. EXECUTE запускает динамический SQL (с format() для безопасной подстановки идентификаторов). Держите циклы в базе небольшими: большие циклы должны быть множественным SQL, когда возможно. Для массовых операций используйте INSERT/UPDATE с SELECT вместо построчных циклов.

postgresql
# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
  IF price IS NULL THEN RETURN 'unknown';
  ELSIF price < 10 THEN RETURN 'cheap';
  ELSIF price < 50 THEN RETURN 'moderate';
  ELSE RETURN 'expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
  RETURN CASE tier
    WHEN 'gold'   THEN 0.20
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0.00
  END;
END;
$$ LANGUAGE plpgsql;

# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
  LOOP
    EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
    EXIT WHEN val IS NULL;
    i := i + 1;
    EXIT WHEN i > 1000;  -- safety limit
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE plpgsql;

# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

Хранимые процедуры (PG11+)

Процедуры (PG11+) отличаются от функций: они могут управлять транзакциями (COMMIT/ROLLBACK внутри), не возвращают значения как функции (используйте OUT-параметры) и вызываются через CALL. Используйте процедуры для многооператорных действий, которым нужен контроль транзакций (ETL, миграции данных). Функции — для вычислений и запросов, возвращающих значения. Процедуры — стандартный способ инкапсулировать транзакционную логику в SQL. До PG11 функции не могли коммитить и откатывать — процедуры заполнили этот пробел.

postgresql
# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account integer,
  to_account integer,
  amount numeric
) AS $$
BEGIN
  -- inside a procedure, we can use COMMIT/ROLLBACK
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- check for negative balance
  IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);

# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
  OUT user_count integer,
  OUT post_count integer
) AS $$
BEGIN
  SELECT count(*) INTO user_count FROM users;
  SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;

CALL get_stats(?, ?);  -- output params

Обработка ошибок

RAISE EXCEPTION прерывает транзакцию пользовательской ошибкой. Блоки EXCEPTION ловят ошибки (как try/catch) — часто для мягкой деградации. SQLSTATE — 5-символьный код ошибки; SQLERRM — сообщение. USING HINT/DETAIL добавляет полезный контекст. RAISE NOTICE отлично для отладки (видно в psql). Ловите конкретные ошибки (division_by_zero, unique_violation), а не OTHERS, чтобы не маскировать настоящие баги. Внутри EXCEPTION транзакция находится в прерванном состоянии — можно только коммитить или откатывать, но не выполнять запросы (до процедур PG11+).

postgresql
# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
  IF age < 0 THEN
    RAISE EXCEPTION 'Age cannot be negative: %', age
      USING HINT = 'Please provide a valid age (0-150)';
  ELSIF age > 150 THEN
    RAISE EXCEPTION 'Age seems unrealistic: %', age;
  ELSE
    RAISE NOTICE 'Age validated: %', age;
  END IF;
END;
$$ LANGUAGE plpgsql;

# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
  result := a / b;
  RETURN result;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log

# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity;  -- or in exception handler: SQLSTATE, SQLERRM

Динамический SQL

Динамический SQL (EXECUTE) выполняет строки как SQL — необходим, когда имена таблиц и колонок переменны. Всегда используйте format() с %I (идентификаторы) и %L (литералы), чтобы предотвратить SQL-инъекции, — никогда не конкатенируйте пользовательский ввод напрямую! USING передаёт параметры безопасно (как подготовленные операторы). Динамический SQL мощен, но непрозрачен для планировщика и инструментов статического анализа. Используйте его при необходимости (динамические имена таблиц, генерация DDL), но где возможно предпочитайте статический SQL. Тестируйте тщательно — ошибки динамического SQL проявляются во время работы, а не компиляции.

postgresql
# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

# format() for safe SQL construction
#   %I = identifier (table/column name, quoted)
#   %L = literal (value, quoted)
#   %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'

# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM users WHERE %I = $1', field
  ) USING value;
END;
$$ LANGUAGE plpgsql;

# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
  EXECUTE format(
    'WITH deleted AS (
       DELETE FROM logs WHERE created_at < now() - interval '%s days'
       RETURNING *
     ) SELECT count(*) FROM deleted'
  ) INTO deleted_count USING days_old;

  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;
14

Безопасность и права

Пользователи и роли

В PostgreSQL пользователи и роли — одна сущность: «пользователь» это просто роль с привилегией LOGIN. Создавайте групповые роли (без LOGIN) для централизованного управления правами, затем выдавайте их логин-ролям. VALID UNTIL задаёт срок действия пароля. CONNECTION LIMIT ограничивает число одновременных соединений на роль. SET search_path на роль отлично для изоляции мультитенантности. Всегда используйте роли для группировки прав, а не выдавайте права отдельным пользователям. DROP ROLE требует предварительного удаления всех привилегий и членств.

postgresql
# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';

# create a role that cannot log in (for grouping)
CREATE ROLE readonly;

# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;

# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;

# view roles
\du  -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;

Выдача привилегий

Привилегии: SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER для таблиц; USAGE/CREATE для схем; USAGE/SELECT для последовательностей; EXECUTE для функций; CONNECT/CREATE/TEMPORARY для баз. Привилегии на уровне колонок мощны для безопасности. ALTER DEFAULT PRIVILEGES выдаёт права на будущие объекты — необходимо, когда новые таблицы создаются часто. Всегда выдавайте USAGE на последовательность ролям, которые вставляют в таблицы с serial/identity-колонками. Отзывайте лишние привилегии ради принципа наименьших прав.

postgresql
# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns

# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;

# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;

# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;

# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

Построчная безопасность (RLS)

Row Level Security (RLS) обеспечивает доступ на уровне строк: пользователи видят только строки, подходящие под политику. USING фильтрует чтение; WITH CHECK проверяет запись. Задавайте переменные сессии приложения (SET app.user_id), чтобы идентифицировать текущего пользователя. FORCE RLS подчиняет политикам даже владельца таблицы. Атрибут роли BYPASSRLS обходит весь RLS (для администраторов). RLS мощен для мультитенантных приложений — отдельные представления на пользователя не нужны. Осторожно: политики RLS должны быть корректны, так как они молча фильтруют данные. Тщательно тестируйте на разных пользователях.

postgresql
# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

# policy for all operations
CREATE POLICY user_posts_all ON posts
  USING (user_id = current_setting('app.user_id')::int)
  WITH CHECK (user_id = current_setting('app.user_id')::int);

# USING:    filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)

# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts;  -- only sees user 42's posts

# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;  -- even owner respects RLS
GRANT BYPASSRLS TO admin_role;  -- role-level bypass

# policy for admins (see all)
CREATE POLICY admin_all ON posts
  FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

Аутентификация и SSL

pg_hba.conf — первая линия защиты: он управляет тем, какие хосты и пользователи могут подключаться и как. Всегда используйте scram-sha-256 (а не md5 или trust). hostssl требует SSL для соединения. sslmode=require обеспечивает шифрование; verify-full также проверяет сертификат (самый безопасный). Для продакшена: SSL для всех удалённых соединений, ограничьте pg_hba.conf известными IP и используйте scram-sha-256. После правок перезагружайте через pg_reload_conf(). Следите за несанкционированными соединениями через pg_stat_activity.

postgresql
# pg_hba.conf controls who can connect and how
# host  database  user  address  method
#   host all all 127.0.0.1/32 scram-sha-256
#   host all all 0.0.0.0/0   scram-sha-256
#   hostssl all all 0.0.0.0/0 scram-sha-256  # require SSL

# reload pg_hba.conf
SELECT pg_reload_conf();

# authentication methods:
#   trust:       no password (NEVER use in production)
#   md5:         legacy password hash (deprecated)
#   scram-sha-256:  modern password auth (default, recommended)
#   cert:        client certificate
#   peer:        OS username matches DB username (local only)

# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret';  -- re-hash with scram

# SSL connections
SHOW ssl;                  -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;

# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full

# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;

Шифрование и аудит

pgcrypto даёт шифрование на уровне колонок: pgp_sym_encrypt/decrypt для обратимого шифрования, crypt/gen_salt для хеширования паролей. Для паролей используйте bcrypt (bf): он медленный по замыслу (против перебора). Для соответствия требованиям pgAudit логирует все DDL- и DML-операции в серверный лог. Для чувствительных данных предпочитайте шифрование на уровне приложения (ключи не должны жить в базе). Всегда хешируйте пароли (никогда не шифруйте их обратимо). Следите за активными запросами через pg_stat_activity для аудита. Сочетайте эти инструменты для эшелонированной защиты.

postgresql
# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text,
  ssn bytea  -- encrypted
);

INSERT INTO users (email, ssn) VALUES
  ('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));

SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;

# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf'));      -- bcrypt
SELECT crypt('password', gen_salt('md5'));     -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;

# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log

# session auditing
SELECT
  pid, usename, application_name, client_addr,
  query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';
15

Резервное копирование и восстановление

Основы pg_dump

pg_dump создаёт логическую резервную копию (SQL-операторы для воссоздания базы). Собственный формат (-F c) сжат и поддерживает параллельное и выборочное восстановление — всегда предпочитайте его. Формат каталога (-F d) включает параллельный дамп и восстановление. pg_dumpall сохраняет все базы плюс роли и табличные пространства (для полных бэкапов кластера). --clean --if-exists делает дампы идемпотентными (можно восстанавливать многократно). Для больших баз рассмотрите --jobs для параллельного дампа (только формат каталога).

postgresql
# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)

# backup with options
pg_dump -d mydb -F c -f mydb.dump \
  --no-owner           -- don't include ownership commands
  --no-privileges      -- don't include GRANT/REVOKE
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data, no schema
  --schema-only        -- only schema, no data
  --clean              -- include DROP statements
  --if-exists          -- use IF EXISTS in DROPs

# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases

# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz

# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dump

pg_restore

pg_restore восстанавливает из собственного или каталогового форматов (не из простого SQL — для него psql). --jobs=N включает параллельное восстановление (гораздо быстрее на многоядерных). --clean --if-exists сначала удаляет существующие объекты. --no-owner необходим при восстановлении под другим пользователем. --list показывает содержимое дампа; --table/--schema — для выборочного восстановления. Для больших восстановлений временно отключайте триггеры и ограничения, затем включайте. Всегда тестируйте восстановление: бэкап, который нельзя восстановить, бесполезен.

postgresql
# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump

# restore with options
pg_restore -d mydb mydb.dump \
  --clean              -- drop objects before recreating
  --if-exists          -- use IF EXISTS in DROPs
  --no-owner           -- don't restore ownership
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data
  --jobs=4             -- parallel restore (4 threads)
  --verbose

# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump

# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb

# list contents of a dump
pg_restore -l mydb.dump

# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts

# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump  -- list items with indices

Физический бэкап и PITR

pg_basebackup создаёт двоичную копию всего каталога данных — быстрее pg_dump для больших баз и поддерживает PITR. Архивация WAL (журнала предзаписи) необходима для PITR: архивируйте все файлы WAL, затем восстановите базовый бэкап и проиграйте WAL до нужной точки. recovery_target_time позволяет восстановиться до конкретного момента (отменить ошибки вроде случайного DROP TABLE). pg_create_restore_point создаёт именованную точку восстановления. PITR — золотой стандарт бэкапа; сочетайте с pg_dump для логических копий.

postgresql
# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
  -Ft -z -P          -- tar format, compressed, progress
  --wal-method=stream  -- stream WAL files separately

# set up WAL archiving (postgresql.conf)
#   archive_mode = on
#   archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();

# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
#      restore_command = 'cp /archive/%f %p'
#      recovery_target_time = '2025-01-15 14:30:00'
#      recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time

# create a recovery point
SELECT pg_create_restore_point('before_migration');

# check WAL archiving status
SELECT * FROM pg_stat_archiver;

Настройка репликации

Потоковая репликация: primary отправляет WAL репликам в реальном времени. pg_basebackup -R настраивает реплику одной командой. Реплики только для чтения (hot standby) и могут обслуживать читающие запросы. Следите за отставанием репликации (pg_stat_replication на primary, pg_last_xact_replay_timestamp на реплике). Повышение (pg_ctl promote) делает реплику primary — для переключения при сбое. Для автоматического переключения используйте Patroni, repmgr или pg_auto_failover. Каскадная репликация снижает нагрузку на primary, выстраивая реплики цепочкой.

postgresql
# on primary (postgresql.conf):
#   wal_level = replica
#   max_wal_senders = 10
#   hot_standby = on  (for replica)

# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';

# in pg_hba.conf, allow replication connections:
#   host replication repluser 0.0.0.0/0 scram-sha-256

# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
  -Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo

# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();

# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data

# cascading replication
#   replica can also be a primary to other replicas
#   set cascade_level or use primary_conninfo pointing to another replica

Логическая репликация

Логическая репликация (PG10+) реплицирует изменения на уровне таблиц — гибче физической: разные версии PostgreSQL, выборочные таблицы, разные схемы на сторонах. Publisher создаёт PUBLICATION; subscriber создаёт SUBSCRIPTION. Начальные данные копируются, затем изменения идут потоком в реальном времени. Конфликты (например, INSERT с существующим PK) останавливают репликацию — обрабатывайте через SKIP или ручные исправления. Отлично для обновлений без простоя (репликация со старой версии на новую, затем переключение). Используйте для обмена данными между базами или частичной репликации.

postgresql
# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;

# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;

# on subscriber:
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host user=repluser dbname=mydb'
  PUBLICATION my_pub;

# initial data copy happens automatically
# subsequent changes are replicated in real-time

# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');

# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution

# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!

# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;
16

Производительность и настройка

EXPLAIN

EXPLAIN показывает план запроса; ANALYZE выполняет запрос и показывает реальные тайминги. Ищите Seq Scan на больших таблицах (нет индекса), высокий Rows Removed by Filter (неэффективный запрос) и внешние сортировки (увеличьте work_mem). Index Only Scan самый быстрый (нужен покрывающий индекс). Bitmap Scan занимает промежуточное положение между Seq Scan и Index Scan. Числа 'cost' — оценки планировщика; 'actual time' — реальность. Всегда используйте EXPLAIN (ANALYZE, BUFFERS) для настройки: он раскрывает истинное узкое место. Сравнивайте оценённые и фактические строки, чтобы заметить устаревшую статистику.

postgresql
# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;

# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;

# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;

# common plan nodes:
#   Seq Scan:        full table scan (slow for large tables)
#   Index Scan:      index lookup + heap fetch
#   Index Only Scan: index-only (fastest, needs covering index)
#   Bitmap Scan:     bitmap of matching rows, then fetch
#   Hash Join:       hash one table, probe with other
#   Nested Loop:     for each left row, scan right (small datasets)
#   Sort:            explicit sort (look for "Sort Method: external merge" = bad)

# warning signs:
#   "Seq Scan on large_table" -> missing index
#   "Sort Method: external disk" -> increase work_mem
#   "Rows Removed by Filter: 999999" -> bad filter, missing index

VACUUM и ANALYZE

MVCC в PostgreSQL создаёт мёртвые строки при UPDATE/DELETE — VACUUM освобождает их. autovacuum работает автоматически (держите его включённым!), но может требовать настройки для таблиц с интенсивной записью. VACUUM FULL перезаписывает таблицу (возвращает место ОС), но берёт эксклюзивную блокировку — вместо него для онлайн-реорганизации используйте pg_repack. ANALYZE обновляет статистику, на которую опирается планировщик, — критично после массовых загрузок. Устаревшая статистика ведёт к плохим планам. Настраивайте autovacuum_scale_factor для каждой таблицы (ниже для больших и активно пишущихся). Следите за n_dead_tup, чтобы заметить отставание вакуума.

postgresql
# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users;            -- regular vacuum
VACUUM FULL users;       -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users;    -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users;  -- with output

# autovacuum (should be ON in production)
SHOW autovacuum;  -- should be on
SHOW autovacuum_vacuum_threshold;     -- default 50
SHOW autovacuum_vacuum_scale_factor;  -- default 0.2 (20% dead rows)

# tune autovacuum per table
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,  -- vacuum at 10% dead rows
  autovacuum_analyze_scale_factor = 0.05
);

# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users;         -- sample the table
ANALYZE;               -- all tables

# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

Память и конфигурация

shared_buffers — общий кеш PostgreSQL (классическая рекомендация — 25% RAM). effective_cache_size сообщает планировщику о кеше ОС (ставьте 50–75% RAM, это лишь подсказка). work_mem — на каждую операцию сортировки или хеширования; слишком высокое значение при многих соединениях может исчерпать память. maintenance_work_mem ускоряет VACUUM и CREATE INDEX. Для SSD ставьте random_page_cost=1.1 (по умолчанию 4.0 рассчитано на медленные HDD). Эти настройки сильно влияют на производительность — всегда измеряйте изменения.

postgresql
# key memory settings (in postgresql.conf)
SHOW shared_buffers;        -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size;  -- default 4GB (set to 50-75% of RAM)
SHOW work_mem;              -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem;  -- default 64MB (for VACUUM, CREATE INDEX)

# recommended production settings (example for 16GB RAM):
#   shared_buffers = 4GB
#   effective_cache_size = 12GB
#   work_mem = 64MB          (careful: per-operation, not global)
#   maintenance_work_mem = 512MB
#   max_connections = 100
#   random_page_cost = 1.1   (for SSDs; default 4.0 is for HDDs)

# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

# WAL settings for write performance
SHOW wal_buffers;        -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size;       -- default 1GB

# parallel query
SHOW max_parallel_workers;        -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4;  -- more parallelism

# reload config
SELECT pg_reload_conf();

Оптимизация запросов

Наибольшие выигрыши: индексируйте колонки FK, используйте COPY для массовых загрузок, избегайте OFFSET в пагинации (берите keyset) и не оборачивайте индексируемые колонки в функции. EXISTS часто быстрее IN для подзапросов. SELECT * мешает index-only scan и тратит ввод-вывод. Пакетные вставки (многострочные или COPY) в 10–100 раз быстрее одиночных. После массовых загрузок запускайте ANALYZE, чтобы у планировщика была точная статистика. Всегда делайте EXPLAIN (ANALYZE) до и после изменений, чтобы проверить улучшения.

postgresql
# 1. use indexes (check with EXPLAIN)
# bad:  SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
#   (or use an expression index on lower(email))

# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;

# 3. use EXISTS instead of IN for subqueries
-- BAD:  SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)

# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');  -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);

# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)

# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD:  SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10

# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

# 8. analyze after bulk loads
ANALYZE products;  -- update statistics for the planner

Мониторинг запросов

pg_stat_statements — самый ценный инструмент мониторинга: он отслеживает число запросов, общее и среднее время и строки. Включите его в shared_preload_libraries. pg_stat_activity показывает запросы в реальном времени (ищите долгие и 'idle in transaction'). Следите за долей мёртвых строк для обнаружения раздувания. Находите неиспользуемые индексы (idx_scan=0), чтобы удалить их: они тратят место и замедляют запись. Установите log_min_duration_statement для автоматического логирования медленных запросов. Сочетайте эти инструменты с EXPLAIN, чтобы систематически находить и устранять узкие места.

postgresql
# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# currently running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

# long-running transactions (block vacuum)
SELECT
  pid,
  now() - xact_start AS transaction_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# table sizes (find bloat candidates)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
17

Секционирование

Секционирование по диапазону

Секционирование по диапазону идеально для временных рядов. PostgreSQL автоматически направляет вставки в нужную секцию и отсекает секции в запросах (сканируются только релевантные). Создайте DEFAULT-секцию для данных вне диапазона (или настройте задачу, создающую будущие секции). Каждая секция — отдельная таблица: ими можно управлять независимо (удалять старые данные через DROP TABLE вместо медленного DELETE). Подсекционирование (сначала диапазон, затем хеш) работает для очень больших данных. Автоматизируйте создание секций расширением pg_partman.

postgresql
# partition by date range (most common for time-series)
CREATE TABLE orders (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     integer NOT NULL,
  amount      numeric
) PARTITION BY RANGE (created_at);

# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00);  -- goes to orders_2025_01

# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

Секционирование по списку

Секционирование по списку — для дискретных значений (регионы, категории, ID арендаторов). Каждая секция хранит строки для конкретных значений. Как и диапазонное, PostgreSQL отсекает секции в запросах. Это отлично для мультитенантных приложений: данные каждого арендатора изолированы, их можно независимо бэкапить и восстанавливать и переносить на разные хранилища. Используйте DEFAULT для неожиданных значений. Чтобы добавить секцию для нового значения, CREATE TABLE ... FOR VALUES IN (...). Отсоединяйте секцию через ALTER TABLE ... DETACH PARTITION (полезно для архивирования).

postgresql
# partition by discrete values (e.g., region, category)
CREATE TABLE users (
  id       serial,
  name     text,
  region   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US', 'CA');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'AU', 'SG');

CREATE TABLE users_other PARTITION OF users DEFAULT;

# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US');  -- users_us

# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP';  -- only scans users_apac

# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
  id          serial,
  tenant_id   integer NOT NULL,
  data        jsonb
) PARTITION BY LIST (tenant_id);

CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);

Секционирование по хешу

Хеш-секционирование распределяет строки равномерно по секциям, хешируя ключ секционирования, — отлично для распределения нагрузки. Запросы с фильтром по ключу секционирования (WHERE user_id = 42) отсекаются до одной секции; диапазонные запросы (WHERE user_id > 100) сканируют все секции. Число секций фиксируется при создании (модуль должен быть степенью 2): добавить позже можно только пересоздав таблицу. Выбирайте число секций по ожидаемому размеру данных. Берите хеш-секционирование, когда нужна равномерность и вы всегда запрашиваете по ключу секционирования.

postgresql
# partition by hash (distribute evenly)
CREATE TABLE events (
  id          bigserial,
  user_id     integer NOT NULL,
  event_type  text,
  data        jsonb
) PARTITION BY HASH (user_id);

# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login');  -- routes to a partition

# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;

# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100;  -- scans all 4

# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!

Обслуживание секций

DETACH PARTITION превращает секцию в самостоятельную таблицу — идеально для архивирования старых данных без блокировки родителя. ATTACH PARTITION добавляет существующую таблицу как новую секцию (требует совпадения схемы и ограничений). Используйте tablespaces для многоуровневого хранения (переносите старые секции на более дешёвые диски). pg_partman автоматизирует создание и обслуживание секций — необходимо для временных рядов. Исключение ограничений (по умолчанию 'partition') — механизм отсечения секций. Всегда планируйте создание секций заранее: исчерпание будущих секций вызывает сбои INSERT.

postgresql
# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table

# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.

# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions

# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on;  -- default 'partition' is usually fine

# check partition info
SELECT
  inhrelid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;

Стратегия секционирования

Секционируйте, когда таблицы становятся большими (>100 ГБ) или нужен быстрый жизненный цикл данных (удалять старые секции вместо DELETE). Выбирайте ключ секционирования по самому частому шаблону запросов: отсечение секций работает, только когда вы фильтруете по ключу секционирования. Уникальные ограничения на секционированных таблицах ДОЛЖНЫ включать ключ секционирования (PG это требует). Индексы на секционированных таблицах автоматически применяются ко всем секциям. Избегайте слишком большого числа секций (накладные расходы на планирование). Для временных рядов месячные секции — частый оптимум. Используйте pg_partman для автоматизации.

postgresql
# when to partition:
#   - table > 100GB (hard to vacuum, slow queries)
#   - time-series data (drop old data fast)
#   - multi-tenant (isolate tenants)
#   - parallel maintenance (vacuum per partition)

# choosing partition key:
#   - most common filter in queries (enables pruning)
#   - time for time-series (created_at, ordered_at)
#   - tenant_id for multi-tenant

# choosing partition type:
#   - RANGE: time-series, ordered data
#   - LIST: discrete categories (region, tenant)
#   - HASH: even distribution, always query by key

# partition count:
#   - too few:  doesn't help (large partitions)
#   - too many: planning overhead (1000+ partitions slow planning)
#   - sweet spot: 50-500 partitions

# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id);  -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);

# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
  UNIQUE (id, created_at);  -- must include created_at!

# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
  REFERENCES orders(id, created_at);
18

Продвинутые возможности

Полнотекстовый поиск

В PostgreSQL встроен полнотекстовый поиск: tsvector (предобработанный текст), tsquery (поисковые термы) и оператор @@. GIN-индексы делают поиск быстрым. to_tsvector разбивает и стеммирует слова (английский, китайский и др. — настройте язык). ts_rank оценивает результаты; ts_headline подсвечивает совпадения. Триггер tsvector_update_trigger автоматически поддерживает поисковую колонку. Для простого поиска это соперничает с Elasticsearch — внешний сервис не нужен. Для сложного поиска в масштабе рассмотрите Elasticsearch/OpenSearch, но начинайте со встроенного FTS PostgreSQL.

postgresql
# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', title || ' ' || body);

# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');

# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');

# rank results
SELECT
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# highlight matches
SELECT
  ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;

# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

Продвинутые запросы к JSONB

Путевые выражения JSONB (PG12+) используют язык путей SQL/JSON ($) для мощных запросов: фильтры, предикаты и извлечение одним выражением. jsonb_agg и jsonb_build_object конструируют JSON из реляционных данных — отлично для ответов API. GIN-индекс jsonb_path_ops меньше и быстрее стандартного GIN, но поддерживает только @> и путевые операторы (@?, @@). Используйте jsonb_path_query для извлечения подходящих элементов массива и jsonb_agg для сборки JSON-массивов из строк — идеально для генерации ответов API прямо в SQL без ORM.

postgresql
# JSONB path queries
SELECT data#>>'{address,city}' FROM users;  -- text at path

# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';

# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';

# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;

# JSONB aggregation (build JSON from rows)
SELECT
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';

# group by JSON field
SELECT
  data->>'category' AS category,
  count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;

# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];

# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operators

Геоданные с PostGIS

PostGIS превращает PostgreSQL в полноценную пространственную базу — добавьте его через CREATE EXTENSION. Используйте geography для GPS-координат (широта/долгота) и geometry для проекционных координат. ST_DWithin находит точки в пределах расстояния; ST_Distance измеряет точно. Оператор <-> включает KNN-запросы (ближайшие соседи), эффективно использующие GiST-индекс. Всегда создавайте GiST-индекс на колонках geometry/geography. Учтите: ST_MakePoint принимает (долгота, широта) — сначала долгота! Для серьёзных геозадач PostGIS соперничает со специализированными ГИС.

postgresql
-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create a table with geography (lat/lng)
CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location geography(POINT, 4326)
);

-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);

-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);

-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;

-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
  'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;

Foreign Data Wrappers (FDW)

Foreign Data Wrappers (FDW) позволяют запрашивать внешние источники данных, как если бы они были локальными таблицами. postgres_fdw подключается к другим серверам PostgreSQL; file_fdw читает CSV. Есть FDW для MySQL, Oracle, SQLite, REST API и др. Запросы по возможности передаются на удалённый сервер (WHERE, JOIN), но кросс-серверные соединения загружают данные локально. FDW отличен для интеграции данных, миграций и федеративных запросов без ETL. Производительность ниже локальных таблиц из-за сети.

postgresql
-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- create a foreign server
CREATE SERVER foreign_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');

-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
  SERVER foreign_db
  OPTIONS (user 'remote_user', password 'secret');

-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_db
  INTO remote_schema;

-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;

-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
  id int, level text, message text, ts timestamp
) SERVER csv_server
  OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');

-- join local and remote data
SELECT l.id, u.name FROM local_orders l
  JOIN remote_schema.users u ON l.user_id = u.id;

Advisory-блокировки и конкурентность

Advisory-блокировки — определяемые приложением блокировки, не привязанные к строкам таблиц; идеальны для координации. Сессионные блокировки живут до явного освобождения или конца сессии; транзакционные автоматически снимаются при COMMIT/ROLLBACK. pg_try_advisory_lock неблокирующий — отлично для паттерна «только один воркер». Сочетайте с FOR UPDATE SKIP LOCKED для безопасной обработки очереди задач. Блокировки идентифицируются bigint или двумя int. Всегда парь блокировку с разблокировкой в путях ошибок. Advisory-блокировки — основа безопасных систем фоновых задач в PostgreSQL.

postgresql
-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);

-- release it
SELECT pg_advisory_unlock(12345);

-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT;  -- lock auto-released

-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345);  -- true if acquired, false if held

-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);

-- use case: ensure only one worker runs a job
SELECT CASE
  WHEN pg_try_advisory_lock(999) THEN run_job()
  ELSE 'skipped: another worker is running'
END;

-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;
19

Расширения

Управление расширениями

Расширения добавляют функциональность сверх ядра PostgreSQL — устанавливайте через CREATE EXTENSION. pg_available_extensions показывает, что установлено на диске; pg_extension — что загружено. Расширения устанавливаются для каждой базы отдельно. Некоторые (вроде pg_stat_statements) также требуют записи в shared_preload_libraries. CASCADE удаляет зависимые объекты. Всегда фиксируйте версии расширений в продакшене для воспроизводимости. Расширения — ключевая сила PostgreSQL: они превращают его в платформу, а не просто базу.

postgresql
-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- list installed extensions
SELECT * FROM pg_extension;

-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;

-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;

-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

pg_stat_statements (профилирование запросов)

pg_stat_statements — самое ценное расширение PostgreSQL: оно записывает статистику выполнения каждого запроса. Ищите медленные запросы по total_exec_time (влияние) или mean_exec_time (стоимость одного запроса). Высокое rows/call говорит об отсутствующих индексах. Расширение нормализует запросы (заменяет литералы на $1), поэтому похожие запросы агрегируются. Сбрасывайте статистику после деплоев, чтобы отслеживать свежую производительность. Это первый инструмент для оптимизации: он точно показывает, куда уходит время.

postgresql
-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();

pgcrypto (шифрование и хеширование)

pgcrypto даёт хеширование (digest, HMAC), генерацию UUID (gen_random_uuid) и шифрование (PGP симметричное и асимметричное). gen_random_uuid() — стандартный способ генерировать UUID v4 (встроен с PG13, но pgcrypto даёт его для старых версий). Для хранения паролей предпочитайте bcrypt или argon2 в коде приложения: хеширование pgcrypto — для целостности данных, а не паролей. PGP-шифрование подходит для шифрования колонок при хранении. В продакшене всегда управляйте ключами шифрования вне базы.

postgresql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');

-- generate a UUID v4
SELECT gen_random_uuid();

-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');

-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
  AS encrypted_blob;

-- decrypt data
SELECT pgp_sym_decrypt(
  dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
  'my_passphrase'
) AS decrypted;

-- store encrypted columns
CREATE TABLE secrets (
  id serial PRIMARY KEY,
  data bytea  -- store pgp_sym_encrypt() output here
);

INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));

UUID и генерация ID

Используйте gen_random_uuid() (PG13+) для случайных UUID: он встроен и не требует расширения. uuid-ossp даёт UUID v1 (на основе времени) и v5 (пространство имён), если нужно. Для автоинкрементных целых предпочитайте GENERATED ALWAYS AS IDENTITY (PG10+) вместо serial: это стандарт SQL, он предотвращает случайные ручные вставки и чисто обрабатывает права. RETURNING id получает вставленный ID за один обход. UUID хороши для распределённых систем; identity-колонки проще для одноузловых приложений.

postgresql
-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();

-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- time-based
SELECT uuid_generate_v4();  -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name

-- UUID primary key
CREATE TABLE events (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner

-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

Каталог полезных расширений

Экосистема расширений PostgreSQL огромна. pg_trgm включает нечёткое сопоставление текста (оператор %) и быстрые LIKE-запросы. PostGIS добавляет геопространственные возможности. pg_partman автоматизирует создание секций для временных рядов. TimescaleDB и Citus — сторонние расширения для временных рядов и горизонтального масштабирования соответственно. hstore устарел — используйте jsonb. unaccent убирает диакритику для интернационализированного поиска. Просматривайте доступные расширения через pg_available_extensions и ищите community-расширения в PGXN (PostgreSQL Extension Network).

postgresql
-- PostGIS: geospatial data
CREATE EXTENSION postgis;

-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon';  -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;

-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;

-- pg_partman: partition management
CREATE EXTENSION pg_partman;

-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)

-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];

-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café');  -- 'cafe'
20

Мониторинг и диагностика

Активные запросы (pg_stat_activity)

pg_stat_activity — ваше представление реального времени о том, что делает PostgreSQL. Смотрите state (active, idle, idle in transaction). Долгие запросы и сессии idle-in-transaction — частые проблемы: последние держат блокировки и мешают вакууму. pg_cancel_backend посылает SIGINT (запрос отменяется, сессия выживает); pg_terminate_backend убивает сессию целиком. Всегда исследуйте перед убийством. Установите idle_in_transaction_session_timeout для авто-убийства зависших транзакций. Следите за числом соединений, чтобы не упереться в max_connections.

postgresql
-- see all active queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- kill the session

-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
  now() - xact_start AS xact_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Блокировки и ожидание

Конкуренция за блокировки — частая продакшен-проблема. pg_locks показывает все активные блокировки; невыданные блокировки означают, что сессия ждёт. Запрос цепочки блокировок точно определяет, кто кого блокирует, — необходимо для отладки дедлоков и медленных запросов. AccessExclusive (от ALTER TABLE, DROP, VACUUM FULL) блокирует всё — выполняйте в окна обслуживания. Используйте lock_timeout, чтобы сессии не ждали вечно. Долгие транзакции — обычный виновник: держите транзакции короткими и коммитьте сразу.

postgresql
-- see all locks
SELECT
  pid,
  relation::regclass AS table_name,
  mode,
  granted,
  query
FROM pg_locks
WHERE granted = false;

-- find blocking chains (who blocks whom)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
  AND ul.relation IS NOT DISTINCT FROM bl.relation
  AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- lock modes: AccessShare, RowShare, RowExclusive,
--   ShareUpdateExclusive, Share, ShareRowExclusive,
--   Exclusive, AccessExclusive

-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;

-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';

Вакуум и раздувание

PostgreSQL использует MVCC: UPDATE/DELETE создают мёртвые строки, которые освобождает VACUUM. autovacuum работает автоматически, но может требовать настройки для больших и активно пишущихся таблиц. n_dead_tup показывает ожидающую очистку; высокий dead_pct означает, что autovacuum не успевает. VACUUM FULL возвращает место на диске, но берёт AccessExclusive-блокировку: используйте pg_repack для онлайн-удаления раздувания. Долгие транзакции блокируют вакуум (они видят старые строки), вызывая раздувание и риск переполнения ID транзакций. Следите за xid_age, чтобы предотвратить переполнение (принудительный сбой около 2 млрд).

postgresql
-- check table bloat (dead tuples)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users;  -- reclaims space, locks table!

-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- tune autovacuum per table
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;

-- check wraparound risk (transaction ID age)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;

Анализ логов

Логирование настраивается в postgresql.conf. log_min_duration_statement логирует медленные запросы (0 — все, 100 — дольше 100 мс). log_lock_waits ловит конкуренцию за блокировки. CSV-логирование позволяет запрашивать логи как внешнюю таблицу (file_fdw). Частые префиксы: %t (время), %p (PID), %u (пользователь), %d (база). В продакшене используйте сборщик логов вроде pgBadger для разбора и визуализации. Не логируйте каждый запрос в продакшене (нагрузка на ввод-вывод) — для агрегированного анализа лучше pg_stat_statements.

postgresql
-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100  -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0

-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;

-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
  log_time timestamp,
  user_name text,
  database_name text,
  process_id int,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos int,
  context text,
  query text,
  query_pos int,
  location text,
  file_name text,
  file_line_num int,
  application_name text
) SERVER pglog
  OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');

-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;

Проверки здоровья и метрики

Ключевые метрики здоровья: доля попаданий в кеш (цельте в >99% — если ниже, увеличьте shared_buffers), доля использования индексов (низкая означает отсутствующие индексы или плохие запросы), размеры таблиц (ловите раздувание рано) и число соединений. pg_stat_user_tables и pg_statio_user_tables — золотая жила операционных данных. Настройте мониторинг через Prometheus + postgres_exporter для дашбордов. Следите за ростом мёртвых строк, падением доли попаданий в кеш, ростом соединений и таблицами, растущими быстрее ожидаемого. Регулярные проверки предотвращают ночные аварии.

postgresql
-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- table sizes (with indexes)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- index usage ratio
SELECT
  sum(idx_scan) AS index_scans,
  sum(seq_scan) AS seq_scans,
  round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- connection stats
SELECT count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();

Was this helpful?