Skip to content

ClickHouse Cloud and Managed Postgres

This guide runs Loom on Kubernetes over two managed services: ClickHouse Cloud for projections, vectors, history and analytics, and ClickHouse Managed Postgres as the memory authority. It covers the services, the chart's postgres values, the checks after the install, rotating the Postgres password and the CA, and the SQL surface user. The chart itself is in Kubernetes, the authority in Postgres memory authority.

The overlay deploy/helm/loom/values-clickhouse-cloud.yaml layers on the EKS one. It is for a deployment on an empty ClickHouse: the authority is chosen at a deployment's first boot, and on a ClickHouse that already holds memory the pods refuse to start, as no migration moves memory from the ClickHouse authority to Postgres.

The services

  • ClickHouse Cloud: a service with a database named loom and a user for Loom, its IP access list allowing the cluster's NAT addresses (Kubernetes, On EKS).

  • ClickHouse Managed Postgres: a service with two HA standbys, a database and a role that owns it, and the direct endpoint on port 5432 (Postgres, ClickHouse Managed Postgres). Grant the role pg_read_all_stats, so Loom can see which standbys are attached:

    GRANT pg_read_all_stats TO loom;

Secrets

The overlay reads two Secrets beside loom-secrets: loom-postgres, key password, with the role's password, and loom-postgres-ca, key ca.crt, with the CA bundle. Make them the same way as loom-secrets, each with an ExternalSecret of its own.

ca.crt holds every root certificate the service's CA currently publishes, concatenated as PEM; the driver loads the file as a bundle and trusts nothing else. Take the roots from the CA's published list (the "CA Issuers" URL in the server certificate names the CA); openssl s_client -starttls postgres -showcerts usually prints the chain without its root. Convert a root fetched as DER first:

openssl x509 -inform DER -in root.der -out root.pem

Concatenate the root.pem files into ca.crt, the file the Secret holds. Check it against the chain the server sends, trusting nothing but the bundle, as the driver does: leaf.pem is the first certificate s_client -showcerts prints and chain.pem the rest (-no-CAstore needs version 3 of openssl):

openssl verify -no-CApath -no-CAstore -CAfile ca.crt -untrusted chain.pem \
  -verify_hostname <postgres host> leaf.pem

A certificate the bundle does not cover fails closed: a pod that boots with it does not start, and its log shows SSLCertVerificationError; in a running pod, every new connection fails. When the CA announces a change to its hierarchy, update the bundle under a new Secret name (below).

Install

helm install loom deploy/helm/loom -n loom \
  -f deploy/helm/loom/values-eks.yaml -f deploy/helm/loom/values-clickhouse-cloud.yaml \
  --set publicBaseUrl=https://loom.example.com \
  --set clickhouse.host=<service>.<region>.aws.clickhouse.cloud \
  --set postgres.host=<postgres direct host>

With postgres.host set, the chart gives every pod that runs Loom (web, workers, the hook Jobs and the analytics export):

  • LOOM_STORAGE_AUTHORITY=postgres, LOOM_PG_DURABILITY and LOOM_PG_POOL_MAX_SIZE from postgres.durability and postgres.poolMaxSize;
  • LOOM_PG_DSN with no password, postgres://<user>@<host>:<port>/<database>?sslmode=verify-full&sslrootcert=/etc/loom/pg-ca/<key>, so the password never sits in the pod spec;
  • PGPASSWORD from postgres.passwordSecret, which the driver reads when the DSN has none;
  • postgres.caSecret mounted read-only at /etc/loom/pg-ca;
  • with networkPolicy.enabled, egress to postgres.port.

The chart refuses to render a postgres block it cannot pass on as written, and any of the names it renders set a second time in env, extraEnv, secrets.extra or settingsFile; each refusal names the value to fix, and the block's comments in deploy/helm/loom/values.yaml state the rules. Its values win over the same names in existingSecret and envFrom.

Connections

