Support resource-fingerprint based query optimization for log sources

Problem Statement

Resource attribute filters are among the most common log query patterns, but they perform poorly on the recommended ClickStack schema mainly due to a lack of physical data locality by resource identity (see ClickHouse: log clustering). The primary key is (ServiceName, TimestampTime), which groups rows by service and time but says nothing about the resource identity that produced them. Rows sharing the same resource attributes are interleaved with rows from every other resource in the same service and time range, resulting in poor compression in storage and poor pruning during reads.

The current mitigations fall short:

1. Handpick resource attributes for the primary key: Not viable because logs from different source types (Kubernetes container logs, syslog, OTel SDKs) are written to a single shared table. Different producers attach different resource attributes, so there is no single set of resource columns that could be prepended to the key without either (a) exploding key cardinality for sources that lack those columns, or (b) providing no benefit for sources that do not carry the chosen attributes. The table must remain schema-agnostic to the resource attribute vocabulary of its producers.

2. Skip indexes (bloom filters): Bloom filters can rule out blocks that definitely lack a value, but for high-prevalence values (e.g. a namespace carrying 30% of traffic) the target value exists in nearly every block, resulting in ineffective pruning and high latencies at larger time ranges.

3. Lightweight projections: Since ClickHouse 25.6, lightweight _part_offset-based projections act as secondary indexes with minimal storage overhead. But each projection adds write amplification on insert, and at high ingest rates even 5-10 projections measurably increase insert latency and compaction load.

4. Multiple tables with different primary/sort keys. Create separate tables per source type (e.g. otel_logs_k8s, otel_logs_syslog), each with a primary/sort key tailored to its resource attributes. This gives optimal pruning per source type but breaks the unified query model: HyperDX expects a single table (or view) per data source. A Merge table can unify reads, but the ClickHouse query planner cannot push predicates through a Merge table into per-table primary key pruning -- it falls back to a full scan across all underlying tables. Materialized views for fan-out writes add operational complexity (schema changes must be applied N times, TTL/retention diverges). The approach trades query-layer simplicity for storage-layer optimization.

Impact: Common queries with resource attribute filters have high latencies as a lot of data is scanned in each query. It degrades for wider time ranges and for dashboards where there could be multiple such queries.

---

Proposed Design

Resource fingerprinting solves this by collapsing a selected set of resource attributes into a single hash value (resource_fingerprint) and using it in the sort key. This can be achieved in the following ways:

-- Current:
PRIMARY KEY (ServiceName, TimestampSec)
ORDER BY (ServiceName, TimestampSec, TimestampNano)

-- Option#1: ResourceFingerprint in ORDER BY only
PRIMARY KEY (TimestampBucket, ServiceName, TimestampSubBucket)
ORDER BY (TimestampBucket, ServiceName, TimestampSubBucket, ResourceFingerprint, TimestampSec, TimestampNano)

-- Option#2: ResourceFingerprint in PRIMARY KEY
PRIMARY KEY (TimestampBucket, ServiceName, ResourceFingerprint, TimestampSec)
ORDER BY (TimestampBucket, ServiceName, ResourceFingerprint, TimestampSec, TimestampNano)

	Pros	Cons
Current	Simple, time-ordered	No data co-location: logs from all streams interleaved, resulting in poor compression and poor pruning on resource attributes.
Option#1	Good data co-location through physical sort order. Works as-is with HyperDX (no separate lookup table).	To achieve read perf improvements, attributes used in the ResourceFingerprint must be separately indexed (via bloom filters or projections) for direct attribute filtering.
Option#2	Good data co-location (similar to Option 1). All attributes in the ResourceFingerprint get automatically indexed via a separate lookup table. This means lesser schema migrations and also lesser storage overhead.	A separate lookup table is required to resolve attribute filters (e.g. cluster=X) into matching ResourceFingerprints, which are then used to prune blocks in the main table. This is not currently supported in HyperDX.

Recommendation: Although option#1 works with HyperDX out of the box. Moving to option#2 has significant benefits.

Industry Precedent

Log Fingerprinting is well-established across observability systems:

• Grafana Loki hashes label sets into stream fingerprints and stores chunks per-stream, giving physical data locality by resource identity.
• Datadog Husky stores events in columnar fragments with per-row-group min/max metadata; compaction physically co-locates events sharing the same tag set, and the query engine prunes row groups via metadata checks before decoding columns -- analogous to sort-key-based granule skipping in ClickHouse.
• Honeycomb Retriever originally stored each service as a separate dataset directory; as customers scaled to thousands of services, cross-service queries degraded. Their virtual datasets solution co-locates events from frequently co-queried services into shared "container datasets," cutting median query time from ~20s to ~0.2s -- the same principle of physical locality by resource identity.
• SigNoz adapted this for ClickHouse and documented a 99.5% reduction in blocks scanned.

