Термин дня в Postgres: кардинальность

На неделе обсуждали распределение данных, и кардинальность — часть этой темы.
Кардинальность — это количество уникальных значений в столбце.

> Высокая кардинальность — много уникальных значений. Индексы полезны для поиска и сортировки.

> Низкая кардинальность — всего несколько уникальных значений. Последовательное сканирование может быть быстрее индекса.

Можно узнать кардинальность через pg_stats:

SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'storm_events';

Пример:

attname    | yearmonth
n_distinct | 12

attname    | event_id
n_distinct | -1

> Положительное значение, как 12, — значит 12 уникальных значений.
> Отрицательное, как -1, — все строки уникальны (обычно id).

Кардинальность влияет и на селективность, и на стратегии джойнов в плане выполнения запроса ❤️

👉 @SQLPortal

Ищешь инструмент для рисования схем баз данных прямо в браузере?

Посмотри на drawdb — это бесплатный и open source-проект.

Можно импортировать существующие схемы, экспортировать в разных форматах и делиться диаграммами 😮

👉 @SQLPortal

SQL-псевдоним позволяет присвоить таблице или столбцу временное имя во время выполнения запроса

Псевдонимы особенно полезны при работе со сложными запросами, соединениями (JOIN), или когда нужно переименовать столбцы в результате

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

Псевдонимы создаются с помощью ключевого слова AS

Пример:

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

👉 @SQLPortal

Какой вариант правильный?

A. Общие продажи, сгруппированные по названию книги.
B. Общее количество проданных экземпляров для каждого жанра.
C. Общая выручка, сгруппированная по жанру.
D. Список жанров с их книгами.

Правильный вариант — B

Запрос делает JOIN между таблицами Books и Sales, группирует по b.Genre и считает сумму s.Quantity, то есть — общее количество продаж по жанрам

👉 @SQLPortal

Можешь найти ошибку и поправить запрос?

Запрос ниже должен возвращать общий объём продаж для каждой категории товаров из таблицы Products. Но он выбрасывает ошибку:

SELECT category_id, SUM(price * quantity) 
FROM Products 
WHERE category_id IS NOT NULL;

👉 Ошибка в том, что используется агрегат SUM(...), но нет GROUP BY.

Вот исправленный вариант:

SELECT category_id, SUM(price * quantity) 
FROM Products 
WHERE category_id IS NOT NULL
GROUP BY category_id;

👉 @SQLPortal

В Postgres есть набор зарезервированных слов, которые используются в SQL и нельзя применять для имён таблиц, колонок, индексов и т.д.

Часто вызывают проблемы у новичков:
USER, GROUP, ORDER, TABLE — многие пытались создать таблицу с именем table или user.

Есть и другие зарезервированные слова, реже встречающиеся в названиях:
SELECT, INSERT, UPDATE, WHERE, FROM, BY, LIMIT, JOIN.

Можно обойти это, заключив имя в кавычки:

CREATE TABLE "user";

Но тогда каждый запрос должен использовать эти кавычки — это приводит к ошибкам и неудобно, поэтому так делать и не рекомендуется 💖

👉 @SQLPortal

Измени формат вывода результатов запроса в OracleSQL с помощью команды:

SET SQLFORMAT <формат>

Или добавь комментарий формата после SELECT, например, для вывода в CSV:

SELECT /*csv*/ ...

Затем выполни SQL в режиме скрипта (F5).

Поддерживаемые форматы:

CSV
JSON
INSERT

👉 @SQLPortal

Когда мы говорим с клиентами о бэкапах, мы делим всё на два понятия: одиночная резервная копия и система восстановления до определённого момента (point-in-time recovery).

1. Одиночный бэкап (pg_dump). Можно получить с помощью команды pg_dump в Postgres — это логический бэкап, который представляет данные в виде SQL-скрипта (CREATE TABLE, INSERT INTO и т.д.).

> Подходит для тестов, миграций, восстановления на другой версии Postgres или платформе.
> Но не годится как полноценная система восстановления в проде — например, при порче данных.

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

Делается с помощью pg_basebackup, снапшотов или журнала WAL. Это бэкапы на уровне байтов и блоков, зависят от версии и хранилища, но:

> гораздо быстрее при аварийном восстановлении,
> поддерживают инкрементальные бэкапы,
> позволяют делать восстановление до конкретного времени (PITR).

👉 @SQLPortal

Добавь автодополнение в своё приложение с помощью pg_trgm

Модуль pg_trgm + индекс GIN ускоряет iLIKE-поиск даже на миллионах строк — идеально подходит для type-ahead (поиска по мере ввода).

Мгновенный отклик + минимум усилий — максимум пользы

👉 @SQLPortal