Each Loom process holds up to postgres.poolMaxSize connections; a call that finds them all busy waits up to LOOM_PG_STATEMENT_TIMEOUT_MS for one, then answers 503. A rollout runs the old and the new pods side by side, so size the service for twice the steady count:

poolMaxSize × (webWorkers × web pods + worker processes) × 2
  + booting processes + running erasures + CLI runs  ≤  max_connections − reserved

Web pods are autoscaling.maxReplicas with autoscaling on, else replicaCount; worker processes are workers.replicaCount with workers.enabled, else one, which a web pod runs. Each running erasure holds one more connection for its Postgres step. The overlay sets webWorkers to 2, what the image picks for the chart's 2-CPU limit, and poolMaxSize to 5: with three web pods and one workers pod that is 70 connections, which fits a service whose max_connections leaves 100 for Loom. Raising webWorkers needs resources.limits.cpu and memory (1 GiB per process) to match. Replicas added past the chart's count by anything else are outside the sum.

After the install

  • Loom refuses to start when the service's synchronous_standby_names is empty. It starts with fewer standbys attached than it names, logging the WARNING Postgres durability: fewer synchronous standbys are attached than required. A commit then waits for them up to LOOM_PG_STATEMENT_TIMEOUT_MS plus one second, and answers 503 POSTGRES_UNAVAILABLE with outcome_unknown (what the boot checks).
  • GET /v1/health reports storage_authority: postgres and pg_ok. GET /v1/health?include=postgres adds a postgres block with standby: synchronous; the block is missing whenever pg_ok is false, when the check's SELECT 1 fails for any reason or takes longer than 2 s.
  • A role without pg_read_all_stats still starts, with the boot WARNING Postgres durability: cannot see pg_stat_replication; standbys are unverified, and standby reads null. The WARNING is the reliable sign; standby is also null when its read fails or takes longer than 2 s.

Rotating the password and the CA

The password, in this order, back to back:

  1. Update the password in the secret store and confirm loom-postgres has synced.
  2. Run ALTER ROLE loom PASSWORD '…' on the service.
  3. Run kubectl rollout restart on the web and workers Deployments together.

Connections already open keep working. Between steps 2 and 3, a call whose pod must open a new connection (the pool closes connections idle for 300 s) answers 500, and the server log shows InvalidPasswordError (SQLSTATE 28P01); retrying does not help until the pod restarts. A pod that starts between steps 1 and 2 (a scale-up, an eviction, the analytics export or a hook Job) fails to boot until step 2, and boots on its next restart.

The CA: create the new bundle under a new Secret name and helm upgrade with postgres.caSecret.name set to it. The pod spec changes, so the pods roll. Delete the old Secret only once the rollout has finished and no revision you might roll back to names it.

The SQL surface user

An org reads its agents' memory as SQL through a ClickHouse user that loom sql-access grant makes (SQL surface). Run it in a web pod with a ClickHouse admin credential that may create users, passing the admin password from a file, never typed at an echoing terminal:

web=$(kubectl get deploy -n loom -o name \
  -l app.kubernetes.io/instance=loom,app.kubernetes.io/component=web)
kubectl exec -i -n loom "$web" -- sh -c \
  'read -r LOOM_CH_PASSWORD; export LOOM_CH_PASSWORD LOOM_CH_USER=default;
   exec loom sql-access grant --user acme_sql --org acme --namespaces support,research' < admin.pass

The command prints the SQL user's password once, to the terminal only: an exec stream is not a container log. --namespaces '*' also covers namespaces made later; never give it on an org that sign-ups share (access). --all instead of --org … --namespaces … fits a deployment whose data belongs to one org. Revoke the user before uninstalling: loom sql-access revoke --user acme_sql, run the same way.

Restoring a service

A restore of either service is followed by loom reconcile, run in a Job made from the web pod's spec while the release is stopped (restoring one store).

Kubernetes · Postgres memory authority · Deployment · SQL surface · Query Loom from Postgres