Component reference · analytics/

The Event Store

Every pageview Plausible ever records lands in ClickHouse, not Postgres. This is how that column store is wired into the app: four Ecto repos aimed at one cluster, a write buffer that trades durability for throughput, and a second migration system for the schema changes Ecto's DSL can't express on a table with a billion rows.

18
tables & dictionaries
4
Ecto repos, one cluster
54
schema migrations
6
staged data‑migration jobs

Revision 2. The first version of this page shipped with every schema fact sourced from a direct file read, because EKOS's own ClickHouse SQL parser couldn't compile structure.sql at all. That parser gap is now fixed — see the note at the end for what changed, and one new caveat the fix itself surfaced.

Orientation

Two databases, one app

Plausible runs Postgres and ClickHouse side by side for the same reason most analytics products eventually do: the data an operator edits and the data a browser fires belong to two different engines.

Postgres — Plausible.Repo

  • Mutable, low-volume, foreign-keyed — the shape Postgres is built for
  • sites, users, api_keys, goals
  • funnels / funnel_steps, site_imports, site_memberships
  • subscriptions, plans, enterprise_plans — billing
  • shield_rules_*, shared_links, google_auth, oban_jobs
  • ~40 tables, priv/repo/structure.sql (2,738 lines)

ClickHouse — plausible_events_db

  • Append-mostly, high-volume, columnar — every raw pageview and custom event
  • events_v2, sessions_v2 — the live firehose
  • imported_* (10 tables) — backfilled history from GA4 / UA / CSV
  • ingest_counters, location_data + dictionary — support tables
  • No foreign keys; identity is site_id, a plain integer copied from Postgres
  • 18 objects, priv/ingest_repo/structure.sql (366 lines)
Orientation

Connection layer

One ClickHouse cluster, reached through ecto_ch 0.8.6 — which wraps ch 0.7.1, a pure-Elixir client speaking ClickHouse's native RowBinary wire format over plain HTTP. No C driver, no NIF. But instead of one shared repo, the app splits ClickHouse across four Ecto repos, each with a pool and settings tuned to one job.

RepoRolePool / key settings
ClickhouseRepo Dashboard reads. read_only: true; injects a log_comment JSON tag (caller + OTel trace id) into every query for log correlation, and offers parallel_tasks/2 for fan-out breakdown queries. queue_target 500ms · timeout 15s · max_execution_time=20 · concurrency 3
IngestRepo Target of the event/session write buffers (below). Exposes clustered_table?/1 / replica_count/1, read from system.replicas, so migrations know whether to add ON CLUSTER. flush interval & buffer size configurable
AsyncInsertRepo A second connection to the same cluster for fire-and-forget writes. async_insert=1, wait_for_async_insert=0, pool_size 1
DeletionRepo Site deletions and import-cleanup mutations — kept off the read/write hot paths. pool_size 2

Migrations shared across repos lean on Plausible.MigrationUtils (lib/plausible/migration_utils.ex) for on_cluster_statement/1, dictionary connection params, and an enterprise_edition?/0 check that lets a single migration file branch between CE and EE schemas rather than forking into two.

Schema

Table reference

Read from priv/ingest_repo/structure.sql directly — EKOS's generic SQL‑DDL recovery pass couldn't parse this file (see the note at the end of this page), so every engine, key, and column claim below is a direct read, not a compiled fact.

Live firehose

TableEnginePartition / orderNotes
events_v2 MergeTree PART toYYYYMM(timestamp)
ORDER (site_id, toDate(timestamp), name, user_id, timestamp)
SAMPLE user_id		 One row per pageview/custom event. meta.key/meta.value as parallel Array(String) for custom-event props; revenue_reporting_amount/revenue_source_amount as Nullable(Decimal(18,3)). 9 ALIAS columns (country/region/city names via dictGet).
sessions_v2 VersionedCollapsingMergeTree(sign, events) PART toYYYYMM(start)
ORDER (site_id, toDate(start), user_id, session_id)
SAMPLE user_id		 A session is updated by inserting a −1 row for the old state and a +1 row for the new; ClickHouse collapses the pair at merge time — no application-side UPDATE. minmax index on timestamp. Same alias-column pattern as events_v2.

