Что такое PostgreSQL?

# Что такое PostgreSQL

PostgreSQL — это мощная, открытая объектно-реляционная система управления базами данных (ORDBMS). Это один из самых популярных выборов для enterprise-приложений, особенно в сочетании с Java.

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

1. Open Source

• Исходный код открыт и свободно распространяется
• Лицензия PostgreSQL (очень permissive)
• Активное сообщество разработчиков
• Не требует лицензионных платежей

2. ACID гарантии

-- Пример ACID транзакции
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Либо обе операции выполнены, либо ни одна

Постгрес гарантирует:

• Atomicity: Либо всё, либо ничего
• Consistency: Данные остаются в консистентном состоянии
• Isolation: Транзакции не влияют друг на друга
• Durability: Сохранённые данные не теряются

3. Поддержка сложных типов данных

-- JSON
CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSONB  -- JSON Binary (оптимизированный)
);

INSERT INTO users VALUES (1, '{"name": "John", "age": 30}');

-- Массивы
CREATE TABLE posts (
    tags TEXT[],
    metrics INTEGER[][]
);

-- UUID
CREATE TABLE items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

-- Геопространственные данные (PostGIS)
CREATE TABLE locations (
    id INT,
    point POINT,
    polygon POLYGON
);

4. Мощный язык запросов SQL

-- Common Table Expressions (CTE)
WITH RECURSIVE employees AS (
    SELECT id, name, manager_id, 1 as level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, level + 1 
    FROM employees e
    JOIN employees parent ON e.manager_id = parent.id
)
SELECT * FROM employees;

-- Window Functions
SELECT 
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_department_salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- Full Text Search
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & performance');

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

1. Подключение через JDBC

import java.sql.*;

public class PostgreSQLConnection {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String user = "postgres";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        }
    }
}

2. Использование с Hibernate/JPA

import javax.persistence.*;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "name", nullable = false)
    private String name;
    
    @Column(name = "email", unique = true)
    private String email;
    
    @Type(type = "jsonb")
    private UserMetadata metadata;  // PostgreSQL JSONB
}

3. Spring Data JPA

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.email = :email")
    User findByEmail(@Param("email") String email);
    
    @Query(value = "SELECT * FROM users WHERE data->>'role' = :role", nativeQuery = true)
    List<User> findByJsonRole(@Param("role") String role);
}

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

1. Надежность

-- PostgreSQL очень надёжен в production
-- Гарантии целостности данных
ALTER TABLE orders ADD CONSTRAINT fk_user 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

2. Масштабируемость

-- Индексы для быстрого поиска
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

-- Composite индексы
CREATE INDEX idx_user_status_date ON users(status, created_at);

3. Расширяемость

-- Пользовательские функции на PL/pgSQL
CREATE FUNCTION calculate_age(birthdate DATE) RETURNS INT AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(birthdate));
END;
$$ LANGUAGE plpgsql;

SELECT name, calculate_age(dob) FROM users;

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

• Row-level security (RLS)
• Шифрование данных
• Гранулярный контроль доступа

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy ON accounts
    USING (user_id = current_user_id());

Сравнение с другими СУБД

Практический пример: Blog приложение

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    tags TEXT[],
    metadata JSONB,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_posts_user ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published, created_at DESC);
CREATE INDEX idx_comments_post ON comments(post_id);

Когда использовать PostgreSQL

✅ Используй PostgreSQL если:

• Нужны ACID гарантии
• Сложная бизнес-логика в SQL
• JSON данные
• Масштабируемый проект
• Enterprise приложения
• Нужна полная поддержка SQL стандартов

❌ Рассмотри альтернативы если:

• Очень высокие требования к write-тропроизводительности (MongoDB)
• Простое приложение без транзакций
• Нужна горизонтальная масштабируемость из коробки

Заключение

PostgreSQL — это надёжная, мощная и гибкая реляционная база данных, отлично подходящая для Java-приложений. Её сочетание ACID гарантий, поддержки JSON, мощного SQL и активного сообщества делает её идеальным выбором для production-систем.