Brain memory tuning

Under sustained burst load, the prepared statements cached on each pooled asyncpg connection can add up to thousands of compiled statements in C memory across the pool while the Python heap stays flat. The settings below control how those statements are prepared and rotate connections often enough that brain RSS stays bounded. This page covers the knobs and the metrics to watch.

Defaults

Setting	Default	What it controls
Z4J_DATABASE_STATEMENT_CACHE_SIZE	50	The most prepared statements each pooled connection keeps. It sizes both caches a connection holds: asyncpg's own statement cache (statement_cache_size, used for executemany batches and the connection ping) and SQLAlchemy's cache of the statements it prepares itself (prepared_statement_cache_size). 0 disables both, so statements are unnamed and PostgreSQL parses each one again on every execution. A prepared_statement_cache_size query parameter in Z4J_DATABASE_URL takes precedence for SQLAlchemy's cache.
Z4J_DATABASE_MAX_INACTIVE_CONNECTION_LIFETIME_SECONDS	60	Maximum age in seconds of a pooled connection, counted from when it was opened (SQLAlchemy pool_recycle). An older connection is replaced the next time it is checked out, whether or not it sat idle. Recycling drops the per-connection statement caches as a side effect.

These defaults target a sustained mixed-query brain on Postgres with the stock 20+10 pool. Each connection keeps about 50 prepared statements in each cache (SQLAlchemy's cache trims itself back to 50 once it passes 75), and a connection older than 60 seconds is replaced at its next checkout, taking everything cached on it along. Hot queries keep the prepare-once speedup in between.

When to lower the cache size

Lowering Z4J_DATABASE_STATEMENT_CACHE_SIZE lowers how many prepared statements each pooled connection keeps. 0 disables both caches: statements become unnamed, so PostgreSQL does not keep them on each backend connection and the brain keeps none either.

Consider 0 when your workload is "many distinct queries, low repeat rate" rather than "few hot queries hit constantly". The cache only helps when the same prepared statement gets reused.

Cost: PostgreSQL parses each statement again on every execution. Treat 0 as opt-in; the default of 50 is the right starting point for most operators.

When to shorten pool_recycle

Shorten Z4J_DATABASE_MAX_INACTIVE_CONNECTION_LIFETIME_SECONDS below 60 when:

• You scrape z4j_brain_rss_bytes and see slow growth that does not flatten between traffic peaks.
• You run with Z4J_DATABASE_STATEMENT_CACHE_SIZE at its default and still want a faster cache eviction floor.
• Your traffic is bursty enough that connections sit idle for long stretches between bursts, accumulating stale prepared plans.

Cost: more connect/reconnect churn against Postgres, which grows as the value shrinks.

What to watch

Scrape /metrics and chart these three signals together:

• z4j_brain_rss_bytes (gauge, sampled from /proc/self/status at scrape time, summed across the brain's worker processes rather than one process). The slope is the headline leak signal. Flat or slow growth is healthy; rapid growth points at an unbounded cache or a new retention path. 0 on non-Linux.
• z4j_db_pool_checked_out vs z4j_db_pool_size (gauges). Steady-state checked-out should be well below pool size. If it pins to the max, the cache playbook will not help; you need more pool capacity or fewer concurrent writers.
• z4j_postgres_deadlocks_total (counter, wired via the SQLAlchemy handle_error listener, with no labels). Should hover at or near zero in steady state. Sustained non-zero rates indicate lock-order contention; the counter cannot say which statement deadlocked, so find the statements in the PostgreSQL log (typically INSERT INTO workers, UPDATE agents, or UPDATE queues) and check that any new write site sorts its keys before INSERT/UPDATE.

The overview dashboard in the deploy/grafana/ directory of the source repository on GitHub renders all three plus task throughput for correlation. The dashboards are not in the PyPI sdist or the container image. Import via Dashboards, New, Import.

Pool sizing

Z4J_DATABASE_POOL_SIZE (default 20, range 1-200) and Z4J_DATABASE_MAX_OVERFLOW (default 10, range 0-200) are settings, so contention symptoms (sustained high z4j_db_pool_checked_out) are a config change rather than a code change.

Size them against the database server, not against the brain. Total backend demand is workers * (pool_size + max_overflow), and serve defaults to min(4, cpu_count) workers, so raising the pool multiplies. On PostgreSQL the combined value also has a floor below which boot can deadlock: pool_size + max_overflow must be at least the number of leader-gated background workers plus one, plus one more with the embedded scheduler, and the brain refuses to start rather than run under it, naming the number it needs. Most deployments stay comfortably below saturation on the defaults.