Это один из лучших AI-инструментов для разработчиков 🔥

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

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

Отлично подходит для прототипов и MVP.

Линк: database.build

👉 @SQLPortal

PostGIS 3.6 скоро выйдет, и в нём появилась новая функция: ST_CoverageClean

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

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

Теперь, с поддержкой очистки покрытий в GEOS, PostGIS может автоматически исправлять такие несовершенные данные с помощью ST_CoverageClean

CREATE TABLE polygons_clean AS (
  SELECT
    id,
    ST_CoverageClean(geom, gapMaximumWidth => 10000) OVER () AS geom
  FROM polygons
);

👉 @SQLPortal

Нравится контраст между буферизацией страниц данных в MySQL и Postgres. Подходы кардинально разные.

InnoDB в MySQL делает ставку на больший контроль. С innodb_flush_method='O_DIRECT' движок обходит большую часть накладных расходов на кэш страниц ОС и управляет всем сам. Объём кэша задаёт innodb_buffer_pool_size. На выделенных БД-серверах это часто 70%+ всей RAM.

Пул делится на несколько подэкземпляров. Каждый держит двухчастный список страниц LRU: young и old. Новые страницы попадают в old и должны «прожить» без вытеснения заданное время (innodb_old_blocks_time), прежде чем их пустят в young. Это защищает от thrashing при массовых операциях вроде table scan.

Postgres выбирает более простой внутренний кэш и полагается на кэш страниц ОС для загрузки/выгрузки. Размер задаёт shared_buffers. Часто ставят около 30% доступной RAM, оставляя место под кэш ОС.

Для вытеснения Postgres использует clock-sweep. У каждой страницы в shared_buffers есть счётчик: он увеличивается при обращениях (до лимита) и уменьшается при проходе «часов». Когда счётчик падает до 0, страницу можно заменить другой.

Отличный README из репозитория Postgres: ссылка

👉 @SQLPortal

Совет по SQL

Использование ORDER BY с несколькими столбцами

Когда вы пишете ORDER BY с несколькими колонками, база данных сначала сортирует все строки по первой колонке (в примере это sales). Это образует группы строк с одинаковым значением. Например, Mira и Bob попали в одну группу, так как у обоих значение продаж равно 12 000.

Основная сортировка по sales (по убыванию) дала ничью между Mira и Bob. Тогда в ход идёт вторая колонка. Сортировка по age (по возрастанию) ставит Mira (30 лет) перед Bob. Такой принцип применяется ко всем значениям, которые совпали на первом шаге.

Если и вторая сортировка тоже даёт одинаковые значения, некоторые СУБД сохраняют порядок вставки строк как дополнительный критерий. Но на больших и сложных наборах данных порядок чаще всего будет случайным.

👉 @SQLPortal

ByteByteGo выпустили наглядный разбор основных типов блокировок, которые используются в СУБД для управления конкурентным доступом к данным.

В списке:

• Shared Lock (S) — позволяет читать, но не менять данные
• Exclusive Lock (X) — блокировка для чтения и изменения
• Update Lock (U) — предотвращает взаимные блокировки при обновлениях
• Schema Lock — защита структуры базы
• Bulk Update Lock (BU) — оптимизация при массовых вставках
• Key Range Lock — предотвращает фантомные записи в индексах
• Row-Level Lock — блокировка на уровне строки
• Page-Level Lock — блокировка страницы данных
• Table-Level Lock — блокировка всей таблицы

👉 @SQLPortal

Совет по Postgres: не всегда нужно гонять SELECT COUNT(*)

Можно посмотреть внутреннюю статистику таблиц:

SELECT reltuples::numeric as count
FROM pg_class
WHERE relname = 'table_name';

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

👉 @SQLPortal

В Postgres 18 подъезжает приятное обновление: теперь при использовании pg_dump или pg_upgrade можно сохранять статистику таблиц 🎉

Это значит, что больше не нужно гонять долгий ANALYZE после апгрейда или дампа с восстановлением. Для тестов и разработки можно будет сразу брать статистику из продакшена. В pg_dump добавили опции -statistics-only и -no-statistics, так что можно самому решать, включать ли статистику.

Небольшое, но реально полезное улучшение, которое упростит жизнь многим разработчикам

👉 @SQLPortal

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

Подробнее об этом подходе тут

👉 @SQLPortal

Функции нумерации строк в 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