PostgreSQL для веб-разработчика

Введение

PostgreSQL всё чаще появляется в веб-проектах, где раньше безальтернативно использовался MySQL. Не потому что «модно», а потому что задачи усложнились: JSON-данные, аналитика, сложные выборки, отчёты, очереди, агрегации. В таких сценариях MySQL начинает «скрипеть».

Эта статья — не про внутренности PostgreSQL и не для DBA. Это практический гайд для веб-разработчика, который пишет backend на PHP, Node.js или Python и хочет нормально работать с PostgreSQL, а не бояться его.

Почему я пишу эту статью

Я несколько раз делал миграции с MySQL на PostgreSQL в реальных проектах: интернет-магазины, внутренние сервисы, API, аналитика. И каждый раз видел одни и те же грабли: неправильные типы, отсутствие индексов, страх EXPLAIN ANALYZE, попытки использовать PostgreSQL «как MySQL».

Хочу сэкономить тебе пару ночей и один нервный срыв.

Кому адресовано

веб-разработчикам с опытом MySQL / SQLite
тем, кто выбирает БД для нового проекта
тем, кто уже использует PostgreSQL, но «по минимуму»

Чего здесь не будет

теории реляционных БД
архитектуры PostgreSQL
тюнинга shared_buffers на 40 страниц
DBA-магии

PostgreSQL vs MySQL в 2026

Критерий	PostgreSQL	MySQL
Типы данных	Богатые (JSONB, ARRAY, UUID)	Базовые
JSON	JSONB + индексы	JSON без индексов
Аналитика	Window Functions, CTE	Ограниченно
Расширяемость	EXTENSION	Почти нет
Лицензия	PostgreSQL License	GPL / коммерция
Full-text	Встроенный	Слабее
Когда выбирать	Сложные запросы, аналитика	Простой CRUD

Коротко:

CRUD-блог, простой сайт — MySQL;
API, аналитика, JSON, отчёты — PostgreSQL.

Установка PostgreSQL

Ubuntu / Debian

sudo apt update
sudo apt install postgresql postgresql-contrib

CentOS / Rocky Linux

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

macOS (Homebrew)

brew install postgresql@16
brew services start postgresql@16

Windows (WSL2)

Установи Ubuntu в WSL2
Далее — как для Ubuntu
Работай только внутри WSL, не из-под Windows.

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

sudo -u postgres psql

Типичная ошибка:

psql: FATAL: role "user" does not exist

Решение:

sudo -u postgres createuser --interactive

Типы данных: отличия от MySQL

INTEGER, SERIAL vs AUTO_INCREMENT

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

SERIAL — это sugar над sequence. В PostgreSQL это нормально.

VARCHAR vs TEXT

email TEXT

В PostgreSQL нет разницы по производительности между ними. TEXT — твой друг.

BOOLEAN vs TINYINT(1)

is_active BOOLEAN DEFAULT true

Без 0/1. Человечно.

JSONB

CREATE TABLE products (
  id SERIAL,
  attrs JSONB
);

SELECT *
FROM products
WHERE attrs @> '{"color": "red"}';

JSONB индексируется и реально работает. Подробно: снипет «JSONB и GIN-индекс».

ARRAY

tags TEXT[];

SELECT *
FROM articles
WHERE 'postgres' = ANY(tags);

Массивы прямо в БД — иногда очень удобно.

UUID

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE events (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT
);

TIMESTAMPTZ vs TIMESTAMP

created_at TIMESTAMPTZ DEFAULT now()

Всегда используй TIMESTAMPTZ. Внутри хранится UTC, локаль выставляется на уровне приложения.

Фичи PostgreSQL, которых нет в MySQL

Window Functions

SELECT
  product_id,
  category_id,
  RANK() OVER (PARTITION BY category_id ORDER BY sales DESC)
FROM products;

Без подзапросов и боли.

CTE (WITH)

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT COUNT(*) FROM paid_orders;

Читаемо и удобно.

Full-Text Search

SELECT *
FROM articles
WHERE to_tsvector(title || ' ' || body)
      @@ plainto_tsquery('postgres');

LISTEN / NOTIFY

LISTEN order_created;
NOTIFY order_created, '{"id":123}';

Простейшая очередь без Kafka.

Практические примеры

Пример 1: e-commerce

SELECT
  o.id,
  SUM(oi.price * oi.qty) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

Пример 2: поиск + фильтр

SELECT *
FROM products
WHERE to_tsvector(name)
      @@ plainto_tsquery('iphone')
AND price < 50000;

Пример 3: JSON

SELECT *
FROM products
WHERE attrs->>'brand' = 'Apple';

Пример 4: Window Functions

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) t
WHERE rn <= 3;

Пример 5: отчёт по продажам за период (CTE)

WITH daily_totals AS (
  SELECT
    date_trunc('day', created_at) AS day,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'paid'
    AND created_at >= now() - interval '30 days'
  GROUP BY date_trunc('day', created_at)
)
SELECT
  day::date,
  revenue,
  SUM(revenue) OVER (ORDER BY day) AS revenue_cumulative
FROM daily_totals
ORDER BY day;

Пример 6: выборка с JSONB и агрегацией

-- Товары с атрибутом «цвет» и средняя цена по бренду
SELECT
  attrs->>'brand' AS brand,
  COUNT(*) AS cnt,
  ROUND(AVG((attrs->>'price')::numeric), 2) AS avg_price
FROM products
WHERE attrs ? 'color'
GROUP BY attrs->>'brand'
HAVING COUNT(*) > 5;

