Введение в PostgreSQL

Что такое PostgreSQL

PostgreSQL — объектно-реляционная СУБД с открытым исходным кодом. Существует с 1986 года (как Postgres), с 1996 — PostgreSQL.

Ключевые особенности:

• ACID — транзакции полностью надёжны.
• Расширяемость — свои типы, функции, операторы.
• JSONB — документы внутри реляционной БД.
• Полнотекстовый поиск.
• Геоданные (PostGIS).
• Векторный поиск (pgvector).
• Бесплатная лицензия (PostgreSQL License).

Почему PostgreSQL

Популярность:

• Stack Overflow Survey — второй год подряд СУБД №1.
• Крупные проекты: Instagram, Reddit, Twitch, Apple, Discord, GitLab.
• Заменяет MySQL в новых проектах всё чаще.

Плюсы против MySQL:

• Строже типизация.
• Лучше JSON.
• Больше индексов (GIN, GiST, BRIN).
• Оконные функции и CTE.
• Материализованные представления.
• Настоящие транзакции DDL.

Клиент-серверная модель

Приложение → PostgreSQL Server → Файлы на диске

Компоненты:

• Сервер — один процесс postgres, обслуживает подключения.
• Backend-процессы — по одному на клиента.
• Файлы данных — хранятся в PGDATA.
• WAL — журнал предзаписи для надёжности.

Установка (Ubuntu)

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql

Проверка:

sudo -u postgres psql --version
# psql (PostgreSQL) 16.2

Docker:

docker run -d \
    --name postgres \
    -e POSTGRES_PASSWORD=secret \
    -p 5432:5432 \
    postgres:16-alpine

Первое подключение

sudo -u postgres psql

Внутри:

SELECT version();
\l              -- список баз
\du             -- список ролей
\q              -- выход

Создание базы

CREATE DATABASE myapp;
CREATE USER app WITH ENCRYPTED PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE myapp TO app;

-- Переключиться
\c myapp

-- Дать права на схему public
GRANT ALL ON SCHEMA public TO app;

Структура данных

Иерархия:

Сервер
  └── База данных (myapp)
        └── Схема (public)
              └── Таблица (users)
                    └── Строка
                          └── Колонка

Схема — пространство имён. public — по умолчанию.

Подключение из приложения

Laravel (.env):

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=myapp
DB_USERNAME=app
DB_PASSWORD=secret

Node.js (pg):

import pg from 'pg';

const pool = new pg.Pool({
    host: '127.0.0.1',
    port: 5432,
    database: 'myapp',
    user: 'app',
    password: 'secret',
});

const result = await pool.query('SELECT NOW()');
console.log(result.rows);

Строка подключения:

postgresql://app:secret@127.0.0.1:5432/myapp

Роли и права

Роль — это и пользователь, и группа.

-- Создать роль
CREATE ROLE readonly LOGIN PASSWORD 'read123';

-- Дать права
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Правило: приложению давать только необходимые права. Не postgres.

Схема vs База данных

БД — изолированный набор данных, свой набор подключений. Схема — внутри одной БД.

Когда что:

• Отдельная БД — для разных приложений.
• Отдельная схема — для логических групп внутри приложения (auth, billing, analytics).

Практика

1. Установите PostgreSQL (apt или Docker).

2. Создайте базу и пользователя:

CREATE DATABASE shop;
CREATE USER shop_user WITH PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE shop TO shop_user;
\c shop
GRANT ALL ON SCHEMA public TO shop_user;

3. Подключитесь:

psql -h 127.0.0.1 -U shop_user -d shop

4. Проверьте версию:

SELECT version();
SELECT current_database(), current_user;

Итог

• PostgreSQL — объектно-реляционная СУБД с ACID.
• Клиент-сервер: backend-процесс на клиента.
• Иерархия: сервер → БД → схема → таблица.
• Роли — пользователи и группы.
• Права — выдавать по минимуму.
• Установка: apt, Docker, бинарники.

Дальше — установка и подключение (углублённо).