はじめに

Qiita記事を7.7万件ほど集めてPostgreSQLに突っ込み、「どんな記事が伸びるか」を集計していたのですが、タグ別の中央値を出すクエリで毎回コーヒーを淹れる時間ができていました。集計のたびに数百ミリ秒〜秒単位で待つ。分析って、この「待ち時間 × 試行回数」がそのまま思考のテンポを削るんですよね。

集計を一本投げるたびに数秒待って、頭の中の問いが冷めてしまった経験、ありませんか？そこで「分析ワークロードだけClickHouseに逃がす」ことにしました。本記事は、その移行を実際に最後までやった記録です。手順だけでなく、途中で踏んだ罠（Nullable地獄・タイムゾーンずれ・NULバイト）も全部書きます。

対象データはQiita記事 76,764件。移行元はローカルのPostgreSQL 17、移行先はClickHouseです。

対象読者

PostgreSQL/MySQLは使うが、分析特化DBは未経験の人

「ClickHouse速いらしい」は聞くが、自分のデータをどう移すのか分からない人

移行の全体像

先に地図を出します。移行は スキーマ設計 / データ転送 / クエリ書き換え の3ステップに分解できて、ClickHouseには postgresql() というテーブル関数があるので、PostgreSQLを直接読んでそのまま流し込めます。

ポイントは「全部のテーブルを移す」のではなく「集計が重い分析テーブルだけ逃がす」こと。トランザクション処理はPostgreSQLに残したままで構いません。順に見ていきます。

ステップ1: スキーマをClickHouse流に設計し直す

最初にやりがちな失敗が「PostgreSQLのDDLをそのまま持ってくる」ことです。これだと半分しか恩恵を受けられません。ClickHouseには発想の転換が2つ要ります。

ひとつめは、索引の考え方。PostgreSQLでは「遅いクエリにCREATE INDEX」が定石ですが、ClickHouseにはPostgreSQLのようなB-tree二次索引（独立した行単位の CREATE INDEX）はありません。まず ORDER BY（ソートキー）でデータの物理的な並びを決め、補助的にdata skipping indexやprojectionを使う、という設計に切り替えます。ClickHouseの主キーは全行ではなく既定8192行（granule）ごとに1エントリだけ持つスパース索引なので、ソート順に沿った範囲スキャンが圧倒的に速くなる仕組みです¹。今回は時系列で引くことが多いので ORDER BY (created_at, id) にしました。

ふたつめは、Nullableを避けること。これは後述のハマりどころとも直結します。ClickHouseのNullableカラムは「値の列」とは別に「NULLかどうかのUInt8マスク列」を持つので、触るたびに余計な処理が走ります。公式も明確に「Nullableは避けて、空文字や0などの既定値で代用しろ」と言っています²。欠損のない列はわざわざNullableにしません。

最終的なターゲットのDDLはこうなりました。

migrated.qiita_items

CREATE TABLE migrated.qiita_items
(
    id               String,
    title            String,
    created_at       DateTime('Asia/Tokyo'),
    updated_at       DateTime('Asia/Tokyo'),
    likes_count      UInt32,
    stocks_count     UInt32,
    comments_count   UInt32,
    tags             Array(String),
    user_id          String,
    user_followers   UInt32,
    user_items_count UInt32,
    url              String,
    title_length     UInt16,
    body_length      UInt32,
    code_block_count UInt16,
    heading_count    UInt16,
    image_count      UInt16,
    link_count       UInt16
)
ENGINE = MergeTree
ORDER BY (created_at, id);

件数系はすべて符号なし整数（UInt）、日時は DateTime('Asia/Tokyo') にしました。ClickHouseはタイムスタンプ自体をタイムゾーン非依存のUnix時刻で保持し、Asia/Tokyo は列のメタデータとして解釈・表示・カレンダー計算に使われます（「型に埋め込む」というより「列にタイムゾーンを指定する」イメージ）。Qiitaの投稿時刻は秒精度なので DateTime で十分でしたが、サブ秒が必要なら DateTime64 を選びます。タグはPostgreSQLの text[] に対応する Array(String) です。

