PostgreSQL profile

This page applies only to the self-hosted PostgreSQL profile. PostgreSQL stores its documents, vectors, BM25 index, graph, queue and authorization state. The CockroachDB Cloud profile uses a separate migration, C-SPANN and the portable queue described on Database profiles. This page covers how the PostgreSQL cluster is created, tuned and stored. It assumes you know the service list from Deployment topology and can read SQL.

Initialization creates three roles

The db service starts with POSTGRES_INITDB_ARGS: --data-checksums and mounts src/deploy/initdb/roles.sh into /docker-entrypoint-initdb.d/. PostgreSQL runs that script exactly once, against an empty data directory, before any migration connects.

  aizk_admin ──owns──▶ aizk database ──▶ every table, bypasses RLS
       │
       └──creates──▶ aizk_app   NOSUPERUSER NOBYPASSRLS NOCREATEDB NOCREATEROLE
       │                  └──▶ SELECT/INSERT/UPDATE/DELETE by default privilege
       └──creates──▶ logto     ──owns──▶ logto database, a separate database

aizk_admin is a fixed literal in the Compose file rather than a variable, because Settings.admin_database_url hardcodes the same name and only the password travels through the environment. aizk_app is the role every request path uses, and it can neither bypass row security nor own a table, which is the whole point of Row level security. logto owns only the separate logto database.

The script is idempotent, so run it again after a role restore or a secret rotation to replace archived password hashes with the current .env values.

docker compose --env-file .env -f src/deploy/docker-compose.yml exec -T db \
  /docker-entrypoint-initdb.d/roles.sh

The healthcheck authenticates over TCP with the current project secret rather than using pg_isready, so password drift shows up as an unhealthy container instead of a mystery later.

Extensions and preloaded libraries

The image is tensorchord/vchord-suite:pg18-latest, pinned by digest. Compose replaces its CMD outright, so the suite’s own settings are repeated verbatim alongside ours.

shared_preload_libraries=vchord,vchord_bm25,vector,pg_tokenizer,pg_stat_statements
search_path="$user", public, bm25_catalog, tokenizer_catalog

pg_stat_statements rides along so query statistics come from the catalog view rather than an ad-hoc EXPLAIN ANALYZE, and admin database setup runs the matching CREATE EXTENSION IF NOT EXISTS. A preloaded library only takes effect on the next server start.

The request context lives in transaction-local app.scopes.read, app.scopes.write, app.scopes.public and app.operator settings. They have no server default. A session that has not bound a caller therefore sees nothing rather than everything.

Tuning assumes a 256 GB host

These are the committed defaults, each overridable through the matching AIZK_PG_ variable.

Setting	Default	Why
`shared_buffers`	16GB	keep the active graph and its indexes warm
`effective_cache_size`	128GB	a planner estimate, it reserves nothing
`work_mem`	16MB	bounded sorts without multiplying across plans
`maintenance_work_mem`	2GB	faster vacuum and index builds
`effective_io_concurrency`	200	model NVMe rather than a rotating disk
`maintenance_io_concurrency`	200	same parallelism for maintenance
`random_page_cost`	1.1	random NVMe reads cost near sequential
`checkpoint_timeout`	15min	spread checkpoint writes
`max_wal_size`	8GB	fewer forced checkpoints during graph rebuilds
`min_wal_size`	2GB	keep segments around for reuse
`default_toast_compression`	lz4	denser and faster than pglz on stored text
`wal_compression`	zstd	less full-page-image WAL, spends CPU
`track_io_timing`	on	make I/O visible in diagnostics
`log_lock_waits`	on	catch lock stalls before they read as queue lag
`log_min_duration_statement`	1000ms	slow statements only
`autovacuum_vacuum_scale_factor`	0.05	vacuum earlier than the default
`autovacuum_analyze_scale_factor`	0.02	analyze earlier than the default

A smaller host must lower the memory values before PostgreSQL first starts. Treat all of this as a measured starting point rather than an answer. After realistic ingestion, look at pg_stat_statements, the cache hit rate, checkpoint frequency, temporary file volume and queue lag, then change one group at a time.

