Неделя Postgres contrib. Поговорим о фичах, которые уже поставляются вместе с Postgres, но по умолчанию выключены.

Contrib-расширения входят в библиотеку contrib внутри PostgreSQL. То есть код расширения уже лежит в системе, если ты ставил Postgres из официальных пакетов. Все, что нужно — выполнить CREATE EXTENSION, чтобы включить его.

Расширения, которые не входят в contrib, надо собирать или компилировать вместе с Postgres перед тем, как их можно будет подключить через CREATE EXTENSION.

👉 @SQLPortal

😏😏😏

👉 @SQLPortal

SQL-совет:

Простая демонстрация вложенных выражений CASE.

👉 @SQLPortal

Postgres pgstattuple — расширение из contrib, которое показывает физическое состояние таблицы или индекса.

Пример:

SELECT * FROM pgstattuple('accounts');

Что там видно:
• tuples — количество строк
• dead tuples — строки, которые удалены или изменены
• free space и free percent — свободное место и его процент

Если dead tuples или free percent слишком высокие, стоит проверить настройки vacuum.

👉 @SQLPortal

В DBeaver можно спокойно работать с большими SQL-скриптами через панель Outline.

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

Функция доступна в десктопных версиях, начиная с 24.0.

👉 @SQLPortal

Postgres расширение из набора contrib под названием amcheck позволяет проверить таблицы и индексы на повреждение.

Проверка индекса:

SELECT * FROM bt_index_check('accounts_pkey'::regclass::oid);

Проверка таблицы:

SELECT * FROM verify_heapam('accounts');

Если вернулось 0 строк — все ок. Если что-то вернулось — значит есть повреждение.

Если ты собираешься решать Advent of Code в SQL или Postgres в этом году, у Грега Сабино Маллейна есть серия постов, где он пару лет назад проходил AoC на Postgres и разобрал кучу полезных приемов и примеров кода.

https://crunchydata.com/blog/topic/advent-of-code

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

👉 @SQLPortal

К слову о MySQL как основе интернета. У Uber больше 2 600 MySQL-кластеров в проде.

Недавно они перевели многие из них с классической схемы primary-replica на групповую репликацию на базе Paxos.

У них в блоге есть ещё и годные бенчмарки по этому переходу.

👉 @SQLPortal

Расширение Postgres contrib pg_prewarm. Это штука, которую можно повесить на рестарт, чтобы заранее загрузить данные в shared buffers, а не ждать, пока система сама подтянет их по мере использования.

Для таблиц:

SELECT pg_prewarm('users') AS users_heap_blocks_loaded;

Для индексов:

SELECT pg_prewarm('users_pkey') AS users_index_blocks_loaded;

👉 @SQLPortal

У тебя есть таблица с более чем 50 миллионами строк.

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

Запрос 1

SELECT * FROM customers c
WHERE c.id IN (
  SELECT customer_id 
  FROM orders 
  WHERE order_date > '2024-01-01'
);

Запрос 2

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o 
  WHERE o.customer_id = c.id 
    AND o.order_date > '2024-01-01'
);

👉 @SQLPortal

Contrib-расширение Postgres pgcrypto.

-- Шифруем данные с использованием секретного ключа
INSERT INTO payments (cc)
VALUES ( pgp_sym_encrypt('41....257', 'secret-key') );

-- Расшифровываем данные для чтения, используя тот же секретный ключ
SELECT pgp_sym_decrypt(cc, 'secret-key') AS decrypted_cc
FROM payments;

👉 @SQLPortal

3 вида шардирования баз данных:

• Range-based — разбивает данные по диапазонам значений ключа
• Hash-based — выбирает шарду с помощью хеш-функции
• Tenant-based — каждому клиенту (тенанту) выделяется своя отдельная база

Пояснения:

Range-based sharding делит данные по диапазонам ключа (например: ID 1–1 000 — первая шарда, 1 001–2 000 — вторая).

Hash-based sharding использует хеш от ключа, чтобы определить, в какую шарду поместить или откуда прочитать запись. Это даёт более равномерное распределение.

Tenant-based sharding выделяет каждому клиенту собственную базу данных, что упрощает изоляцию, миграции и управление данными.

Просто, понятно и работает во всех масштабах.

👉 @SQLPortal

Интерактивный гайд о шардировании баз данных, как это работает для реляционных баз.

Каждому инженеру стоит понимать базовые принципы и основные сложности шардирования. Эта статья как раз про это и предназначена.

Читаем здесь

👉 @SQLPortal

Postgres использует архитектуру один процесс на одно подключение. Поэтому по возможности всегда стоит использовать пулер подключений.

Пулер подключений ставится между приложением и базой данных. Он держит пул открытых соединений и динамически сопоставляет входящие запросы с этими соединениями. Самый популярный инструмент для этого — PgBouncer, и у него есть три режима работы.

(1) Session pooling: каждое входящее подключение напрямую маппится на одно из соединений в пуле PgBouncer.

(2) Transaction pooling: каждая входящая транзакция получает соединение из пула и освобождает его после завершения транзакции.

(3) Statement pooling: каждый отдельный SQL-запрос маппится на соединение из пула. Многооператорные транзакции не поддерживаются. Самый ограниченный режим.

Вариант 1 не решает проблему высокой конкурентности в Postgres, поэтому обычно не рекомендуется для сценариев с большим числом одновременных подключений. Вариант 3 слишком ограничен, так как многооператорные транзакции важны для многих кейсов.

Вариант 2 - лучший баланс между поддержкой функциональности и контролем количества подключений к Postgres.

Режим statement это то, что мы используем в PlanetScale для всех своих инстансов PgBouncer.

👉 @SQLPortal

Ключевые слова SQL не чувствительны к регистру, в отличие от Python.

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

Писать SQL-ключевые слова в верхнем регистре не является правилом, но это одна из тех привычек, которые отличают новичков от профессионалов.

SQL — декларативный язык. Когда ты быстро просматриваешь запрос, мозгу важно отличать то, что должна делать база данных (команды), от тех данных, к которым эти команды применяются. Выделение команд вроде SELECT, FROM, WHERE, GROUP BY и ORDER BY в верхнем регистре делает их сразу заметными. Это визуальная структура. Глаз сразу понимает, где ключевое слово, а где имя колонки или таблицы.

В итоге, заглавные ключевые слова в SQL используют ради ясности, читабельности, аккуратного стиля и удобства поддержки кода в долгой перспективе.

👉 @SQLPortal

AI-агенты могут помочь собрать схему и сгенерировать тестовые данные.

Крис Хойна делится примером промпта для хранения данных о машинах, грузовиках, мотоциклах и их производителях.

После этого онa показывает, как использовать этот промпт вместе с SQLcl MCP Server и Cline, чтобы создать таблицы.

👉 @SQLPortal