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

JSON types in Postgres json * текст * без дубликатов ключей * не индексируется * парсится во время выполнения, поэтому медленнее jsonb * бинарный формат, заранее распарсен * без дубликатов ключей * индексируется * лучше по производительности запросов 👉 @SQLPortal

👩‍💻 В сеть вывалилась гигантская куча курсов и книг Держи сотни гигабайт свежих уроков, и каждую неделю мы подкидываем ещё! • 1612 ГБ — DevOps • 1402 ГБ — Python • 1300 ГБ — C, C++ • 1815 ГБ — Frontend • 1515 ГБ — Backend • 898 ГБ — ИБ, Хакинг • 996 ГБ — Kotlin, Swift • 212 ГБ — JavaScript • 315 ГБ — Flutter • 820 ГБ — Go, PHP • 419 ГБ — Java, Rust • 648 ГБ — GameDev • 517 ГБ — Windows, Linux • 998 ГБ — Дизайн (UX/UI) • 617 ГБ — Нейросети (ML/RL) • 546 ГБ — БД (SQL & NoSQL) • 687 ГБ — Аналитика данных • 115 ГБ — QA-тестирование Подписывайся и не плати за то, что можно получить бесплатно

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