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.
Top comments (0)