Support tables

TableEngineKeyNotes
ingest_counters SummingMergeTree(value) ORDER (domain, toDate(event_timebucket), metric, toStartOfMinute(...)) Per-minute rollup counters, site_id Nullable(UInt64). A later migration adds a materialized projection for site-traffic queries — which is why its cleanup path (Operational tooling) needs a heavier delete than every other table.
location_data MergeTree ORDER (type, id) Small reference table (granularity 128). Its COMMENT '2024-07-09' doubles as a version marker — the LocationsSync data migration only re-runs when that date is stale.
location_data_dict DICTIONARY PK (type, id) Backs every *_name ALIAS column above via dictGet. LIFETIME(MIN 0 MAX 0) — never auto-refreshes; only LocationsSync repopulates it. LAYOUT(COMPLEX_KEY_CACHE(500,000)).
schema_migrations TinyLog — Ecto's migration-version ledger for IngestRepo, the ClickHouse equivalent of Postgres's own schema_migrations.

Imported history 10 tables

One table per dimension — imported_visitors, imported_sources, imported_pages, imported_entry_pages, imported_exit_pages, imported_locations, imported_devices, imported_browsers, imported_operating_systems, imported_custom_events — all plain MergeTree, unpartitioned, ordered (site_id, date, …dimension), and all set replicated_deduplication_window = 0 since bulk backfills reinsert the same rows on retry by design. These are the landing zone for GA4 / Universal Analytics / CSV imports — see Imported data.

Data flow

Write path

A pageview never hits ClickHouse directly. It's routed, buffered, and flushed in batches — the design trades a few seconds of durability for the throughput ClickHouse actually wants (few, large inserts, not one per request).

WRITE PATH READ PATH Pageview / custom event Persistor Embedded / Remote WriteBuffer flush @ byte-size cap or every 5s tick events_v2 sessions_v2 MergeTree family INSERT … FORMAT RowBinary Dashboard query Stats query builder Query → Optimizer → Runner ClickhouseRepo read_only pool max_execution_time 20s SELECT + AsyncInsertRepo (fire-and-forget) + DeletionRepo (site cleanup)
Writes and reads reach the same tables through two different Ecto repos and pools — a bursty, buffered write path tuned for throughput, and a bounded, read-only path tuned to fail fast rather than hold a connection open.

Every event first passes through Plausible.Ingestion.Persistor (lib/plausible/ingestion/persistor.ex), which routes it to Embedded or Remote based on a backend_percent_enabled rollout percentage, hashed per user_id with :erlang.phash2 — an operator can shift ingestion to a remote persistor service gradually rather than all at once. Embedded resolves the session through Session.CacheStore, merges it onto the event, and hands both to a WriteBuffer GenServer per stream (events, sessions).

The buffer (lib/plausible/ingestion/write_buffer.ex) accumulates RowBinary iodata and flushes on whichever comes first: the buffered byte-size crossing CLICKHOUSE_MAX_BUFFER_SIZE_BYTES (default 100,000 bytes), or a periodic tick at CLICKHOUSE_FLUSH_INTERVAL_MS (default 5,000ms). A flush is one IngestRepo.query! using FORMAT RowBinaryWithNamesAndTypes, with the insert header built once at compile time from the Ecto schema's field types. The process traps exit signals so a supervised shutdown still flushes whatever's buffered rather than dropping it.

Data flow

Read path

Dashboard queries don't hand-write SQL against the tables above — they go through a 24-file query-builder pipeline in lib/plausible/stats/.

