Функции нумерации строк в SQL:

ROW_NUMBER — последовательные целые числа, начиная с единицы.

RANK — строки с одинаковым значением сортировки получают один и тот же номер; следующая строка после «ничьей» получает значение так, как если бы использовался ROW_NUMBER

DENSE_RANK — работает как RANK, но нумерация идёт подряд без пропусков после «ничьих».

👉 @SQLPortal

Google представил новый язык программирования — Mangle, предназначенный для работы с базами данных. 😐

Он распространяется с открытым исходным кодом и сочетает в себе подход логического программирования с поддержкой рекурсивных правил. В языке есть агрегации, вызовы функций и опциональная типизация.

Репозиторий проекта доступен здесь: github.com/google/mangle

👉 @SQLPortal

Проверь соответствие безопасности своей Oracle Database с помощью Database Security Assessment Tool (DBSAT) v4.0.

Инструмент включает в себя проверку соответствия стандартам STIG, анализ пользователей и их прав, а также выявление уязвимостей по непропатченным CVE.

Об этом рассказывает Pedro Lopes.

https://blogs.oracle.com/database/post/dbsat40

👉 @SQLPortal

JOIN против UNION в Postgres:

JOIN нужен для объединения столбцов ➡️
UNION нужен для объединения строк ⬇️

✤ JOIN объединяет столбцы из двух и более таблиц по связанному полю. Это как добавлять данные по горизонтали. Используй JOIN, когда нужно посмотреть данные из разных таблиц рядом. Пример:

SELECT employees.name, departments.location
FROM employees
JOIN departments
ON employees.dept_id = departments.id;

✤ UNION складывает результаты двух и более SELECT в один набор строк. Это про объединение данных по вертикали. Таблицы должны иметь одинаковое количество столбцов и совместимые типы данных. Пример:

SELECT city
FROM customers
UNION
SELECT city;

👉 @SQLPortal

Хранение по строкам и хранение по колонкам это два разных способа организации данных, которые дают разный результат в работе.

В строковом варианте, как в Postgres или MySQL, все значения одной строки лежат рядом на диске.

Такой подход хорошо работает для транзакционных систем, где запросы чаще всего достают одну или несколько строк целиком, например SELECT * FROM camera WHERE model = 'A7R'. Здесь выгодно то, что обычно обрабатывается не так много строк, но при этом почти всегда требуется сразу много полей.

В колонковом варианте, как в BigQuery или ClickHouse, все значения одной колонки собраны вместе и хранятся в длинных последовательностях. Это упрощает сжатие данных и позволяет быстрее работать с аналитическими запросами.

Если нужно обработать миллионы строк, но использовать только одно поле, например в запросе SELECT avg(price) FROM camera, то колонковая база читает только нужный столбец, а строковой пришлось бы загружать каждую строку целиком.

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

Но по мере роста нагрузки обычно выбирают разные решения: Postgres или MySQL для OLTP и отдельные колонночные хранилища для аналитики и бизнес-отчётов. В итоге выбор всегда зависит от задачи, и лучший вариант — подбирать инструмент под конкретный сценарий.

👉 @SQLPortal

Знал ли ты, что можно пользоваться psql — консольным клиентом Postgres без установки всего PostgreSQL?

Да-да, это реально. Благодаря libpq можно подключаться к Postgres без того, чтобы тащить весь пакет целиком.

Для macOS это делается так:

brew install libpq

(на Linux — что-то вроде apt-get install postgresql-client).

После установки появится сообщение вроде такого, и нужно будет добавить ещё один шаг, чтобы правильно прописать путь:

==> libpq
libpq is keg-only, which means it was not symlinked into /opt/homebrew,  
because it conflicts with PostgreSQL.
If you need to have libpq first in your PATH, run:
echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> /Users/.bash_profile

После этого можно подключаться к удалённому хосту через стандартную строку подключения:

psql postgres://{username}:{password}@{hostname}:{port}/{database-name}

