ApiService PostgreSQL Test Stability - Implementation Tasks

Overview

Investigation and remediation plan for unstable execution of Kds.Ai.ApiService.Tests caused by PostgreSQL connection pool exhaustion during per-test schema bootstrap.

This task plan is derived from analysis of the current test infrastructure in:

• backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Infrastructure/PostgresDatabase.cs
• backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Infrastructure/TestsBase.cs
• backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Infrastructure/AppFactory.cs

It is also grounded in the current TUnit guidance for ASP.NET integration tests and parallel execution:

• https://tunit.dev/docs/intro
• https://tunit.dev/docs/guides/performance

It is also derived from repeated local executions of:

• dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj"

Observed failure signature:

• Npgsql.NpgsqlException: The connection pool has been exhausted, either raise 'Max Pool Size' (currently 20) or 'Timeout' (currently 15 seconds) in your connection string.
• failure location: Kds.Ai.ApiService.Tests.Infrastructure.TestsBase.InitializeAsync(...)
• failure phase: Before(Test) hook, before individual test logic runs

The current test harness uses one shared PostgreSQL testcontainer per test session, creates a fresh schema per test, and performs schema creation plus seed data setup in parallel across many tests. The shared connection string explicitly caps MaxPoolSize at 20, which is too small for the current concurrency level and bootstrap pattern.

Source Documents

• repository analysis performed during this session
• failing local test output from repeated Kds.Ai.ApiService.Tests runs
• current TUnit integration test harness for API service

Spec Name

The canonical implementation spec should be referred to as ApiService PostgreSQL Test Stability.

Fixed Decisions

• This is a test infrastructure problem, not an application runtime bug.
• The goal is stable repeated execution of the full Kds.Ai.ApiService.Tests project.
• The fix must target the test harness and its PostgreSQL usage pattern, not production connection settings.
• The final state must support repeated green runs of the full project without relying on lucky scheduling.
• The solution should prefer deterministic test infrastructure changes over simply retrying failed runs.

Scope

This task includes:

• documenting the exact failure mode and reproduction path,
• identifying why per-test schema bootstrap exhausts the shared Npgsql pool,
• reviewing TUnit parallelism and the current per-test setup pattern,
• choosing and implementing a stability fix in the API test harness,
• validating the fix by re-running the full API test project multiple times.

In short, this slice ends when Kds.Ai.ApiService.Tests can run reliably as a full suite without intermittent Npgsql pool exhaustion during Before(Test) initialization.

Out of Scope

The following items are explicitly excluded from this task:

• application feature changes,
• warehouse or Redshift runtime behavior,
• changes to production PostgreSQL connection strings,
• unrelated test projects unless they need the same harness correction,
• masking the issue by documenting retries as the expected workflow.

Problem Statement

What Fails

The full API integration test project is unstable:

• some runs succeed completely,
• other runs fail before test bodies execute,
• failures happen in TestsBase.InitializeAsync(...) while opening PostgreSQL connections.

Current Harness Behavior

• PostgresDatabase provisions one shared PostgreSQL container per test session.
• PostgresDatabase.ConnectionString forces MaxPoolSize = 20.
• every test gets its own schema via TestsBase.InitializeAsync(...).
• InitializeAsync(...) opens a raw NpgsqlConnection, creates a schema, creates EF tables, and seeds data.
• DisposeAsync() later opens another raw connection and drops the schema.
• TUnit runs tests in parallel by default.

Why It Is Unstable

The current design combines:

• aggressive per-test setup,
• shared container and shared connection pool,
• explicit low pool limit,
• parallel test execution.

That means many tests compete for the same Npgsql pool at the exact same phase, and once enough tests are initializing simultaneously, connection acquisition starts timing out.

Observable Evidence

• repeated failures show the same exception family and same hook location,
• failing tests vary between runs,
• the failures are distributed across unrelated endpoint test classes,
• stack traces point to pool rent/open rather than SQL command failures,
• some full runs have succeeded earlier in the same workspace, which confirms flakiness rather than deterministic functional failure.

Reproduction

Primary reproduction command:

dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj"

Typical failing message:

Npgsql.NpgsqlException: The connection pool has been exhausted, either raise 'Max Pool Size' (currently 20) or 'Timeout' (currently 15 seconds) in your connection string.

Typical stack anchor:

Kds.Ai.ApiService.Tests.Infrastructure.TestsBase.InitializeAsync(CancellationToken cancellationToken)

Latest validation run in this workspace:

• command: dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj"
• result: 92 total, 52 passed, 40 failed, duration about 49s
• all observed failures still originate from Before(Test) in TestsBase.InitializeAsync(...)

Technical Analysis

Shared Pool Bottleneck

PostgresDatabase.ConnectionString returns the container connection string with:

var builder = new NpgsqlConnectionStringBuilder(Container.GetConnectionString())
{
    MaxPoolSize = 20,
};

This hard cap is low relative to:

• 92 tests in the project,
• parallel Before(Test) execution,
• additional connections opened by EF DbContext and WebApplicationFactory startup.

Hot Path In Every Test

TestsBase.InitializeAsync(...) does all of the following per test:

1. open raw Npgsql connection,
2. create schema,
3. construct EF KdsAiDbContext,
4. create all tables,
5. seed base rows,
6. initialize app factory lazily for later test use.

The teardown path opens another raw connection to drop the schema.

Failure Characteristics

Because the exception happens in Before(Test), the impacted tests are not semantically broken. They are simply unable to acquire a connection in time.

This is why failing test names vary from run to run and do not cluster around one business area.

TUnit-Specific Interpretation

