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

Наглядный гайд с функциями от SQL до Pandas

👉 @SQLPortal

Знаешь, что Postgres можно тюнить по одной таблице?

ALTER TABLE SET позволяет задавать отдельные параметры тюнинга, такие как fill factor, vacuum и параллельные воркеры, для конкретных нагрузок. Это более гранулярный и точный подход и может быть лучше, чем настройка на уровне всей базы данных.

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

ALTER TABLE orders
SET (
  fillfactor = 80,
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05,
  parallel_workers = 4
);

ALTER TABLE events_log
SET (
  fillfactor = 70,
  autovacuum_enabled = true,
  parallel_workers = 2
);

👉 @SQLPortal

В Postgres 18 появилась поддержка аутентификации пользователей через OAUTH 2.0

👉 @SQLPortal

Использование привязанных переменных — ключ к быстрой работе приложений

Пишите SQL вот так:

'SELECT ... WHERE col = ' || var

Каждое новое значение VAR создаёт новый запрос ⇒ много разборов ⇒ медленно

Используйте привязку вот так:

'SELECT ... WHERE col = :bind'

Один и тот же запрос для любого значения ⇒ один разбор ⇒ быстро

👉 @SQLPortal

Внутренняя архитектура PostgreSQL

1. Архитектура на основе процессов
PostgreSQL использует архитектуру «один процесс на соединение», то есть для каждого клиента создаётся отдельный процесс операционной системы.

2. Журнал предзаписи
PostgreSQL применяет WAL для обеспечения надёжности данных, согласованности, восстановления после сбоев и репликации

3. Многоверсионность и управление параллелизмом
Благодаря MVCC PostgreSQL позволяет выполнять параллельные транзакции без необходимости жёсткой блокировки данных

4. Конвейер выполнения запросов
Запросы в PostgreSQL проходят через чёткую последовательность этапов: разбор, планирование, исполнение и возврат результатов

5. Система индексации
PostgreSQL поддерживает множество типов индексов (B-Tree, GIN, GiST, BRIN и др.) для оптимизации запросов под разные типы данных

6. Партиционирование таблиц
PostgreSQL позволяет разбивать большие таблицы на более мелкие части (партиции) по диапазону значений, списку или хешу, что упрощает управление и ускоряет обработку.

7. Логическая декодировка
Позволяет транслировать изменения из WAL в логическом формате, что полезно для репликации и систем отслеживания изменений (CDC).

8. Расширения
PostgreSQL построен как модульная система и поддерживает расширения, которые позволяют добавлять новые функции и возможности.

9. Сбор статистики
Механизм сбора статистики PostgreSQL отслеживает активность в реальном времени, что помогает анализировать и оптимизировать производительность базы данных

👉 @SQLPortal

Это визуальная шпаргалка по SQL-командам, оформленная в виде рисунков с фруктами и человечками. Она охватывает основные конструкции SQL на примере таблицы fruits

По моему одна из лучших, что есть 🐸

👉 @SQLPortal

Ты обожаешь Postgres и SQL, но начальству нужны отчёты в Excel? К счастью, в Postgres есть простой способ выгрузить данные из SQL-запроса сразу в CSV.

Экспортировать CSV-файл для любого SQL-запроса можно так:

psql --csv -c 'select * from orders'

psql уже содержит встроенную утилиту для работы с CSV. Просто укажи параметры подключения, запрос в кавычках — и готово.

– флаг --csv выводит данные в формате CSV прямо в stdout
– добавь >, чтобы направить вывод в файл на локальной системе

Этот способ не такой гибкий, как \copy, но для простого экспорта результатов запроса — это быстрое и удобное решение.

Прямой вывод в CSV избавляет от лишней постобработки. Нам нравится, как psql позволяет быстро и эффективно работать со структурированными данными — для отчётов, аудита и загрузки в таблицы.

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

Появилась новая игра для изучения SQL — SQL Protocol.