👉 @SQLPortal

Под руку попалась огромная подборка всего, что связано с PostgreSQL

Там собраны лучшие тулзы и библиотеки, статьи и доки, практические гайды, полезные расширения и утилиты из реального продакшена, а ещё комьюнити-ресурсы, где делятся опытом, радостями и болью работы с Postgres 🐘

👉 @SQLPortal

Пакетная обработка апдейтов в Postgres

Если нужно обновить несколько тысяч строк, то в большинстве баз это делается одной командой (или внутри транзакции) с обычным SQL. Но если речь про миллионы строк, то лучше разбить апдейты на батчи и написать более продвинутую функцию, которая будет ограничивать количество строк за раз.

► Крупные апдейты в Postgres запускают долгий процесс, который коммитится только в конце. При этом:
- генерируется огромный WAL
- может заблокироваться таблица
- тратятся CPU и память

► Батчинг позволяет коммитить апдейты частями, а именно:
- обновлять данные небольшими порциями по несколько тысяч строк
- уменьшать конкуренцию за блокировки
- бережнее расходовать CPU и память, пока база продолжает работать с другими задачами

Базовая идея — DO-функция, которая задаёт размер батча и крутится по циклу, ограничивая выборку этим размером.

Пример:

DO $$
DECLARE
    batch_size INTEGER := 5000;
    rows_updated INTEGER;
BEGIN
    LOOP
        WITH batch AS (
            SELECT ctid
            FROM demo_batch
            WHERE status = 'old'
            LIMIT batch_size
            FOR UPDATE SKIP LOCKED
        )
        UPDATE demo_batch d
        SET status = 'new'
        FROM batch
        WHERE d.ctid = batch.ctid;
        
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        
        IF rows_updated = 0 THEN
            EXIT;
        END IF;
        
        PERFORM pg_sleep(0.5); 
        RAISE NOTICE 'Updated % rows in this batch.', rows_updated;
        
        COMMIT;
    END LOOP;
END $$;

👉 @SQLPortal

Достать значения из предыдущей или следующей строки в SQL можно так:

<fn>(val) OVER (ORDER BY ...)

где <fn> это

LAG — предыдущая строка

LEAD — следующая строка

По умолчанию они смотрят только на одну строку назад или вперёд.

Второй параметр позволяет взять значение из N-й строки до или после текущей.

👉 @SQLPortal

Нужно замерить размер таблицы в Postgres? Не забудь про индексы и TOAST.

SELECT pg_relation_size('table_name'); покажет размер основного файла таблицы, но pg_relation_size() возвращает только размер самого HEAP-сегмента. Он не учитывает индексы, TOAST-таблицы, карты свободного пространства и прочие файлы, которые связаны с таблицей на диске.

pg_total_relation_size() вернёт полный размер объекта вместе с heap-данными, индексами, TOAST, Free Space Map и Visibility Map.

👉 @SQLPortal

В Oracle Database 23.9 появилась фича GROUP BY ALL, которая автоматически добавляет в GROUP BY все колонки из SELECT, кроме агрегатных.

Дани Шнайдер показывает, как это удобно использовать для поиска дубликатов:

SELECT t.*, COUNT(*)
FROM t
GROUP BY ALL
HAVING COUNT(*) > 1;

👉 @SQLPortal

Postgres NTILE

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

Эта функция особенно полезна в статистическом и финансовом анализе, когда нужно понять, как данные распределяются по сегментам, выявить тренды или сравнить группы с разными характеристиками.

Например, вызов NTILE(4) разбивает данные на четыре квартиля и помещает каждую строку в одну из четырёх групп.

WITH DailySales AS (
    SELECT
        date_trunc('day', o.order_date) AS sales_date,
        SUM(o.total_amount) AS daily_total_sales
    FROM
        orders o
    GROUP BY
        date_trunc('day', o.order_date)
)
SELECT
    sales_date,
    daily_total_sales,
    NTILE(4) OVER (
        ORDER BY daily_total_sales DESC
    ) AS sales_quartile