According to the TUnit performance guidance, parallel execution should be explicitly limited when tests share constrained infrastructure such as a database. The current test project does not define any ParallelLimiter, so the engine is free to schedule more bootstrap work than the shared PostgreSQL container and connection pool can sustain.

The same guidance also matters for choosing the correct primitive:

• ParallelLimiter is the right tool for capping concurrent integration tests against a shared database,
• ParallelGroup is not sufficient because tests inside a group still run concurrently,
• NotInParallel would over-correct by serializing too much of the suite.

That makes this primarily a missing concurrency-governor problem, amplified by an artificially low MaxPoolSize.

Recommended Solution

Decision

The recommended implementation direction is Option D: combine reduced TUnit parallelism with removal of the test-only low pool cap.

This should be treated as the primary fix for the API integration harness.

Why This Is The Best Fit

• It directly addresses the root cause: too many concurrent Before(Test) database bootstraps.
• It follows the TUnit performance guidance for shared database resources.
• It keeps per-test schema isolation intact.
• It avoids the worst tradeoff of fully serial execution.
• It avoids relying on a bigger pool alone, which would only move the failure threshold.

Concrete Changes To Make

1. Add a TUnit ParallelLimiter for Kds.Ai.ApiService.Tests.

Recommended shape:

using TUnit.Core;
using TUnit.Core.Interfaces;

[assembly: ParallelLimiter<ApiServicePostgresParallelLimit>]

public sealed class ApiServicePostgresParallelLimit : IParallelLimit
{
    public int Limit => 6;
}

Rationale for starting at 6:

• each test performs schema bootstrap in Before(Test),
• each bootstrap can consume more than one connection when raw NpgsqlConnection, EF Core table creation, and app startup overlap,
• 6 is low enough to stop thundering-herd pressure but still preserves useful parallelism.

This value should be treated as the starting point, not a magic constant. If one validation pass still shows pressure, reduce to 4 or 5 before considering any larger redesign.

1. Remove the explicit MaxPoolSize = 20 override from Infrastructure/PostgresDatabase.cs.

Recommended change:

• stop forcing a test-only pool of 20,
• use the provider default pool settings from Container.GetConnectionString() unless a later measurement shows a clear reason to tune them.

Rationale:

• the current cap is much lower than the effective workload generated by the suite,
• it is not representative of the intended integration-test capacity,
• keeping the hard cap while only adding a limiter leaves unnecessarily little headroom for EF Core and host startup.

1. Keep the existing per-test schema model.

Do not replace schema isolation as part of the immediate fix. The schema-per-test pattern is not the core bug; the unbounded concurrency against that pattern is.

1. Do not use [NotInParallel] as the main project-wide fix.

That would likely stabilize the suite, but at a disproportionate runtime cost. TUnit's limiter is the more appropriate control.

Optional Hardening After Stabilization

These are secondary improvements, not the primary fix:

• move TestsBase toward TUnit's WebApplicationTest lifecycle model (SetupAsync, ConfigureTestConfiguration) so per-test setup is expressed in the framework's intended extension points,
• reduce bootstrap cost by consolidating repeated seeding logic,
• if runtime is still too high, consider a more advanced pattern such as pre-created template state or transaction-based isolation for tests that do not need schema-level independence.

Task Checklist

Phase 1: Diagnosis Lock-In

• [x] Confirm the minimal, reproducible failure mode for full Kds.Ai.ApiService.Tests
• [x] Capture the exact concurrency and connection-pool bottleneck in the current harness
• [x] Decide whether the primary fix is pool-size increase, reduced parallelism, bootstrap redesign, or a combination

Phase 2: Harness Stabilization

• [ ] Update PostgresDatabase / TestsBase infrastructure to remove the pool exhaustion failure mode
• [ ] Ensure the chosen fix is deterministic and does not rely on manual retries
• [ ] Keep test isolation guarantees intact while improving stability

Phase 3: Verification

• [ ] Run the full Kds.Ai.ApiService.Tests project repeatedly
• [ ] Verify that the suite becomes stable across repeated runs
• [ ] Document the final reasoning and any remaining tradeoffs

Candidate Fix Directions

Option A: Increase Pool Size

Raise MaxPoolSize in test-only infrastructure.

Pros:

• smallest code change,
• likely addresses immediate bottleneck.

Cons:

• may only move the threshold,
• does not reduce bootstrap pressure.

Option B: Reduce Test Parallelism For This Project Or Base Class

Constrain concurrency for tests using shared per-test schema bootstrap.

Pros:

• directly addresses contention,
• deterministic.

Cons:

• increases execution time.

Option C: Reduce Connection Churn During Setup

Refactor initialization so fewer connections are opened during Before(Test) and After(Test).

Pros:

• better long-term fix,
• lowers infrastructure pressure.

Cons:

• more intrusive change.

Option D: Combine A + B Or A + C

Most likely final direction if one isolated change is insufficient.

Recommended Selection

Choose Option D, specifically:

• use B as the controlling fix by adding a TUnit ParallelLimiter,
• combine it with a small A correction by removing the artificial MaxPoolSize = 20 bottleneck,
• keep C as a follow-up optimization only if stabilization succeeds but execution time remains unattractive.

This gives the suite a deterministic concurrency ceiling and removes the most obvious self-inflicted pool restriction without forcing a larger harness rewrite in the same change.

Success Criteria

• dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj" completes green repeatedly
• no Npgsql pool exhaustion errors occur in TestsBase.InitializeAsync(...)
• test isolation by schema remains intact
• the solution is documented as a test harness stabilization, not a production setting change