6 структур данных для экономии места в базе данных

Фильтр Блума
Снижает стоимость поиска, проверяя, является ли элемент членом множества.

HyperLogLog
Оценивает количество уникальных элементов.

Cuckoo filter
Проверяет, принадлежит ли элемент множеству.

Minhash
Находит сходство между множествами.

SkipList
Эффективно ищет, вставляет и удаляет элементы из отсортированного списка.

Count-min sketch
Находит частоту элементов в большом потоке данных.

👉 @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

Что такое pg_partman и что он добавляет к встроенному в Postgres партиционированию?

В Postgres из коробки есть нативное партиционирование:
- Декларативный синтаксис партиционирования в CREATE TABLE
- Автоматическое отсечение партиций (partition pruning), когда запросы исключают нерелевантные партиции
- Прозрачные DML-операции — INSERT/UPDATE/DELETE работают по всем партициям без дополнительной обработки

Что добавляет pg_partman:
- Автоматически создаёт новые партиции по расписанию
- Автоматически удаляет старые партиции по времени или количественным правилам
- Фоновый воркер для выполнения обслуживания без внешних заданий

https://github.com/pgpartman/pg_partman

👉 @SQLPortal

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 соответствующим колонкам.

Если в базе есть несколько полей, хранящих дату рождения или 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 vs CHECK CONSTRAINT

Может возникнуть вопрос - зачем использовать DOMAIN, если можно просто навесить CHECK constraint прямо на колонку?
Простой ответ: CHECK constraints не так просто изменять — их нужно удалять и создавать заново.

Кроме того, DOMAIN можно создать на уровне схемы, и если в нескольких таблицах есть колонки с email или датой рождения, можно использовать один DOMAIN для управления всеми этими полями, тем самым централизуя логику.

👉 @SQLPortal

Бросок кубика определяет исход атаки в D&D.

Поэтому знание распределения результатов бросков помогает выбрать правильное действие.

При этом нужно учитывать преимущество , помеху и удачу.

Sean D. Stuber показывает, как сделать это в Oracle SQL.

https://seanstuber.com/2025/07/29/using-sql-to-derive-dice-distributions-in-dnd/

👉 @SQLPortal

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 на любой выбранный момент времени.

Но у нас маленькая команда и простое приложение...

Красота PITR в том, что это не только для больших приложений и сложных баз. Даже маленькие команды совершают ошибки и хотят обеспечить непрерывную работу для своих пользователей.

🔸PITR полезен не только при сбоя - это ещё и мощный инструмент для тестирования.

Во многих сценариях, например, на Crunchy Bridge, вы можете создать новую версию базы на конкретном моменте времени с помощью концепции форка. Форк создаёт базу с настройками, близкими к текущей, и применяет бэкапы и WAL-файлы на выбранный момент времени. Форки это мощный инструмент для команд разработчиков баз, чтобы тестировать новые версии, изменения приложений и миграции.

👉 @SQLPortal

Часто приходится ковыряться в терминале с CSV или JSON, а стандартные CLI-инструменты всё превращают в кашу.

Tabiew — open-source тулза для комфортного просмотра таблиц прямо в терминале. Есть Vim-стиль управления и SQL-запросы к данным.

Форматы: CSV, Parquet, JSON, Excel и прочее. Можно быстро фильтровать, искать, листать и даже строить графики.

Фишки

🔸Поддержка CSV, Parquet, Arrow, Excel и др.
🔸SQL-фильтрация и анализ прямо в терминале
🔸Vim-подобные хоткеи
🔸Поиск на лету и мульти-табличные операции
🔸Темы Monokai, Nord и др.
🔸Лёгкий, ест даже огромные файлы без лагов

👉 @SQLPortal

Манипуляции с JSON в PL/SQL с использованием объектных типов, добавленных в версии 12.2

JSON_ELEMENT_T
JSON_SCALAR_T
JSON_OBJECT_T
JSON_ARRAY_T

С помощью функций get, put и remove можно получать, добавлять и удалять значения из JSON-объектов и массивов

👉 @SQLPortal

Инструмент для анализа данных на базе AI от Microsoft 🤖

Представляем LLM-powered no-code-инструмент, который преобразует данные и строит крутые визуализации.

Ключевые фичи

🔸Преобразование данных с помощью AI
🔸Интерактивный UI
🔸Ввод на естественном языке

Ссылка на GitHub-репозиторий - https://github.com/microsoft/data-formulator

👉 @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

Я сторонник того, чтобы всегда выбирать инструмент под конкретную задачу

