Performance Tuning for CocoIndex Connectors: PostgreSQL & SQLite Optimization Guide

Performance tuning for cocoindex connectors relies on respecting driver-specific bind-parameter limits, scaling asyncpg connection pools for PostgreSQL, and batching writes inside transactions for SQLite to eliminate I/O bottlenecks.

CocoIndex connectors translate declarative target states into high-throughput database operations using intelligent batching and connection management. This guide examines the internal architecture of the PostgreSQL and SQLite connectors in the cocoindex-io/cocoindex repository to provide actionable performance tuning for cocoindex connectors that maximizes throughput while respecting underlying driver constraints.

Architectural Deep Dive into Connector Batching

Understanding how CocoIndex handles bulk operations requires examining the two-level target hierarchy and batching strategies implemented in the core connector modules.

PostgreSQL Connector Internals (TableTarget and RowTarget)

In python/cocoindex/connectors/postgres/_target.py, the TableTarget class manages schema-level operations while RowTarget handles individual upserts and deletes. The connector respects asyncpg's hard limit of 32,767 bind parameters per query (defined as _BIND_LIMIT = 32767).

The engine automatically calculates chunk sizes using max(1, _BIND_LIMIT // num_pk) to ensure primary key bindings never exceed the driver limit. When executing bulk deletes, the _execute_delete_chunk method (lines 701-730) processes these chunks asynchronously, while _execute_upsert_chunk handles insertions.

Connection management relies on dependency injection via ContextKey[asyncpg.Pool]. The TableTarget retrieves the pool during initialization, allowing the engine to share connections across multiple targets.

SQLite Connector Internals (ManagedConnection)

The SQLite implementation in python/cocoindex/connectors/sqlite/_target.py uses a ManagedConnection wrapper around a single sqlite3.Connection. Unlike PostgreSQL, SQLite limits variables to 999 per query (_BIND_LIMIT = 999).

Thread-safe concurrent access is guaranteed by an internal RWLock: reads acquire a shared lock via the readonly() context manager, while writes acquire an exclusive lock through transaction(). Vector operations leverage the sqlite-vec extension, loaded lazily via Vec0TableDef when first accessed.

Critical Performance Bottlenecks

Bottleneck	Impact	Mitigation
Bind-parameter overflow	Exceeding _BIND_LIMIT triggers TooManyBindings errors and forces per-row fallback queries.	The connector auto-chunks data, but you must ensure pool sizing accommodates parallel chunk execution.
Connection pool saturation	Small asyncpg pools create contention under high write throughput.	Increase max_size to 20-30 connections and maintain min_size warm connections.
Transaction overhead	Individual commits per row generate excessive WAL traffic and fsync calls.	Use ManagedConnection.transaction() for SQLite and let the PostgreSQL connector handle internal batching.
Extension cold-start	First vector operation incurs latency loading pgvector or sqlite-vec.	Pre-load extensions during application initialization.
SQLite write-lock contention	The global RWLock serializes writes, potentially blocking readers.	Group writes into large transactions and shard data across multiple SQLite files if write volume is extreme.

Practical Performance Tuning Strategies

Scale PostgreSQL Throughput with Larger asyncpg Connection Pools

By default, asyncpg creates modest connection pools. For high-throughput ETL workloads, explicitly configure a larger pool to allow parallel execution of multiple chunked queries.

import asyncpg
import cocoindex as coco
from cocoindex.connectors.postgres import TableTarget

async def init_pg_pool(dsn: str) -> asyncpg.Pool:
    return await asyncpg.create_pool(
        dsn,
        min_size=5,
        max_size=30,          # Increase from default 10

        command_timeout=60,
    )

# Inject the pool via ContextKey for dependency injection

db_key = coco.ContextKey[asyncpg.Pool]("postgres")
env = coco.Environment()
env.context_provider.provide(
    db_key, 
    await init_pg_pool("postgresql://user:pw@localhost/db")
)

# TableTarget automatically retrieves the pool via the ContextKey

target = await coco.use_mount(
    TableTarget, 
    table_name="high_volume_events", 
    db=db_key
)

Increasing max_size allows the CocoIndex engine to schedule simultaneous batch jobs for different partitions, each respecting the 32,767 bind-parameter limit while maximizing CPU utilization on the PostgreSQL server.

Maximize SQLite Throughput with Transaction Batching

SQLite's performance degrades significantly without transaction wrapping due to disk synchronization overhead. The ManagedConnection.transaction() context manager acquires the write lock and defers commits until the block exits.

from cocoindex.connectors.sqlite import TableTarget, ManagedConnection
import cocoindex as coco

# Obtain the managed connection (created lazily on first use)

conn = await coco.use_mount(ManagedConnection)

# Generate sample data

rows = [{"id": i, "value": f"v{i}"} for i in range(10_000)]

# Bulk insert inside a single transaction

with conn.transaction():
    table = await coco.use_mount(
        TableTarget, 
        table_name="items", 
        connection=conn
    )
    for row in rows:
        table.upsert(row)

This pattern reduces 10,000 individual commits to a single atomic transaction, improving write throughput by orders of magnitude while the internal RWLock ensures thread safety.

Eliminate Cold-Start Latency by Pre-loading Extensions

Vector operations require loading extensions (pgvector for PostgreSQL, sqlite-vec for SQLite). Loading these during application initialization prevents latency spikes during the first batch ingestion.

# PostgreSQL: Pre-load pgvector

await pool.execute("CREATE EXTENSION IF NOT EXISTS vector")

# SQLite: Pre-load sqlite-vec within a transaction

with conn.transaction():
    conn._conn.execute("SELECT load_extension('sqlite-vec')")

In python/cocoindex/connectors/postgres/_target.py, the _VectorIndexHandler class assumes the extension exists; pre-creating it ensures the first vector insert doesn't stall waiting for CREATE EXTENSION to complete.

Summary

• Respect bind limits: The PostgreSQL connector chunks data using _BIND_LIMIT // num_pk (32,767 max), while SQLite uses a 999-parameter limit; never exceed these driver boundaries.
• Scale connection pools: Increase asyncpg max_size to 20-30 connections for PostgreSQL to enable parallel batch execution without pool starvation.
• Batch inside transactions: Use ManagedConnection.transaction() for SQLite to amortize commit costs, and rely on the PostgreSQL connector's internal chunking for bulk operations.
• Pre-load extensions: Initialize pgvector or sqlite-vec at startup to eliminate first-batch extension loading delays.
• Monitor lock contention: For SQLite, group writes aggressively; for PostgreSQL, monitor asyncpg.Pool saturation to prevent connection wait times.

Frequently Asked Questions

How does CocoIndex handle PostgreSQL's bind parameter limit?

In python/cocoindex/connectors/postgres/_target.py, the TableTarget class automatically calculates chunk sizes using max(1, _BIND_LIMIT // num_pk) where _BIND_LIMIT is 32,767. Rows are partitioned into chunks processed by _execute_upsert_chunk or _execute_delete_chunk, ensuring no single query exceeds asyncpg's parameter binding limit.

What is the optimal connection pool size for high-throughput PostgreSQL workloads?

While the default asyncpg pool size is 10, production workloads handling millions of rows should configure max_size=20 to 30 connections. This allows CocoIndex to execute multiple chunked queries in parallel—each respecting the 32,767 bind limit—without connection starvation. Set min_size=5 to maintain warm connections and avoid connection establishment overhead.

How can I maximize SQLite write performance in CocoIndex?

Wrap bulk operations inside ManagedConnection.transaction() as implemented in python/cocoindex/connectors/sqlite/_target.py. This acquires the write-lock once and defers disk synchronization until the transaction commits, reducing I/O overhead from 10,000 individual fsync calls to a single atomic commit. For extreme write volumes, consider sharding across multiple SQLite files to bypass the global RWLock serialization.

When should I pre-load vector extensions with CocoIndex connectors?

Always pre-load pgvector (PostgreSQL) and sqlite-vec (SQLite) during application initialization before processing the first batch. The first call to create a vector index or insert vector data triggers CREATE EXTENSION IF NOT EXISTS or SELECT load_extension(), causing measurable latency spikes. Pre-loading ensures consistent throughput from the first query.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}