Миграция с MySQL на PostgreSQL

Подготовка

mysqldump db > dump.sql

Основные правки схемы

AUTO_INCREMENT → SERIAL
VARCHAR(255) → TEXT
TINYINT(1) → BOOLEAN

Способ А: pgloader (рекомендую)

pgloader mysql://user:pass@localhost/db postgresql://user:pass@localhost/db

Подходит для больших баз.

Способ Б: SQL-конвертер

Работает, но требует ручной доработки.

Проверка данных

SELECT COUNT(*) FROM users;

Миграция схемы в Laravel (PostgreSQL)

После переноса данных приложение должно создавать таблицы под Postgres. Пример миграции:

// database/migrations/xxxx_create_products_table.php
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->jsonb('attrs')->nullable();  // не json(), а jsonb()
    $table->boolean('is_active')->default(true);
    $table->timestamps();
});
// Индекс по JSONB для поиска
DB::statement('CREATE INDEX idx_products_attrs_gin ON products USING GIN (attrs)');

Откат

Всегда держи MySQL read-only, пока не проверил всё.

ORM и клиенты

Node.js (pg — нативный клиент)

Готовый снипет с Pool и параметризованными запросами: PostgreSQL: Node.js (pg) — Pool и запросы.

const { Pool } = require('pg');
const pool = new Pool({
  host: 'localhost',
  database: 'myapp',
  user: 'app_user',
  password: process.env.DB_PASSWORD,
});

// Параметризованный запрос (защита от SQL-инъекций)
const res = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);

// JSONB-поле
const products = await pool.query(
  `SELECT * FROM products WHERE attrs @> $1`,
  [JSON.stringify({ color: 'red' })]
);

Prisma (схема под PostgreSQL):

generator client {
  provider = "prisma-client-js"
}
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
model User {
  id    Int    @id @default(autoincrement())
  email String
}

TypeORM — аналогично, в ormconfig укажи type: 'postgres'.

Python (psycopg2)

import psycopg2
from psycopg2.extras import RealDictCursor

conn = psycopg2.connect(
    host="localhost", dbname="myapp",
    user="app_user", password="secret"
)

with conn.cursor(cursor_factory=RealDictCursor) as cur:
    cur.execute(
        "SELECT * FROM orders WHERE created_at > %s",
        (datetime.now() - timedelta(days=7),)
    )
    rows = cur.fetchall()

PHP

Doctrine — driver: pdo_pgsql, типы как в документации.
Laravel Eloquent — в .env ставишь DB_CONNECTION=pgsql, миграции те же, для JSONB используй $table->jsonb('attrs').

ORM vs raw SQL

CRUD → ORM
отчёты, аналитика → сырой SQL

EXPLAIN ANALYZE

Перед тем как оптимизировать — замерь. Пошаговый снипет: EXPLAIN ANALYZE — как найти медленный запрос. Запрос:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';

Пример вывода без индекса (плохо):

Seq Scan on orders  (cost=0.00..1234.00 rows=5000 width=80) (actual time=0.05..89.22 rows=4823 loops=1)
  Filter: (created_at > (now() - '7 days'::interval))
  Rows Removed by Filter: 95177
Planning Time: 0.12 ms
Execution Time: 91.45 ms

Seq Scan по всей таблице, отфильтровано 95k строк — дорого.

После создания индекса CREATE INDEX idx_orders_created_at ON orders(created_at);:

Index Scan using idx_orders_created_at on orders  (cost=0.42..312.18 rows=5000 width=80) (actual time=0.03..8.11 rows=4823 loops=1)
  Index Cond: (created_at > (now() - '7 days'::interval))
Planning Time: 0.08 ms
Execution Time: 9.02 ms

Index Scan — в разы быстрее. Ориентируйся на:

Seq Scan на больших таблицах — кандидат на индекс.
Index Scan / Index Only Scan — хорошо.
Если rows в плане сильно отличается от actual rows — обнови статистику: ANALYZE orders;.

Резервные копии

Готовые команды и варианты форматов: снипет «pg_dump и pg_restore».

pg_dump

pg_dump db > backup.sql
pg_dump -s db > schema.sql

pg_restore

pg_restore -d db backup.dump

Cron

0 3 * * * pg_dump db | gzip > /backup/db.sql.gz

S3 (пример)

aws s3 cp backup.sql.gz s3://my-backups/

Мониторинг и оптимизация

Логи медленных запросов

В postgresql.conf или через ALTER SYSTEM:

ALTER SYSTEM SET log_min_duration_statement = 500;  -- логировать запросы дольше 500 ms

Индексы

CREATE INDEX idx_orders_created_at ON orders(created_at);

Нет индекса на created_at в большой таблице = боль.

VACUUM

PostgreSQL не чистит всё за тебя идеально. Важен автовакуум и периодический ANALYZE.

Размеры

SELECT pg_database_size('db');
SELECT pg_table_size('orders');

Типичные ошибки

забыли COMMIT или долго держим транзакцию открытой;
дедлоки при конкурентных транзакциях;
сортировка и фильтрация по полям без индекса;
хранение структурированных данных в JSON вместо нормальной таблицы;
TRUNCATE ... CASCADE без понимания, что удалится.

Итог

PostgreSQL — это не «сложная БД для избранных». Это рабочий инструмент для веб-разработчика, если использовать его по назначению.

Если проект перерастает CRUD — PostgreSQL почти всегда даёт больше возможностей и меньше костылей.

Главное — не пытайся писать под PostgreSQL как под MySQL.

Требования