An API request is parsed into an internal Query struct (query_builder.ex, api_query_parser.ex), passed through query_optimizer.ex, then executed by query_runner.ex into a query_result.ex. table_decider.ex picks events_v2, sessions_v2, or the matching imported_* table per requested metric; compare.ex handles period-over-period comparisons. A separate, lower-level module, Plausible.Stats.Clickhouse (lib/plausible/stats/clickhouse.ex), hand-writes Ecto.Querys with fragment/1 for ClickHouse-only functions like toDate and countIf, and uses ClickhouseRepo.parallel_tasks/2 to fan a large site-ID list out into concurrent chunked queries.

Data flow

Imported data

History that predates Plausible tracking gets backfilled into the imported_* tables through a pluggable importer framework (lib/plausible/imported/, 18 files).

SourceFileNotes
Google Analytics 4google_analytics4.exPulls via the GA4 Data API
Universal Analyticsuniversal_analytics.exLegacy GA, sunset upstream but still importable
CSVcsv_importer.exUser-supplied export files

Ten per-dimension writers (browser.ex, device.ex, page.ex, location.ex …) map each source's rows onto the matching imported_* table, batched through a buffer.ex writer analogous to the live write buffer. The job itself is tracked in Postgres, not ClickHouse — site_import.ex backs the site_imports table on the app-database side of the split.

Change management

Data migrations

