pgvector Index Management Embedding Pipeline Optimization

Sizing asyncpg Pools for Embedding Ingestion

This page shows how to size an asyncpg connection pool for a high-throughput embedding ingestion service so it saturates PostgreSQL without exhausting the server’s max_connections budget. It scopes the problem narrowly: given a fixed number of concurrent workers pulling embeddings from a model API and writing vector rows, how to derive min_size/max_size, set the timeout and lifetime knobs, register the pgvector codec on every pooled connection, and keep connections off the hot path during slow embedding-API awaits.

Up: Connection Pooling for pgvector Ingestion Pipelines

A pool that is too small serializes writers behind a checkout queue and leaves PostgreSQL idle; a pool that is too large lets one service consume the whole max_connections budget, starving every other service and the autovacuum workers that keep vector tables healthy. The right size is a function of two numbers you already control — how many coroutines write concurrently, and how much of the server’s connection budget this service is allowed to claim — not a round number copied from a tutorial. Because ingestion interleaves a slow network await (the embedding API) with a fast database await (the upsert), the discipline of when you hold a pooled connection matters as much as how many you provision, which is why this sits inside the broader connection pooling for pgvector ingestion pipelines stage.

Prerequisites

  • PostgreSQL 15+ with the pgvector extension 0.5+ installed (CREATE EXTENSION vector;), and a target table with a vector(d) column already created.
  • asyncpg >= 0.28 and pgvector >= 0.2.5 (the Python package that ships pgvector.asyncpg.register_vector).
  • Knowledge of the server’s max_connections and how many other services share the same database — check with SHOW max_connections;.
  • A running asyncio ingestion loop. The concurrency-limiting and backoff patterns assumed here come from async processing with Python asyncio.
  • A batch write path. This procedure hands each pooled connection to the upsert routine described in batch upsert patterns for pgvector.
Pool sizing as a shared max_connections budget, with the API await kept outside checkout On the left, a column of concurrent ingestion workers. Each worker performs a slow embedding-API await before acquiring a connection, then checks out from a bounded asyncpg pool sized max_size. The pool sits inside a dashed budget box together with other services' pools and a headroom reserve; a caption states that the sum of every pool max plus headroom must stay at or below the server max_connections. On the right, the PostgreSQL server accepts the upsert. An annotation marks that the embedding-API await is outside the checkout and the upsert await is inside it. Pool max_size is a claim on a shared connection budget Worker 1 API await (outside) Worker 2 API await (outside) Worker N API await (outside) SHARED max_connections BUDGET This pool · max_size = N checkout holds one conn for the upsert Other services' pools Headroom (superuser + autovacuum) POSTGRESQL upsert await (inside checkout) Σ pool max_size across services + headroom ≤ max_connections
Every pool's max_size is a standing claim on the server's max_connections; the slow API await must sit outside the connection checkout.

Prerequisites recap: the sizing identity

Two rules bound the pool. The first ties max_size to how many coroutines can be inside the database section at once — never the total number of documents in flight. The second keeps the whole fleet under the server ceiling:

TEXT
sum(pool_max across every service) + headroom <= max_connections

Headroom reserves connections for superuser_reserved_connections, autovacuum, replication, and ad-hoc psql sessions. On a default max_connections = 100 server shared by three ingestion services, budgeting each pool at max_size = 25 leaves 25 for headroom — comfortable. Setting each to max_size = 40 overcommits the server and produces FATAL: sorry, too many clients already under load.

Step-by-step procedure

1. Derive max_size from database-section concurrency

Count the coroutines that can hold a connection simultaneously — this equals the concurrency limit on the write stage, not the fetch stage. If an asyncio.Semaphore caps embedding writes at 20 in flight, the pool never needs more than 20 connections plus a small burst margin. Deriving max_size from the total task count instead is the most common oversizing mistake.

PYTHON
# The pool is sized to the DB-write concurrency, not the total in-flight tasks.
WRITE_CONCURRENCY = 20          # asyncio.Semaphore bound on the upsert stage
BURST_MARGIN = 5                # absorbs brief checkout contention
POOL_MAX_SIZE = WRITE_CONCURRENCY + BURST_MARGIN   # 25

# Sanity-check against the shared server budget before deploying:
#   POOL_MAX_SIZE * n_services + HEADROOM <= max_connections

2. Create the pool with production knobs and a pgvector init callback

min_size keeps a warm floor so the first burst does not pay connection-setup latency; max_size is the hard ceiling from step 1. The init= callback runs once per physical connection as it is created (and after any reconnect), which is the only correct place to register the pgvector codec — do it per checkout and you pay the round-trip on every acquire; skip it and binding a NumPy vector raises invalid input for query argument.

PYTHON
import asyncpg
from pgvector.asyncpg import register_vector

async def _init_connection(conn: asyncpg.Connection) -> None:
    # Runs once per physical connection, not per checkout.
    await register_vector(conn)

pool = await asyncpg.create_pool(
    dsn="postgresql://ingest@db.internal:5432/vectors",
    min_size=5,
    max_size=POOL_MAX_SIZE,             # 25, from step 1
    max_inactive_connection_lifetime=300.0,   # recycle idle conns after 5 min
    command_timeout=30.0,               # cap any single statement
    max_queries=50_000,                 # recycle a conn after N queries
    init=_init_connection,
    server_settings={"application_name": "embed-ingest"},
)

Setting application_name is not cosmetic — it is how the verification query below attributes active and idle connections back to this service.

