Core features

Recommendation catalog

Every finding OptiHouse can produce, grouped into ten delivery waves — from quick storage wins to SQL rewrites and physical design advice. Each recommendation carries a severity, an expected impact and an auto-generated runbook.

How to read a recommendation

Every finding has a type (one of the rows below), a severity (low / medium / high / critical), an expected impact estimated in bytes, CPU or dollars, and a confidence score. High-confidence findings can be applied or auto-applied; low-confidence ones are surfaced as soft signals.

Status markers

Recommendation types in Wave 1–5 are fully shipped. Wave 6–10 ship at MVP depth — the analyzer, runbook and UI exist, and the runbook spells out the real integration steps.

Wave 1 — Quick wins

ID	Recommendation	What it surfaces
1.1	Zombie materialized views	MVs that still consume merges and storage but feed nothing.
1.2	Storage fragmentation map	Tables with too many tiny parts and low average part size.
1.3	Auto-generated runbooks	Every finding gets probable causes, diagnostic SQL and remediation steps.
1.4	Query scheduling	Concurrent bursts at the same minute that could be staggered.
1.5	Recommendation impact tracking	Before/after metrics once a fix is applied — bytes saved, parts reduced.

Wave 2 — Query intelligence

ID	Recommendation	What it surfaces
2.1	Query fingerprint drift	A query pattern that started reading or running noticeably more than before.
2.2	Partition efficiency score	Queries that scan far more partitions than they return rows from.
2.3	Workload classification	Users bucketed into ETL / BI / monitoring / ad-hoc traffic.
2.4	Workload seasonality	Recurring daily peak hours worth planning capacity around.
2.5	Query blast radius	A single user or pattern contending for a disproportionate share of resources.

Wave 3 — Operational health

ID	Recommendation	What it surfaces
3.1	Insert pressure analyzer	Tables receiving many small inserts that create part-merge debt.
3.2	Merge debt index	A growing merge backlog that the cluster is not keeping up with.
3.3	Replica imbalance detector	Replicas carrying uneven load, lag or part counts.

Wave 4 — Enterprise FinOps & storage

ID	Recommendation	What it surfaces
4.1	Cost attribution	Compute and storage cost broken down per team, product or service.
4.2	Cold data advisor	Large tables not touched for months — candidates for a colder tier.
4.3	Schema evolution risk	Schema changes that look risky for downstream consumers.
4.4	Upgrade advisor	Readiness scoring before bumping the ClickHouse version.

Wave 5 — Advanced differentiators

ID	Recommendation	What it surfaces
5.1	Query replay simulator	Top tables worth a what-if rewrite (MVP: candidate surfacing).
5.2	SQL CI/CD guardrails	An anti-pattern summary you can wire into a PR check.
5.3	Historical incident correlation	Concurrent symptoms that tend to appear together during incidents.

Wave 6 — SQL rewriter engine

ID	Recommendation	What it surfaces
6.1	Auto-PREWHERE advisor	Heavy-read queries that would benefit from a PREWHERE predicate.
6.2	FINAL removal rewriter	`FROM ... FINAL` queries rewritten with `argMax` or `LIMIT 1 BY`.
6.3	JOIN reorder & algorithm hints	Memory-heavy joins that should switch to `grace_hash` / `parallel_hash`.
6.4	Subquery → CTE refactor	Repeated subqueries collapsed into a single shared CTE.

Wave 7 — Physical design advisor

ID	Recommendation	What it surfaces
7.1	Per-column codec advisor	Columns that would compress better with Delta, Gorilla, T64 or ZSTD.
7.2	LowCardinality / Enum advisor	String columns with few distinct values worth wrapping in LowCardinality.
7.3	Skipping index recommender	Filter columns that would benefit from a `minmax`, `set` or `bloom_filter` index.
7.4	Projection advisor	Read patterns that a projection inside the source table would accelerate.
7.5	PK reorder & type downsizing	Primary-key column order and oversized column types worth revisiting.

Wave 8 — What-if simulator

ID	Recommendation	What it surfaces
8.1	Drop column preview	Large columns that never appear in any observed query.
8.2	Add projection preview	A workload-wide summary of projection candidates.
8.3	Repartition / TTL preview	Old data on big tables that a TTL move would free up.
8.4	Disk capacity forecast	Disks above a utilisation threshold, with a days-until-full estimate.

Wave 9 — Integrations

ID	Recommendation	What it surfaces
9.1	dbt manifest integration	Queries traced back to the dbt model that emitted them.
9.2	GitHub / GitLab PR bot	Anti-patterns turned into a comment on the pull request that introduced them.
9.3	Slack / email weekly digest	A scheduled summary of new findings and dollar savings.
9.4	Prometheus / OpenTelemetry exporter	Platform metrics exported to your existing observability stack.

Wave 10 — Workload governance

ID	Recommendation	What it surfaces
10.1	Resource pool advisor	`CREATE WORKLOAD` pools derived from how users actually behave.
10.2	Noisy neighbor detector	A user that is both a large share of load and very bursty.
10.3	Pre-flight cost estimator	An estimate of what a query will cost before it runs.