Что такое PostgreSQL?

Что такое PostgreSQL

PostgreSQL — это мощная, свободная объектно-реляционная система управления базами данных (ORDBMS). Она известна надёжностью, расширяемостью и соответствием стандартам SQL. PostgreSQL может работать на всех основных операционных системах и часто используется в enterprise приложениях.

История и популярность

PostgreSQL разрабатывается с 1986 года (изначально как Postgres, на базе INGRES). Сегодня это вторая по популярности реляционная БД после MySQL/MariaDB, но во многих enterprise проектах она предпочтительнее благодаря своей мощности и надёжности.

Используют: Spotify, Instagram, Skype, GitHub, Reddit, Uber и многие другие.

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

1. ACID транзакции

• A (Atomicity) — всё выполняется или ничего
• C (Consistency) — БД остаётся в консистентном состоянии
• I (Isolation) — транзакции не мешают друг другу
• D (Durability) — данные сохраняются навсегда

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Либо обе UPDATE, либо ни одна

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

SELECT * FROM articles 
 WHERE to_tsvector('russian', content) @@ to_tsquery('russian', 'python');

3. JSON и JSONB типы данных

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  metadata JSONB  -- JSONB быстрее для запросов
);

INSERT INTO users VALUES (1, 'Alice', '{"age": 30, "city": "New York"}');

SELECT * FROM users WHERE metadata->>'age' = '30';

4. Массивы

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  tags VARCHAR[] -- Массив строк
);

INSERT INTO products VALUES (1, ARRAY['electronics', 'laptop', 'portable']);

SELECT * FROM products WHERE 'laptop' = ANY(tags);

5. Расширяемость через расширения (Extensions)

-- Установка расширения для работы с UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100)
);

-- PostGIS для географических данных
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(POINT, 4326)  -- Точка с координатами
);

Установка и подключение

Установка (Linux/Ubuntu):

sudo apt update
sudo apt install postgresql postgresql-contrib

# Запуск сервиса
sudo systemctl start postgresql
sudo systemctl status postgresql

Подключение:

# Через psql (клиент PostgreSQL)
sudo -u postgres psql

# Или через параметры
psql -h localhost -U postgres -d mydb

Основные SQL операции

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

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  salary DECIMAL(10, 2),
  department VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Индексы для производительности:

CREATE INDEX idx_email ON employees(email);  -- B-tree (стандарт)
CREATE INDEX idx_name_gin ON employees USING GIN(name);  -- Для массивов
CREATE INDEX idx_full_text ON articles USING GIN(to_tsvector('russian', content));

Запросы:

-- SELECT
SELECT name, salary FROM employees WHERE salary > 50000;

-- GROUP BY
SELECT department, COUNT(*), AVG(salary) 
FROM employees 
GROUP BY department;

-- JOIN
SELECT e.name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.id;

-- Подзапросы
SELECT name FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

Использование с Python

С помощью psycopg2 (низкоуровневый драйвер):

import psycopg2

connection = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="secret"
)

cursor = connection.cursor()

# Выполнение запроса
cursor.execute("SELECT * FROM employees WHERE salary > %s", (50000,))
results = cursor.fetchall()

for row in results:
    print(row)

cursor.close()
connection.close()

С помощью SQLAlchemy (ORM):

from sqlalchemy import create_engine, Column, Integer, String, Numeric
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql://postgres:secret@localhost/mydb')
Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    salary = Column(Numeric)

Session = sessionmaker(bind=engine)
session = Session()

# Запрос
employees = session.query(Employee).filter(Employee.salary > 50000).all()
for emp in employees:
    print(emp.name, emp.salary)

session.close()

Транзакции и блокировки

try:
    cursor.execute("BEGIN;")
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    cursor.execute("COMMIT;")
    connection.commit()
except Exception as e:
    cursor.execute("ROLLBACK;")
    connection.rollback()
    print(f"Ошибка: {e}")

SELECT FOR UPDATE (блокировка для обновления):

BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- Заблокировать строку
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Резервные копии и восстановление

# Экспорт в SQL файл
pg_dump -U postgres mydb > backup.sql

# Экспорт в бинарный формат (быстрее)
pg_dump -U postgres -Fc mydb > backup.dump

# Восстановление из SQL
psql -U postgres mydb < backup.sql

# Восстановление из бинарного
pg_restore -U postgres -d mydb backup.dump

Репликация и масштабирование

Репликация (для отказоустойчивости):

• Primary (master) — основная БД
• Replica (slave) — копия для чтения

# Проверка репликации
psql -c "SELECT * FROM pg_stat_replication;"

Docker для PostgreSQL

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  postgres:13

# Подключение из хоста
psql -h localhost -U postgres -d mydb

Преимущества PostgreSQL

1. Надёжность — ACID транзакции, целостность данных
2. Масштабируемость — работает с огромными объёмами данных
3. Расширяемость — расширения, пользовательские типы данных
4. Производительность — оптимизированный запросчик
5. Бесплатность — open source, нет лицензионных расходов
6. Стандартизация — почти полное соответствие SQL стандарту
7. JSON и массивы — гибкость для структурированных данных

Выводы

• PostgreSQL — это мощная реляционная БД для production
• ACID транзакции обеспечивают надёжность
• JSON, массивы, расширения делают её гибкой
• Индексы и оптимизация обеспечивают производительность
• Репликация даёт отказоустойчивость
• Используй SQLAlchemy или psycopg2 для работы из Python