Hacktoberfest 2026：メンテナが10月に向けて印を付けた、オープンで初心者向けの issue。 Hacktoberfest の issue を見る

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

オープン
  • 何か問題がありますか？
#508 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
4/5
見積もり時間
3〜5日
初心者へのやさしさ
55/100
issue の種類
バグ
明瞭さ
明確に書かれている
活発さ
活発
技術スタック
sql, typescript
領域
backend, database

調査の方向性

packages/db/code-migrations/3-init-ch.ts から始め、profile-buffer.ts と getProfileById を調べて、現在のパーティショニングと検索の挙動を理解します。月次の削除クエリとマイグレーションの制約については profile.service.ts を確認します。更新間でプロフィールが重複排除され、単一レコード検索で月次パーティションへのファンアウトを回避し、再構築後もプロフィール一覧クエリが正しいページを効率的に返せれば完了です。

索引モデルが issue の本文から書いたものです。

説明

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.

主要言語
TypeScript
スター
7.1k
フォーク
516
平均マージ
4日 40分
マージ済み PR（30日）
4

環境構築

  • Dockerfile または Docker Compose ファイルあり
  • プルリクエストのテンプレートなし
  • コントリビューションガイドなし

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

Openpanel-dev/openpanel のほかの issue

Openpanel-dev/openpanel の issue をすべて見る

似ている issue

TypeScript の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。