FROM
    DailySales
ORDER BY
    sales_date;

В этом примере данные о дневных продажах делятся на четыре группы по величине выручки. Первая группа — дни с самыми большими продажами, четвёртая -- с самыми маленькими.

👉 @SQLPortal

В Oracle Database виртуальные колонки давно позволяют определять вычисляемые поля.

По умолчанию выражение считается при чтении.

А начиная с релиза 23.7 можно хранить результат выражения при записи с помощью синтаксиса:

<col> <data_type> AS ( ... ) MATERIALIZED

Gerald Venzl разобрал это подробнее

👉 @SQLPortal

Lateral join в Postgres:

LATERAL позволяет подзапросу ссылаться на колонки таблицы, которая указана раньше в секции FROM. По сути это что-то вроде for-each цикла между двумя таблицами.

Иногда lateral join может заменить коррелированные подзапросы, и при этом работать быстрее.

Типовые кейсы:

- выборка последней записи в группе
- получение топ-N связанных записей для каждой строки
- агрегации или ранжирование по строкам
- объединение JSON или массивов с данными из других таблиц

Классный интерактивный туториал в браузере: тык

👉 @SQLPortal

Выборка top-N строк в Oracle SQL:

FETCH FIRST n ROWS ONLY — вернуть первые n строк.

FETCH FIRST n ROWS WITH TIES — вернуть первые n строк плюс все строки с тем же значением сортировки, что и у n-й строки.

FETCH FIRST n PERCENT ROWS [ ONLY | WITH TIES ] — вернуть заданный процент строк (с опцией только n% строк или n% плюс все «ничейные» строки при одинаковом значении сортировки).

👉 @SQLPortal

Опции Postgres EXPLAIN

➣ Базовый EXPLAIN выглядит так:

QUERY PLAN
Seq Scan on orders  (cost=0.00..17.00 rows=1000 width=18)
(1 row)

Он показывает:

как выполнялся скан — последовательное сканирование, индексное сканирование и т. д.
cost — внутренний расчетный показатель, основанный на I/O, CPU и прочем
rows — количество строк, которые будут обработаны
width — количество байт на строку

➣ EXPLAIN ANALYZE даёт больше инфы, добавляя время планирования и выполнения:

Seq Scan on orders  (cost=0.00..17.00 rows=1000 width=18)
(actual time=0.136..0.440 rows=1000 loops=1)
Planning Time: 0.209 ms
Execution Time: 0.660 ms

➣ EXPLAIN (ANALYZE, BUFFERS) добавит данные о том, из буфера (shared buffer cache) бралось или с диска.
Начиная с Postgres 18 — BUFFERS будет включен по умолчанию.

Seq Scan on orders  (cost=0.00..17.00 rows=1000 width=18)
(actual time=0.080..0.283 rows=1000 loops=1)
Buffers: shared hit=7
Planning Time: 0.126 ms
Execution Time: 0.440 ms

EXPLAIN умеет выдавать результат не только в текстовом формате, к которому все привыкли. Есть ещё JSON, XML и YAML.
YAML удобен тем, что наглядно показывает каждое поле и его значение.

Пример:

EXPLAIN (ANALYZE, BUFFERS, FORMAT YAML)

Вывод:

Plan:
  Node Type: "Seq Scan"
  Parallel Aware: false
  Async Capable: false
  Relation Name: "orders"
  Alias: "orders"
  Startup Cost: 0.00
  Total Cost: 17.00
  Plan Rows: 1000
  Plan Width: 18
  Actual Startup Time: 0.102
  Actual Total Time: 0.451
  Actual Rows: 1000
  Actual Loops: 1
  Shared Hit Blocks: 7
  Shared Read Blocks: 0
  Shared Dirtied Blocks: 0
  Shared Written Blocks: 0
  Local Hit Blocks: 0
  Local Read Blocks: 0
  Local Dirtied Blocks: 0
  Local Written Blocks: 0
  Temp Read Blocks: 0
  Temp Written Blocks: 0