ステップ2: postgresql() でPostgreSQLを直接読む

ここがClickHouseの気持ちいいところ。postgresql() テーブル関数を使うと、ClickHouseのSQLの中からPostgreSQLのテーブルをそのまま参照できます³。

まず疎通確認。ClickHouseからPostgreSQLの行数を数えてみます。

SELECT count()
FROM postgresql('postgres:5432', 'qiita', 'qiita_items', 'bench', 'bench');
-- → 76764

引数は host:port・データベース名・テーブル名・ユーザー・パスワードの順です。一発で76,764件返ってきました。エクスポートもCSV変換もなしに、別のDBの中身がクエリ一行で見えるのは普通に感動します。

ひとつ仕様として知っておくと良いのが、述語のプッシュダウン。= や > のような単純なWHEREはPostgreSQL側で実行されますが、JOIN・集計・ソート・LIMITはPostgreSQLからデータを取り切ったあとClickHouse側で実行されます³。なので「巨大テーブルを毎回 postgresql() で集計する」のは非効率で、移行（一度ClickHouseに取り込む）に向いた関数です。

ハマりどころ①: 自動マッピングは全部Nullableで来る

「じゃあ INSERT INTO ... SELECT * FROM postgresql(...) で終わりじゃん」と思って実行したら、型エラーで弾かれました。原因を探るために、postgresql() がPostgreSQLの型をどうマッピングしているか覗いてみます。

DESCRIBE TABLE postgresql('postgres:5432','qiita','qiita_items','bench','bench');

結果がこれです（抜粋）。

列	ClickHouseが推測した型
title	Nullable(String)
created_at	Nullable(DateTime64(6))
likes_count	Nullable(Int32)
tags	Array(Nullable(String))

見ての通り、ほぼ全列がNullableで来ます。さらに日時は DateTime64(6)（マイクロ秒精度）、配列は Array(Nullable(String))。ステップ1で「Nullableは避ける」と決めた非Nullableのターゲット表に、このままINSERTしようとすると当然こけます。ここで一度ハマりました。

解決は素直で、INSERT時に ifNull / assumeNotNull でNullableを剥がしてやればいいだけです。

この「全列Nullable化」は external_table_functions_use_nulls という設定で制御できます（既定は 1）。0 にすると外部テーブル関数がNullableを付けず既定値で読み込みますが、その場合はNULLが既定値（空文字や0）に潰れる点に注意してください。また assumeNotNull はNULLが来ると不定値を返すので、NULLが無いと確信できる列にだけ使います（今回の created_at などはNOT NULLなので安全。不安なら ifNull で明示的に既定値を与えるのが無難です）。

ステップ3: 磨いた型に INSERT ... SELECT で流し込む

Nullableを剥がしつつ、ステップ1の型へ変換しながら投入します。

migrate_from_pg.sql

INSERT INTO migrated.qiita_items
SELECT
    id,
    ifNull(title, ''),
    toDateTime(assumeNotNull(created_at), 'Asia/Tokyo'),
    toDateTime(assumeNotNull(updated_at), 'Asia/Tokyo'),
    toUInt32(ifNull(likes_count, 0)),
    toUInt32(ifNull(stocks_count, 0)),
    toUInt32(ifNull(comments_count, 0)),
    arrayMap(x -> ifNull(x, ''), tags),
    ifNull(user_id, ''),
    toUInt32(ifNull(user_followers, 0)),
    toUInt32(ifNull(user_items_count, 0)),
    ifNull(url, ''),
    toUInt16(ifNull(title_length, 0)),
    toUInt32(ifNull(body_length, 0)),
    toUInt16(ifNull(code_block_count, 0)),
    toUInt16(ifNull(heading_count, 0)),
    toUInt16(ifNull(image_count, 0)),
    toUInt16(ifNull(link_count, 0))
FROM postgresql('postgres:5432', 'qiita', 'qiita_items', 'bench', 'bench');

