Performance: Replace 10ms sleep with Ristretto Wait() in ConnectionCache.SetWithTTL

[e-gineer]

Description

connection/connection_cache.go contains a hardcoded time.Sleep(10 * time.Millisecond) after every cache write operation. This causes significant linear performance degradation under load.

Current Code (line 60)

func (c *ConnectionCache) SetWithTTL(ctx context.Context, key string, value interface{}, ttl time.Duration) error {
    // ...
    c.cache.Set(ctx, key, value, store.WithExpiration(ttl))
    
    // wait for value to pass through buffers (necessary for ristretto)
    time.Sleep(10 * time.Millisecond)  // <-- PROBLEM
    
    return err
}

Why This Was Added

The sleep was added when ConnectionCache was created (commit 5799c20, August 2022) with the comment "wait for value to pass through buffers (necessary for ristretto)". Ristretto uses buffered writes for performance, and the intent was to ensure subsequent Get() calls would see the value.

Why This Is Problematic

• The 10ms value is arbitrary - Ristretto buffer flush typically takes microseconds
• The sleep is unconditional - applied even when no immediate Get() follows
• Under load, this accumulates to massive latency:
  • 100 cache writes = 1 second of sleep
  • 1000 cache writes = 10 seconds of sleep

Proposed Fix

Use Ristretto's built-in Wait() method instead. From Ristretto's documentation:

Wait() blocks until all buffered writes have been applied. This ensures a call to Set() will be visible to future calls to Get().

This provides the same correctness guarantee while only waiting as long as actually necessary.

Benchmark Results

Benchmark	Before (10ms sleep)	After (Wait())	Improvement
SetWithTTL	10,932,740 ns (10.9ms)	1,729 ns (0.0017ms)	~6,300x faster
SetWithTTLThenGet	10,903,701 ns (10.9ms)	2,062 ns (0.0021ms)	~5,300x faster

Testing Performed

1. Benchmark test - Proves the performance improvement
2. Consistency test - Verifies Wait() properly ensures immediate visibility after writes (100 consecutive set/get pairs all succeed)
3. Full SDK test suite - All existing tests pass (go test ./...)

Risk Assessment

Factor	Assessment
Concurrency risk	LOW - Wait() is the proper synchronization mechanism provided by Ristretto
Performance impact	VERY POSITIVE - ~6,300x improvement per cache write
Scope of change	Small - 3 lines changed in one file
Rollback difficulty	Trivial
Behavior change	None - same correctness guarantees, just faster

Impact

This affects all Steampipe plugins that use ConnectionCache. Under high-throughput scenarios (many connections, frequent cache writes), this fix eliminates a significant source of latency.