PostgreSQL - мощная система управления базами данных (СУБД)

Введение

PostgreSQL — это мощная объектно-реляционная система управления базами данных (СУБД) с открытым исходным кодом, которая сочетает в себе надёжность, гибкость и поддержку современных стандартов SQL. Благодаря активному сообществу и множеству расширений PostgreSQL используется как для небольших проектов, так и для масштабных корпоративных решений.

История развития

История PostgreSQL началась в 1986 году в Калифорнийском университете в Беркли, где профессор Майкл Стоунбрейкер запустил проект POSTGRES — преемника Ingres. В 1996 году проект получил поддержку SQL и был переименован в PostgreSQL.

1986 — старт проекта POSTGRES.
1996 — выход PostgreSQL 6.0 с поддержкой SQL.
2005 — добавлены роли и улучшенная репликация.
2010 — введены JSON-данные, параллельные запросы.
2020+ — улучшение производительности, логической репликации, индексов и поддержки больших данных.

Архитектура

Серверный процесс (postgres) — управляет соединениями, транзакциями и запросами.
WAL (Write-Ahead Logging) — механизм журналирования для отказоустойчивости.
MVCC — мультиверсионный контроль параллельности.
Расширяемость — поддержка собственных типов, функций и индексов.
Расширения — такие как PostGIS (геоданные), pg_partman (партиционирование).

Установка

Linux (Ubuntu/Debian)

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

macOS (Homebrew)

brew install postgresql
brew services start postgresql

Windows

Загрузите установщик с официального сайта и следуйте инструкциям.

Подключение

psql -U postgres -h localhost

Создание базы данных и таблиц

CREATE DATABASE company;

\c company

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    salary NUMERIC(10,2),
    hire_date DATE
);

CRUD-операции

Вставка данных

INSERT INTO employees (name, position, salary, hire_date)
VALUES ('Иван Иванов', 'Менеджер', 60000.00, '2022-05-01');

Выборка данных

SELECT name, position, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Обновление данных

UPDATE employees
SET salary = salary * 1.1
WHERE position = 'Менеджер';

Удаление данных

DELETE FROM employees WHERE id = 3;

Транзакции и MVCC

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Для отката изменений используйте ROLLBACK. MVCC обеспечивает работу транзакций без блокировок чтения.

Индексы в PostgreSQL

PostgreSQL поддерживает B-Tree, Hash, GIN, GiST, BRIN.

CREATE INDEX idx_salary ON employees(salary);

Работа с JSON/JSONB

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO products (data)
VALUES ('{"name": "Ноутбук", "price": 75000, "features": {"ram": "16GB"}}');

SELECT
    data->>'name' AS product_name,
    data->'features'->>'ram' AS ram
FROM products;

Полнотекстовый поиск

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE INDEX idx_articles_fts
ON articles USING GIN (to_tsvector('russian', body));

SELECT id, title
FROM articles
WHERE to_tsvector('russian', body)
@@ plainto_tsquery('базы данных');

Функции и процедуры

CREATE OR REPLACE FUNCTION bonus(salary NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN salary * 0.1;
END;
$$ LANGUAGE plpgsql;

SELECT name, bonus(salary) FROM employees;

Триггеры

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    action TEXT,
    created_at TIMESTAMP DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(action)
    VALUES ('INSERT INTO employees');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_employees_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_insert();

Репликация

# Настройка на мастере
wal_level = replica
max_wal_senders = 3

# На реплике
pg_basebackup -h master_host -D /var/lib/postgresql/data -U replicator -P -R

Бэкапы

# Логический дамп
pg_dump company > company.sql

# Восстановление
psql -d company -f company.sql

Безопасность

CREATE ROLE developer LOGIN PASSWORD 'StrongPass!';
GRANT SELECT, INSERT, UPDATE ON employees TO developer;

Заключение

PostgreSQL — это универсальная и мощная СУБД, которая сочетает надёжность, гибкость и производительность. Она подходит для любых задач, от стартапов до корпоративных систем.

🔗 Официальная документация PostgreSQL