SQE Production Guide

How to run SQE in production when you start small but expect to scale data volume, query concurrency, and team count significantly. The goal is one honest configuration path you extend over time, not a throwaway dev stack you replace at scale.

For the full security, performance, quality, and observability audit, see docs/internal/audit/2026-07-10-sqe-full-audit.md.

Principles

1. Coordinator stays on greedy memory pool. Fair pool starves wide analytic plans at scale.
2. Set an honest memory_limit. Use 40-50% of allocatable RAM per process, not the config default on a small box.
3. Spill must be on with fast local disk before you push SF10-class workloads.
4. Security and audit from day one if compliance or multi-team access matters. Retroactive audit history is not recoverable.
5. Add workers when scan CPU saturates, not before you have evidence single-node is the bottleneck.

Phase 0: Day one (single coordinator)

Topology

• One sqe-coordinator or sqe-server (all-in-one). No workers.
• Slim Docker image (docker build .) with the catalog crate’s default feature set plus the Glue Hive root, which sqe-server always compiles in. The remaining backends (HMS, JDBC, Hadoop): docker build --build-arg CATALOG_FEATURES=sqe-catalog/full-backends -t sqe-full ..

Security (required even at small scale)

[auth]
# Use real OIDC bearer_token validation.
# Do NOT use: anonymous, bearer_passthrough, or client_credentials as the only provider.

[rate_limit]
enabled = true

[coordinator.tls]
# Enable Flight SQL TLS, or terminate TLS at ingress.

[storage.tvf]
allow_local_paths = false
allow_http = false
allowed_object_store_prefixes = ["s3://your-warehouse/"]

Keep the global list to what every user may read. A bucket that belongs to one workspace goes in that catalog’s tvf_prefixes instead, so only sessions scoped to that catalog can read it with the engine’s credentials (see read_parquet). When the workspaces share one layout, a single entry with the {catalog} placeholder ("s3://staging/ingest/ws/{catalog}/") does the same: it expands only to the session’s catalog, and only when that catalog answered the caller’s own bearer. If you rely on any of these prefixes to keep workspaces apart, set allow_inline_credentials = false too. Otherwise a caller who passes their own keys bypasses them.

Memory

[coordinator]
memory_pool = "greedy"        # default; do not switch to fair on the coordinator
unspillable_headroom_percent = 20   # default; share spillable operators may not take
memory_limit = "12GB"         # tune to ~40-50% of box RAM (example for a 32 GB host)
spill_to_disk = true
spill_dir = "/fast-nvme/sqe-spill"

Greedy pool lets one large operator use the budget until spill. Fair pool divides memory across every registered spillable consumer. Wide plans (TPC-DS class) register dozens of consumers and fail before spilling when fair is enabled. Workers use fair pool internally; that asymmetry is intentional.

unspillable_headroom_percent keeps the top share of the pool, and of each query’s query.max_query_memory, for consumers that cannot spill: the scan’s decode buffers, a sort’s merge, hash-join builds. Spillable operators (hash aggregates, sorts, repartition buffers) are refused at that line and spill there. Without it a large aggregate fills the cap and the scan feeding it is refused first, failing a query that could have spilled; with the 20% default the same SF10 aggregate under a 2 GB cap completes. The cost is that a lone spillable operator spills a little earlier at a tight cap. 0 turns it off; valid values are 0 to 50. It applies to the greedy pool only.

Within one query, sorts do not take the spillable bytes greedily. A sort runs one sorter per partition; each sorter may grow to an equal share of the spillable bytes before it spills, and the sorters’ merge reservations share a quarter of the query’s cap (at most sort_spill_reservation_bytes, at least 1 MB each). Without this, at 16 partitions the default 10 MB merge reservations alone take 160 MB, and the first sorters to fill leave nothing for the rest. Other operators, such as hash aggregates, still grow to the spillable line.