3. Hold the connection only across the database await

Acquire after the embedding API returns and release as soon as the upsert commits. Holding a pooled connection across the model-API await is the single biggest cause of pool exhaustion: a 400 ms API call pins a connection for 400 ms of pure network wait, so 25 connections cap you at ~62 writes/sec regardless of how fast PostgreSQL is.

PYTHON
import asyncio

sem = asyncio.Semaphore(WRITE_CONCURRENCY)

async def ingest_one(pool, doc_id, chunk_index, text, embed_api):
    # SLOW network await — NO connection held here.
    vector = await embed_api.embed(text)

    async with sem:                      # bound DB-section concurrency
        async with pool.acquire() as conn:   # checkout starts here
            await conn.execute(
                """
                INSERT INTO doc_chunks (doc_id, chunk_index, embedding)
                VALUES ($1, $2, $3)
                ON CONFLICT (doc_id, chunk_index)
                DO UPDATE SET embedding = EXCLUDED.embedding
                """,
                doc_id, chunk_index, vector,   # NumPy array binds via the codec
            )
        # checkout ends here — connection back in the pool immediately

4. Batch the writes to amortize the checkout

A per-row checkout wastes the connection you worked to provision. Accumulate rows and flush them through one connection with executemany or COPY, so each checkout does more work and the pool serves more writers. The batch-shaping and conflict-handling choices are covered in batch upsert patterns for pgvector.

PYTHON
async def flush_batch(pool, rows):
    # rows: list[tuple[str, int, np.ndarray]]
    async with pool.acquire() as conn:
        async with conn.transaction():
            await conn.executemany(
                """
                INSERT INTO doc_chunks (doc_id, chunk_index, embedding)
                VALUES ($1, $2, $3)
                ON CONFLICT (doc_id, chunk_index)
                DO UPDATE SET embedding = EXCLUDED.embedding
                """,
                rows,
            )

Parameter reference

Parameter Type Default Production recommendation Notes
min_size int 10 min_size = steady-state writer count (e.g. 5) Warm floor; connections opened eagerly so the first burst skips setup latency. Keep well below max_size.
max_size int 10 write-stage concurrency + small burst margin Hard ceiling and the number that counts against max_connections. Derive from the DB-section semaphore, never total tasks.
max_inactive_connection_lifetime float (s) 300.0 300.0 Idle connections above min_size are closed after this window, releasing budget back to the server between bursts.
command_timeout float (s) None 30.0 (tune to slowest upsert) Caps a single statement; prevents one stuck INSERT from pinning a connection forever. Set per-query for long index builds.
max_queries int 50000 50000 Recycles a physical connection after N queries to bound server-side memory growth on long-lived pools.
init callable None register_vector wrapper Runs once per physical connection; the correct place to register the pgvector codec. Also re-runs after reconnect.
server_settings.application_name str driver default "embed-ingest" Tags every connection so pg_stat_activity attributes load to this service.
timeout (on acquire) float (s) None 10.0 Fail fast on checkout instead of hanging when the pool is exhausted; surfaces backpressure as a catchable error.

Verification

Confirm the live pool matches the budget. Query pg_stat_activity grouped by application_name and state to see active versus idle connections per service.

SQL
SELECT application_name,
       state,
       count(*) AS conns,
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE datname = 'vectors'
GROUP BY application_name, state
ORDER BY application_name, state;

The embed-ingest row count in active plus idle should never exceed the pool’s max_size, and the sum across all application_name values plus reserved slots should sit under max_connections. From Python, pool.get_size() and pool.get_idle_size() expose the same picture without a round-trip:

PYTHON
print("pool size:", pool.get_size(), "idle:", pool.get_idle_size())

If get_size() is pinned at max_size while get_idle_size() is 0 under normal load, the pool is the bottleneck — either the API await is leaking into the checkout (step 3) or max_size is genuinely too small for the write concurrency.

Troubleshooting

  • Pool exhaustion — acquire hangs or times out. Checkouts are outrunning releases. Confirm the embedding-API await is outside pool.acquire() (step 3); an in-checkout network wait multiplies hold time by the API latency. If the API is correctly outside, raise max_size only after re-checking the max_connections budget, and set an acquire(timeout=...) so backpressure surfaces as an error instead of a hang.
  • idle in transaction connections accumulate. A code path opened a transaction (or ran a statement) and never committed or rolled back, so the connection is checked out but wedged. Wrap writes in async with conn.transaction(): (step 4) and never await a slow external call inside that block. Find offenders with SELECT pid, query FROM pg_stat_activity WHERE state = 'idle in transaction'; and set idle_in_transaction_session_timeout as a backstop.
  • invalid input for query argument $N when binding a vector. The pgvector codec is not registered on that connection. Ensure register_vector runs in the pool’s init= callback (step 2), not ad hoc after acquire(); a connection created before the callback was wired, or a raw connection outside the pool, will lack the codec.
  • FATAL: sorry, too many clients already. The fleet overcommitted max_connections. Sum every service’s max_size plus headroom against SHOW max_connections;; a too-large pool starves autovacuum and other services even if this one runs fine. Shrink max_size or move the fan-in to a proxy — see PgBouncer vs RDS Proxy for pgvector connection pooling.
  • Throughput plateaus far below PostgreSQL’s capacity. Per-row checkouts dominate. Batch writes through one connection with executemany/COPY (step 4) so each checkout amortizes across many rows, and confirm min_size is high enough that steady-state writers never pay reconnect cost.