PostgreSQL

📜 Общее описание и историческая справка

PostgreSQL — мощная объектно-реляционная СУБД с открытым исходным кодом. Её история начинается в 1986 году в Калифорнийском университете Беркли под руководством профессора Майкла Стоунбрейкера как проект POSTGRES (Post-Ingres). В 1995 году проект был переименован в PostgreSQL, чтобы подчеркнуть поддержку SQL. В 1996 году сообщество разработчиков открыло исходный код под лицензией PostgreSQL License. С тех пор PostgreSQL завоевал репутацию самой продвинутой открытой СУБД, обладающей высокой надёжностью, поддержкой стандартов SQL и расширяемостью. Ключевые вехи: версия 8.0 (2005) — поддержка Windows, версия 9.0 (2010) — горячее резервное копирование, версия 10 (2017) — логическая репликация и партиционирование, версия 12 (2019) — улучшенные возможности JSON, версия 14 (2021) — параллельные запросы и улучшения производительности, версия 16 (2023) — дополнительные возможности для администраторов.

Источники: Официальная история PostgreSQL, книга "The Internals of PostgreSQL" (H. Sawada).

🏭 Область применения и рекомендации

PostgreSQL используется в самых разных проектах:

  • Геоинформационные системы (благодаря расширению PostGIS).
  • Веб-приложения высокой нагрузки (Instagram, Reddit, Spotify).
  • Аналитические платформы и хранилища данных.
  • Проекты, где важна строгая согласованность и сложные запросы (финансовые системы).

Рекомендации: PostgreSQL — отличный выбор для большинства приложений, особенно когда требуется поддержка стандартов SQL, сложные типы данных (JSONB, массивы), а также расширяемость. Он бесплатен и имеет активное сообщество.

🛠️ Как создать/инициировать базу

-- Подключиться к серверу и создать базу
CREATE DATABASE mydb;

-- Переключиться на новую базу (в psql)
\c mydb;

-- Создать таблицу
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Также можно создать базу через pgAdmin, используя графический интерфейс, или через утилиту createdb в командной строке.

💡 Основные нюансы языка (SQL в PostgreSQL)

PostgreSQL строго следует стандарту SQL, но имеет свои особенности:

  • Поддержка JSONB — эффективное хранение и индексирование JSON-документов.
  • Типы данных: массивы, составные типы, диапазоны, геометрические типы.
  • Расширяемость: пользовательские функции на PL/pgSQL, Python, Perl и др.
  • Полнотекстовый поиск (tsvector, tsquery).
  • Оконные функции и общие табличные выражения (WITH RECURSIVE).
  • Управление параллелизмом на основе MVCC (многоверсионность).

🔑 Наборы ключевых команд

-- Список баз
\l

-- Выборка с ограничением
SELECT * FROM users LIMIT 10;

-- Вставка с возвратом id
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com') RETURNING id;

-- Обновление
UPDATE users SET email = 'new@example.com' WHERE username = 'john_doe';

-- Анализ таблицы (обновление статистики)
ANALYZE users;

-- Вакуум (очистка мёртвых строк)
VACUUM VERBOSE users;

📊 Построение запросов (сложные примеры)

Рекурсивный запрос для построения иерархии категорий:

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Агрегация с использованием JSONB:

SELECT 
    department_id,
    jsonb_agg(jsonb_build_object('name', name, 'salary', salary)) AS employees
FROM employees
GROUP BY department_id;

Оконные функции с накоплением итога:

SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

📚 Ссылки на документы, статьи, книги