Как бы я ни любил Postgres и MySQL, они заточены под OLTP-нагрузку. А вот когда нужны аналитика или работа с временными рядами — ClickHouse отлично подходит 🤵

На выходных почитал их статью — море интересных инженерных решений

👉 @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

Старые приёмы работы с Postgres которые могут быть полезны. Пример функция soundex

Можно написать поиск по именам которые звучат как Jon — их не обязательно писать так же или иметь совпадающую триграмму

SELECT first_name, last_name
FROM customers
WHERE soundex(first_name) = soundex('Jon');

Такой запрос найдёт "John" "Jon" и даже "Joan"

Soundex — это специальный алгоритм для английского произношения. Он берёт первую букву слова и оставляет её в качестве якоря. Остальную часть слова он упрощает сгруппировав согласные в несколько общих категорий звуков. Например согласные которые произносятся с помощью губ как B и P считаются одной группой. Гласные (A E I O U) и мягкие согласные вроде H и W полностью игнорируются так как они мало влияют на основное звучание. В итоге получается стандартизированный код из четырёх символов. Запрос ищет другие совпадения с таким же четырёхсимвольным кодом

Soundex заметно слабо работает с текстом на других языках так что с реализацией стоит быть аккуратным. Но он может помочь в некоторых задачах поиска и сортировки особенно если у вас ещё нет ИИ или эмбеддингов

Больше про soundex, полнотекстовый поиск и нечеткое сравнение строк читайте здесь: ссылка

👉 @SQLPortal

libpq deep dive День 2 - переменные окружения Postgres

Когда вы подключаетесь к Postgres, соединение под капотом ведет libpq

libpq проверяет параметры подключения в таком порядке приоритета

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

Talk to DBs

Вышел AI-инструмент, который позволяет общаться с базами данных на обычном языке

→ Загружаешь URL своей базы
→ Задаёшь любой вопрос
→ Получаешь SQL-запрос, результаты и пояснения

https://github.com/Arindam200/awesome-ai-apps/tree/main/simple_ai_agents/talk_to_db

👉 @SQLPortal

Разбор libpq. День 3 — libpq и режим pipeline

Новое в Postgres 14 — libpq теперь поддерживает пакетные запросы.

Раньше Postgres работал по принципу «одна команда — один ответ». Сначала отправлялся запрос на сервер, ждали ответ, потом отправляли следующий. В режиме pipeline приложение может отправить целую пачку команд сразу.

Вместо того чтобы ждать ответа на каждый запрос, приложение шлёт их оптом, сервер обрабатывает и возвращает результаты одним пакетом.

В Postgres есть набор функций для работы с таким подходом, а некоторые драйверы (например, psycopg3) уже поддерживают эти возможности, что упрощает выполнение нескольких запросов и получение всех результатов за один раз.

👉 @SQLPortal

Postgres и MySQL поддерживают два типа репликации — логическую и физическую

Репликация используется для HA-схем с primary/replica и для миграций между провайдерами без простоя

Почему два вида

🔸Физическая репликация

Передаёт изменения реальных дисковых блоков с primary на целевой сервер.
Сюда входят изменения данных таблиц, индексов и системных каталогов.
В итоге на целевом сервере получается побайтовая копия исходной базы.
Это быстро и эффективно, но требует, чтобы версии серверов совпадали (или хотя бы были совместимы на уровне байтов)

В MySQL это называется ROW-level replication (binlog_format=ROW), а в Postgres — физическая репликация

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

Передаёт сами изменения в логическом виде.
Например, при UPDATE строки реплика получит команду вида «изменить колонку X на значение Y в строке Z», а не побайтовые изменения.
Это медленнее, но позволяет реплицировать между разными основными версиями сервера

В MySQL это называется STATEMENT-level replication (binlog_format=STATEMENT)

Оба варианта дают широкие возможности по масштабированию и миграциям баз данных

👉 @SQLPortal

libpq deep dive День 4 . Несколько хостов в одном подключении

Новое в Postgres 16 Теперь libpq умеет распределять подключения между несколькими хостами В строке подключения можно указать сразу список серверов, а не только один вместе с данными пользователя и базы

Пример строки подключения

host=pg-replica-01,pg-replica-02,pg-replica-03 user=app_user dbname=my_app_db load_balance_hosts=random

Здесь указано несколько хостов и в конце добавлен параметр load_balance_hosts=random

Как это работает

1) libpq берет список хостов pg-replica-01 pg-replica-02 pg-replica-03 и случайным образом их перемешивает
2) Пытается подключиться к хостам в новом случайном порядке пока соединение не установится

Это простой способ распределить нагрузку на read реплики без использования внешнего балансировщика

👉 @SQLPortal