HyperDx Implementation Details

The fingerprinting technique requires a two-step Clickhouse query: first resolve which fingerprints match the resource filter (small lookup table), then use those fingerprints to prune blocks in the main table. That two-step pattern must be expressed in the SQL itself.

Three areas of HyperDx need changes:

1. Source configuration -- a new resourceLookupTable field tells the query layer where to resolve fingerprints
2. Query serializer -- resource attribute filters are intercepted and rewritten as fingerprint lookups instead of direct column/Map filters
3. Chart renderer -- the fingerprint clause is injected into the final SQL output

The fingerprint resolution can be implemented in either two ways:

	Common Table Expression (CTE)	User Defined Function (UDF)
Summary	SQL query has a surrounding WITH clause to get matching fingerprints	SQL query has a nested function call to get matching fingerprints
HyperDX complexity	Higher -- must build and prepend WITH clause, manage CTE naming	Lower -- collect filters into arrays, emit a function call
Updateability	Redeploy HyperDX to change resolution logic	ALTER FUNCTION in ClickHouse, no HyperDX redeploy
Performance risk	None -- CTE is standard SQL	UDF is lambda-based; verify it evaluates once, not per-row
Multi-attribute filters	Must build compound WHERE in the CTE	Single call with array args, AND semantics via arrayAll
Map / JSON support	HyperDX must generate the correct extraction function per storage format	UDF encapsulates the extraction logic; switch from Map to JSON by altering the function

Recommendation: I am currently inclined towards the CTE approach as hyperdx remains in control of the exact query and there are some unknowns with UDFs.

Source Configuration

A new optional resourceLookupTable is added to LogSourceSchema:

type ResourceLookupConfig = {
  databaseName: string;
  tableName: string;               // e.g. "otel_logs_resource"
  fingerprintColumn: string;       // e.g. "resource_fingerprint"
  timeBucketColumn: string;        // e.g. "seen_at_bucket"
  timeBucketIntervalSeconds: number; // e.g. 1800 (30 min)
  fingerprintAttributes?: string[];  // whitelist of resource attribute keys included in the fingerprint
                                     // only filters on these keys route through the lookup table;
                                     // filters on non-whitelisted keys use direct column access
};

Source configuration will also have a feature-flag with an enableResourceFingerprinting boolean so it can be toggled per source without redeployment.

Example source config:

{
  "from": {
    "databaseName": "otel",
    "tableName": "otel_logs"
  },
  "resourceAttributesExpression": "ResourceAttributes",
  "resourceLookupTable": {
    "databaseName": "otel",
    "tableName": "otel_logs_resource",
    "fingerprintColumn": "resource_fingerprint",
    "timeBucketColumn": "seen_at_bucket",
    "timeBucketIntervalSeconds": 1800,
    "fingerprintAttributes": [
      "k8s.cluster.name",
      "k8s.namespace.name",
      "k8s.pod.name",
      "k8s.container.name",
      "k8s.node.name",
      "deployment.environment",
      "deployment.region"
    ]
  }
}

Query Serializer

a. Track resource attribute filters. When a field resolves to a resource attribute sourced from ResourceAttributes (or a related materialized column), check if the attribute key is in the fingerprintAttributes whitelist. If yes, collect the predicate into a resourceFilters array on the serializer context for fingerprint resolution. If not (or if fingerprintAttributes is not configured), generate a direct WHERE clause as before. This ensures only attributes that are part of the fingerprint hash route through the lookup table -- filtering on non-whitelisted attributes still works via direct column/Map/Json access.

b. Fingerprint resolution. Translating collected resource filters into fingerprint lookups with a CTE (Common Table Expression):

WITH __resource_filter AS (
    SELECT resource_fingerprint
    FROM otel_logs_resource
    WHERE ResourceAttributes['k8s.namespace.name'] = 'production'
      AND seen_at_bucket BETWEEN toStartOfInterval($start, INTERVAL 30 MINUTE) - 1800
                              AND toStartOfInterval($end, INTERVAL 30 MINUTE)
)
SELECT Timestamp, ServiceName, SeverityText, Body
FROM otel_logs
WHERE TimestampTime >= fromUnixTimestamp64Milli(1773613079000)
  AND TimestampTime <= fromUnixTimestamp64Milli(1773699479000)
  AND ServiceName IN ('my-service')
  AND resource_fingerprint IN __resource_filter
ORDER BY TimestampTime DESC, Timestamp DESC
LIMIT 200

Ending Notes

Since this might be a big change, I want to get opinions on the proposed solution. Thank you.

Start by locating LogSourceSchema, the query serializer, and the chart renderer described in the issue. Trace how resource attribute filters become SQL, then determine how the optional lookup configuration and feature flag should flow through those entry points. Done means whitelisted filters resolve resource fingerprints through the proposed CTE while non-whitelisted filters retain direct access.