Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

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

Aperta
  • Qualcosa non va?
#508 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
55/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
sql, typescript
Ambito
backend, database

Direzione di ricerca

Inizia da packages/db/code-migrations/3-init-ch.ts, quindi esamina profile-buffer.ts e getProfileById per comprendere il comportamento attuale del partizionamento e della ricerca. Esamina profile.service.ts per la query di eliminazione mensile e i vincoli della migrazione. Il lavoro è completato quando i profili vengono deduplicati tra gli aggiornamenti, le ricerche puntuali evitano il fan-out sulle partizioni mensili e la query dell’elenco dei profili restituisce ancora in modo efficiente la pagina corretta dopo la ricostruzione.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
TypeScript
Stelle
7.1k
Fork
516
Merge medio
1g 16h
PR unite (30g)
10

Preparare l'ambiente

  • Include un Dockerfile o un file Docker Compose
  • Nessun modello di pull request
  • Nessuna guida per i contributori

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di Openpanel-dev/openpanel

Tutte le issue di Openpanel-dev/openpanel

Issue simili

Altre issue su TypeScript

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.