配列の中身のNullableも arrayMap(x -> ifNull(x, ''), tags) で剥がしているのがちょっとした勘所です。これで76,764件が移行先テーブルに入りました。

ステップ4: 移行が壊れていないか検証する

データ移行で一番怖いのは「件数は合ってるけど中身が微妙に壊れている」パターンです。とくに日時のタイムゾーンと配列は静かに壊れがち。なので移行元と移行先で、別々の角度から5つの指標を突き合わせました。

検証指標	PostgreSQL（移行元）	ClickHouse（移行先）	一致
min(created_at) のepoch秒	1764514812	1764514812	✓
max(created_at) のepoch秒	1780841127	1780841127	✓
いいね数の総和	258,047	258,047	✓
タグ要素の総数	275,850	275,850	✓
ユニークなuser_id数	17,715	17,715	✓

epoch秒で突き合わせているのがミソで、これが合っていれば「同じ時点を保持できた」ことを確認できます（タイムスタンプはタイムゾーンを跨いでも時点としては不変なので、保存時のズレを検知できる）。ただしepoch一致が言えるのはあくまで時点まで。月次・日次のバケットが正しいかは別問題なので、toStartOfMonth(ts, 'Asia/Tokyo') などでも別途突合します（この罠は後述します）。指標はすべて一致しました。ここまで来てやっと「移行できた」と安心できます。あなたも移行したら、件数だけでなくこういう「壊れていたら気づける指標」で必ず突合してください。

ステップ5: 分析SQLを書き換える

テーブルが移っても、既存の分析SQLはそのままでは動きません。PostgreSQLとClickHouseは方言が違うからです。よく使うものを対訳にしておきます。

やりたいこと	PostgreSQL	ClickHouse
中央値（厳密）	`percentile_cont(0.5) WITHIN GROUP (ORDER BY x)`	`medianExact(x)` / `quantileExact(0.5)(x)`
中央値（近似・高速）	（`percentile_cont` は常に厳密・補間あり）	`median(x)` / `quantile(0.5)(x)`
配列の展開	`, unnest(tags) AS tag`	`ARRAY JOIN tags AS tag`
条件付き集計	`count(*) FILTER (WHERE cond)`	`countIf(cond)`
帯分け	`CASE WHEN ... END`	`multiIf(...)`
曜日	`extract(isodow FROM ts)`	`toDayOfWeek(ts)`
月で丸める	`date_trunc('month', ts)`	`toStartOfMonth(ts)` / `dateTrunc('month', ts)`

ひとつ注意したいのが中央値です。ClickHouseの median(x) は quantile() のエイリアスで、既定では reservoir sampling による近似値を返します（厳密ではなく、非決定的になりうる）。一方PostgreSQLの percentile_cont は隣接2値を補間する厳密な連続パーセンタイル。移行前後で値をきっちり突き合わせたいなら、ClickHouse側は medianExact(x) を使ってください。探索フェーズで「だいたいの中央値が速く欲しい」なら近似の median(x) で十分です。

正直、書き味はClickHouseのほうが好きです。percentile_cont(0.5) WITHIN GROUP (ORDER BY x) があの長さなのに対して、medianExact(x) で済む。集計をたくさん試す探索フェーズでは、この短さが地味に効きます。

たとえばタグ別のいいね中央値トップを出すクエリは、こう書き換わります。

SELECT tag, count() AS n, median(likes_count) AS med
FROM migrated.qiita_items
ARRAY JOIN tags AS tag
GROUP BY tag
HAVING n >= 100
ORDER BY med DESC, n DESC
LIMIT 15;

ハマりどころ②: タイムゾーンで月次集計が1か月ずれた

これが一番ヒヤッとしました。移行直後に月次の記事数を出したら、PostgreSQL側より月の数がひとつ多い。原因はタイムゾーンでした。

