Function NewPostgreSQL

internal/database/postgres.go:41–84

NewPostgreSQL creates a new instance of the PostgreSQL database

(ctx context.Context, connectionURI string)

// NewPostgreSQL creates a new instance of the PostgreSQL database
func NewPostgreSQL(ctx context.Context, connectionURI string) (*PostgreSQL, error) {
	// Parse connection config for pool settings
	config, err := pgxpool.ParseConfig(connectionURI)
	if err != nil {
		return nil, fmt.Errorf("failed to parse PostgreSQL config: %w", err)
	}

	// Configure pool for stability-focused defaults.
	// MaxConns was 30 per pod (60 total across 2 replicas) which saturated under
	// the 2026-04-28 scraper bursts (15 req/s on /v0/servers caused queue blowup
	// even though individual queries were fast). 60 per pod gives 120 total,
	// leaving 80 of PG max_connections=200 for autovacuum/admin/headroom.
	config.MaxConns = 60                      // Handle scraper-burst concurrent load
	config.MinConns = 10                      // Keep connections warm for fast response
	config.MaxConnIdleTime = 30 * time.Minute // Keep connections available for bursts
	config.MaxConnLifetime = 2 * time.Hour    // Refresh connections regularly for stability

	// Create connection pool with configured settings
	pool, err := pgxpool.NewWithConfig(ctx, config)
	if err != nil {
		return nil, fmt.Errorf("failed to create PostgreSQL pool: %w", err)
	}

	// Test the connection
	if err = pool.Ping(ctx); err != nil {
		return nil, fmt.Errorf("failed to ping PostgreSQL: %w", err)
	}

	// Run migrations using a single connection from the pool
	conn, err := pool.Acquire(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to acquire connection for migrations: %w", err)
	}
	defer conn.Release()

	migrator := NewMigrator(conn.Conn())
	if err := migrator.Migrate(ctx); err != nil {
		return nil, fmt.Errorf("failed to run database migrations: %w", err)
	}

	return &PostgreSQL{
		pool: pool,
	}, nil
}

// buildFilterConditions constructs WHERE clause conditions from a ServerFilter
//