Она сделана в стиле «Матрицы». Внутри надо играть за агента, идти по сюжету и решать множество SQL-задач от простых к сложным. Также есть PvP Arena 🤑

👉 @SQLPortal

Лёгковесный настольный клиент с открытым исходным кодом, который позволяет подключаться к разным базам данных через единый инструмент: MySQL, PostgreSQL, SQLite, Redis, MongoDB, ClickHouse и другие. Не нужно ставить отдельные клиенты под каждую СУБД.

DBX собран на Tauri 2 + Vue.js 3 + Rust, размер установщика около 15 МБ, без бандла Chromium.

Поддерживает подключение к десяткам баз данных, включая OceanBase и openGauss. SQL-редактор содержит AI-ассистента для генерации и оптимизации запросов на естественном языке; таблицы поддерживают inline-редактирование, сортировку и поиск; для Redis и MongoDB есть отдельные браузеры.

https://github.com/t8y2/dbx

👉 @SQLPortal

Превратите PostgreSQL в тайм-серверную СУБД с помощью одного расширения

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

TimescaleDB решает это через гипертаблицы, которые автоматически разбивают данные на временные чанки.

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

Другие возможности:
• уменьшение объёма хранения до 95% за счёт колоночного сжатия с полной поддержкой запросов
• ускорение запросов через непрерывные агрегаты, которые обновляются только новыми данными
• встроенные политики хранения для автоматического удаления старых данных

Плюс TimescaleDB — это open source. Достаточно установить расширение и продолжать использовать PostgreSQL.

Ссылка: https://tsdb.co/ktran-x

👉 @SQLPortal

В Oracle SQL можно проверить, получится ли привести выражение к нужному типу данных, с помощью функции

VALIDATE_CONVERSION ( <expr> AS <datatype>, <fmt> )

Она пытается преобразовать выражение в указанный тип

Если формат не указан, используется формат сеанса (настройки NLS)

Возвращает 1, если преобразование удалось, и 0, если нет

👉 @SQLPortal

Нашёл удобный сервис для быстрого запуска SQL-запросов онлайн — RunSQL

> Мгновенно пишешь и выполняешь SQL без установки ПО
> Поддержка PostgreSQL
> Можно быстро делиться своими запросами через ссылку
> Подходит для тестов, демонстраций и обучения

Просто открыл сайт — и сразу работаешь с базой. Минимализм и скорость

👉 @SQLPortal

Роадмэп по SQL

