PostgreSQL connection pooling for WriterPostgres and ReaderPostgres

[oto-macenauer-absa]

Feature Description

Introduce connection pooling for both WriterPostgres (src/writers/writer_postgres.py) and ReaderPostgres (src/readers/reader_postgres.py) so that a new TCP connection to PostgreSQL is not opened on every write or read call.

Problem / Opportunity

Both WriterPostgres and ReaderPostgres currently call psycopg2.connect() on every invocation, opening and tearing down a full TCP connection each time. Under any sustained load — particularly for the new POST /stats/{topic_name} read endpoint which is expected to serve dashboard-style traffic — this will rapidly exhaust Postgres max_connections. Lambda warm instances reuse the handler/writer/reader objects across invocations, so a persistent or pooled connection can be maintained at the instance level.

Acceptance Criteria

• WriterPostgres reuses a single connection (or draws from a pool) across warm-Lambda invocations instead of calling psycopg2.connect() per write.
• ReaderPostgres does the same for read_stats() calls.
• Stale/broken connections are detected and re-established automatically (e.g. via connection.closed check or a ping before use).
• No real PostgreSQL calls are made in unit tests — existing mock patterns in conftest.py continue to work unchanged.
• Integration tests pass with pooled connections.
• All quality gates pass (./ci_local.sh).

Proposed Solution

Use psycopg2.pool.SimpleConnectionPool (min=1, max=1 for Lambda single-threaded execution) lazily initialised on the writer/reader instance. Before each operation, acquire a connection from the pool, return it after use. On OperationalError (broken pipe, server restart), discard the pool and reinitialise.

Alternatively, store a single lazy connection on the instance and check connection.closed before each use — simpler but less future-proof if threading assumptions change.

A pg_bouncer sidecar at the infrastructure layer is a valid complement but does not remove the need for application-level connection reuse within a warm Lambda instance.

Dependencies / Related

• Identified in PR EventStats lambda with Stats endpoint #113 review (finding Forcefully terminate environment on request #9).
• Affects both event_gate_lambda.py (write path) and event_stats_lambda.py (read path, introduced in EventStats lambda with Stats endpoint #113).