14500
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
Правило SQL про GROUP BY, которое должен знать каждый
Если ты выбираешь в SELECT неагрегированные колонки, добавь их в GROUP BY. Это не просто хорошая практика, в некоторых СУБД без этого будет ошибка.
Почему это важно? Потому что GROUP BY определяет, что именно значит одна строка результата. Базе нужно понимать, какое единственное значение положить в каждую ячейку этой строки.
В примере выше одна строка = должность (job_title) внутри отдела (department).
Поэтому и department, и job_title обязаны быть в GROUP BY.
Если неагрегированную колонку не добавить:
❌часть баз сразу упадет с ошибкой
❌другие молча вернут случайные значения
❌запрос может "работать" сегодня и врать завтра
Общее правило: Каждая колонка в SELECT должна быть функционально зависима от GROUP BY. Если внутри группы у колонки может быть несколько значений, она не может появляться в SELECT, пока:
- ты не добавил ее в GROUP BY, или
- не завернул в агрегатную функцию.
👉 @SQLPortal
То, что Postgres в одиночку не тянет, он может закрывать вместе с DuckDB.
Расширение pg_duckdb показывает, как две базы могут работать в связке: аналитические данные крутятся в DuckDB, а потом джойнятся с транзакционными данными в Postgres. Фишка в том, что расширение встраивает DuckDB прямо внутрь Postgres. DuckDB буквально запускается внутри процесса Postgres.
Как это работает (см. диаграмму):
1. Приложение отправляет запрос в Postgres, который джойнит данные портфеля (Postgres) с историческими рыночными данными (lakehouse)
2. pg_duckdb видит доступ к lakehouse и делегирует выполнение DuckDB
3. DuckDB подтягивает нужные рыночные данные из lakehouse, читает таблицы Postgres и делает join
4. Postgres возвращает приложению финальный результат
👉 @SQLPortal
В Postgres 18 появилась новая фича для ограничений по временным диапазонам: WITHOUT OVERLAPS. Это прям то, что нужно для систем бронирований, и в целом заметно проще, чем прошлые решения.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE staff_schedule (
staff_id int,
shift_duration tstzrange,
task_description text,
PRIMARY KEY (staff_id, shift_duration WITHOUT OVERLAPS)
);
PostgreSQL по умолчанию не страхует тебя от забытых транзакций, idle-сессий и улетевших в бесконечность запросов: все это тихо накапливается, пока база не начинает казаться "залипшей".
Если мигрируешь на PostgreSQL, эти 5 параметров таймаутов критично настроить
👉 @SQLPortal
SQL тренажеры для практики
- sql-academy.org
- sql-ex.ru
- schoolsw3.com
- SQL Fiddle
- sqltest.online
- Oracle LiveSQL
- stratascratch.com
- stepik.org (Интерактивный тренажер SQL)
- sql-practice.com
- pgexercises.com
- HackerRank
- sqlzoo.net
👉 @SQLPortal
Менеджер баз данных с редактором SQL и искусственным интеллектом: Dataflare
👉 @SQLPortal
JSON, вставка данных.
Ты можешь изменять существующие JSONB-объекты с помощью функции jsonb_set(). Она возвращает новый JSONB-объект, где значение установлено по указанному пути ключей (keypath). Если по этому пути не хватает промежуточных объектов, она их создаст.
👉 @SQLPortal
SELECT * FROM users LIMIT 100
SQL: "Не говори больше ничего, вот вся продакшен-база данных"
👉 @SQLPortal
Alibaba выложили в опенсорс AliSQL (на базе MySQL 8.0.44) с встроенным движком DuckDB. Теперь аналитические нагрузки можно гонять прямо в MySQL — достаточно переключить storage engine на DuckDB.
👉 @SQLPortal
Indexing
Postgres реально хорош в индексации JSON: для этого обычно используют GIN, чтобы индексировать поле целиком.
У некоторых есть небольшой перфоманс-трик: добавить GIN-индекс с jsonb_path_ops. Это вариант для схем JSON, которые не меняются, и работает он только с @> (contains). Зато может быть быстрее и занимать меньше места, чем обычный GIN-индекс.
👉 @SQLPortal
В Postgres есть специальные операторы, которые делают запросы к JSON-данным простыми и быстрыми.
Базовые операторы:
-> извлечь как json
--> извлечь как текст
0, 1, 2 ... извлечь по смещению (offset)
@> левый операнд содержит правый
<@ правый операнд содержит левый
? ключ верхнего уровня
👉 @SQLPortal
psql-лайфхак:
\e откроет твой дефолтный текстовый редактор, чтобы написать SQL или команды. Сохраняешь файл, закрываешь, и psql автоматически выполнит то, что ты написал.
🛌🛌🛌
👉 @SQLPortal
OUTER JOIN или внешнее соединение позволяет возвратить все строки одной или двух таблиц, которые участвуют в соединении.
Outer Join имеет следующий формальный синтаксис:
SELECT столбцы
FROM таблица1
{LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1
[{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]...
CREATE TABLE Products
(
Id SERIAL PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Company VARCHAR(20) NOT NULL,
ProductCount INTEGER DEFAULT 0,
Price NUMERIC NOT NULL
);
CREATE TABLE Customers
(
Id SERIAL PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id SERIAL PRIMARY KEY,
ProductId INTEGER NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INTEGER NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL,
ProductCount INTEGER DEFAULT 1,
Price NUMERIC NOT NULL
);
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders LEFT JOIN Customers
ON Orders.CustomerId = Customers.Id;
PL/pgSQL делает PostgreSQL чем-то большим, чем просто средством для запросов
пример функции, которая принимает массив структур с данными и внутри Postgres делает цикл и инсертит каждую запись — без участия приложения.
CREATE OR REPLACE FUNCTION public.insert_user_activities (
param_udt_storefrontactivities udt_storefront_activities[]
) RETURNS void LANGUAGE plpgsql AS $function$
DECLARE
var_activity udt_storefront_activities;
BEGIN
-- Insert user activities from the provided array
IF param_udt_storefrontactivities IS NOT NULL THEN
FOREACH var_activity IN ARRAY param_udt_storefrontactivities
LOOP
INSERT INTO tbl_storefront_activities (
providerid,
activitycode,
userid,
orderid,
notes,
createddate
)
VALUES (
var_activity.providerid,
var_activity.activitycode,
var_activity.userid,
var_activity.orderid,
var_activity.notes,
CURRENT_TIMESTAMP
);
END LOOP;
END IF;
END;
$function$
У ClickHouse появился managed-сервис Postgres.
Теперь прям очевидно, почему ClickHouse так упарывались в расширение pg_clickhouse.
Конкуренция станет еще жарче: Snowflake + Crunchy, Databricks + Neon и TigerData.
Lakehouse-платформам нужны транзакционные данные, и Postgres уже должен быть провайдером номер один.
Рад видеть, как тема Postgres для аналитики получает больше внимания и инвестиций. Для Postgres это плюс.
Ссылка: https://clickhouse.com/cloud/postgres
👉 @SQLPortal
Postgres бывает реально коварным, когда в тексте намешан разный регистр. Для поиска и индексации такого текста есть расширение citext, оно в некоторых случаях помогает. И это поле/тип тоже можно индексировать обычным b-tree.
- запросы могут находить результаты в любом регистре и в любой комбинации
- обычные text и varchar нельзя проиндексировать как регистронезависимые, а citext можно
- также помогает с UNIQUE-ограничениями на текст, когда на вход прилетает разный регистр
-- Подключаем расширение
CREATE EXTENSION IF NOT EXISTS citext;
-- Создаем таблицу с типом citext
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name CITEXT UNIQUE NOT NULL,
description TEXT
);
``` :contentReference[oaicite:0]{index=0}
::contentReference[oaicite:1]{index=1}
Неиспользуемые индексы в PostgreSQL: риски, обнаружение и безопасное удаление
Индексы нужны, чтобы ускорять доступ к данным. Они позволяют PostgreSQL избегать full scan по таблице и заметно сокращают время выполнения запросов в read-heavy нагрузках.
По реальному прод-опыту мы видели, что грамотно спроектированные, точечные индексы могут ускорять запросы в 5 раз и больше, особенно на больших транзакционных таблицах.
Но индексы не бесплатны.
В этом посте разберем, какие проблемы могут создавать неиспользуемые индексы и как удалять их в проде безопасно, с планом отката.
1. Почему большие неиспользуемые индексы со временем становятся проблемой
Со временем такие индексы могут незаметно ухудшать производительность базы. Вот типовые эффекты, которые встречаются в проде.
1.1. Медленнее INSERT, UPDATE и DELETE
Каждая запись должна обновить все индексы таблицы, включая те, которыми запросы никогда не пользуются.
1.2. Больше нагрузки на VACUUM и autovacuum
В индексах так же копятся мертвые tuple, как и в таблицах. Их тоже нужно вакуумить, из-за чего растут I/O и время работы vacuum.
1.3. Дольше окна обслуживания
Операции вроде VACUUM и REINDEX выполняются тем дольше, чем больше индексов и чем они крупнее.
1.4. Пустая трата диска и “засорение” кеша
Большие неиспользуемые индексы едят место на диске и могут вытеснять полезные данные из shared_buffers, снижая эффективность кеша.
Поэтому периодически находить и аккуратно удалять неиспользуемые индексы в проде реально полезно, но только через контролируемый и хорошо проверенный процесс.
2. Как безопасно удалить неиспользуемые индексы в PostgreSQL
Ниже чеклист в стиле “можно в прод”, который стоит пройти перед удалением любого индекса.
2.1. Проверь, когда в последний раз сбрасывали статистику
Если статистику недавно reset’нули, индекс может выглядеть “неиспользуемым”, хотя он нужен под нагрузкой.
SELECT
datname,
stats_reset
FROM pg_stat_database
WHERE datname = current_database();
stats_reset (или NULL, то есть статистику никогда не сбрасывали) дает больше доверия метрикам использования индексов.SELECT
i.relname AS index_name,
c.conname AS constraint_name,
c.contype AS constraint_type,
c.conrelid::regclass AS table_name
FROM pg_constraint c
JOIN pg_class i ON i.oid = c.conindid
WHERE i.relname = '<IDX_NAME>';
SELECT
s.indexrelname AS index_name,
s.relname AS table_name,
s.idx_scan,
s.idx_tup_read,
s.idx_tup_fetch
FROM pg_stat_user_indexes s
WHERE s.indexrelname = '<IDX_NAME>';
0.SELECT pg_get_indexdef('<IDX_NAME>'::regclass) AS create_index_sql;DROP INDEX CONCURRENTLY не блокирует чтение и запись по таблице, поэтому подходит для прода.DROP INDEX CONCURRENTLY <IDX_NAME>;
Индексация без простоя с CONCURRENTLY
Создание индекса может залочить таблицу, из-за чего она фактически становится недоступной и приложение/пользователи начинают получать ошибки.
Если добавить CONCURRENTLY, Postgres соберет индекс конкурентно, в фоне. Это займет чуть больше времени, зато пользователи вообще не заметят, что индекс строится.
👉 @SQLPortal
SQL tip:
Обновление значений в таблице через коррелированный подзапрос.
👉 @SQLPortal
Радим Марек: Введение в буферы в PostgreSQL
Про то, как PostgreSQL держит данные в памяти и почему от этого сильно зависит производительность
👉 @SQLPortal
Колонарное хранилище vs построчное (row store) наглядно
Берем простую агрегацию и сравниваем, как она выполняется в row store и columnar store.
В row store данные лежат строками, поэтому движку приходится читать каждую строку целиком, чтобы из нее вытащить значение колонки age.
В columnar store данные лежат по колонкам, поэтому он читает сразу только значения колонки age, без трогания остальных полей и без лишнего I/O.
👉 @SQLPortal
На Stepik вышел курс по Linux
Этот курс закрывает всю обязательную Linux-базу для работы в IT: от установки Linux и работы с файлами до сетей, прав, дисков, процессов, автоматизации на Bash и многого другого. Всё сразу закрепляется на практике (200+ заданий с автопроверкой)
Материал подаётся понятным языком, шаг за шагом, на реальных примерах и с наглядными схемами
После прохождения вы получите сертификат, который можно добавить в резюме.
Есть бесплатные демо-уроки для ознакомления. В ближайшие 48ч курс доступен со скидкой 25% по промокоду «POST25»: открыть курс на Stepik
Вчера мы говорили про GIN-индексы, но они не поддерживают ORDER BY. В некоторых ограниченных случаях можно использовать выраженные индексы на JSON-выборках, которые просто фиксируют текстовое значение по заданному keypath (пути к ключу).
CREATE INDEX idx_state
ON users ((details->'location'->>'state'));
{ "name": "David", "location": { "city": "Lawrence", "state": "KS" } }
Yandex B2B Tech запустили Managed Sharded PostgreSQL (SPQR) – сервис для горизонтального масштабирования самой популярной опенсорс БД в мире. Теперь банкам и ритейлу станет проще обрабатывать миллионы транзакций в реальном времени и без боли масштабировать свои системы по мере роста нагрузок.
В стандартном PostgreSQL горизонтального масштабирования нет, а при больших объёмах данных без него уже никуда. Шардированный PostgreSQL позволяет ускорять запуск новых продуктов в 3–4 раза, упрощает сопровождение и снижает операционные риски – инженерам не нужно вручную собирать и поддерживать сложную инфраструктуру.
Технология уже обкатана в проде – её используют Яндекс ID, Яндекс Пэй и Едадил, стабильность подтверждена и внешними компаниями. PostgreSQL сегодня выбирают 55,6% профессиональных разработчиков, а Managed Service for PostgreSQL в Yandex Cloud обслуживает уже больше 5 тысяч клиентов.
В итоге: быстрее time-to-market, меньше затрат на разработку при запуске высоконагруженных продуктов
👉 @SQLPortal
Все надоело и пропал интерес, чувствуешь себя амебой и хочется только залипать в телефоне. Бывает?
Homo Manifestans — канал для айтишников, у которых периодически опускаются руки и отключается мозг, ибо переработки и постоянная тревожность не приводят к другим исходам 🤗
✓ Как научиться отвлекаться от работы и отдыхать?
✓ Как совместить кучу рабочих задач и время с семьей?
✓ Как справиться с прокрастинацией?
✓ Как не растерять запал, даже если начальник и коллеги 💩 и кажется, что ничего не выходит?
Подписывайтесь на канал @vadimpetrovpsi и научитесь работать без упахивания, выгорания и ущерба для личной жизни!
Псс. Заходите в закреп — там много полезного, и даже бесплатный мини-курс по выходу из апатии:
👉 /channel/+q40Dycj29QJhYmFi
MySQL 8.4.8 теперь доступен!
MySQL 8.4.8 (версия Long-Term Support, LTS) приносит несколько улучшений:
✅Улучшения InnoDB Redo Logging: сообщения об ошибках теперь включают текущий Log Sequence Number (LSN) и детали про емкость redo log, что дает более глубокое понимание работы redo log.
✅Встроенная библиотека OpenSSL обновлена до версии 3.0.18.
✅Как обычно, релиз закрывает разные баги, включая race conditions в InnoDB и проблемы с очисткой binary log (purging), чтобы улучшить общую стабильность и производительность.
Подробнее см. Release Notes
👉 @SQLPortal
JSON types in Postgresjson
* текст
* без дубликатов ключей
* не индексируется
* парсится во время выполнения, поэтому медленнееjsonb
* бинарный формат, заранее распарсен
* без дубликатов ключей
* индексируется
* лучше по производительности запросов
👉 @SQLPortal
Нашел интересную статью о внутреннем устройстве Postgres
👉 @SQLPortal
Мифическая база данных MySQL постепенно затухает...😭
Уже больше 4 месяцев в её публичном репозитории на GitHub не было ни единого коммита. Плохой сигнал.
Пауза совпала с сентябрьскими сокращениями в Oracle в команде, которая этим занимается.
Это не значит, что MySQL исчезнет. Она стоит на миллионах серверов и будет работать ещё годами.
Но это вполне ясный маркер чего-то более глубокого:
MySQL больше не конкурирует как open-source сообщество, а живёт как чисто коммерческий продукт.
Меньше прозрачности, другие приоритеты.
Обгон со стороны PostgreSQL уже неизбежен.
👉 @SQLPortal
PlanetScale показали pg_strict для Postgres.
Идея простая: это расширение, которое добавляет в Postgres “страховочную сетку” и ловит опасные запросы до того, как они реально выполнятся.
У всех бывало: хотел поправить одну строку, а всё уехало в UPDATE без WHERE. Или случайный DELETE, или “быстренько проверю миграцию” на не той базе. В итоге урон не от бага в коде, а от одной команды в psql.
Что обещает pg_strict:
- проверять запросы заранее
- блочить потенциально опасные операции
- спасать прямо на уровне базы, а не уже после инцидента
👉 @SQLPortal