SQL MASTER TREE
│
├── 1. Database Fundamentals
│ ├── What is DB / DBMS / RDBMS
│ ├── Tables, Rows, Columns
│ ├── Primary Key
│ ├── Foreign Key
│ ├── Candidate Key
│ ├── Composite Key
│ ├── Constraints
│ │ ├── NOT NULL
│ │ ├── UNIQUE
│ │ ├── PRIMARY KEY
│ │ ├── FOREIGN KEY
│ │ ├── CHECK
│ │ └── DEFAULT
│ └── Data Integrity
│
├── 2. SQL Data Types
│ ├── Numeric
│ │ ├── INT
│ │ ├── BIGINT
│ │ ├── DECIMAL
│ │ └── FLOAT
│ ├── String
│ │ ├── CHAR
│ │ ├── VARCHAR
│ │ └── TEXT
│ ├── Date & Time
│ │ ├── DATE
│ │ ├── TIME
│ │ ├── DATETIME
│ │ └── TIMESTAMP
│ └── Boolean / Binary
│
├── 3. DDL (Data Definition Language)
│ ├── CREATE
│ │ ├── DATABASE
│ │ ├── TABLE
│ │ └── INDEX
│ ├── ALTER
│ │ ├── ADD COLUMN
│ │ ├── MODIFY COLUMN
│ │ └── DROP COLUMN
│ ├── DROP
│ │ ├── DATABASE
│ │ └── TABLE
│ └── TRUNCATE
│
├── 4. DML (Data Manipulation Language)
│ ├── INSERT
│ ├── UPDATE
│ ├── DELETE
│ └── MERGE / UPSERT
│
├── 5. DQL (Data Query Language)
│ ├── SELECT
│ ├── DISTINCT
│ ├── WHERE
│ │ ├── AND
│ │ ├── OR
│ │ └── NOT
│ ├── ORDER BY
│ ├── GROUP BY
│ ├── HAVING
│ └── LIMIT / OFFSET
│
├── 6. SQL Operators
│ ├── Arithmetic (+ - * /)
│ ├── Comparison (= != > < >= <=)
│ ├── Logical (AND OR NOT)
│ ├── BETWEEN
│ ├── IN
│ ├── LIKE
│ └── IS NULL
│
├── 7. SQL Functions
│ ├── Aggregate
│ │ ├── COUNT
│ │ ├── SUM
│ │ ├── AVG
│ │ ├── MIN
│ │ └── MAX
│ ├── String
│ │ ├── CONCAT
│ │ ├── SUBSTRING
│ │ ├── LENGTH
│ │ └── TRIM
│ ├── Numeric
│ │ ├── ROUND
│ │ └── ABS
│ └── Date
│ ├── NOW
│ ├── DATEADD
│ └── DATEDIFF
│
├── 8. Joins
│ ├── INNER JOIN
│ ├── LEFT JOIN
│ ├── RIGHT JOIN
│ ├── FULL JOIN
│ ├── CROSS JOIN
│ └── SELF JOIN
│
├── 9. Subqueries
│ ├── Scalar Subquery
│ ├── Correlated Subquery
│ └── Nested Subquery
│
├── 10. Views
│ ├── CREATE VIEW
│ ├── UPDATE VIEW
│ └── MATERIALIZED VIEW
│
├── 11. Indexing
│ ├── Clustered Index
│ ├── Non-Clustered Index
│ ├── Composite Index
│ └── Index Optimization
│
├── 12. Transactions
│ ├── BEGIN
│ ├── COMMIT
│ ├── ROLLBACK
│ └── SAVEPOINT
│
├── 13. ACID Properties
│ ├── Atomicity
│ ├── Consistency
│ ├── Isolation
│ └── Durability
│
├── 14. Normalization
│ ├── 1NF
│ ├── 2NF
│ ├── 3NF
│ ├── BCNF
│ └── Denormalization
│
├── 15. Advanced SQL
│ ├── Stored Procedures
│ ├── Triggers
│ ├── CTE (WITH)
│ ├── Window Functions
│ │ ├── ROW_NUMBER
│ │ ├── RANK
│ │ ├── DENSE_RANK
│ │ └── PARTITION BY
│ └── Recursive Queries
│
├── 16. Performance Optimization
│ ├── Query Optimization
│ ├── Execution Plan
│ ├── Index Tuning
│ └── Query Caching
│
├── 17. SQL Ecosystem
│ ├── MySQL
│ ├── PostgreSQL
│ ├── SQLite
│ ├── SQL Server
│ └── Oracle DB
│
└── 18. Real-World Usage
├── Backend APIs
├── Data Analytics
├── Reporting Systems
├── ETL Pipelines
└── Data Warehousing

👉 @SQLPortal

13 типов баз данных :

1. Реляционные
↳ хранят структурированные данные в таблицах с фиксированной схемой и доступом через SQL-запросы.

2. Ключ-значение
↳ хранят простые пары ключ–значение для сверхбыстрого поиска и кэширования.

3. Колонночные
↳ хранят данные по колонкам вместо строк для оптимизации аналитических запросов.

4. Wide-column (ширококолонночные)
↳ используют семейства колонок для гибкого хранения в распределённых системах большого масштаба.

5. Документные
↳ хранят данные как JSON-подобные документы с вложенной и гибкой структурой.

6. Векторные
↳ хранят эмбеддинги для поиска похожих объектов и задач ИИ-извлечения.

7. Временных рядов
↳ хранят данные с метками времени для метрик, логов и событий.

