PostHog to ClickHouse: How to Move Your Data

Moving PostHog into ClickHouse has a pleasing symmetry to it, because PostHog is itself built on ClickHouse. You are not fighting a mismatch between an event store and an analytical database, you are moving event data into the kind of engine it was already living in, and then joining it to everything PostHog has never heard of.

The friction is on the way out rather than the way in. PostHog's REST API applies rate limits across your whole organisation, and only one of the connector's streams syncs incrementally. This guide covers the managed path with Airbyte and the two settings that decide whether a backfill finishes.

PostHog to ClickHouse at a glance:

Why move data from PostHog to ClickHouse?

Two situations account for most of these pipelines.

The first is joining. PostHog answers questions about product usage extremely well and cannot answer anything involving revenue, support load or contract value, because it does not hold that data. Your own ClickHouse instance can hold all of it, and event data queried alongside commercial data is where the useful answers are.

The second is control. Your own ClickHouse means your own retention, your own table engines, your own materialised views and no query limits other than the hardware you chose. If you only need product questions answered, PostHog already does that and this pipeline earns you nothing.

What do you need before you start?

Short list, with one item that is a conversation rather than a credential:

A PostHog personal API key and a start date. If you self-host PostHog, change the base URL from the default to your instance. Self-hosted instances are not subject to PostHog Cloud's rate limits, though your own infrastructure becomes the constraint instead. The PostHog source documentation lists every field.

An agreed share of the rate limit. PostHog applies its limits across the organisation rather than per key, so your sync competes with every other tool and script on the account. Find out what else talks to PostHog before choosing a schedule.

An honest estimate of event volume. PostHog recommends batch exports for large or regular exports of events. If you are moving hundreds of millions of events, use those. If you are moving a few million plus cohorts, flags and persons, this connector is the right tool.

A ClickHouse instance and credentials. With permission to create tables in the target database. The destination writes directly into typed columns rather than storing everything as JSON in a raw table.

If your ClickHouse instance restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a PostHog to ClickHouse pipeline in Airbyte?

Step 1: Decide which streams you actually need

Only events is incremental. Projects, annotations, cohorts, feature flags, insights and persons are re-read in full on every sync. For the configuration objects that is cheap, since they number in the hundreds. Persons is the exception, and on a consumer product with a large identified user base it is genuinely large and re-read every time.

Step 2: Configure the PostHog source

Click Sources in the left navigation, then New Source, and select PostHog, following adding a source. Supply the API key, start date and base URL. Set events_time_step deliberately: it controls how many days of events each request covers, and it is the difference between a first sync that completes and one that dies partway through. Start small for the backfill and raise it once you reach steady state.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. The destination writes through ClickHouse's native protocol using batch inserts, and uses the ReplacingMergeTree table engine for deduplication, which matters more than it sounds and is covered below.

Step 4: Create the connection and choose a cursor type

Click Connections, then New connection, select your streams and a sync mode for each. Use incremental on events. For deduplication ClickHouse orders duplicates by the cursor column and prefers a timestamp or numeric type, so a string cursor makes it fall back to Airbyte's extraction timestamp, which does not necessarily reflect the natural order of your data. The sync logs warn about this, and it is worth reading them once.

If the sync is slow after tuning events_time_step, look at persons next. Splitting it into its own connection on a slower schedule is a legitimate fix and usually the right one.

Why do duplicates appear and then disappear?

The ClickHouse destination deduplicates using the ReplacingMergeTree table engine, which collapses duplicate rows during background merges. Those merges happen when ClickHouse decides they should, not when your sync finishes.

So a query run shortly after a sync can legitimately return duplicates that the same query returns cleanly an hour later. This is expected behaviour rather than a fault, and it surprises analysts who have not been told. To guarantee deduplicated results at query time, add the FINAL operator.

FINAL costs performance, so the usual pattern is a view using it for correctness-critical queries and direct table access for exploratory work where a handful of duplicate events does not change the answer. Set that up before anyone files a bug about duplicate rows, because someone will.

Why does the rate limit affect your whole organisation?

Because PostHog applies its limits at the organisation level rather than per API key. The analytics endpoints are capped at 240 requests per minute and 1,200 per hour, and every tool, script and personal key on the account draws from the same budget.

The practical effect is that a colleague running a script against the persons API can throttle your sync, and your backfill can throttle them. That makes the schedule an organisational decision rather than a technical one, and it makes a large initial backfill something to plan rather than start on a whim.

Many teams end up running both approaches: PostHog batch exports for the event firehose, and this connector for cohorts, feature flags, insights and persons. That is a sensible split rather than a compromise, because the two mechanisms are good at different things.

Frequently asked questions

Why does my ClickHouse table contain duplicate events?

ReplacingMergeTree removes duplicates during background merges rather than immediately. Query with FINAL if you need guaranteed deduplication before those merges run.

Why is only the events stream incremental?

The other streams describe configuration rather than an append-only log, and re-reading them in full is cheap enough that incremental support was never added. Persons is the one to watch on large instances.

Does the PostHog rate limit apply per API key?

No. It is applied across your entire organisation, so other tools and scripts using different personal keys share the same budget as your sync.

My backfill keeps failing partway through. What should I change?

Reduce events_time_step so each request covers fewer days. Smaller slices keep individual requests within what the API returns and let the sync checkpoint more often.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want a ClickHouse view using FINAL for correctness-critical queries, which is a short piece of SQL.

Get your PostHog data into ClickHouse

Tune events_time_step before anything else, use a timestamp cursor so deduplication orders correctly, watch persons on large instances, and teach your analysts what FINAL does before they report duplicate events as a bug.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same PostHog source can feed a warehouse alongside this one. For that pairing, see PostHog to BigQuery, and for another product analytics source landing in ClickHouse, Mixpanel to ClickHouse.

Start syncing now →