7786
Авторский канал про Базы Данных и SQL Ресурсы, гайды, задачи, шпаргалки. Информация ежедневно пополняется! Автор: @energy_it
Lost Update: почему транзакций недостаточно без правильной модели конкурентного доступа!
Очень распространённое заблуждение: если запросы выполняются внутри транзакции — значит данные уже защищены от гонок, но это не так.
Одна из классических проблем конкурентного доступа — lost update. Например, есть таблица:
accounts(id, balance)
id | balance
1 | 1000
SELECT balance
FROM accounts
WHERE id = 1;
UPDATE accounts
SET balance = 900
WHERE id = 1;
UPDATE accounts
SET balance = 800
WHERE id = 1;
lost update.BEGIN;
SELECT balance
FROM accounts
WHERE id = 1;
-- вычисления в приложении
UPDATE accounts
SET balance = :new_balance
WHERE id = 1;
COMMIT;
SELECT и UPDATE другая транзакция может изменить строку.UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE.SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
FOR UPDATE ставит row-level lock. Пока транзакция не завершится, другая транзакция не сможет изменить эту строку или получить несовместимую блокировку на неё.accounts(id, balance, version)
SELECT balance, version
FROM accounts
WHERE id = 1;
UPDATE accounts
SET
balance = :new_balance,
version = version + 1
WHERE
id = 1
AND version = 5;
UPDATE затронет 0 строк. Приложение понимает: данные устарели, нужно перечитать и повторить операцию. Ещё важно понимать: READ COMMITTED, REPEATABLE READ, SERIALIZABLE ведут себя по-разному в разных СУБД.lost update при параллельных запросах.
Транзакционная блокировка без блокировки строк!
Иногда нужно запретить параллельную обработку одного объекта, но подходящей строки для FOR UPDATE ещё может не быть.
SELECT pg_advisory_xact_lock(10, 42);
pg_advisory_xact_lock создаёт транзакционную пользовательскую блокировку. Первый аргумент удобно использовать как namespace, второй — как id объекта.SELECT pg_advisory_xact_lock(20, user_id);
COMMIT или ROLLBACK блокировка снимается автоматически.SELECT pg_try_advisory_xact_lock(20, user_id);
pg_try_advisory_xact_lock: он сразу вернёт true или false, и приложение сможет аккуратно пропустить задачу.BEGIN;
SELECT pg_advisory_xact_lock(30, 123);
UPDATE invoices SET status = 'paid' WHERE id = 123;
COMMIT;
advisory lock не блокирует таблицу и не заменяет ограничения БД. Это кооперативная блокировка, поэтому все участники должны использовать один и тот же ключ.
🐱 SQL Window Functions — информативный гайд по оконным функциям в SQL!
Отличный ресурс для тех, кто хочет разобраться в SQL и освоить оконные функции. На сайте подробно объясняются инструменты для аналитики и сложной обработки данных. Всё сопровождается наглядными примерами и практическими кейсами.
📌 Оставляю ссылочку: antonz.org
➡️ SQL Ready | #ресурс
💅 Структурированный справочник по MySQL!
Репозиторий представляет собой структурированную базу знаний по MySQL, где собраны как основы работы с базами данных, так и более сложные темы. Материал подан в формате конспекта, поэтому его удобно использовать и для изучения, и для быстрого повторения перед собеседованием или рабочими задачами.
Оставляю ссылочку: GitHub 📱
В России можно посещать IT-мероприятия хоть каждый день: как оффлайн, так и онлайн
Но где их находить? Как узнавать о них раньше, чем когда все начнут выкладывать фотографии оттуда?
Переходите на канал IT-Мероприятия России. В нём каждый день анонсируются мероприятия со всех городов России
📆 в канале размещаются как онлайн, так и оффлайн мероприятия;
👩💻 можно найти ивенты по любому стеку: программирование, frontend-backend разработка, кибербезопасность, дата-аналитика, osint, devops и другие;
🎙 разнообразные форматы мероприятий: митапы с коллегами по цеху, конференции и вебинары с известными опытными специалистами, форумы и олимпиады от важных представителей индустрии и многое другое
А чтобы не искать по разным форумам и чатам новости о предстоящих ивентах:
🚀 IT-мероприятия России — подписывайся и будь в курсе всех предстоящих мероприятий!
PostgreSQL умеет замораживать часть запроса и запрещать оптимизатору его разворачивать!
Начиная с PostgreSQL 12 оптимизатор получил право разворачивать CTE прямо внутрь основного запроса.
WITH data AS (
SELECT *
FROM orders
)
SELECT *
FROM data
WHERE user_id = 42;
WITH может вообще исчезнуть из плана выполнения, потому что PostgreSQL встроит его обратно в запрос. Но иногда это плохо.CTE дорогой расчёт, который нельзя выполнять повторно. Тут появляется малоизвестная фича:WITH expensive AS MATERIALIZED (
SELECT *
FROM huge_events
WHERE created_at >= now() - interval '1 day'
)
SELECT COUNT(*)
FROM expensive;
MATERIALIZED заставляет PostgreSQL сначала физически вычислить CTE, а потом использовать результат дальше.WITH data AS NOT MATERIALIZED (
SELECT *
FROM orders
)
SELECT *
FROM data
WHERE user_id = 42;
CTE обратно в запрос.CTE с MATERIALIZED и без него иногда отличается по производительности в десятки раз на больших объёмах данных.
🐱 SQL, MySQL и PostgreSQL — один из лучших материалов для изучения баз данных!
Это обучающий материал с теорией, объяснениями и практическими задачами. Здесь разбираются устройство баз данных, связи между таблицами, индексы, нормализация, проектирование схем и многое др. Большой акцент сделан на понимании того, как правильно проектировать бд и решать задачи, которые часто встречаются на собеседованиях и в работе.
Оставляю ссылочку: GitHub 📱
🧐 SQL Tutorial — подробный гайд по SQL с примерами!
На сайте собрано множество обучающих материал по SQL: от базовых запросов SELECT и WHERE до JOIN, подзапросов, функций, сортировки и работы с таблицами. Всё объясняется простым языком с примерами запросов и постепенным усложнением тем, поэтому материал подойдёт как новичкам, так и тем, кто хочет систематизировать знания по бд.
📌 Оставляю ссылочку: ravesli.com
➡️ SQL Ready | #ресурс
📂 Напоминалка по шардингу баз данных!
Например, шардинг по диапазону распределяет данные по определённым диапазонам значений, а шардинг по хэшу помогает равномерно распределять нагрузку между серверами.
На картинке — основные стратегии шардинга и маршрутизации запросов, которые используются в распределённых базах данных и высоконагруженных системах.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
✍️ PostgreSQL Querying — практическое изучение SQL на PostgreSQL!
Здесь подробно разбираются запросы, работа с данными, фильтрация, JOIN’ы, агрегации и другие конструкции, которые постоянно используются в разработке. Материал подаётся последовательно и на примерах, поэтому намного проще понять логику запросов и научиться писать их самостоятельно.
Оставляю ссылочку: GitHub 📱
❤️ PostgreSQL Tutorial — подробная документация и учебник по PostgreSQL!
На сайте собрана большая база материалов по PostgreSQL: установка, настройка, SQL-запросы, работа с таблицами, индексами, функциями, транзакциями и администрированием базы данных. Материал подаётся последовательно. Отличный ресурс как для новичков, так и для разработчиков, которым нужен удобный справочник с примерами и практическими объяснениями.
📌 Оставляю ссылочку: postgresql.leopard.in.ua
➡️ SQL Ready | #ресурс
✍️ Информативная статья вышла на Хабре: «Обратная сторона массивов в PostgreSQL»!
В этой статье:• Разбирается, почему массивы в PostgreSQL — это отдельная модель хранения со своими ограничениями и компромиссами;
• Показываются скрытые проблемы массивов: потеря ссылочной целостности, особенности GIN-индексов, TOAST, MVCC и дорогостоящие обновления;
• Объясняется, как правильно работать с массивами, когда использовать JSONB, intarray, pgvector и в каких случаях массивы действительно оправданы.
🔊 Продолжайте читать на Habr!
🎓 PostgreSQL должен быть спроектирован до первого инцидента.
В enterprise-среде PostgreSQL — это уже не просто база данных, а часть критичной инфраструктуры с требованиями к доступности, восстановлению, безопасности и мониторингу.
На курсе «Архитектура PostgreSQL для корпоративных систем» разбираем, как строить устойчивую PostgreSQL-инфраструктуру под реальные корпоративные нагрузки.
📌 В фокусе курса:
• High Availability (высокая доступность)
• Disaster Recovery (аварийное восстановление)
• failover-сценарии
• RPO/RTO
• резервирование и мониторинг
• архитектурные ошибки и точки отказа
🐾 АИСыч напоминает: б бы «Backup — это не стратегия. Стратегия — это проверенное восстановление».
SQLREADY20
Почему индекс может не использоваться, даже если он есть!
Очень частая ситуация: индекс создан, запрос написан вроде нормально, но SQL всё равно делает Seq Scan или Full Table Scan.
Имеем такую таблицу:
users(
id,
email,
created_at
)
CREATE INDEX idx_users_email
ON users(email);
SELECT *
FROM users
WHERE email = 'test@example.com';
SELECT *
FROM users
WHERE LOWER(email) = 'test@example.com';
email. А в условии используется выражение:LOWER(email)
LOWER() к строкам; сравнивать результат; читать гораздо больше данных, чем ожидалось.expression/functional index:CREATE INDEX idx_users_email_lower
ON users(LOWER(email));
SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-10';
DATE(created_at)
created_at может не помочь, потому что функция применяется к колонке.SELECT *
FROM orders
WHERE created_at >= '2025-01-10'
AND created_at < '2025-01-11';
WHERE user_id::text = '100'
user_id — integer, то здесь преобразование применяется к колонке. В такой ситуации обычный индекс по user_id может не использоваться.WHERE user_id = 100
WHERE user_id = '100'
LIKE:WHERE email LIKE '%gmail.com'
100–1000 строк, то Full Scan может быть дешевле, чем прыжки по индексу.LIKE, OR-условия и устаревшая статистика могут сделать индекс бесполезным или менее выгодным для оптимизатора.
📂 Напоминалка по оптимизации баз данных!
Например, индексы ускоряют поиск данных, а репликация помогает распределять нагрузку и повышать отказоустойчивость.
На картинке — 9 основных подходов для улучшения производительности базы данных, которые полезно держать под рукой.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
👍 PostgreSQL Performance Essentials — практическое руководство по оптимизации PostgreSQL!
Этот репозиторий особенно полезен тем, кто уже работает с PostgreSQL и хочет больше разобраться в производительности базы данных. Здесь хорошо показано, как анализировать запросы, понимать execution plan и находить узкие места, которые замедляют работу приложения.
Оставляю ссылочку: GitHub 📱
Сидеть и работать в корпорации — страшно, жизнь-то мимо проходит. Уходить строить бизнес — страшно, а вдруг прогорит. Один из вариантов — разрабатывать свой пет-проект по вечерам. Многие успешные компании, например, Twitter, создавались именно так. Это не значит, что ваш проект обязательно заработает миллиарды, но заработать больше, чем в найме, и получить ценный опыт — вполне реально.
Перед началом разработки появляется множество вопросов, например:
• Как выбрать идею для пет-проекта?
• Что нужно знать про маркетинг
• Как запуститься и довести до первых продаж не имея бюджета на рекламу?
В телеграм-канале «Твой пет проект», Михаил Табунов делится своим опытом с разработчиками и менеджерами.
Он рассказывает, где искать идею для нового проекта, что нужно знать о маркетинге, как запустить стартап и привлечь первых 10 клиентов, а также о многих других важных вещах.
Подписывайтесь на «Твой пет проект», получайте пользу от практиков рынка!
/channel/+8Frwa03ciVlhNTky
📂 Напоминалка по MongoDB и шардированию!
Например, Query Router (mongos) принимает запросы от приложения и распределяет их по нужным шардам, а Replica Set внутри каждого shard обеспечивает отказоустойчивость и репликацию данных.
На картинке — базовая архитектура MongoDB Cluster: Client Application, Driver, Query Router, Config Server и Shards с Primary/Secondary-нодами.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
N+1 проблема в SQL: почему приложение внезапно начинает делать тысячи запросов!
Одна из самых частых проблем backend-приложений — N+1 queries. Особенно часто это появляется при работе через ORM, потому что код выглядит нормально, а реальные SQL-запросы скрыты внутри слоя абстракции.
Например, есть таблицы:
users(id, name)
orders(id, user_id, amount)
SELECT
id,
name
FROM users;
SELECT
id,
user_id,
amount
FROM orders
WHERE user_id = ?;
users, N запросов на получение orders. Это и есть классическая N+1 problem.users = User.objects.all()
for user in users:
orders = list(user.order_set.all())
print(orders)
SELECT для каждого user.order_set.all().JOIN:SELECT
u.id,
u.name,
o.id,
o.amount
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id;
SELECT
id,
user_id,
amount
FROM orders
WHERE user_id IN (?, ?, ?, ...);
batch loading даже эффективнее огромного JOIN, потому что JOIN может раздувать result set и создавать большое количество дублирующихся строк при one-to-many связях.User.objects.prefetch_related("order_set")
User.objects.select_related("profile")
prefetch_related() обычно используется для reverse FK и many-to-many; select_related() — для FK и OneToOne.select(User).options(
selectinload(User.orders)
)
users
→ orders
→ payments
→ items
🧐 PostgreSQL в Selectel — статьи, гайды и практические кейсы по PostgreSQL!
Это подборка материалов по PostgreSQL: настройка и администрирование баз данных, оптимизация запросов, репликация, резервное копирование, индексы, мониторинг и др. темы. Помимо теории, здесь много практических статей и кейсов, которые помогают лучше понять работу PostgreSQL и применять полученные знания.
📌 Оставляю ссылочку: selectel.ru
➡️ SQL Ready | #ресурс
📂 Напоминалка по проектированию систем!
Например, кэширование помогает ускорить чтение и снизить нагрузку на БД, а CDN уменьшает задержки для пользователей из разных регионов.
На картинке — 8 распространённых проблем проектирования систем и практические способы их решения: кэширование, балансировка нагрузки, репликация, шардинг, централизованное логирование и другие базовые архитектурные паттерны.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
Почему CHECK constraint может пропустить неправильные данные из-за NULL!
Редкая, но неприятная ловушка SQL: многие думают, что CHECK constraint требует, чтобы условие всегда было TRUE. Но это не так, CHECK запрещает только FALSE. А результат UNKNOWN — пропускается.
Именно поэтому nullable-колонки внутри CHECK могут вести себя не так, как ожидает разработчик.
Допустим, есть таблица товаров:
products(
id,
price,
discount
)
ALTER TABLE products
ADD CONSTRAINT chk_discount_price
CHECK (discount <= price);
INSERT действительно не пройдёт:INSERT INTO products(id, price, discount)
VALUES (1, 100, 150);
150 <= 100 даёт FALSE. А CHECK constraint запрещает строки, где выражение возвращает FALSE. Но дальше начинается важный нюанс SQL и трёхзначной логики.INSERT уже может пройти:INSERT INTO products(id, price, discount)
VALUES (2, 100, NULL);
INSERT INTO products(id, price, discount)
VALUES (3, NULL, 50);
CHECK отклонит такие строки. Но SQL работает иначе, если в сравнении участвует NULL, результатом становится не TRUE и не FALSE, а: UNKNOWN150 <= 100 -- FALSE
NULL <= 100 -- UNKNOWN
50 <= NULL -- UNKNOWN
NULL <= NULL -- UNKNOWN
CHECK constraint считает строку валидной, если результат выражения — TRUE или UNKNOWN. Запрещается только явно FALSE.CHECK сам по себе НЕ заменяет NOT NULL. Если колонка обязательная — это нужно указывать отдельно.CREATE TABLE products(
id bigint PRIMARY KEY,
price numeric NOT NULL,
discount numeric NOT NULL,
CONSTRAINT chk_discount_price
CHECK (discount <= price)
);
CHECK. Но в реальных системах скидка часто может отсутствовать. То есть NULL — это нормальное состояние: скидки нет.constraint лучше писать явно и читаемо:ALTER TABLE products
ADD CONSTRAINT chk_discount_price
CHECK (
discount IS NULL
OR discount <= price
);
price остаётся nullable: price numeric, то выражение: discount <= price снова может вернуть UNKNOWN. Например:discount = 50
price = NULL
50 <= NULL, будет UNKNOWN, а строка снова станет валидной.price numeric NOT NULL
CHECK (end_date >= start_date)
constraint гарантирует корректный диапазон дат. Но если end_date nullable, такой CHECK спокойно пропускает:end_date = NULL
start_date date NOT NULL,
end_date date NOT NULL,
CHECK (end_date >= start_date)
CHECK участвуют nullable-поля, constraint может пропускать строки из-за UNKNOWN, CHECK не заменяет NOT NULL. Для обязательных значений всегда нужен отдельный NOT NULL constraint.
PostgreSQL умеет пропускать заблокированные строки без ожидания!
Большинство знают SKIP LOCKED только для очередей через SELECT ... FOR UPDATE. Но мало кто использует его для параллельной пакетной обработки внутри PostgreSQL.
Если несколько воркеров одновременно обрабатывают огромную таблицу задач:
SELECT id
FROM jobs
WHERE processed = false
FOR UPDATE;
SKIP LOCKED процессы начинают ждать друг друга даже при наличии свободных строк.SELECT id
FROM jobs
WHERE processed = false
FOR UPDATE SKIP LOCKED;
UPDATE:WITH cte AS (
SELECT id
FROM jobs
WHERE processed = false
LIMIT 100
FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET processed = true
WHERE id IN (SELECT id FROM cte);
Антиджойн в SQL — как находить отсутствующие связи!
Одна из самых частых задач в аналитике — найти строки, для которых не существует связанных данных. Например, пользователей без заказов или товары без продаж.
Таблицы:
users(id, email)
orders(id, user_id)
NOT IN:id="x8d2qa"
SELECT *
FROM users
WHERE id NOT IN (
SELECT user_id
FROM orders
);
NULL, результат может стать пустым.NULL в SQL ломает сравнение NOT IN.NOT EXISTS:id="m4z7pk"
SELECT *
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);
LEFT JOIN:id="f1q9vc"
SELECT u.*
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
WHERE o.id IS NULL;
LEFT JOIN оставляет все строки users, а WHERE o.id IS NULL отбирает только те, где совпадений не нашлось.id="k6n2yb"
SELECT *
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM users u
WHERE u.id = o.user_id
);
foreign key.
NOT VALID constraints — как добавить CHECK и FOREIGN KEY на huge таблицу без долгой блокировки?
Обычно добавление CHECK или FOREIGN KEY на большую таблицу рискованная операция, потому что PostgreSQL начинает сразу сканировать все старые данные.
ALTER TABLE orders
ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id)
REFERENCES users(id);
NOT VALID:ALTER TABLE orders
ADD CONSTRAINT orders_price_check
CHECK (price > 0)
NOT VALID;
ALTER TABLE orders
VALIDATE CONSTRAINT orders_price_check;
VALIDATE CONSTRAINT не блокирует обычный concurrent DML как классический ALTER TABLE.FOREIGN KEY:ALTER TABLE orders
ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id)
REFERENCES users(id)
NOT VALID;
🖥 Разбираем методы управления пользователями и правами!В этой шпаргалке собраны ключевые команды для создания, изменения и удаления пользователей, назначения и отзыва прав, а также проверки текущих ролей и сессий. Они применяются при управлении безопасностью базы данных, настройке доступа и аналитической работе с ролями.
➡️ SQL Ready | #шпора
PostgreSQL умеет обновлять только реально изменившиеся строки и это может сильно сократить WAL и нагрузку!
Многие приложения делают UPDATE даже тогда, когда данные вообще не изменились.
UPDATE users
SET name = 'Alex'
WHERE id = 1;
name уже равен 'Alex', PostgreSQL всё равно создаст новую версию строки (MVCC), запишет WAL, обновит индексы и увеличит нагрузку на autovacuum.SELECT n_tup_upd
FROM pg_stat_user_tables
WHERE relname = 'users';
UPDATE, можно сравнивать старые и новые значения прямо в WHERE:UPDATE users
SET
name = $1,
email = $2
WHERE id = $3
AND (name, email) IS DISTINCT FROM ($1, $2);
IS DISTINCT FROM безопасно сравнивает даже NULL значения, в отличие от обычного !=:SELECT
NULL = NULL,
NULL IS DISTINCT FROM NULL;
WAL, bloat, количество HOT/non-HOT update и нагрузку на autovacuum без изменения архитектуры.
😎 SQL Cheatsheet — удобная шпаргалка для повседневной работы!
Этот репозиторий хорошо подойдёт тем, кто постоянно работает с базами данных и хочет быстро освежать в памяти нужные SQL-конструкции. Здесь всё подано компактно и по делу: запросы, JOIN’ы, агрегации, подзапросы и др. Особенно удобно использовать для подготовки к собеседованиям.
Оставляю ссылочку: GitHub 📱
PostgreSQL может избежать полной сортировки таблицы при ORDER BY ... LIMIT!
Большинство думает, что ORDER BY всегда сортирует вообще всю таблицу. Но в PostgreSQL для LIMIT есть отдельная оптимизация — Top-N Heap Sort.
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;
scan.execution plan:Sort Method: top-N heapsort
sort мог бы уйти на диск:SET work_mem = '4MB';
EXPLAIN ANALYZE
SELECT *
FROM events
ORDER BY created_at DESC
LIMIT 50;
work_mem PostgreSQL может избежать полной сортировки всех строк и работать заметно быстрее именно благодаря оптимизации Top-N.CREATE INDEX idx_events_created_at
ON events (created_at DESC);
❤️ SQL DEV — большая база для изучения SQL и PostgreSQL!
Здесь собрано огромное количество материалов по SQL: запросы, JOIN’ы, подзапросы, оконные функции, индексы, оптимизация и работа с PostgreSQL. Репозиторий отлично подходит как для изучения базы, так и для углубления в более сложные темы. Особенно полезно то, что здесь много практических примеров и разборов реальных SQL-конструкций.
Оставляю ссылочку: GitHub 📱