When concurrent queries fill the node pool, a scan whose own query still has room under query.max_query_memory waits for the others to release memory instead of failing. [catalog] scan_memory_wait_ms (default 30000) is the most one scan partition waits in total. 0 restores the immediate failure. A scan refused by its own query’s cap does not wait, so a join query can be retried with sort-merge joins straight away. Without query.max_query_memory there is no per-query cap to compare against, and scans fail at once as before.

A read query that the node pool refuses before its first batch, with room left under its own cap, runs again once there is room (query.memory_rerun, default on). This covers what the scan wait cannot: a sort taking its merge reservation back after a spill, an aggregate, a hash-join build. The query drops its attempt, waits until the pool has room for a full query.max_query_memory (at most half the pool), and runs the same plan again on the coordinator. One rerun waits or starts at a time per coordinator, so two queries refused together do not collide again. The wait ends at query.timeout_secs. A rerun refused again is retried with sort-merge joins when it has a hash join, and fails otherwise. The cost is the abandoned attempt’s time: on an SF10 probe with six heavy queries on a 4 GB pool, all six complete, against four before, and the reran sorts finish in 40 to 60 s against 13 s alone.

Memory caps do not bound CPU. Every join runs on the coordinator, and a cross join, or a join with no equal-key condition (ON a.x < b.y), compares every pair of input rows while holding little memory. query.max_cartesian_rows (default 10,000,000,000) refuses such a read before it runs when the product of its two inputs’ row counts exceeds the cap. The counts come from table statistics, exact for a whole Iceberg table and estimated below a filter. A scalar subquery crossed with a fact table passes. A cross join of two 100,000-row tables does not. The error names the cap, not the estimates. An input without statistics is admitted. 0 turns the gate off. The cap applies to the same plans as the sort gate: reads and EXPLAIN, not the write path. Equi-joins with skewed keys and large aggregations are not gated; query.timeout_secs and the memory caps above are what bound them.

Observability

Enable durable audit and lineage early if governance matters. Do not write audit logs to /tmp or container emptyDir.

[metrics]
prometheus_port = 9090
otlp_endpoint = "https://otel-collector:4317"   # https: audit events carry user identities and SQL
trace_sample_rate = 0.10      # 10% at low QPS; lower to 0.01-0.05 at high QPS

audit_log_path = "/var/log/sqe/audit/audit.jsonl"   # must survive pod restart (PVC)

[metrics.audit_export]
enabled = true
target = "otlp"
# OCSF audit ships to SIEM via OTLP logs; uses metrics.otlp_endpoint when otlp_endpoint is empty

[metrics.openlineage]
enabled = true
http_endpoint = "https://marquez.example.com/api/v1/lineage"
spool_path = "/var/spool/sqe-ol"
emit_selects = false            # enable only when read lineage is required

See docs/site/book/src/operations/openlineage.md for sink options and Marquez setup.

Catalog

• Polaris or Nessie over Iceberg REST.
• SigV4 in the default build covers AWS Iceberg REST (Glue/S3 Tables federated endpoints) without the full Glue SDK image.
• Defer full-backends until runtime config actually dispatches to HMS or JDBC SQL catalogs. Glue Hive roots need no extra feature.

Phase 1: Growth (roughly 10x data, add workers)

When to add workers

Add sqe-worker replicas when:

• Coordinator CPU is saturated during scans while network and object storage still have headroom.
• Single-node benchmarks show a scan throughput ceiling, not memory exhaustion.
• Large queries hit memory_limit with spill enabled and still need more parallel decode.

Topology

                    Ingress (TLS)
                         |
              +----------+----------+
              |   sqe-coordinator   |  planning, auth, policy, audit
              +----------+----------+
                         |  Arrow Flight + signed scan tickets
         +---------------+---------------+
         v               v               v
    sqe-worker      sqe-worker      sqe-worker

Coordinator: greedy pool. Workers: fair pool (hardcoded in sqe-worker).

