postgres

client-side patterns for postgres (examples use asyncpg). moved here from the python notes.

connection pools

a pool amortizes connection setup and caps concurrent connections. lazily initialize a module-level singleton and hand out connections through a context manager so every use site gets acquire/release right:

_pool: asyncpg.Pool | None = None

async def get_pool() -> asyncpg.Pool:
    global _pool
    if _pool is None:
        _pool = await asyncpg.create_pool(db_url, min_size=2, max_size=10)
    return _pool

@asynccontextmanager
async def get_conn() -> AsyncGenerator[asyncpg.Connection, None]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        yield conn

batch writes with unnest

unnest() turns parallel arrays into rows, so thousands of inserts become one statement and one round trip — pass one array per column:

await conn.execute(
    """
    INSERT INTO follows (follower_id, rkey, subject_id)
    SELECT * FROM unnest($1::bigint[], $2::text[], $3::bigint[])
    ON CONFLICT (follower_id, rkey) DO UPDATE
    SET subject_id = EXCLUDED.subject_id
    """,
    follower_ids, rkeys, subject_ids,
)

ON CONFLICT ... DO UPDATE makes the batch an upsert, so replays are idempotent.

sources

• follower-weight/db.py