Confirm checksums are actually on, since they detect corrupted pages when they are read and are easy to assume rather than verify.

docker compose --env-file .env -f src/deploy/docker-compose.yml exec -T db \
  psql -U aizk_admin -d aizk -Atc "SHOW data_checksums"

Two compression settings, two menus

PostgreSQL compresses in two places with different menus. default_toast_compression covers out-of-line column values and accepts only pglz and lz4. wal_compression covers WAL full page images and also accepts zstd. Zstd never landed for TOAST, so lz4 is the only upgrade there.

Setting	Override	Committed	Effect
`default_toast_compression`	`AIZK_PG_TOAST_COMPRESSION`	`lz4`	faster both ways and usually denser on text, which is what every stored derivative is
`wal_compression`	`AIZK_PG_WAL_COMPRESSION`	`zstd`	denser full page images, so less WAL, smaller archives and less future replication bandwidth, and this workload never stops writing through chunk inserts, embeddings and queue churn
CPU cost of that zstd			paid only on the first touch of a page after each checkpoint, against fewer bytes written, so it is a trade this write-heavy host wins
where both are applied	the `db` command line in `src/deploy/docker-compose.yml`		recreate the container, since a reload will not pick a command line up

Neither rewrites anything. WAL applies to every segment written afterward and TOAST only to new values, so old rows keep pglz until something rewrites them.

psql -U aizk_admin -d aizk -Atc "SELECT name, setting FROM pg_settings
  WHERE name IN ('default_toast_compression', 'wal_compression')"

Reclaiming space after a bulk cleanup

Deleting rows and dropping columns leave dead tuples. Autovacuum and the nightly VACUUM (ANALYZE) make that space reusable inside the existing files without an exclusive lock, which is usually enough. Handing the file back is separate, worth doing once after the first pgqueuer_log prune or the 0008_storage_footprint migration, and it rewrites TOAST under the current setting.

psql -U aizk_admin -d aizk -c "VACUUM FULL VERBOSE pgqueuer_log"   # access exclusive lock
pg_repack -U aizk_admin -d aizk -t artifact_content               # same result, no long lock

Storage layout

Every persistent path is a Compose variable that takes either a named volume or an absolute host directory. The named-volume defaults are fine for development and prove nothing about which physical disk holds the bytes.

AIZK_POSTGRES_DATA_VOLUME=/srv/aizk/postgres
AIZK_OBJECT_DATA_VOLUME=/srv/aizk/objects
AIZK_BACKUP_VOLUME=/srv/aizk/backups
AIZK_CLAMAV_DATA_VOLUME=/srv/aizk/clamav
AIZK_LOKI_VOLUME=/srv/aizk/loki
AIZK_ALLOY_VOLUME=/srv/aizk/alloy
AIZK_GRAFANA_VOLUME=/srv/aizk/grafana

Separate subdirectories keep ownership and backup policy explicit even when one device holds them all. Ownership is not uniform. The PostgreSQL process in the pinned image runs as UID and GID 999, so its host directory must be 999:999 with mode 0700, while the aizk runtime directories belong to UID 10001 and the observability directories belong to Loki, Alloy and Grafana separately. Each one needs the UID its own image uses.

Note that the mount point is /var/lib/postgresql and not the data directory inside it, because PostgreSQL 18 images store data under a major-version subdirectory.

Encryption at rest, honestly

Core PostgreSQL has no transparent cluster encryption, and its own documentation points at filesystem or block encryption when a stolen drive is the threat. On Linux that means LUKS2 over dm-crypt. Column encryption with pgcrypto is not a substitute here, because embeddings, BM25 indexes, graph traversal and reranking all need searchable plaintext inside the database process.

A passphrase entered after reboot is the strongest simple unlock option and needs an operator present. A TPM or network-bound key can allow unattended reboot, but each adds its own trust and recovery requirements.

Storing the LUKS key on the same machine’s unencrypted root disk protects against removal of the database SSD and nothing else. It is not full at-rest encryption and should not be described as such.

Backups and recovery covers dumps, restores and the real gaps.
Row level security explains what aizk_app can and cannot see.
Migrations and DDL explains how the schema gets created.
Hardware and cost sizes the host these defaults assume.