Sizing

Set SQE_MEMORY_LIMIT in Kubernetes so the process cap matches the pod limit. A documented 64 GB default on a 31 GB node caused kernel OOM in benchmark runs.

Worker scan budget

A worker splits worker.memory_limit into sub-budgets under [worker.memory]. The one that decides how many concurrent scans a worker can hold in RAM is scan_budget, a fifth of the limit by default. It is one budget per worker, shared by every scan the coordinator sends, so a worker with forty inbound scans admits decoded batches from all forty against the same capacity and parks the rest until the coordinator drains. A quarter of it is the fetch lane that charges each open parquet file while it streams; the rest admits decoded batches. When the coordinator’s sqe_worker_reported_scan_budget_free_bytes sits at zero while the coordinator is busy elsewhere, the worker is waiting on the coordinator, not the other way round. Raise scan_budget only together with memory_limit: the sub-budgets must fit inside it.

[worker.memory]
scan_budget = "6GB"     # default: memory_limit / 5
flight_budget = "3GB"   # encoded frames awaiting shipment, default: memory_limit / 10

Branches to merge before heavy SF10 load

These are reliability fixes, not optional optimizations:

Phase 2: Large scale (100x data, multi-team, SF10+)

Architecture

• Distributed execution by default for large table scans.
• Warehouse on fast object storage with a fair SQE-vs-Trino comparison path (same endpoint for both engines).
• Polaris with Postgres persistence. In-memory Polaris loses warehouse metadata on stack restart.
• Coordinator HA (multiple coordinators behind a load balancer) only after you understand session stickiness: sessions are coordinator-local today.

Security hardening

• Per-user OIDC. No shared service token for all users.
• Ranger policy backend (policy.engine = "ranger", not passthrough).
• mask_key configured for hash column masks.
• Inline-credential TVFs disabled or restricted to admin roles.
• Restrict catalog/execution error detail for untrusted clients if needed.

Observability at scale

• OpenTelemetry collector with tail sampling: always retain slow and failed traces; sample the rest.
• Alert on:
  • Memory pressure in Red band (>95% pool utilization)
  • sqe_audit_export_spool_lag_bytes growing (SIEM backpressure)
  • sqe_lineage_channel_dropped_total > 0 (lineage overload)
• Close the DML audit gap: write-path PolicyAudit and QueryStats on INSERT/MERGE/UPDATE/DELETE (tracked in audit doc).

Performance tuning order

