State storage — LMDB or Postgres

CocoIndex persists target states and memoization results so it can detect changes and apply minimal updates between runs. Pick LMDB (zero-config, local) or Postgres (shared, managed) by the form of `db_path`, tune via `LmdbSettings` / `PgSettings`.

Version

v 1.0.62

CocoIndex persists target states and memoization results so it can detect what changed between runs and apply only the necessary updates. This page covers where that state lives and how to configure the backend.

Backend selection

The backend is selected by the form of db_path:

The simplest way to set db_path is the COCOINDEX_DB environment variable — it accepts either a path or a URL:

# LMDB — local file (default)
export COCOINDEX_DB=./cocoindex.db

# Postgres — a postgres:// or postgresql:// URL
export COCOINDEX_DB=postgres://cocoindex:[email protected]:5432/coco

Programmatic equivalents (override the env var):

# In a lifespan function
@coco.lifespan
def coco_lifespan(builder: coco.EnvironmentBuilder) -> Iterator[None]:
    builder.settings.db_path = "postgres://localhost/coco"
    yield

# Or directly when constructing Settings
settings = coco.Settings(db_path="postgres://localhost/coco")

If neither db_path nor COCOINDEX_DB is set, CocoIndex raises an error at Environment construction time.

Picking a backend

For most local development and single-machine production deployments, LMDB is the right choice — it has no operational overhead and is fast. Switch to Postgres when you want the engine state to live in a managed external database (centralized backup, separate from the host filesystem, etc.).

LMDB

The default. State lives in a local LMDB directory.

Configuration

The simplest setup — just point COCOINDEX_DB (or Settings.db_path) at any filesystem path:

export COCOINDEX_DB=./cocoindex.db

Programmatic:

@coco.lifespan
def coco_lifespan(builder: coco.EnvironmentBuilder) -> Iterator[None]:
    builder.settings.db_path = pathlib.Path("./cocoindex.db")
    yield

# or
settings = coco.Settings(db_path=pathlib.Path("./cocoindex.db"))

Setting db_path in the lifespan or Settings takes precedence over COCOINDEX_DB.

Tuning — LmdbSettings

The LMDB backend has two tunable knobs, grouped under coco.LmdbSettings and attached to Settings as db_settings. The defaults work well for most use cases.

When to adjust:

• map_size — most deployments never need to touch it. The map grows on its own and each step is cheap, so it is worth setting only when that growth actually costs you something: a large state re-climbing from 4 GiB on every restart, or resize pauses you can measure. If you do size it up, every enlargement logs LMDB map full, auto-resizing to N bytes and retrying — the largest N you see is the number to use. On 64-bit systems, map_size is a virtual address space reservation, so setting it larger than needed is safe and consumes neither physical memory nor disk.
• max_dbs — raise it if you have an unusually large number of apps sharing a single database directory.

Via environment variables:

export COCOINDEX_LMDB_MAP_SIZE=8589934592   # 8 GiB
export COCOINDEX_LMDB_MAX_DBS=2048

Or programmatically:

settings = coco.Settings(
    db_path=pathlib.Path("./cocoindex.db"),
    db_settings=coco.LmdbSettings(
        map_size=8 * 1024 * 1024 * 1024,  # 8 GiB
        max_dbs=2048,
    ),
)

Settings.from_env() picks up COCOINDEX_LMDB_MAX_DBS / COCOINDEX_LMDB_MAP_SIZE automatically.

Postgres (Plus)

Use the Postgres backend when you want CocoIndex’s internal state in an external, managed database.

Configuration

Set db_path to a Postgres connection URL, postgres://… or postgresql://…. Any URL that tokio-postgres accepts works — user, password, host, port and database name in the URL itself, and options such as sslmode in the query string:

# Via env var
export COCOINDEX_DB=postgres://cocoindex:[email protected]:5432/coco

# With TLS
export COCOINDEX_DB="postgres://cocoindex:[email protected]:5432/coco?sslmode=require"

# Unix-socket / passwordless local
export COCOINDEX_DB=postgresql:///coco

Programmatic:

@coco.lifespan
def coco_lifespan(builder: coco.EnvironmentBuilder) -> Iterator[None]:
    builder.settings.db_path = "postgres://cocoindex@db/coco"
    yield

# or
settings = coco.Settings(db_path="postgres://cocoindex@db/coco")

Either postgres:// or postgresql:// is recognized, exactly as written; everything else is treated as a filesystem path (LMDB), except a connection setting in another form.

Required Postgres privileges

CocoIndex creates and upgrades its own tables — cocoindex_meta, plus ten for each app (see Tables) — so there’s no migration step to run. It also re-runs that DDL on every start, not only the first: every process that updates or drops an app begins with CREATE TABLE IF NOT EXISTS cocoindex_meta, and opening the app re-applies its table definitions (CREATE TABLE IF NOT EXISTS, ALTER TABLE … ADD COLUMN IF NOT EXISTS, CREATE INDEX IF NOT EXISTS). Postgres checks privileges on these statements even when the objects already exist, so the connecting role needs the following for as long as CocoIndex connects as it:

• USAGE and CREATE on the schema that holds the tables. Revoking CREATE after the first run makes the next start fail with permission denied for schema.
• Ownership of the tables. ALTER TABLE and CREATE INDEX require it, as does DROP TABLE when you drop an app; grants on the tables aren’t enough. The tables belong to the role that created them, so to connect as another role later, make it a member of that one (GRANT cocoindex TO new_role).
• CREATE on the database, only if you set schema to a schema that doesn’t exist yet. CocoIndex checks for the schema on every start and runs CREATE SCHEMA only when it’s missing, so a schema created ahead of time needs just the schema grants above.

