Files
sql/conf/postgresql.conf
zeekayandHanzo Dev 528450cfa2 zap_fdw: transactions over ZAP, a jsonb entity table, and replies that are valid JSON
/begin opens a transaction on its connection; /query and /exec run inside it until /commit or /rollback. A statement worker hands such a connection to a transaction worker (zap.tx_workers, default 8) over a datagram socket the postmaster opens, so a transaction never holds up another client's statements. A request outside one commits before its reply. Errors carry the SQLSTATE, and a serialization failure or deadlock answers 409. _entities stores data as jsonb with timestamptz times, converting a text table in place, which is what hanzoai/orm reads with ->> and indexes. Values are emitted by column type and strings escaped; an id that starts with a digit is no longer a number. Statement workers bind only once recovery has finished, so a standby does not listen.

max_worker_processes is 32: the listener now runs zap.workers + zap.tx_workers background workers.

Co-authored-by: Hanzo Dev <[email protected]>
2026-10-02 16:24:25 -07:00

72 lines
2.3 KiB
Plaintext

# Hanzo SQL - PostgreSQL configuration tuned for AI workloads
# https://github.com/hanzoai/sql
# Connection
listen_addresses = '*'
port = 5432
max_connections = 200
# Memory - tuned for vector operations and large working sets
shared_buffers = 512MB
work_mem = 64MB
maintenance_work_mem = 256MB
effective_cache_size = 1536MB
# WAL
wal_level = replica
max_wal_size = 2GB
min_wal_size = 256MB
checkpoint_completion_target = 0.9
# Query planner - favor index scans for vector similarity
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100
# Parallel queries - leverage multi-core for vector ops
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# Background workers: the parallel workers above, pg_cron, documentdb, and the
# ZAP listener's statement and transaction workers (zap.workers + zap.tx_workers).
max_worker_processes = 32
# Logging
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = off
log_disconnections = off
log_lock_waits = on
log_timezone = 'UTC'
# Locale
timezone = 'UTC'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
# Extensions
# pg_documentdb_core and pg_documentdb must be in shared_preload_libraries;
# pg_documentdb_extended_rum_core is loaded at runtime by pg_documentdb_core
shared_preload_libraries = 'vector,pg_cron,pg_documentdb_core,pg_documentdb,zap_fdw'
# pg_cron - run jobs in any database (not just postgres)
cron.database_name = 'postgres'
# pgvector specific - HNSW index build parallelism
max_parallel_maintenance_workers = 4
# pg_documentdb - RUM library load option
# Default is 'require_documentdb_extended_rum' which crashes if the .so has missing deps.
# Use 'try' so PG starts even if the RUM extension fails to load.
documentdb_core.rum_library_load_option = 'try_documentdb_extended_rum'
# ZAP binary protocol — Hanzo SQL speaks ZAP natively (hanzoai/orm sql:// backend).
# zap_fdw is in shared_preload_libraries above; the listener binds zap.port.
zap.enabled = true
zap.port = 9651
zap.workers = 4 # statement workers: many connections each, a request at a time
zap.tx_workers = 8 # transaction workers: one connection holding a transaction each
#zap.database = '' # empty = use POSTGRES_DB env var, then fallback to 'postgres'