Single Connection vs Pool

~12 min · aiosqlite, connection-pool, concurrency

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Why one connection often beats a pool

aiosqlite serializes operations on a single connection through a background thread. With WAL mode, this is enough for many web apps — readers and writers don't block each other at the SQLite level, and the per-connection serialization is fast enough that the bottleneck is rarely the queue.

When to consider a pool:

You have multiple writers and writes are long-running enough to benefit from parallelism (rare with SQLite's single-writer-at-a-time semantics).
You're running on a multi-core machine and reads can saturate one connection's serialization.
You need separate read-only and writer connections for safety.

For most Pippa-shaped apps (and for most local-first products), a single shared connection is correct and simpler.

Tip: If you do want a pool, write a tiny one yourself with asyncio.Queue rather than reaching for SQLAlchemy. SQLAlchemy is great when you need its query builder; for a single-file SQLite app it's usually overkill.

Code

Tiny async connection pool·python

import asyncio, aiosqlite
from contextlib import asynccontextmanager

class AioSqlitePool:
    def __init__(self, path: str, size: int = 4):
        self.path = path
        self._pool: asyncio.Queue = asyncio.Queue(maxsize=size)
        self._size = size

    async def setup(self):
        for _ in range(self._size):
            conn = await aiosqlite.connect(self.path)
            conn.row_factory = aiosqlite.Row
            await conn.execute('PRAGMA journal_mode = WAL')
            await self._pool.put(conn)

    @asynccontextmanager
    async def acquire(self):
        conn = await self._pool.get()
        try:
            yield conn
        finally:
            await self._pool.put(conn)

    async def close(self):
        while not self._pool.empty():
            conn = await self._pool.get()
            await conn.close()

External links

aiosqlite — Connection model

Exercise

Implement the tiny pool above and benchmark: at what concurrency level does a 4-connection pool start outperforming a single shared connection on read-heavy workloads? On write-heavy workloads? Note when the pool helps and when it just adds latency.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousConnection Lifecycle in FastAPI Next →A Real ConversationStore — CRUD End-to-End

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.