Как использовал PostgreSQL в своих проектах на C#?

Моё использование PostgreSQL в проектах на C#

В моих проектах PostgreSQL выступает как мощная и надежная реляционная база данных, сочетающаяся с ORM Entity Framework Core (EF Core) для эффективной работы с данными в C#. Вот ключевые аспекты применения.

Интеграция через EF Core и Npgsql

Основной способ подключения — использование драйвера Npgsql и EF Core. Конфигурация в DbContext выглядит следующим образом:

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Database=mydb;Username=postgres;Password=secret");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Настройка отношений и индексов
        modelBuilder.Entity<User>()
            .HasIndex(u => u.Email)
            .IsUnique();

        modelBuilder.Entity<Order>()
            .HasOne(o => o.User)
            .WithMany(u => u.Orders)
            .HasForeignKey(o => o.UserId);
    }
}

Использование специфичных функций PostgreSQL

PostgreSQL предлагает уникальные возможности, которые я активно применял:

• JSONB для полуструктурированных данных: В проектах с гибкой схемой данных использовал тип JSONB для хранения динамических атрибутов.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Дополнительные динамические свойства
    [Column(TypeName = "jsonb")]
    public string Attributes { get; set; }
}

// В запросах использовал LINQ для работы с JSON
var products = context.Products
    .Where(p => EF.Functions.JsonContains(p.Attributes, @"{ "color": "red" }"))
    .ToList();

• Полнотекстовый поиск: Для реализации сложного поиска по тексту применял Full-Text Search с индексами GIN.

-- Создание индекса в PostgreSQL
CREATE INDEX idx_articles_content ON articles USING gin(to_tsvector('english', content));

// Использование в C# через raw SQL или расширения
var articles = context.Articles
    .FromSqlRaw("SELECT * FROM articles WHERE to_tsvector('english', content) @@ plainto_tsquery('english', {0})", searchTerm)
    .ToList();

• Расширения (Extensions): Например, PostGIS для геоданных в проектах с картами или pgcrypto для шифрования на уровне базы.

Миграции и управление схемой данных

EF Core миграции использовались для управления изменениями схемы базы данных. Однако для сложных изменений (например, переименование столбцов с данными) часто применялись raw SQL миграции для минимизации рисков.

// Пример миграции EF Core с дополнительным SQL
migrationBuilder.Sql("ALTER TABLE users ADD COLUMN IF NOT EXISTS temporary_column TEXT;");

Оптимизация производительности

• Индексы: Тщательно проектировал индексы (B-tree, GIN, GIST) на основе анализа запросов. Использовал CREATE INDEX CONCURRENTLY для избежания блокировок в production.
• Connection Pooling: Настройка пула соединений Npgsql для уменьшения времени открытия новых подключений.
• READ COMMITTED Isolation Level: В большинстве случаев использовал этот уровень изоляции для баланса между параллельностью и согласованностью.

Работа с транзакциями и хранимыми процедурами

Для сложной бизнес-логики иногда использовал хранимые процедуры и функции PostgreSQL, вызываемые из C#.

CREATE OR REPLACE FUNCTION calculate_discount(order_id INT)
RETURNS DECIMAL AS $$
BEGIN
    -- Логика расчета
    RETURN ...;
END;
$$ LANGUAGE plpgsql;

using var transaction = context.Database.BeginTransaction();
try
{
    var discount = context.Database
        .SqlQueryRaw<decimal>("SELECT calculate_discount({0})", orderId)
        .First();
    transaction.Commit();
}
catch
{
    transaction.Rollback();
}

Мониторинг и анализ запросов

Регулярно использовал EXPLAIN ANALYZE для анализа планов выполнения запросов, особенно для сложных JOIN и агрегаций. Инструменты вроде pg_stat_statements помогали выявлять "тяжелые" запросы в production.

Инфраструктура и резервное копирование

В production-окружении использовал:

• Репликацию (master-slave) для увеличения доступности и распределения нагрузки чтения.
• Регулярное логическое резервное копирование (pg_dump) и физическое (PITR) для восстановления данных.
• Мониторинг через Prometheus и Grafana с экспортером для PostgreSQL.

Сложные запросы и отчетность

Для генерации сложных отчетов с агрегациями и оконными функциями (window functions) часто использовал direct SQL запросы, которые затем мапились на C# объекты через FromSqlRaw.

var salesReport = context.SalesReport
    .FromSqlRaw("SELECT region, sum(amount) as total, rank() OVER (ORDER BY sum(amount) DESC) FROM sales GROUP BY region")
    .ToList();

Таким образом, PostgreSQL в моих проектах — это не просто хранилище данных, а мощный инструмент с использованием его специфичных функций, глубокой интеграцией через EF Core и вниманием к оптимизации и надежности в production.