Давайте попрактикуемся в SQL

Вопрос: Используя таблицу Order, напишите запрос, чтобы найти общую сумму продаж (amount) за февраль 2025 года

Ответ:
SELECT SUM(total_amount) AS total_sales
FROM orders
WHERE MONTH(order_date) = 2 AND YEAR(order_date) = 2025;

👉 @SQLPortal

В PostgreSQL 18 поле Buffers появится в стандартном EXPLAIN-выводе

Теперь при выполнении EXPLAIN ANALYZE вы сразу увидите информацию о буферах:

EXPLAIN ANALYZE SELECT * FROM orders;

QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on orders  (cost=0.00..1.02 rows=2 width=90) (actual time=0.012..0.013 rows=2 loops=1)
  Buffers: shared hit=5
Planning Time: 0.055 ms
Execution Time: 0.023 ms

> Buffers: shared hit=5 означает, что данные были считаны из кэша (shared buffers), то есть из оперативной памяти.
> А если бы пришлось читать с диска, строка выглядела бы так:

Buffers: shared hit=5, shared read=3

Почему это важно? 🤔

Показатель cache hit ratio — отличный индикатор производительности работы с памятью в Postgres.
Чем больше данных берётся из буфера, тем быстрее работает запрос.

А если читается с диска — это значит:

> больше нагрузки на I/O

> выше потребление CPU

> возможно, нужна настройка конфигурации или больше памяти

👉 @SQLPortal

Excel с нуля — в одном файле

"Excel Fundamentals Manual" — PDF-гайд, где пошагово разобрано всё, что нужно новичку ✊

👉 @SQLPortal

Встречайте новое расширение Microsoft для PostgreSQL в VScode

Оно напичкано мощными инструментами, которые ускорят вашу работу с Postgres:

> Интеграция с Agent Mode
> Визуализация схем
> Мгновенный запуск PostgreSQL в Docker
> Запросы с поддержкой IntelliSense

— читать больше 😎

👉 @SQLPortal

Совет по Postgres на сегодня: используйте lock_timeout

Устанавливайте lock_timeout в сессиях приложения — так блокировки автоматически снимаются, если держатся слишком долго. Это поможет избежать ситуаций, когда таблица остаётся залоченной и мешает другим операциям.

ALTER SYSTEM SET lock_timeout = '10s';

Таймаут можно задать и для конкретной сессии. Например, если вы запускаете миграцию и ожидаете долгую блокировку — просто увеличьте значение lock_timeout

👉 @SQLPortal

Команда Pgpool выпустила срочные обновления для всех актуальных веток:

> 4.6.1
> 4.5.7
> 4.4.12
> 4.3.15
> 4.2.22

Этот релиз содержит исправление уязвимости безопасности.

В механизме аутентификации клиентов Pgpool-II обнаружена уязвимость, позволяющая обойти аутентификацию. В результате злоумышленник может войти под любым пользователем, что может привести к утечке данных, их подмене или даже полной остановке базы данных.
(CVE-2025-46801)

Уязвимость затрагивает:

> Все версии Pgpool-II 4.0 и 4.1
> Версии с 4.2.0 по 4.2.21
> С 4.3.0 по 4.3.14
> С 4.4.0 по 4.4.11
> С 4.5.0 по 4.5.6
> Версию 4.6.0

Рекомендуется срочно обновиться до: 4.6.1, 4.5.7, 4.4.12, 4.3.15 или 4.2.22 — или изменить конфигурации, чтобы они не попадали под уязвимые шаблоны 😡

> Подробности

👉 @SQLPortal

Хочешь узнать, когда та или иная фича появилась в Oracle Database?

Зацени Oracle Database Features & Licensing:

> https://apex.oracle.com/pls/apex/r/features/dbfeatures/home

Там можно посмотреть, в каких версиях доступна нужная функциональность.

А во вкладке Licensing — в каких редакциях Oracle её можно легально использовать 🔗

👉 @SQLPortal

Нашёл крутейший сайт для тех, кто хочет с нуля вкатиться в SQL. Там всё по полочкам: SELECT, JOIN, WHERE и даже про подзапросы есть. Прям интерактивно учишься — пишешь код, сразу видишь результат.

Если хочешь разобраться в базах — w3schools.com/sql 😡

👉 @SQLPortal

Совет по Postgres: создавайте индексы с опцией CONCURRENTLY. Это позволяет строить индекс в фоне, пока продолжаются обычные операции чтения и записи. Без этой опции таблица блокируется на запись на всё время построения индекса. Однако CONCURRENTLY работает дольше и не может использоваться внутри транзакции.

Пример:

CREATE INDEX CONCURRENTLY foobar ON foo (bar);

👉 @SQLPortal