Plain Ecto migrations (priv/ingest_repo/migrations/*.exs, 54 files) cover anything expressible as create table / alter table or a single execute. Anything bigger — a full table rewrite, a staged backfill that has to run for hours against production data — goes through a second framework: Plausible.DataMigration (lib/plausible/data_migration.ex).

Its use macro wires unwrap/2, which loads a .sql.eex template from priv/data_migrations/<Name>/sql/ and EEx-renders it with assigns (cluster-conditional syntax, table names) — the reason these files carry the .eex extension and sit outside EKOS's plain-.sql recovery scan. run_sql_confirm/3 prompts for interactive confirmation before executing, because these are meant to be run by hand from a remote console against production, not applied blindly like a schema migration.

VersionedSessionszero-downtime engine change

Moved sessions_v2 from a plain CollapsingMergeTree to today's VersionedCollapsingMergeTree: create a shadow _tmp_versioned table, dual-write, then EXCHANGE the two tables atomically — the only way to change a MergeTree engine on a live table without downtime.

NumericIDsnon-blocking table rewrite

Converted string domain keys to numeric site_id via a create-tmp → insert-into-tmp → ATTACH → DROP sequence, avoiding a blocking in-place rewrite of the whole table.

AcquisitionChannelstaged column backfill

Adds a materialized acquisition_channel column to both events_v2 and sessions_v2 as three independent phases — add_column, update_column, backfill — so the schema change and the historical recompute can be run and monitored separately.

LocationsSyncdictionary refresh

Truncates and repopulates location_data, rebuilds location_data_dict, and recreates the ALIAS columns on events_v2 / sessions_v2 / imported_locations. Idempotent by design: it checks location_data's own COMMENT against the code's current version before doing anything.

PopulateEventSessionColumnslong-running background job

Designed to run for hours or days as a background job with its own report_progress/kill entry points, not inline in a migration step.

Change management

Operational tooling

ComponentPurpose
Workers.ClickhouseCleanSites Oban worker, queue :clickhouse_clean_sites. Async site deletion: lightweight DELETE … IN PARTITION for the partitioned events_v2/sessions_v2; plain DELETE for the ten unpartitioned imported_* tables; a heavier ALTER TABLE … DELETE mutation specifically for ingest_counters, because ClickHouse refuses lightweight deletes on a table carrying a projection.
Mix.Tasks.CleanClickhouse Test-suite helper that truncates every ClickHouse table except migrations and dictionaries; wired into the test alias in mix.exs.
ClickhouseLocationData Thin Ecto schema over location_data, backing LocationsSync.
debug_controller + clickhouse.html.heex Admin-only debug page surfacing live ClickHouse query/health info for a given site.
Change management

Configuration

All four repos share one connection string and diverge in pool shape and query behavior (config/runtime.exs).

CLICKHOUSE_DATABASE_URL
Shared connection string for all four repos.
CLICKHOUSE_FLUSH_INTERVAL_MS
Write-buffer tick interval — default 5,000ms.
CLICKHOUSE_MAX_BUFFER_SIZE_BYTES
Write-buffer byte-size flush trigger — default 100,000 bytes (CLICKHOUSE_MAX_BUFFER_SIZE is a deprecated alias).
CLICKHOUSE_DEFAULT_STORAGE_POLICY
Optional ClickHouse storage-policy override for IngestRepo tables.
ClickhouseRepo settings
readonly=1, max_execution_time=20, join_algorithm="direct,parallel_hash,hash", cancel_http_readonly_queries_on_client_close=1.
Change management

Schema evolution

54 migrations span 2020 to 2026. The checked-in structure.sql is a periodically-regenerated snapshot rather than a per-migration artifact — it has only two commits in its own git history, and its 32 pre-seeded schema_migrations rows undercount the 54 files that actually exist today.

2020-09-15
create_events_and_sessions — the original tables, plain MergeTree, keyed on domain (a string), not yet site_id.
2021-03-23
Retrofits SAMPLE BY user_id onto an already-live table via ALTER TABLE … MODIFY SAMPLE BY.
2024-02-22
Triggers the VersionedSessions data migration — the engine change described above.
2024-11-20
Adds the ingest_counters projection, version-gated for ClickHouse 24.8's deduplicate_merge_projection_mode setting — the migration rescues the exception and soft-fails on older ClickHouse.
2025-02-18
Creates a ClickHouse user-defined function (CREATE OR REPLACE FUNCTION) for custom-event array lookups.
2026-06-01
remap_sources_v3 — a large hardcoded referrer-remapping table, including AI-assistant traffic sources (ChatGPT, Claude, DeepSeek, Grok, Copilot).
2026-06-25
Most recent migration: renames recovery_id → replay_session_id, gated on Plausible.MigrationUtils.enterprise_edition?/0 — CE/EE divergence handled inline, not as separate migration files.
Revision 2 — what changed since the first version of this page. The first version flagged that EKOS's own ClickHouse SQL-DDL recovery pass failed whole-file on priv/ingest_repo/structure.sql: the underlying sqlparser ClickHouse dialect didn't parse CODEC(…), so every schema fact on this page was sourced from a direct file read instead of compiled knowledge. That gap is now closed, in two RFCs: RFC 0057 fixed CODEC(…); RFC 0058 fixed four more clauses the same file needed — table-level INDEX … TYPE … GRANULARITY, PARTITION BY, SAMPLE BY, SETTINGS, and whole CREATE DICTIONARY statements. Re-running the pipeline from a clean cache now compiles structure.sql into 15 real, evidence-backed Table objects with zero parse warnings — every fact in the Table reference section above is corroborated by the ledger today, not only by the source read that originally produced it.

One new caveat the fix itself surfaced. A separate, pre-existing EKOS subsystem — identity resolution (crates/identity), untouched by either RFC — auto-merges 6 of those 15 real tables into a single identity at confidence 0.93: imported_visitors, imported_operating_systems, imported_exit_pages, imported_entry_pages, imported_devices, and imported_browsers share both a name prefix and the same 8-column base schema, which is enough to clear the default 0.85 merge threshold. Querying EKOS's ledger for, say, imported_browsers's columns today returns imported_visitors's columns instead. The Imported history row above is unaffected — it was authored from a direct source read, not the ledger — but anyone querying this schema through EKOS directly should know the five merged-away tables aren't separately retrievable yet. Tracked, not yet fixed: a change here affects identity-resolution scoring for every same-kind object across EKOS's whole compiled estate, not just this repo, so it wasn't made unilaterally.