Reference

Best practices

Sizing, mode choice, a production checklist and how to upgrade.

Choose a pooling mode#

  • Start with transaction. It gives the most sharing and works with prepared statements.
  • Use session for clients that rely on session state StreamQL cannot replay, or when you see many pins with POOL_PINNED_* reason codes that you cannot remove.
  • Use statement only for autocommit workloads without transactions.

Size pools#

  • max_size is per database and role. Keep the sum of all pools, plus connections from outside StreamQL, under PostgreSQL max_connections, and describe it in [budget].
  • A pool that is too small shows up as pool wait in Slow queries and waiting clients in Pools. A pool that is too large moves the queue into PostgreSQL.
  • Use a reserve pool for short bursts instead of a larger fixed size, and max_db_connections to protect a database shared by several pools.
  • Watch Pool wait p99 in Overview: it should be a small fraction of database time.

Production checklist#

  • Set admin.token from a secret reference and keep the admin port off the public internet.
  • Set admin.state_file so console changes survive restarts, and log.format = "json".
  • Use upstream.sslmode = "verify-full" with upstream.sslrootcert, and client TLS on listen.tls.
  • Use scram-sha-256 and per-role users; never trust.
  • Configure OIDC and require MFA for admins (access.mfa = "required_admins").
  • Run at least two gateways with a pod disruption budget, and test a rolling restart under load.
  • Create alert channels and route critical alerts.
  • Run streamql config validate in CI before you deploy a change.

Upgrades#

  • Prefer rolling updates: each node drains (drain.grace_s), then reconnecting clients land on another node.
  • For a single node with systemd socket activation, or with runtime.handoff_socket, start the new binary with --upgrade so it takes over the listeners without dropping clients.
  • Roll out configuration to a fleet with a staged policy and a bake time.

PostgreSQL compatibility#

PostgreSQL 13, 14, 15, 16, 17, 18 and 19 are tested in the integration matrix. Features that differ between versions are gated by version, for example plan capture uses GENERIC_PLAN on 16 and later. Clients that ask for protocol 3.2 are negotiated down to 3.0.