Minimal setup, with the tables in public:

CREATE DATABASE coco;
CREATE ROLE cocoindex WITH LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE coco TO cocoindex;
\c coco
GRANT USAGE, CREATE ON SCHEMA public TO cocoindex;

With a dedicated schema (schema="cocoindex"), create the schema yourself and grant the role USAGE and CREATE on it:

CREATE DATABASE coco;
CREATE ROLE cocoindex WITH LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE coco TO cocoindex;
\c coco
CREATE SCHEMA cocoindex;
GRANT USAGE, CREATE ON SCHEMA cocoindex TO cocoindex;

Alternatively, grant CREATE on the database (GRANT CONNECT, CREATE ON DATABASE coco TO cocoindex) and let CocoIndex create the schema on first start, so the role owns it and needs no grants on it. That privilege also lets the role create any other schema in the database.

Inspecting a state store from the CLI without loading the app — cocoindex ls --db and cocoindex show --db — only reads it, so a separate read-only role is enough for that:

GRANT USAGE ON SCHEMA public TO cocoindex_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cocoindex_reader;
-- Tables created later (a new app's first run) need SELECT too:
ALTER DEFAULT PRIVILEGES FOR ROLE cocoindex IN SCHEMA public
    GRANT SELECT ON TABLES TO cocoindex_reader;

Tuning — PgSettings

settings = coco.Settings(
    db_path="postgres://cocoindex@db/coco",
    db_settings=coco.PgSettings(pool_max_size=32, schema="cocoindex"),
)

schema can also be set via the COCOINDEX_PG_SCHEMA environment variable (picked up by coco.Settings.from_env()):

export COCOINDEX_DB=postgres://cocoindex@db/coco
export COCOINDEX_PG_SCHEMA=cocoindex

Putting CocoIndex’s tables in a dedicated schema keeps them out of public and makes them easy to inspect, back up, or drop as a unit. Create the schema yourself to keep the role’s privileges narrow; if CocoIndex has to create it, the role needs CREATE on the database — see Required Postgres privileges.

Tables

All of CocoIndex’s tables live in one schema: schema if you set it, otherwise the first schema on the connection’s search_path that exists — by default, a schema named after the role if there is one, else public.

cocoindex_meta (key TEXT PRIMARY KEY, value JSONB) is shared by every app stored in the schema. It holds an app:<name> row per app, recording the app’s ID, its table prefix, and its tables_version (the layout its tables are at), plus env:app_id_counter, the next app ID to assign.

Each app gets ten tables, named app_<name>_<id><suffix>. <name> is the app name lowercased, with each character other than an ASCII letter or digit replaced by _, and shortened if needed so every name fits Postgres’s 63-byte limit; <id> is the app’s ID in hex (four digits, or eight past ffff); <suffix> is one of those below. So an app named MyApp with ID 1 gets app_myapp_0001__track, app_myapp_0001__cmemo, and so on. To find an app’s prefix (<name>_<id>):

SELECT value->>'table_prefix' FROM cocoindex_meta WHERE key = 'app:MyApp';

Keys and values are mostly encoded BYTEA, so inspect them with cocoindex show — which lists an app’s component paths, and with --target-states each target state’s owner — rather than SQL. The tables are only consistent with one another: back up and restore an app’s tables together with cocoindex_meta, and don’t edit or truncate them by hand. An __idseq restored from an older backup than __fnmemo, for example, would hand out IDs already in use.

When a CocoIndex upgrade changes the layout, the new version upgrades an app’s tables in place the first time it opens the app, in the same transaction that bumps the app’s tables_version.

Dropping an app

App.drop() (or cocoindex drop) first reverts the app’s target states — the tables, rows, files, and so on that it created in your targets — then, in one SERIALIZABLE transaction, drops the app’s ten tables (the __sedge_rev index goes with __sedge) and deletes its app:<name> row from cocoindex_meta. cocoindex_meta itself, the app-ID counter, and the schema stay.

• A failed revert makes drop() raise and leaves the app’s tables in place; run it again once the cause is fixed, and it picks up where it stopped.
• Locks. The transaction waits at most 5 seconds for a lock (lock_timeout), so a drop that runs into another session holding a lock on one of the tables — an open psql transaction, a pg_dump in progress — fails instead of hanging. The target states are already reverted by then; run the drop again to remove the tables.
• App IDs aren’t reused. Running the app again after a drop registers it under a new ID, so its tables get new names.

Operational notes

• Concurrency. A per-environment connection pool serves reads and writes. Concurrent writes run as separate transactions, and serialization conflicts between them are retried automatically. One more connection allocates IDs outside those transactions, so concurrent writes never conflict over ID allocation.

• Multi-process. A single process per database today. Multi-process support (multiple workers sharing a Postgres backend) is on the roadmap.

• Connection poolers. CocoIndex works behind a pooler such as PgBouncer, in session or transaction pooling mode. It sets no parameters on the connection, so it needs no ignore_startup_parameters or track_extra_parameters settings. In transaction pooling mode, the pooler must support prepared statements: for PgBouncer, version 1.21 or later with max_prepared_statements above 0 (the default since 1.24). Cloud SQL’s Managed Connection Pooling defaults max_prepared_statements to 0, so raise it there, or use session mode.

  Earlier releases set search_path on the connection when schema was set. A pooler that dropped it silently put the tables in the connection’s default schema. If yours are there, move cocoindex_meta and the app_… tables into schema with ALTER TABLE … SET SCHEMA before the next update.

See also

• App — configuring db_path programmatically via a lifespan.
• Multiple environments — running isolated state stores side by side.