8. Неизменяемые реестры
↳ фиксируют записи без возможности изменения или удаления для защиты от подмены данных.

9. Графовые
↳ хранят связи между сущностями и оптимизируют запросы по отношениям.

10. Геопространственные
↳ работают с координатами и геоданными для карт и пространственных запросов.

11. В памяти
↳ держат данные в ОЗУ для минимальной задержки доступа.

12. BLOB-хранилища
↳ предназначены для больших бинарных объектов: изображения, видео, файлы.

13. Полнотекстовый поиск
↳ индексируют текст для быстрого поиска по содержимому.

👉 @SQLPortal

Совет по продуктивной работе с Postgres: шаблонные базы данных

Многие разработчики используют шаблонные базы данных для настройки дев-окружений. Суть подхода в следующем: вы запускаете один экземпляр Postgres для разработки и при необходимости создаёте/удаляете базы данных. При этом база данных для разработки создаётся на основе шаблона, который синхронизирован со схемой продакшена.

Для распределённых команд, использующих облачные решения, шаблонная база — это экономичный и безопасный способ работы.

Преимущества использования шаблонной базы данных для разработки:

● Упрощает работу с расширениями, конфигурацией Postgres, управлением версиями и установкой зависимостей — всё, что обычно замедляет локальную разработку
● Обеспечивает соответствие схемы продакшену, так как шаблон можно быстро создать из полной копии базы без данных
● Сокращает время на развёртывание и удаление дев-машин
● Позволяет использовать анонимизированные тестовые данные, подходящие для разработки

Подробнее о шаблонных базах данных

➡️ Создайте шаблон на основе продакшена, выгрузив только схему и расширения:

pg_dump -U my_user -h my_host -p 5432 -d my_database --exclude-table-data --no-owner --no-privileges > template.sql

Флаг --exclude-table-data исключает данные, но сохраняет полную схему и все расширения.

➡️ Создайте базу и восстановите в неё шаблон:

-- создаём базу
CREATE DATABASE my_template_db OWNER postgres;

-- восстанавливаем из дампа
psql -U my_user -h my_host -p 5432 -d my_template_db -f template.sql

Добавьте сид-данные на этом этапе. Это позволит быстро клонировать шаблон без необходимости повторного восстановления.

➡️ Разрешите использовать базу как шаблон:

ALTER DATABASE my_template_db IS_TEMPLATE TRUE;

Важно: использовать шаблонные базы можно только от имени суперпользователя Postgres. На платформе Crunchy Bridge это поддерживается, но не все облачные провайдеры предоставляют доступ к суперюзеру.

➡️ Создайте новую базу на основе шаблона:

CREATE DATABASE new_db WITH TEMPLATE my_template_db OWNER my_user;

Это создаст копию my_template_db под именем new_db

➡️ Обновите строку подключения, чтобы использовать новую базу в разработке — и можно начинать писать код.

👉 @SQLPortal

Аналог DBeaver: https://tableplus.com/

Тот же клиент для работы с базами данных с графическим интерфейсом.

По сути, это инструмент, который позволяет:

подключаться к разным СУБД (MySQL, PostgreSQL, SQLite, SQL Server и др.)
писать и выполнять SQL-запросы
смотреть и редактировать данные в таблицах
управлять схемой базы (таблицы, индексы и т.д.)

Кстати, скоро туда завезут прикольную фичу: возможность перетаскивать столбцы из левой панели прямо в редактор SQL-запросов. 🫡

👉 @SQLPortal

В копилку тренажёров: sqltutor.ru

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

👉 @SQLPortal

LangChain Community Spotlight: text2sql

Агентный text-to-SQL SDK, построенный на Deep Agents из LangChain, который автономно исследует схемы, пишет запросы и сам себя корректирует — достигая 100% точности на бенчмарке Spider без использования извлечения с дополнением генерации или предрассчитанных схем.

Посмотреть: https://github.com/Text2SqlAgent/text2sql-framework

👉 @SQLPortal