Planning:
  Shared Hit Blocks: 0
  Shared Read Blocks: 0
  Shared Dirtied Blocks: 0
  Shared Written Blocks: 0
  Local Hit Blocks: 0
  Local Read Blocks: 0
  Local Dirtied Blocks: 0
  Local Written Blocks: 0
  Temp Read Blocks: 0
  Temp Written Blocks: 0
Planning Time: 0.261
Triggers:
Execution Time: 0.745

👉 @SQLPortal

Oracle Database 23.9 добавила не-позиционные вставки.

Это позволяет задавать значения столбцов в выражении SET (похоже на UPDATE).

INSERT INTO employees
SET employee_id = 210, first_name = 'Gerald', ...

Gerald Venzl подробно объясняет детали и рассказывает предысторию.

👉 @SQLPortal

Охотьтесь на N+1 и убирайте их

Проблема N+1-запросов опасна и легко может ускользнуть из виду. Сценарий такой. Один запрос получает набор данных. Затем для каждой строки нужен ещё один запрос. Количество запросов при загрузке страницы зависит от размера выборки, и если N большой, производительность падает

Пример на псевдокоде

// один запрос
users = db.query('SELECT * FROM users')

// превращается в N дополнительных
for (const user of users) {
  posts = db.query(`
      SELECT * FROM posts 
      WHERE user_id = user->id`)
  user.posts = posts
}
// только теперь можно вернуть результат

Лучше переписать на один запрос с JOIN, чтобы забрать всё сразу

results = await db.query(
  `SELECT u.*, p.* 
   FROM users u 
   LEFT JOIN posts p 
   ON u.id = p.user_id`)

Интересный момент в том, что иногда N+1 почти не заметен. Если N маленький, база настроена и находится рядом с приложением, всё может работать быстро. Но если N=100, сразу ощущается тормоз и база нагружается лишней работой. Исправив это, вы улучшите UX и снизите нагрузку на сервер БД

Иногда такие запросы пишут руками. Но часто их генерирует ORM незаметно для вас. В любом случае избавляйтесь от них

👉 @SQLPortal

Skip scan для B-деревьев в Postgres 18

В Postgres 18 появится заметный прирост производительности для некоторых многоколонковых B-tree индексов.

Классический B-tree-поиск работает так: индекс читается, начиная с позиции, определяемой условиями запроса по колонкам индекса в их порядке. Если в запросе нет условия (WHERE или LIMIT BY) по первой (ведущей) колонке, то база не может использовать этот метод и вынуждена выполнять более медленный полный скан таблицы.

Skip scanning позволяет «пропускать» части индекса и всё же эффективно использовать его.

Этот подход работает, когда в запросе отсутствует условие по ведущей колонке, и при этом у пропущенной колонки низкая кардинальность (небольшое количество уникальных значений).

Оптимизация выполняется следующим образом:

I. Определяются все уникальные значения в пропущенных ведущих колонках.
II. Для каждого значения выполняется целевой проход по индексу.

Пример:

Допустим, у нас есть таблица sales со столбцами status и date.

Создаём многоколонковый индекс:

CREATE INDEX idx_status_date
ON sales (status, date);

Далее выполняется запрос без фильтрации по status:

SELECT * FROM orders
WHERE order_date > '2025-01-01';

До версии 18 приходилось выполнять полный скан.

В Postgres 18+, если у status низкая кардинальность и всего несколько различных значений, планировщик сможет выполнять отдельные индексные проходы (в подходящих случаях).

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

Планировщик сам решает, стоит ли использовать skip scan, опираясь на статистику таблицы и количество уникальных значений в пропускаемых колонках. Если уникальных значений слишком много, последовательное сканирование может оказаться быстрее.

👉 @SQLPortal