1. Honest memory_limit + spill_to_disk on NVMe
2. Add workers (2, then 4, then N)
3. parallel_probe_scan and parallel_scan are already ON by default. Turn parallel_probe_scan OFF for workloads of small window and rollup queries (measured at SF1: TPC-DS q67 +45%, q30 +79%, against a +4.0% suite total inside noise). IcebergScanExec still advertises one output partition at the table provider; the parallelism comes from a post-planning rule instead, and flipping that wiring still needs a TPC-DS rerun (issue #414).
4. jemalloc A/B for glibc RSS parking after large sorts
5. Do not move coordinator to memory_pool = "fair"

Coordinator-to-worker HTTP/2 windows are tuned by default as of 2026-08-18 and worth checking if you pinned an older config. Before that fix, the coordinator’s worker channels ran on h2’s 65,535-byte stream and connection windows, which cap one stream at window / RTT no matter how much bandwidth the link has: about 64 MB/s across a 1 ms hop, 6 MB/s across 10 ms. The connection window is shared, so every scan fragment multiplexed to the same worker drew from the same 64 KB. The defaults are now 8 MB per stream and 16 MB per connection, matching what the coordinator’s own Flight SQL server already used. Loopback rigs never showed the old ceiling; a separate-host deployment does.

[coordinator.transport]
initial_stream_window_size = 8388608       # 8 MB
initial_connection_window_size = 16777216  # 16 MB
max_decoding_message_bytes = 16777216      # 16 MB, both ends of the worker link

max_decoding_message_bytes replaces tonic’s 4 MiB receive default on the worker’s Flight server and on the coordinator’s clients. Workers read it from the same [coordinator.transport] section, so one value covers both ends. A scan ticket that would still exceed it sheds its pushed runtime-filter predicate and ships the fragment unfiltered rather than failing; watch the ticket_predicate_dropped_oversize metric if scans get slower after raising runtime_filter_inlist_max_values.

Production baseline (sqe.toml)

Copy and tune per environment:

[coordinator]
memory_pool = "greedy"
memory_limit = "24GB"          # 40-50% of pod allocatable RAM
spill_to_disk = true
spill_dir = "/var/sqe/spill"

[worker]
memory_limit = "24GB"
spill_to_disk = true
spill_dir = "/var/sqe/spill"

[rate_limit]
enabled = true

[metrics]
prometheus_port = 9090
otlp_endpoint = "https://otel-collector.observability:4317"
trace_sample_rate = 0.05

audit_log_path = "/var/log/sqe/audit/audit.jsonl"

[metrics.audit_export]
enabled = true
target = "otlp"

[metrics.openlineage]
enabled = true
emit_selects = false
# Set http_endpoint and/or file_path; use spool_path when HTTP is configured.

[storage.tvf]
allow_local_paths = false
allow_http = false
allowed_object_store_prefixes = ["s3://prod-warehouse/"]

Environment overrides follow the usual SQE_* / SQE_METRICS__* conventions (see sqe.toml.example and docs/site/book/src/deployment/configuration.md).

Memory pool decision

Common scale-up mistakes

Observability planes (what to use when)

Route audit logs and traces through an OpenTelemetry Collector when possible. Audit uses a dedicated OTLP log exporter so records are not dropped by trace sampling or log filters.

What production mode enforces

coordinator.production_mode = true (or SQE_PRODUCTION_MODE=1) turns the following into startup errors. The table is the one in sqe-core/src/config/production.rs; a unit test fails when the two drift. Each security.allow_* waiver is an explicit acknowledgement, logged at startup.

Checklist summary

Before first production traffic

• Real OIDC auth (no anonymous / passthrough)
• Rate limiting enabled
• TLS on Flight or at ingress
• TVF prefix allowlist; local paths and HTTP off
• memory_pool = greedy, honest memory_limit, spill on fast disk
• Audit log on persistent volume
• audit_export and openlineage configured if governance required

Before SF10-class load

• Distributed workers if scan CPU bound
• Memory fixes merged (#367, #366, #365, #364)
• SQE_MEMORY_LIMIT matches pod resources
• Polaris (or catalog) backed by durable storage
• OTel alerts on memory Red and audit spool lag

Before multi-tenant

• Per-user identity end to end
• Policy backend (Ranger) with mask_key
• DML policy fields in audit (when implemented)
• Inline TVF credentials restricted

Related docs

• Deployment configuration
• OpenLineage operations
• Full audit: docs/internal/audit/2026-07-10-sqe-full-audit.md (repo only, not published)
• Example config: sqe.toml.example in the repo root
• QUICKSTART.md in the repo root for local validation before production cutover

Recent Audit Remediation Notes (2026-07-11)

• [rate_limit] section and full production examples now documented in sqe.toml.example (see MRs !590, !591).
• Stale AUDIT.md and the full 2026-07-10-sqe-full-audit.md refreshed with progress (MRs !592, !594).
• Jemalloc and memory recommendations added to example (an earlier change).
• Babysat fixes for several R items verified and pushed (!579–!581).
• Perf Group 4 branches currently at main; MRs !585–!588 have notes.
• Production config validator, DML audit, observability wiring, and CI for write/distributed have seen substantial progress via prior and current MRs.

Refer to the full audit for the prioritized plan. Keep sqe.toml.example and this guide in sync as more items land.