Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Abierto
  • ¿Algo no está bien?
#508 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
55/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
sql, typescript
Área
backend, database

Línea de trabajo

Empieza en packages/db/code-migrations/3-init-ch.ts y luego inspecciona profile-buffer.ts y getProfileById para entender el comportamiento actual de particionado y búsqueda. Revisa profile.service.ts para consultar la query de depuración mensual y las restricciones de la migración. El trabajo estará terminado cuando los perfiles se dedupliquen entre actualizaciones, las búsquedas puntuales eviten la expansión entre particiones mensuales y la query de la lista de perfiles siga devolviendo de forma eficiente la página correcta después de la reconstrucción.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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.

Lenguaje dominante
TypeScript
Estrellas
7.1k
Forks
516
Merge medio
4 d 40 min
PR fusionados (30 d)
4

Preparar el entorno

  • Incluye un Dockerfile o un archivo de Docker Compose
  • Sin plantilla de pull request
  • Sin guía de contribución

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de Openpanel-dev/openpanel

Todos los issues de Openpanel-dev/openpanel

Issues similares

Más issues de TypeScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.