chart.properties: profile properties often missing from breakdown/filter pickers (unordered LIMIT 10000 sample)

[niajkitir]

Problem

chart.properties builds the list of available profile properties from an arbitrary sample of 10,000 profiles, unioning their properties map keys. On a project with millions of identified profiles, any property set on a small fraction of them is usually absent from the breakdown/filter picker, so it can't be selected at all.

Because the sample uses LIMIT with no ORDER BY, which rows come back depends on how ClickHouse schedules the read. The list therefore changes between requests — a property can appear, then vanish on reload.

Code

packages/trpc/src/routers/chart.ts (current main, f72310c3):

const profiles = await clix(ch, 'UTC')
  .select<Pick<IServiceProfile, 'properties'>>(['properties'])
  .from(TABLE_NAMES.profiles)
  .where('project_id', '=', projectId)
  .where('is_external', '=', true)
  .limit(10_000)          // arbitrary sample, no ORDER BY
  .execute();

Evidence

Self-hosted project with a few million identified profiles and ~400 distinct profile-property keys. Re-running the same sample query while varying max_threads (to emulate the varying parallelism a busy server sees):

max_threads	profile keys returned	rare property present?
1	27	no
2	182	yes
12	234	yes
32	166	yes

So the picker offers 27–234 of ~400 keys depending on the request. On an idle server the property usually shows up; under load it usually doesn't, which makes the bug look intermittent to users.

Not a performance problem — the sample query returns in ~140ms.

The search input is also not at fault: it's a substring includes() match in PropertiesCombobox. The item simply isn't in the fetched array, so neither a partial nor an exact search term can surface it.

Reproduction

1. Project with millions of identified profiles.
2. Set a profile property on a small subset (~0.03%).
3. Open a report → add a breakdown or filter → Profile properties.
4. Property is usually not listed and search finds nothing; reloading a few times occasionally makes it appear.

Suggested fix

Aggregate over all profiles rather than sampling rows — the same approach getGroupPropertyKeys (packages/db/src/services/group.service.ts) already uses for group properties:

SELECT DISTINCT arrayJoin(mapKeys(properties)) AS key
FROM profiles
WHERE project_id = {projectId} AND is_external = true

Measured on the same project: ~750ms, returns all keys, and is identical at max_threads 1 / 4 / 16. It also avoids shipping 10,000 full properties maps into Node just to discard the values. FINAL isn't needed, since older row versions can only contribute keys that genuinely existed. Good candidate for the existing cacheMiddleware if the extra latency matters.

Related, same procedure

The event-property half uses:

.orderBy('length(property_key)', 'ASC')
.orderBy('created_at', 'DESC')
.limit(10_000);

Once a project passes 10,000 distinct event property keys this silently drops the longest ones first, which tend to be the most descriptive. We've seen a project in the 8k range, so it's reachable in normal use. Raising the cap, or ordering by recency rather than length, would avoid a confusing partial list.