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

В нескольких production-проектах я использовал PostgreSQL как основную реляционную БД благодаря ее надежности, богатому функционалу и открытой лицензии. Работа строилась через Npgsql — официальный и высокопроизводительный ADO.NET-провайдер.

1. Подключение и конфигурация Устанавливается пакет Npgsql.EntityFrameworkCore.PostgreSQL для EF Core.

// В Startup.cs / Program.cs
services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection"))
           // Важная оптимизация для PostgreSQL
           .UseSnakeCaseNamingConvention() // Опционально: snake_case в БД
);

Строка подключения в appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Host=localhost;Database=myapp;Username=postgres;Password=secret"
}

2. Работа с продвинутыми типами данных PostgreSQL

• JSONB: Хранение и запросы к полуструктурированным данным.

  // Модель EF Core
  public class Product
  {
      public int Id { get; set; }
      // Словарь метаданных хранится как JSONB
      public Dictionary<string, object> Metadata { get; set; } = new();
  }
  // В DbContext
  modelBuilder.Entity<Product>()
              .Property(p => p.Metadata)
              .HasColumnType("jsonb");
  
  // Запрос с фильтрацией по JSONB полю
  var products = await dbContext.Products
      .Where(p => p.Metadata["color"].ToString() == "red")
      .ToListAsync();

• Массивы и составные типы: Полезны для специализированных сценариев.

3. Выполнение сырых SQL-запросов Для сложных отчетов или оптимизированных запросов.

using var connection = new NpgsqlConnection(connectionString);
var sql = @"
    SELECT u.name, COUNT(o.id) as order_count
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.created_at > @cutoffDate
    GROUP BY u.id
    HAVING COUNT(o.id) > @minOrders";

var results = await connection.QueryAsync<UserStats>(sql, new {
    cutoffDate = DateTime.UtcNow.AddMonths(-1),
    minOrders = 5
});
// Используется Dapper для простого маппинга, но можно и через DataReader.

4. Ключевые практики

• Индексы: Активно использовал GIN-индексы для JSONB и полнотекстового поиска, BRIN для временных рядов.
• Конкурентность: Использование FOR UPDATE SKIP LOCKED для реализации надежных очередей задач.
• Репликация: Настройка чтения из реплик для распределения нагрузки в веб-приложениях.