14500
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
Этот курс по PostgreSQL для начинающих поможет понять, подходит ли она для ваших проектов.
В курсе разбираются оператор SELECT, выражения WHERE, агрегатные функции и другие темы.
https://freecodecamp.org/news/posgresql-course-for-beginners/
👉 @SQLPortal
Учись работать с высокоуровневой аналитикой производительности, такой как коэффициент попаданий в кэш, на учебном сайте:
https://crunchydata.com/developers/playground/high-level-performance-analytics
👉 @SQLPortal
Talk to DBs
Вышел AI-инструмент, который позволяет общаться с базами данных на обычном языке
→ Загружаешь URL своей базы
→ Задаёшь любой вопрос
→ Получаешь SQL-запрос, результаты и пояснения
https://github.com/Arindam200/awesome-ai-apps/tree/main/simple_ai_agents/talk_to_db
👉 @SQLPortal
Старые приёмы работы с Postgres которые могут быть полезны. Пример функция soundex
Можно написать поиск по именам которые звучат как Jon — их не обязательно писать так же или иметь совпадающую триграмму
SELECT first_name, last_name
FROM customers
WHERE soundex(first_name) = soundex('Jon');
Soundex — это специальный алгоритм для английского произношения. Он берёт первую букву слова и оставляет её в качестве якоря. Остальную часть слова он упрощает сгруппировав согласные в несколько общих категорий звуков. Например согласные которые произносятся с помощью губ как B и P считаются одной группой. Гласные (A E I O U) и мягкие согласные вроде H и W полностью игнорируются так как они мало влияют на основное звучание. В итоге получается стандартизированный код из четырёх символов. Запрос ищет другие совпадения с таким же четырёхсимвольным кодомSoundex заметно слабо работает с текстом на других языках так что с реализацией стоит быть аккуратным. Но он может помочь в некоторых задачах поиска и сортировки особенно если у вас ещё нет ИИ или эмбеддинговsoundex, полнотекстовый поиск и нечеткое сравнение строк читайте здесь: ссылка
Я сторонник того, чтобы всегда выбирать инструмент под конкретную задачу
Как бы я ни любил Postgres и MySQL, они заточены под OLTP-нагрузку. А вот когда нужны аналитика или работа с временными рядами — ClickHouse отлично подходит 🤵
На выходных почитал их статью — море интересных инженерных решений
👉 @SQLPortal
Инструмент для анализа данных на базе AI от Microsoft 🤖
Представляем LLM-powered no-code-инструмент, который преобразует данные и строит крутые визуализации.
Ключевые фичи
🔸Преобразование данных с помощью AI
🔸Интерактивный UI
🔸Ввод на естественном языке
Ссылка на GitHub-репозиторий - https://github.com/microsoft/data-formulator
👉 @SQLPortal
Часто приходится ковыряться в терминале с CSV или JSON, а стандартные CLI-инструменты всё превращают в кашу.
Tabiew — open-source тулза для комфортного просмотра таблиц прямо в терминале. Есть Vim-стиль управления и SQL-запросы к данным.
Форматы: CSV, Parquet, JSON, Excel и прочее. Можно быстро фильтровать, искать, листать и даже строить графики.
Фишки
🔸Поддержка CSV, Parquet, Arrow, Excel и др.
🔸SQL-фильтрация и анализ прямо в терминале
🔸Vim-подобные хоткеи
🔸Поиск на лету и мульти-табличные операции
🔸Темы Monokai, Nord и др.
🔸Лёгкий, ест даже огромные файлы без лагов
👉 @SQLPortal
Бросок кубика определяет исход атаки в D&D.
Поэтому знание распределения результатов бросков помогает выбрать правильное действие.
При этом нужно учитывать преимущество , помеху и удачу.
Sean D. Stuber показывает, как сделать это в Oracle SQL.
https://seanstuber.com/2025/07/29/using-sql-to-derive-dice-distributions-in-dnd/
👉 @SQLPortal
Что такое pg_partman и что он добавляет к встроенному в Postgres партиционированию?
В Postgres из коробки есть нативное партиционирование:
- Декларативный синтаксис партиционирования в CREATE TABLE
- Автоматическое отсечение партиций (partition pruning), когда запросы исключают нерелевантные партиции
- Прозрачные DML-операции — INSERT/UPDATE/DELETE работают по всем партициям без дополнительной обработки
Что добавляет pg_partman:
- Автоматически создаёт новые партиции по расписанию
- Автоматически удаляет старые партиции по времени или количественным правилам
- Фоновый воркер для выполнения обслуживания без внешних заданий
https://github.com/pgpartman/pg_partman
👉 @SQLPortal
Если ты хочешь изучить SQL или погрузиться в аналитику, начинай с Mode SQL Tutorial. У них всё работает прямо в браузере - никаких заморочек с установкой СУБД.
Что тебя ждёт:
— Basic SQL — SELECT, WHERE, ORDER BY, LIMIT и логические операторы;
— Intermediate SQL — агрегаты, GROUP BY, HAVING, JOINы;
— Advanced SQL — window functions, подзапросы, pivot’ы и performance-tuning;
— SQL Analytics Training — реальные кейсы аналитики, включая A/B тестирование и инсайты продукта.
👉 @SQLPortal
JSON-реляционные duality views в Oracle Database 23ai — это гибкий, производительный и согласованный способ доступа к данным как:
- документам,
- реляционным таблицам,
- и к обоим одновременно 💊
anders__swanson показывает, как создать базовые duality views для этого.
👉 @SQLPortal
Amazon Aurora стала первой OLTP базой данных, которая популяризировала разделение хранения данных и вычислительных ресурсов.
Главным новшеством стала переработка слоя хранения с целью минимизировать операции записи (write I/O) для преодоления задержек, связанных с сетью.
Как бы круто это ни было, она всё же не может превзойти локальные NVMe SSD.
Ссылка на статью:
https://pages.cs.wisc.edu/~yxy/cs764-f20/papers/aurora-sigmod-17.pdf
На видео показана PlanetScale Metal на AWS против Aurora. Те же CPU и объем памяти при I/O-насыщенной нагрузке, похожей на TPCC.
Значительно выше QPS при при этом в 3 раза ниже p99 задержка
👉 @SQLPortal
Новая фича в PostgreSQL 17 — геймчейнджер
Эта новая возможность в PostgreSQL 17 настоящая революция.
🔸Механика страниц в PostgreSQL
PostgreSQL, как и большинство СУБД, работает с страницами фиксированного размера.
Практически всё — таблицы, индексы и т.д. хранится в виде страниц размером 8 КБ.
Каждая страница содержит:
- строки таблицы или кортежи индекса
- фиксированный заголовок
Страницы это просто байты в файлах. Они читаются и кэшируются в буферном пуле
Примеры чтения страниц:
- Страница 0: read(offset=0, size=8192)
- Страница 1: read(offset=8193, size=8192)
- Страница 7: read(offset=57345, size=8192)
Если таблица занимает 100 страниц, то для full table scan придётся выполнить 100 системных вызовов.
А каждый системный вызов имеет накладные расходы.
🔸Что изменилось в Postgres 17
Добавили возможность объединения I/O:
- Можно указать, сколько страниц читать за один системный вызов
- Теоретически можно просканировать всю таблицу за один системный вызов
Это не всегда хорошая идея, подробнее об этом ниже.
Также добавили поддержку векторизованного I/O:
- Используется системный вызов preadv
- Он принимает массив оффсетов и длин — удобно для произвольного чтения
🔸Проблема: не читать лишнего
Допустим, мы делаем seq scan, чтобы найти значение:
1. Читаем страницу 0
2. Находим нужное → выходим
3. Остальные страницы не нужны
Теперь с новой фичей:
- Мы читаем сразу 10 страниц одним I/O
- Загружаем всё в shared buffers
- А нужное было уже на первой странице
> Результат — потрачены ресурсы (диск, память) впустую
🔸Как это сбалансировать
Нужно будет находить баланс:
- Между эффективным I/O
- И избежанием избыточного чтения
🔸Примечание
PostgreSQL действительно вызывает системный вызов ядра, чтобы читать по 8 КБ за раз.
Но ядро может быть настроено на read-ahead — заранее читать больше и кэшировать это в файловом кэше ОС.
👉 @SQLPortal
Изучаем базы данных
Дорожная карта, которая поможет глубже разобраться в мире СУБД
👉 @SQLPortal
Самый частый вопрос на собеседованиях по SQL:
Как обработать дубликаты?
Вот простой способ:
I. Использовать COUNT(*)
II. Сделать GROUP BY по полю, которое должно быть уникальным
III. Добавить HAVING > 1 (как WHERE, но для сгруппированных данных), чтобы показать строки, встречающиеся более одного раза
👉 @SQLPortal
Алгоритм хэш-соединения
1. Строит хэш-таблицу по колонкам соединения меньшей таблицы.
2. Читает большую таблицу и проверяет, хэшируются ли её колонки соединения в таблицу, построенную на шаге 1.
Сложность растёт линейно от размеров таблиц: O(n + m)
Vlad Mihalcea объясняет здесь
👉 @SQLPortal
Doom в SQL уже здесь
Представьте себе шутер уровня AAA, но… целиком построенный на SQL-запросах.
Вся игровая реальность живёт в таблицах базы данных.
Герой двигается и стреляет не с клавиатуры, а через запросы.
Даже такие штуки как raycasting, отрисовка спрайтов, работа с HUD и обработка видимости — реализованы с помощью VIEW.
Короче, хотите сделать хэдшот, то пишите правильный SELECT. 🎯
https://github.com/cedardb/DOOMQL
👉 @SQLPortal
libpq deep dive День 2 - переменные окружения Postgres
Когда вы подключаетесь к Postgres, соединение под капотом ведет libpqlibpq проверяет параметры подключения в таком порядке приоритета
1. Явные параметры подключения psql postgres://postgres:flXEXcSpx@p.qz
Это может быть формат URL как выше или отдельные флаги команды вроде -h <host> или -p <port> psql преобразует свои флаги командной строки в параметры подключения для одной из процедур libpq
2. Переменные окружения
Если нужного параметра подключения не найдено, libpq затем смотрит переменные окружения операционной системы. Это позволяет разработчикам приложений задавать переменные вроде PGHOST, PGUSER, PGDATABASE и получать эти значения в любом приложении, которое использует libpq
Многие ORM и драйверы тоже используют libpq для хранения и поддержки соединения. Схема такая Наше приложение или ORM → драйвер обертка → libpq → PostgreSQL
Когда драйвер вроде psycopg2 который линкуется с libpq инициализирует соединение, он автоматически ищет переменные типа PGHOST, PGUSER и тд в текущем окружении и использует их
3. Значения по умолчанию
Если настройка не указана ни в строке подключения ни в переменной окружения, libpq использует зашитые при компиляции дефолты например подключение к localhost под текущим пользователем ОС
Поэтому psql postgres просто подключится к вашему localhost
👉 @SQLPortal
Разбор libpq \ Что такое libpq
Просто потому что мы любим Postgres, мы решили посвятить эту неделю разбору libpq
Возможно, ты видел упоминание libpq в доках или слышал этот термин от других разработчиков. Но что это за библиотека и зачем она вообще нужна
libpq — это клиентская библиотека на C, которая позволяет тебе или твоему приложению общаться с сервером PostgreSQL. По сути, это базовый мост между тобой, твоим кодом и базой данных
Представь, у тебя есть приложение — это может быть веб-приложение, скрипт для анализа данных, консольный инструмент вроде psql или любая другая софтина, которой нужно ходить в базу
Твой клиент обычно не умеет напрямую говорить на сетевом протоколе PostgreSQL. Вместо этого он вызывает функции libpq — чаще всего через драйвер базы для твоего языка. libpq в этом случае играет роль прослойки или «переводчика» на стороне клиента. Она предоставляет API с набором функций, которые приложение может использовать, чтобы отправлять запросы и получать ответы
Когда твое приложение через libpq отправляет запрос на подключение к Postgres, его принимает Postmaster. Postmaster проверяет аутентификацию, и если всё ок, он поднимает новый отдельный процесс на сервере, который будет обслуживать только твое соединение. Postmaster передает этому процессу сетевое подключение, и дальше libpq общается уже напрямую с ним (Postmaster в этом не участвует). Все SQL-запросы, обращения к данным и ответы идут между libpq и этим выделенным backend-процессом на протяжении всей сессии
👉 @SQLPortal
Кто сказал, что на SQL нельзя делать ресерч по рынку?
Этот запрос вытаскивает для каждой FAANG-компании месяц с пиком и месяц с минимумом цены открытия
Задача из Bloomberg SQL
У тебя есть данные по акциям FAANG (Facebook, Amazon, Apple, Netflix, Google) и надо найти месяц и год, когда у каждой бумаги был
- максимальный прайс открытия
- минимальный прайс открытия
при этом даты должны быть в формате Mon-YYYY
Таблица: stock_prices
Хранит дневные котировки
- date — дата в формате типа 01/31/2023
- ticker — тикер, например AAPL
- open, high, low, close — цены открытия, максимума, минимума, закрытия
Разбор запроса
1. CTE month_open
Группируем данные по месяцу и тикеру
Считаем
- max_open — максимальная цена открытия за месяц по тикеру
- min_open — минимальная цена открытия за месяц по тикеру
2. CTE high
Для каждого тикера берём строку с самым высоким значением open
Используем DISTINCT ON (ticker), чтобы оставить только один месяц с максимумом
Результат
- ticker
- high_mth — месяц с пиком открытия
- high_open — сам пик цены открытия
3. CTE low
То же самое, только для минимума
Результат
- ticker
- low_mth — месяц с минимальным открытием
- low_open — минимальная цена открытия
4. JOIN high и low по ticker
На выходе по каждой бумаге месяц+значение максимума и месяц+значение минимума
👉 @SQLPortal
Манипуляции с JSON в PL/SQL с использованием объектных типов, добавленных в версии 12.2
JSON_ELEMENT_T
JSON_SCALAR_T
JSON_OBJECT_T
JSON_ARRAY_T
get, put и remove можно получать, добавлять и удалять значения из JSON-объектов и массивов
Postgres имеет надёжную систему восстановления Point in Time Recovery. Это одна из тех вещей, которые кажутся ненужными… но не стоит заблуждаться. Речь здесь не только о восстановлении - это про более качественные бэкапы и лучшее тестирование миграций.
🔸Что такое PITR?
PITR это как машина времени для вашей базы данных. Если обычные снапшоты это как фотография вашего дома раз в день, то PITR это как камера видеонаблюдения, записывающая каждое изменение. Point in Time Recovery позволяет откатиться к любому моменту времени в базе и создать восстановленную копию именно на этот момент.
🔸WAL
Postgres использует Write-Ahead Log (WAL) для поддержки PITR. Каждое изменение в базе сначала записывается в WAL-файлы, прежде чем попадёт в основные файлы данных. Постоянно архивируя эти WAL-файлы, вы можете воспроизвести любую последовательность изменений и восстановить базу на любой момент времени. Все продвинутые системы бэкапов, которые реализуют PITR — такие как pgBackRest, WAL-G, WAL-E — используют поток WAL для восстановления к конкретной точке времени.
🔸Можно ли восстановиться к абсолютно любому моменту?
Если у вас есть полный базовый бэкап, от которого вы начинаете восстановление, и все WAL-архивы, накопленные с этого момента, вы сможете создать кластер, полностью согласованный с состоянием Postgres на любой выбранный момент времени.
Но у нас маленькая команда и простое приложение...
Postgres DOMAINs… лучше, чем CONSTRAINTs?DOMAIN позволяет задать конкретную проверку значений.
Например, если я хочу убедиться, что все даты рождения больше 1 января 1930 года, а все email-адреса валидны, я могу создать вот так:
CREATE DOMAIN date_of_birth AS date
CHECK (value > '1930-01-01'::date)
;
CREATE DOMAIN valid_email AS text
NOT NULL
CHECK (value ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
;
date_of_birth и valid_email соответствующим колонкам.CREATE TABLE person_using_domains
(
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
birth_date date_of_birth,
email valid_email
);
DOMAIN, если можно просто навесить CHECK constraint прямо на колонку? CHECK constraints не так просто изменять — их нужно удалять и создавать заново.DOMAIN можно создать на уровне схемы, и если в нескольких таблицах есть колонки с email или датой рождения, можно использовать один DOMAIN для управления всеми этими полями, тем самым централизуя логику.
Партиционирование таблиц в Postgres — это только для огромных таблиц? Вовсе нет.
Да, оно отлично подходит для обслуживания больших таблиц.
Но это ещё и полезный инструмент для управления данными и архивирования по мере роста базы.
Партиционирование разбивает одну большую таблицу на несколько меньших — по ключу партиционирования.
Например, по дате: отдельная таблица на каждый месяц, или по какому-нибудь полю вроде региона заказа.
Партиционирование помогает:
- Ускорить запросы — Postgres ищет только в нужном партиционе, а не во всей таблице
- Снизить блокировки, нагрузку на WAL и CPU во время обслуживания
- Упростить и структурировать архивирование
Пример
Представим, что у вас есть таблица с 500K заказов.
Если просто сделать DELETE за Q1 2023:
- блокируются строки на несколько секунд
- создаётся большое количество WAL
- место освобождается не сразу
Возможно, придётся выполнять такую очистку вне продакшн-часов, чтобы не мешать работе приложения.
Но если таблица партиционирована по дате, вы можете:
- архивировать данные
- удалить нужную партицию (DROP TABLE ...)
Это не затронет активные данные и сразу освободит место.
Думаете, для партиционирования придётся переписывать приложение?
Обычно — нет.
Большинство приложений и ORM:
- работают с прозрачным партиционированием без проблем
- видят всё как одну таблицу
- а Postgres сам направляет запросы в нужные партиции
Всё работает без изменений в коде.
👉 @SQLPortal
6 структур данных для экономии места в базе данных
Фильтр Блума
Снижает стоимость поиска, проверяя, является ли элемент членом множества.
HyperLogLog
Оценивает количество уникальных элементов.
Cuckoo filter
Проверяет, принадлежит ли элемент множеству.
Minhash
Находит сходство между множествами.
SkipList
Эффективно ищет, вставляет и удаляет элементы из отсортированного списка.
Count-min sketch
Находит частоту элементов в большом потоке данных.
База данных имеет право выполнять части SQL-выражения WHERE в любом порядке.
Так как же избежать ошибок, если известно, что выражение может выбросить ошибку,
например, извлечение квадратного корня из отрицательного числа?
Используй CASE, чтобы выполнять вычисление только для допустимых значений:
WHERE CASE
WHEN col > 0 THEN SQRT(col)
ELSE ...
END ...
Интерактивная тренировка SQL
Если хочешь прокачать SQL на практике без установки СУБД и настройки окружения заходи на sqltest.online
👉 @SQLPortal
Новичок в использовании pg_stat_statements для анализа производительности запросов в Postgres?
Попробуй этот простой веб-учебник:
https://crunchydata.com/developers/playground/query-performance-analytics
💡Там есть пара отличных запросов, которые стоит сохранить для дальнейшего использования.
👉 @SQLPortal
Подготовленные выражения / параметризованные запросы в Postgres
Подготовленные выражения — это повторно используемые SQL-запросы, которые позволяют обойти стадию планирования, повторно используя уже построенный план выполнения и подставляя разные данные.
Полноценный SQL-запрос в Postgres проходит через следующие этапы:
- Парсинг — преобразование в внутреннее представление;
- Переписывание / трансформация — применение правил переписывания запроса, создание внутренних представлений (например, CTE или view);
- Планирование — планировщик Postgres использует статистику по таблицам и другую информацию, чтобы выбрать оптимальный план выполнения;
- Выполнение — непосредственный запуск запроса.
Подготовленные выражения используют тот же SQL и сохраняют первые три шага. Это значит, что при выполнении остаётся только извлечь данные — Postgres может полностью пропустить стадию планирования.
Пример
Подготовить запрос и сохранить план выполнения
PREPARE get_user_by_email (text) AS
SELECT * FROM users WHERE email = $1;
EXECUTE get_user_by_email('alice@test.com');EXECUTE get_user_by_email('bob@test.com');DEALLOCATE get_user_by_email;
User.where("email = ?", "alice@test.com")cur.execute("SELECT * FROM users WHERE email = %s", ("alice@test.com",))