1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

「AI時代のデータベース、何が変わる？」について語ろう！
Qiita Tech Festa20262026年7月13日まで開催中！
@ktdatascienceTeam Coffee

PostgreSQLの分析テーブルをClickHouseへ移してみた——postgresql()直読みの手順とハマりどころ全部

1
Posted at

はじめに

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

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

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

対象読者

  • PostgreSQL/MySQLは使うが、分析特化DBは未経験の人
  • 「ClickHouse速いらしい」は聞くが、自分のデータをどう移すのか分からない人

移行の全体像

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

image.png

ポイントは「全部のテーブルを移す」のではなく「集計が重い分析テーブルだけ逃がす」こと。トランザクション処理は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エントリだけ持つスパース索引なので、ソート順に沿った範囲スキャンが圧倒的に速くなる仕組みです1。今回は時系列で引くことが多いので ORDER BY (created_at, id) にしました。

ふたつめは、Nullableを避けること。これは後述のハマりどころとも直結します。ClickHouseのNullableカラムは「値の列」とは別に「NULLかどうかのUInt8マスク列」を持つので、触るたびに余計な処理が走ります。公式も明確に「Nullableは避けて、空文字や0などの既定値で代用しろ」と言っています2。欠損のない列はわざわざ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のテーブルをそのまま参照できます3。

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

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

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

image.png

ひとつ仕様として知っておくと良いのが、述語のプッシュダウン。= や > のような単純なWHEREはPostgreSQL側で実行されますが、JOIN・集計・ソート・LIMITはPostgreSQLからデータを取り切ったあとClickHouse側で実行されます3。なので「巨大テーブルを毎回 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）を使うのが筋です4。論理レプリケーションを使って差分を取り込み続けてくれます。ただしCDCにはPostgreSQL側でreplication slotの作成や接続到達性などの前提があり、PgBouncerのようなプロキシ経由の接続は非対応です。一回限りの移行やアドホックな連携なら postgresql()、継続同期ならClickPipes、と使い分けるイメージです（このあたりはマネージド機能なので、利用時は執筆時点（2026年6月）から仕様が更新されていないか公式を確認してください）。

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

おわりに

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

  1. A practical introduction to primary indexes in ClickHouse — https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes ↩

  2. Avoid nullable columns | ClickHouse Docs — https://clickhouse.com/docs/optimize/avoid-nullable-columns ↩

  3. postgresql table function | ClickHouse Docs — https://clickhouse.com/docs/sql-reference/table-functions/postgresql ↩ ↩2

  4. Ingesting data from Postgres to ClickHouse (using CDC) | ClickHouse Docs — https://clickhouse.com/docs/integrations/clickpipes/postgres ↩

  5. Introducing pg_clickhouse: A Postgres extension for querying ClickHouse — https://clickhouse.com/blog/introducing-pg_clickhouse ↩

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?