14500
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
SQL тренажеры для практики
- sql-academy.org
- sql-ex.ru
- schoolsw3.com
- SQL Fiddle
- sqltest.online
- Oracle LiveSQL
- stratascratch.com
- stepik.org (Интерактивный тренажер SQL)
- sql-practice.com
- pgexercises.com
- HackerRank
- sqlzoo.net
👉 @SQLPortal
Менеджер баз данных с редактором SQL и искусственным интеллектом: Dataflare
👉 @SQLPortal
JSON, вставка данных.
Ты можешь изменять существующие JSONB-объекты с помощью функции jsonb_set(). Она возвращает новый JSONB-объект, где значение установлено по указанному пути ключей (keypath). Если по этому пути не хватает промежуточных объектов, она их создаст.
👉 @SQLPortal
SELECT * FROM users LIMIT 100
SQL: "Не говори больше ничего, вот вся продакшен-база данных"
👉 @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
psql-лайфхак:
\e откроет твой дефолтный текстовый редактор, чтобы написать SQL или команды. Сохраняешь файл, закрываешь, и psql автоматически выполнит то, что ты написал.
🛌🛌🛌
👉 @SQLPortal
OUTER JOIN или внешнее соединение позволяет возвратить все строки одной или двух таблиц, которые участвуют в соединении.
Outer Join имеет следующий формальный синтаксис:
SELECT столбцы
FROM таблица1
{LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1
[{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]...
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
);
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders LEFT JOIN Customers
ON Orders.CustomerId = Customers.Id;
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$
У ClickHouse появился managed-сервис Postgres.
Теперь прям очевидно, почему ClickHouse так упарывались в расширение pg_clickhouse.
Конкуренция станет еще жарче: Snowflake + Crunchy, Databricks + Neon и TigerData.
Lakehouse-платформам нужны транзакционные данные, и Postgres уже должен быть провайдером номер один.
Рад видеть, как тема Postgres для аналитики получает больше внимания и инвестиций. Для Postgres это плюс.
Ссылка: https://clickhouse.com/cloud/postgres
👉 @SQLPortal
psql-лайфхак, который экономит кучу времени: \i (include) для запуска локального файла прямо из psql.
Пример:
\i ~/Documents/query.sql
psql откроет файл, выполнит все SQL-команды внутри, и выведет результат сразу в текущую сессию (как будто ты вручную вставил содержимое). Удобно для повторяемых запросов, миграций, быстрых дебаг-скриптов и репортов.
Хочешь хранить текст? Не используй char(n) или varchar(n).
В других базах это встречается постоянно, но для Postgres — не лучшая идея.
• фиксированная ширина работает медленнее
• TEXT — переменной длины и без лимита
• если нужен размер, ставь constraint и ограничивай полем, а не типом
В итоге TEXT почти всегда выигрывает по практичности и производительности.
👉 @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)
postgres=# SELECT * FROM user_events_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
203590 | 23 | t
(1 row)
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
Совет по 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;
Радим Марек: Введение в буферы в PostgreSQL
Про то, как PostgreSQL держит данные в памяти и почему от этого сильно зависит производительность
👉 @SQLPortal
Колонарное хранилище vs построчное (row store) наглядно
Берем простую агрегацию и сравниваем, как она выполняется в row store и columnar store.
В row store данные лежат строками, поэтому движку приходится читать каждую строку целиком, чтобы из нее вытащить значение колонки age.
В columnar store данные лежат по колонкам, поэтому он читает сразу только значения колонки age, без трогания остальных полей и без лишнего I/O.
👉 @SQLPortal
На Stepik вышел курс по Linux
Этот курс закрывает всю обязательную Linux-базу для работы в IT: от установки Linux и работы с файлами до сетей, прав, дисков, процессов, автоматизации на Bash и многого другого. Всё сразу закрепляется на практике (200+ заданий с автопроверкой)
Материал подаётся понятным языком, шаг за шагом, на реальных примерах и с наглядными схемами
После прохождения вы получите сертификат, который можно добавить в резюме.
Есть бесплатные демо-уроки для ознакомления. В ближайшие 48ч курс доступен со скидкой 25% по промокоду «POST25»: открыть курс на Stepik
Вчера мы говорили про GIN-индексы, но они не поддерживают ORDER BY. В некоторых ограниченных случаях можно использовать выраженные индексы на JSON-выборках, которые просто фиксируют текстовое значение по заданному keypath (пути к ключу).
CREATE INDEX idx_state
ON users ((details->'location'->>'state'));
{ "name": "David", "location": { "city": "Lawrence", "state": "KS" } }
Yandex B2B Tech запустили Managed Sharded PostgreSQL (SPQR) – сервис для горизонтального масштабирования самой популярной опенсорс БД в мире. Теперь банкам и ритейлу станет проще обрабатывать миллионы транзакций в реальном времени и без боли масштабировать свои системы по мере роста нагрузок.
В стандартном PostgreSQL горизонтального масштабирования нет, а при больших объёмах данных без него уже никуда. Шардированный PostgreSQL позволяет ускорять запуск новых продуктов в 3–4 раза, упрощает сопровождение и снижает операционные риски – инженерам не нужно вручную собирать и поддерживать сложную инфраструктуру.
Технология уже обкатана в проде – её используют Яндекс ID, Яндекс Пэй и Едадил, стабильность подтверждена и внешними компаниями. PostgreSQL сегодня выбирают 55,6% профессиональных разработчиков, а Managed Service for PostgreSQL в Yandex Cloud обслуживает уже больше 5 тысяч клиентов.
В итоге: быстрее time-to-market, меньше затрат на разработку при запуске высоконагруженных продуктов
👉 @SQLPortal
Все надоело и пропал интерес, чувствуешь себя амебой и хочется только залипать в телефоне. Бывает?
Homo Manifestans — канал для айтишников, у которых периодически опускаются руки и отключается мозг, ибо переработки и постоянная тревожность не приводят к другим исходам 🤗
✓ Как научиться отвлекаться от работы и отдыхать?
✓ Как совместить кучу рабочих задач и время с семьей?
✓ Как справиться с прокрастинацией?
✓ Как не растерять запал, даже если начальник и коллеги 💩 и кажется, что ничего не выходит?
Подписывайтесь на канал @vadimpetrovpsi и научитесь работать без упахивания, выгорания и ущерба для личной жизни!
Псс. Заходите в закреп — там много полезного, и даже бесплатный мини-курс по выходу из апатии:
👉 /channel/+q40Dycj29QJhYmFi
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
JSON types in Postgresjson
* текст
* без дубликатов ключей
* не индексируется
* парсится во время выполнения, поэтому медленнееjsonb
* бинарный формат, заранее распарсен
* без дубликатов ключей
* индексируется
* лучше по производительности запросов
👉 @SQLPortal
Нашел интересную статью о внутреннем устройстве Postgres
👉 @SQLPortal
Мифическая база данных MySQL постепенно затухает...😭
Уже больше 4 месяцев в её публичном репозитории на GitHub не было ни единого коммита. Плохой сигнал.
Пауза совпала с сентябрьскими сокращениями в Oracle в команде, которая этим занимается.
Это не значит, что MySQL исчезнет. Она стоит на миллионах серверов и будет работать ещё годами.
Но это вполне ясный маркер чего-то более глубокого:
MySQL больше не конкурирует как open-source сообщество, а живёт как чисто коммерческий продукт.
Меньше прозрачности, другие приоритеты.
Обгон со стороны PostgreSQL уже неизбежен.
👉 @SQLPortal
PlanetScale показали pg_strict для Postgres.
Идея простая: это расширение, которое добавляет в Postgres “страховочную сетку” и ловит опасные запросы до того, как они реально выполнятся.
У всех бывало: хотел поправить одну строку, а всё уехало в UPDATE без WHERE. Или случайный DELETE, или “быстренько проверю миграцию” на не той базе. В итоге урон не от бага в коде, а от одной команды в psql.
Что обещает pg_strict:
- проверять запросы заранее
- блочить потенциально опасные операции
- спасать прямо на уровне базы, а не уже после инцидента
👉 @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);
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;
Нужно посчитать все строки в здоровенной таблице в Postgres?
SELECT count(*) FROM table;
Это может быть медленно. Можно спросить внутренние таблицы.
SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table';
Это оценка, но обычно довольно точная.
👉 @SQLPortal
Повышение производительности PostgreSQL: пошаговое руководство по использованию pg_hint_plan
Планировщик запросов PostgreSQL - это сложный инженерный механизм, обычно принимающий блестящие решения относительно того, как выполнять ваши запросы. Однако в сложных сценариях или при необычных распределениях данных вы можете знать лучший способ. Именно в таких ситуациях на помощь приходит pg_hint_plan - мощное расширение, которое позволяет вам руководить, или "советовать", планировщиком для выбора специфичного пути выполнения.
Это руководство проведет вас через весь процесс, начиная с установки pg_hint_plan из источника до использования его, чтобы принудительно выполнить сканирование индекса на большом наборе данных, демонстрируя возможности непосредственного управления производительностью вашего запроса.
👉 @SQLPortal