profiles: PARTITION BY toYYYYMM(created_at) prevents ReplacingMergeTree dedup and makes point lookups scan every partition

[ayushjhanwar-png]

Summary

profiles is created as (code-migrations/3-init-ch.ts):

engine:      ReplacingMergeTree(created_at)
orderBy:     ['project_id', 'id']
partitionBy: 'toYYYYMM(created_at)'

created_at is both the ReplacingMergeTree version and the partition key, and it is rewritten on every profile upsert (profile-buffer.ts sets created_at: formatClickhouseDate(new Date()) on each merge). That causes two problems that compound as a table ages.

1. Duplicate versions can never be collapsed

ReplacingMergeTree only deduplicates within a partition. Because an update moves a row to a different monthly partition, a profile's versions scatter across months and merges can never collapse them.

Measured on our install (129.18M rows, ~10 GiB compressed): of the profile ids with more than one version, 16.5% had versions in more than one monthly partition. Rebuilding the table with a stable partition key dropped it from 129.22M to 115.66M rows — ~13.5M permanently-undedupable rows, ~10% of the table.

2. Point lookups scan every partition

getProfileById / the profile-buffer merge do:

SELECT * FROM profiles WHERE project_id = ? AND id = ? ORDER BY created_at DESC LIMIT 1

There is no date predicate (correctly — you don't know when the profile was last written), so every monthly partition is a candidate. ClickHouse reads ~1 granule per candidate part, so cost is parts × 8192 and grows every month.

Measured, single profile lookup on the same install (10 partitions, 106 active parts):

	Parts read	Rows read	Wall	CPU
current schema	92	584,537	25.8 ms	100.3 ms
repartitioned by project_id	12	97,708	23.6 ms	30.3 ms

Adding an artificial date filter to the current schema shows the same relationship — it is purely partition fan-out:

filter	parts	rows read	CPU
none	84	563,388	116.5 ms
created_at >= now() - 90d	34	221,063	44.2 ms
created_at >= now() - 2d	7	42,739	13.1 ms

This matters on the ingest path: every event from an identified user runs this lookup on a cache miss. A backend job that touched ~100K dormant users in ~30 minutes pushed these lookups to 66–77% of total ClickHouse query CPU and exhausted max_concurrent_queries_for_user.

FINAL reads are hit too, since their cost tracks part count — a count() FINAL for one project went from 5,873 ms / 113 parts / 1.57 GiB to 4,332 ms / 12 parts / 457 MiB, and an email search from 659 ms / 1.96 GiB to 161 ms / 522 MiB.

Why events is fine but profiles isn't

The same toYYYYMM(created_at) is applied to every table in that migration, which is correct for events: created_at is immutable there, and every query filters on a date range. For profiles both assumptions are inverted — the key mutates, and lookups are by id with no date predicate.

Suggested fix

Partition profiles on something stable. PARTITION BY project_id works well (project count is low, it never changes for a row, and it prunes the lookup):

engine:      ReplacingMergeTree(created_at)
orderBy:     ['project_id', 'id']
partitionBy: 'project_id'

Dropping PARTITION BY entirely also works. For reference, PostHog's equivalent person table is ReplacingMergeTree(version) ORDER BY (team_id, id) with no PARTITION BY.

A separate integer version column (instead of reusing created_at) would be more robust still, since it decouples "which row wins" from "when was it written".

Migration note

Partitioning can't be changed in place, but the rebuild is cheap. On 129M rows / 10 GiB it took 5m19s total: create the new table, INSERT … SELECT per partition, then EXCHANGE TABLES (atomic, so the table keeps its name and no application change is needed), then copy the rows written during the backfill. Cluster CPU roughly doubled for the ~12 seconds each partition took, with no query rejections.

One caveat for anyone doing this: the profile-list query in profile.service.ts uses created_at >= now() - INTERVAL 1 MONTH together with FINAL and relies on monthly pruning. After repartitioning it has to be rewritten to pick ids in a non-FINAL subquery and hydrate one page (which is also how PostHog reads person) — that took it from 12.5s to ~1s for us.

Happy to open a PR if you'd like it in this shape.