SQL Portal | Базы Данных

Вчера мы говорили про GIN-индексы, но они не поддерживают ORDER BY. В некоторых ограниченных случаях можно использовать выраженные индексы на JSON-выборках, которые просто фиксируют текстовое значение по заданному keypath (пути к ключу).

CREATE INDEX idx_state
ON users ((details->'location'->>'state'));

{ "name": "David", "location": { "city": "Lawrence", "state": "KS" } }

➡️Не для общего случая, а под конкретные кейсы
➡️Ускоряют lookups по одиночным ключам в JSON
➡️B-tree индекс, поддерживает ORDER BY
➡️Убирает оверхед GIN или необходимость парсить JSON во время выполнения запроса

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

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

psql-лайфхак:

\e откроет твой дефолтный текстовый редактор, чтобы написать SQL или команды. Сохраняешь файл, закрываешь, и psql автоматически выполнит то, что ты написал.


🛌🛌🛌

👉 @SQLPortal

JSON types in Postgres

json

* текст
* без дубликатов ключей
* не индексируется
* парсится во время выполнения, поэтому медленнее

jsonb

* бинарный формат, заранее распарсен
* без дубликатов ключей
* индексируется
* лучше по производительности запросов

👉 @SQLPortal

OUTER JOIN или внешнее соединение позволяет возвратить все строки одной или двух таблиц, которые участвуют в соединении.

Outer Join имеет следующий формальный синтаксис:

SELECT столбцы
FROM таблица1
    {LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1
    [{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]...

Перед оператором JOIN указывается одно из ключевых слов LEFT, RIGHT или FULL, которые определяют тип соединения:

LEFT: выборка будет содержать все строки из первой или левой таблицы

RIGHT: выборка будет содержать все строки из второй или правой таблицы

FULL: выборка будет содержать все строки из обеих таблиц

Перед оператором JOIN может указываться ключевое слово OUTER, но его применение необязательно. После JOIN указывается присоединяемая таблица, а затем идет условие соединения после оператора ON.

К примеру, возьмем следующие таблицы:

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
);

И соединим таблицы Orders и Customers:

SELECT FirstName, CreatedAt, ProductCount, Price, ProductId 
FROM Orders LEFT JOIN Customers 
ON Orders.CustomerId = Customers.Id;

Таблица Orders является первой или левой таблицей, а таблица Customers - правой таблицей. Поэтому, так как здесь используется выборка по левой таблице, то вначале будут выбираться все строки из Orders, а затем к ним по условию Orders.CustomerId = Customers.Id будут добавляться связанные строки из Customers.

👉 @SQLPortal

Нашел интересную статью о внутреннем устройстве Postgres

👉 @SQLPortal

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$

Типичное приложение без этого делало бы так:

на клиенте есть массив
клиент бежит по массиву
отправляет INSERT-ы в базу
ждёт round-trip для каждого запроса

С PL/pgSQL можно перенести всю операцию внутрь базы:

— передаёшь массив один раз
— Postgres сам крутит цикл
— пишет данные
— нет лишних round-trip’ов
— меньше сетевой задержки
— транзакция проще
— логика ближе к данным

👉 @SQLPortal

Мифическая база данных MySQL постепенно затухает...😭

Уже больше 4 месяцев в её публичном репозитории на GitHub не было ни единого коммита. Плохой сигнал.

Пауза совпала с сентябрьскими сокращениями в Oracle в команде, которая этим занимается.

Это не значит, что MySQL исчезнет. Она стоит на миллионах серверов и будет работать ещё годами.

Но это вполне ясный маркер чего-то более глубокого:
MySQL больше не конкурирует как open-source сообщество, а живёт как чисто коммерческий продукт.

Меньше прозрачности, другие приоритеты.
Обгон со стороны PostgreSQL уже неизбежен.

👉 @SQLPortal

У ClickHouse появился managed-сервис Postgres.

Теперь прям очевидно, почему ClickHouse так упарывались в расширение pg_clickhouse.

Конкуренция станет еще жарче: Snowflake + Crunchy, Databricks + Neon и TigerData.

Lakehouse-платформам нужны транзакционные данные, и Postgres уже должен быть провайдером номер один.

Рад видеть, как тема Postgres для аналитики получает больше внимания и инвестиций. Для Postgres это плюс.

Ссылка: https://clickhouse.com/cloud/postgres

👉 @SQLPortal

PlanetScale показали pg_strict для Postgres.

Идея простая: это расширение, которое добавляет в Postgres “страховочную сетку” и ловит опасные запросы до того, как они реально выполнятся.

У всех бывало: хотел поправить одну строку, а всё уехало в UPDATE без WHERE. Или случайный DELETE, или “быстренько проверю миграцию” на не той базе. В итоге урон не от бага в коде, а от одной команды в psql.

Что обещает pg_strict:

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

👉 @SQLPortal

psql-лайфхак, который экономит кучу времени: \i (include) для запуска локального файла прямо из psql.

Пример:

\i ~/Documents/query.sql

psql откроет файл, выполнит все SQL-команды внутри, и выведет результат сразу в текущую сессию (как будто ты вручную вставил содержимое). Удобно для повторяемых запросов, миграций, быстрых дебаг-скриптов и репортов.

👉 @SQLPortal

В Postgres 18 наконец-то завезли параллельную сборку GIN-индексов при CREATE INDEX. То есть индексы под full-text (tsvector) и jsonb теперь могут строиться не в один поток, а с воркерами, как это уже давно было у B-tree/BRIN.

Фишка в том, что оно автоматом упирается в ваши настройки maintenance-параллелизма: max_parallel_maintenance_workers + общий бюджет maintenance_work_mem (и от него тоже зависит, сколько воркеров вообще дадут).

Мини-чеклист, если часто пересоздаете FTS/JSON индексы:

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 8;

CREATE INDEX gin_idx ON docs USING gin (to_tsvector('simple', body));
-- или jsonb
CREATE INDEX gin_json_idx ON docs USING gin (payload);

На больших таблицах это прям приятный буст, особенно когда билд был CPU-bound, а не в потолок по диску.

👉 @SQLPortal

Хочешь хранить текст? Не используй char(n) или varchar(n).

В других базах это встречается постоянно, но для Postgres — не лучшая идея.

• фиксированная ширина работает медленнее
• TEXT — переменной длины и без лимита
• если нужен размер, ставь constraint и ограничивай полем, а не типом

В итоге TEXT почти всегда выигрывает по практичности и производительности.

👉 @SQLPortal

SQL-совет:

Есть одна особенность в SQL, на которой спотыкаются многие новички (и даже часть опытных пользователей):

ORDER BY может быть чувствителен к регистру.

Большинство думает, что сортировка строк — это просто.
Алфавит. От A до Z. И всё.

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

То есть вместо:

apple
banana
cherry

Можно получить:

Apple
Banana
apple
banana

Слова те же, порядок другой.

Почему так?

Потому что некоторые базы воспринимают заглавные и строчные символы как разные значения при сортировке.

В SQLite можно отключить чувствительность к регистру через COLLATE NOCASE.

Здесь не нужно менять сами данные, можно просто сказать SQL, как их сортировать. Пример ниже.

SELECT name FROM products ORDER BY name COLLATE NOCASE;

👉 @SQLPortal

В Postgres каждая sequence хранится как отдельная таблица. Детали по sequence можно посмотреть напрямую, делая select из этой таблицы, или через команду \d в psql, чтобы получить описание.

Как искать sequences в Postgres

1. \d таблицы, ищем sequence-колонку

postgres=# \d user_events
             Table "public.user_events"
 Column     | Type                      | Collation | Nullable | Default
------------+---------------------------+-----------+----------+--------------------------------------------------
 id         | integer                   |           | not null | nextval('user_events_id_seq'::regclass)
 data       | text                      |           |          |
 created_at | timestamp without time zone|          |          | now()

Indexes:
    "user_events_pkey" PRIMARY KEY, btree (id)

2. SELECT * из sequence-таблицы, чтобы посмотреть текущее значение

postgres=# SELECT * FROM user_events_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
     203590 |      23 | t
(1 row)

3. \d sequence-таблицы, чтобы посмотреть максимальное значение и шаг

postgres=# \d user_events_id_seq;
          Sequence "public.user_events_id_seq"
 Type    | Start | Minimum | Maximum     | Increment | Cycles? | Cache
---------+-------+---------+-------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647  |         1 | no      |     1

👉 @SQLPortal

Нужно посчитать все строки в здоровенной таблице в Postgres?

SELECT count(*) FROM table;

Это может быть медленно. Можно спросить внутренние таблицы.

SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table';

Это оценка, но обычно довольно точная.

👉 @SQLPortal

Совет по Postgres: используй filter вместо case when для условных агрегатов. Читается проще, выглядит более идиоматично для SQL и часто работает быстрее.

--  не делай так: громоздко и хуже читается
select
  count(case when status = 'active' then 1 end) as active_count,
  count(case when status = 'archived' then 1 end) as archived_count
from projects;

--  лучше так: чище и оптимизируется Postgres
select
  count(*) filter (where status = 'active') as active_count,
  count(*) filter (where status = 'archived') as archived_count
from projects;

👉 @SQLPortal