Pool gauges are wrong at rest: pool_overflow starts at -pool_size, pool_checked_out never returns to 0, pool_size is the configured maximum #54

Versions: sqlalchemy-foundation-kit 0.4.0 (same code on master, adcd645), SQLAlchemy 2.0.54, asyncpg 0.31.0, prometheus-client 0.26.0, Python 3.13.12, PostgreSQL 17

attach_metrics samples the three pool gauges from the pool's checkout and checkin events (session/manager.py:145-162). None of them means what its help text and the docs say:

• postgres_db_pool_overflow exports pool.overflow() unchanged (session/manager.py:150). QueuePool starts its overflow counter at -pool_size and adds one for each connection it opens (sqlalchemy/pool/impl.py:138, 253-254). With size=10, the gauge reads -9 after the first request and stays negative until more than size connections are open. The help text is "Number of connections over pool_size (within max_overflow)" (contrib/metrics/postgres.py:99-102).
• postgres_db_pool_size exports pool.size(), which is the maxsize set in QueuePool's config (impl.py:244-245). It stays at 10 whether the pool holds one connection or ten. The help text says "Current database connection pool size" (contrib/metrics/postgres.py:91-94), docs/agents.md:339 says "connections the pool holds", and docs/guide/advanced.md:271 says "Current pool size".
• All three go stale after a burst. They are written only on checkout/checkin. SQLAlchemy dispatches checkin before it gives the connection back (sqlalchemy/pool/base.py:769-772: pool.dispatch.checkin(...), then pool._return_conn(self)). So the last sample of a burst still counts the connection being returned, and nothing updates the gauges until the next checkout. An idle pool reports checked_out=1 or more for as long as it stays idle.

Reproduction

import asyncio

from prometheus_client import REGISTRY
from sqlalchemy import text
from sqlalchemy_foundation_kit import AsyncSessionManager
from sqlalchemy_foundation_kit.contrib.metrics import PostgresMetrics
from sqlalchemy_foundation_kit.contrib.settings import PoolSettings
from testcontainers.postgres import PostgresContainer


def gauges() -> str:
    values = {g: REGISTRY.get_sample_value(f"postgres_db_pool_{g}") for g in ("size", "checked_out", "overflow")}
    return "  ".join(f"{name}={value}" for name, value in values.items())


async def main(url: str) -> None:
    manager = AsyncSessionManager(
        url,
        poolclass="async_adapted_queue",
        pool_settings=PoolSettings(size=10, max_overflow=20),
        metrics=PostgresMetrics(),
    )
    pool = manager.engine.pool

    async def hold(n: int) -> None:
        checked_out, release = asyncio.Event(), asyncio.Event()
        ready = 0

        async def one() -> None:
            nonlocal ready
            async with manager.get_session() as session:
                await session.execute(text("SELECT 1"))
                ready += 1
                if ready == n:
                    checked_out.set()
                await release.wait()

        tasks = [asyncio.create_task(one()) for _ in range(n)]
        await checked_out.wait()
        print(f"{n:2} connections checked out:  {gauges()}   (open connections: {pool.checkedin() + pool.checkedout()})")
        release.set()
        await asyncio.gather(*tasks)

    def after() -> None:
        print(f"all returned:                {gauges()}   (pool.checkedout()={pool.checkedout()}, "
              f"open connections: {pool.checkedin() + pool.checkedout()})")

    await hold(1)
    after()
    await hold(12)
    after()
    await manager.aclose()


with PostgresContainer("postgres:17-alpine", driver="asyncpg") as pg:
    asyncio.run(main(pg.get_connection_url()))

Actual

 1 connections checked out:  size=10.0  checked_out=1.0  overflow=-9.0   (open connections: 1)
all returned:                size=10.0  checked_out=1.0  overflow=-9.0   (pool.checkedout()=0, open connections: 1)
12 connections checked out:  size=10.0  checked_out=12.0  overflow=2.0   (open connections: 12)
all returned:                size=10.0  checked_out=2.0  overflow=2.0   (pool.checkedout()=0, open connections: 10)

The first idle pool exports overflow=-9 and checked_out=1 while it has one open connection and none in use. After the burst, every connection is back and the two overflow connections are closed, yet the exporter still reports two connections in use and two in overflow. It keeps reporting that until the next request.

Why

• record_pool_stats (session/manager.py:145-153) passes pool.size(), pool.checkedout() and pool.overflow() through unchanged. Those are QueuePool's internal counters: overflow() is "open connections minus pool_size", which can be negative, and size() is the configured maximum.
• It runs from on_checkout (session/manager.py:155-158) and on_checkin (session/manager.py:160-162). No event fires after a connection has been returned, so the value written by the last checkin is always one return behind.

Proposal

• Read the pool when Prometheus scrapes rather than from events, e.g. Gauge.set_function(pool.checkedout), bound when the metrics are attached to an engine. A narrow optional capability, detected the way CheckoutWaitRecorder is, would keep PostgresMetricsProtocol unchanged. This fixes the staleness and also removes listener work from every checkout and checkin.
• Export pool_overflow as max(pool.overflow(), 0).
• For pool_size, either change the help text and docs to "configured pool_size", or export open connections (checkedin() + checkedout()) and publish the configured pool_size/max_overflow as separate series. The "Pool utilization" query in docs/guide/advanced.md:336-337 divides checked_out by pool_size, so it goes above 100% as soon as overflow connections are used. The pool's capacity is pool_size + max_overflow.