# Performance Tuning for CocoIndex Connectors: PostgreSQL & SQLite Optimization Guide

> Optimize CocoIndex connectors for PostgreSQL and SQLite. Learn to tune performance by respecting driver limits, scaling connection pools, and batching writes to eliminate I/O bottlenecks.

- Repository: [CocoIndex/cocoindex](https://github.com/cocoindex-io/cocoindex)
- Tags: performance
- Published: 2026-05-05

---

**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`](https://github.com/cocoindex-io/cocoindex/blob/main/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`](https://github.com/cocoindex-io/cocoindex/blob/main/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.

```python
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.

```python
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.

```python

# 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`](https://github.com/cocoindex-io/cocoindex/blob/main/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`](https://github.com/cocoindex-io/cocoindex/blob/main/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`](https://github.com/cocoindex-io/cocoindex/blob/main/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.