元データの created_at は +09:00 付き。ClickHouse側は DateTime('Asia/Tokyo') で列にタイムゾーンを指定していたので、toStartOfMonth がJST基準で丸めてくれます。一方、中間の検証で使ったツール側でタイムゾーンを指定せずUTCのまま月丸めすると、2025-12-01 00:00:12+09:00 の同じ時点が 2025-11-30 15:00:12 UTC として丸められ、11月のバケットに落ちてしまう。これで「11月の記事が174件」という幽霊バケットが生まれていました。同じ時点でも、どのタイムゾーンで丸めるかでバケットが変わる、というのが肝です。

タイムゾーン付きの時刻を移行するときは、移行先の型（DateTime('Asia/Tokyo') のようにtzを型に埋める）だけでなく、集計に使うクライアント/セッションのタイムゾーンも明示的に揃えてください。月次・日次の境界は、ずれていても「なんか件数が違う」程度にしか見えず、気づきにくいです。

epoch秒で突合する検証（ステップ4）を入れていたおかげで、ズレにすぐ気づけました。検証指標は手を抜かないに限ります。

ハマりどころ③: PostgreSQL側のNULバイト

これは移行の準備段階、PostgreSQLにデータを入れるときに踏んだ罠です。逆方向の注意点として共有しておきます。

QiitaのタイトルにごくまれにNULバイト（0x00）が混入していて、PostgreSQLの text 型はこれを許しません。COPY の途中で PostgreSQL text fields cannot contain NUL (0x00) bytes と言われて投入が止まりました。

おもしろいのは、ClickHouseの String はただのバイト列なのでNULバイトを平気で格納できること。同じ文字列データでも、DBによって受け入れ可否が違うわけです。PostgreSQL側に入れるときだけ、読み込み時に除去しました。

def clean(s: str) -> str:
    # PostgreSQLのtextはNUL(0x00)を許さない。ClickHouseのStringは許容する
    return s.replace("\x00", "") if s else (s or "")

ClickHouse Cloudで本番移行するなら

正直に書いておくと、今回の postgresql() 直読みはローカルのClickHouse（Docker）で実証しました。理由は単純で、ClickHouse Cloudは私のローカルPostgreSQLには到達できないからです。手元のPostgreSQLとClickHouseを同じDockerネットワークに置いて、サービス名で参照させています。

本番のClickHouse Cloudで継続的に移行・同期したい場合は、マネージドのClickPipes（Postgres CDC）を使うのが筋です⁴。論理レプリケーションを使って差分を取り込み続けてくれます。ただしCDCにはPostgreSQL側でreplication slotの作成や接続到達性などの前提があり、PgBouncerのようなプロキシ経由の接続は非対応です。一回限りの移行やアドホックな連携なら postgresql()、継続同期ならClickPipes、と使い分けるイメージです（このあたりはマネージド機能なので、利用時は執筆時点（2026年6月）から仕様が更新されていないか公式を確認してください）。

なお最近はPostgres側からClickHouseを引く pg_clickhouse 拡張も出ていて、逆向きの連携も選択肢に入ってきています⁵。

おわりに

やってみて思ったのは、ClickHouseへの移行は「DDLを書き換える作業」ではなく「分析のためにデータの持ち方を考え直す作業」だということ。索引をORDER BYに、Nullableを既定値に、と発想を切り替えると、ちゃんと速くなる土台ができます。そして postgresql() のおかげで、転送そのものは拍子抜けするほど簡単でした。

A practical introduction to primary indexes in ClickHouse — https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes ↩
Avoid nullable columns | ClickHouse Docs — https://clickhouse.com/docs/optimize/avoid-nullable-columns ↩
postgresql table function | ClickHouse Docs — https://clickhouse.com/docs/sql-reference/table-functions/postgresql ↩ ↩²
Ingesting data from Postgres to ClickHouse (using CDC) | ClickHouse Docs — https://clickhouse.com/docs/integrations/clickpipes/postgres ↩
Introducing pg_clickhouse: A Postgres extension for querying ClickHouse — https://clickhouse.com/blog/introducing-pg_clickhouse ↩