How to deploy to production#

This guide shows you how to configure zodb-pgjsonb for production workloads.

Tune connection pool settings#

Set pool-size and pool-max-size based on your expected concurrency:

<pgjsonb>
    dsn dbname=zodb user=zodb host=localhost port=5432
    pool-size 4
    pool-max-size 20
    pool-timeout 30.0
</pgjsonb>

  • pool-size: minimum connections kept open (default: 1). Set this to the number of Zope threads for steady-state workloads.

  • pool-max-size: maximum connections (default: 10). Set this to at least the number of concurrent Zope threads plus a margin for background tasks like pack.

  • pool-timeout: seconds to wait for a connection before raising StorageError (default: 30.0).

Each ZODB connection gets its own PGJsonbStorageInstance with a dedicated PostgreSQL connection from the pool.

Size worker threads for GIL headroom#

A single process runs its worker threads under one CPython GIL. When several threads render responses concurrently while others wait on PostgreSQL, a thread returning from a query queues behind the CPU-bound ones for the GIL, which inflates per-load wall time even when the database is fast.

Prefer more processes with fewer threads each over fewer processes with many threads:

  • Keep the thread count per process modest — a small number such as 2 to 4 — and scale out with more processes or replicas to serve the same concurrency.

  • Match pool-size to the thread count per process, as described above, so each thread still has its own pooled connection.

  • Cache CPU-heavy per-request work such as theme compilation out of the request path, so waiting threads re-acquire the GIL sooner.

See also

GIL convoy under concurrency explains why per-load timings inflate under concurrency even when PostgreSQL is fast.

Tune the object cache#

Set cache-local-mb to control the per-instance LRU cache for load() results:

<pgjsonb>
    dsn dbname=zodb user=zodb host=localhost
    cache-local-mb 64
</pgjsonb>

The cache stores pickle bytes keyed by object ID, avoiding repeated PostgreSQL round-trips and JSONB transcoding. The default is 16 MB. Set to 0 to disable caching entirely.

Roll out without a DDL stampede#

When you run several replicas and deploy with a rolling update, every pod starts up and wants to apply its deferred schema DDL. The storage coordinates this with a session-level advisory lock so only one pod runs the DDL at a time. A schema-version gate runs a cheap SELECT before taking that lock, so replicas whose DDL is already current return immediately instead of all queuing on the lock.

You do not need to configure anything for this — it is on by default. The first deploy after upgrading records the version markers; from then on, deploys that do not change the schema skip the lock entirely.

Two environment variables let you tune the behavior:

  • Set ZODB_PGJSONB_DDL_LOCK_TIMEOUT (default 15min) to bound how long a pod waits for the lock before requeuing its DDL for the next transaction.

  • Set ZODB_PGJSONB_FORCE_DDL=1 on a single pod to bypass the gate and re-apply all deferred DDL, for example after you manually dropped an index. Remove it again once that pod has started, so the rest of the fleet keeps using the fast path.

See also

Startup DDL and the schema-version gate explains how the gate works and why it trades re-running idempotent DDL for skipping the lock. For the variables themselves, see Configuration options.

Schedule periodic pack#

Pack removes unreachable objects (history-free mode) or old revisions (history-preserving mode). Schedule a cron job to run pack periodically:

#!/usr/bin/env python
"""Pack the ZODB database."""

import time
from zodb_pgjsonb.storage import PGJsonbStorage

storage = PGJsonbStorage(dsn="dbname=zodb user=zodb host=localhost")
# Pack to 1 day ago
pack_time = time.time() - 86400
storage.pack(pack_time, None)
storage.close()

Save this as pack_db.py and add a cron entry:

0 3 * * * /path/to/venv/bin/python /path/to/pack_db.py

Pack runs pure SQL (recursive CTE on the pre-extracted refs column) and does not load any objects. It also deletes orphaned S3 blobs when S3 tiering is configured.

Monitor blob statistics#

Use get_blob_stats() to check blob storage health:

from zodb_pgjsonb.storage import PGJsonbStorage

storage = PGJsonbStorage(dsn="dbname=zodb user=zodb host=localhost")
stats = storage.get_blob_stats()
print(f"Total blobs: {stats['total_blobs']}")
print(f"Total size: {stats['total_size_display']}")
print(f"PG blobs: {stats['pg_count']} ({stats['pg_size_display']})")
print(f"S3 blobs: {stats['s3_count']} ({stats['s3_size_display']})")
print(f"Largest blob: {stats['largest_blob_display']}")
storage.close()

When running under Zope, blob statistics are available in the ZMI database management tab.

PostgreSQL tuning#

Key postgresql.conf settings for a dedicated PostgreSQL instance:

shared_buffers = 256MB          # 25% of RAM for dedicated PG
work_mem = 32MB                 # For complex queries and sorts
effective_cache_size = 1GB      # Available OS cache
maintenance_work_mem = 256MB    # For VACUUM and REINDEX
max_connections = 100           # Match pool-max-size * instances + overhead

Enable slow query logging to identify performance issues:

log_min_duration_statement = 100   # Log queries slower than 100 ms

Run ANALYZE object_state after large bulk imports or migrations to update planner statistics.

Configure autovacuum for object_state if you have high write throughput:

ALTER TABLE object_state SET (autovacuum_analyze_threshold = 5000);

Backup strategy#

PostgreSQL backup#

Use pg_dump for logical backups:

pg_dump -Fc -f zodb_backup.dump "dbname=zodb user=zodb host=localhost"

For continuous archiving, use pg_basebackup or pgBackRest. All ZODB data (objects, transactions, blobs stored in PG) is captured by standard PostgreSQL backup tools.

S3 blob backup#

Enable S3 bucket versioning to protect against accidental deletion:

aws s3api put-bucket-versioning \
    --bucket my-zodb-blobs \
    --versioning-configuration Status=Enabled

Set up S3 cross-region replication or lifecycle rules for additional durability. Pack deletes orphaned S3 blobs, so versioning preserves a safety net.

Monitoring queries#

Key PostgreSQL views for monitoring:

-- Active connections and queries
SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE datname = 'zodb'
ORDER BY duration DESC;

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Index usage
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;