Platforms
ClickHouse
Keep the standalone server's visits and events in ClickHouse for fast filtered reports on big sites, and move an existing install across with no downtime.
The standalone server can keep its visits and events in ClickHouse, a database built for reading millions of rows at once. Everything else stays where it is, in SQLite, Postgres, or MySQL: sites, goals, funnels, links, accounts, settings, and the rest. Only the two biggest tables move.
When it is worth it
SQLite, Postgres, and MySQL answer an unfiltered dashboard from daily rollups, so they stay quick at any size. A filter has to read the visits it picks, and that is where ClickHouse helps. On a year of a busy site, a dashboard filtered to one page opens in 0.65 seconds, where the others take 9 to 24 seconds. The performance page has the full measurements.
It is worth it past a few million visits a month, or when people filter long ranges often. A small site gains little and has one more database to run.
ClickHouse wants at least 4 GB of memory, and 8 GB for a year of 10 million visits. Runlight needs version 25.8 or newer, self-hosted or on ClickHouse Cloud.
How big it goes
These are measurements on one machine, an Apple M4 Max with 14 cores, with ClickHouse 25.8 in Docker and one Runlight server beside it. Each site had a year of synthetic traffic at about four events a visit. Each time is every request the dashboard makes when it opens, sent together, with nothing else writing. Your hardware and traffic will give other numbers, so treat these as a starting point and measure your own.
| Visits a year | ClickHouse memory | Last 7 days | Last 30 days | Last 12 months | 12 months, filtered |
|---|---|---|---|---|---|
| 10 million | 8 GB | 0.14 s | 0.4 s | 2.8 s | 2.2 to 2.7 s |
| 180 million | 8 GB | 0.9 s | 3.7 s | 38 to 40 s | 30 to 55 s |
180 million visits a year is about 10 million unique visitors a month. At that size, 14 GB of memory was no quicker than 8 GB, since ClickHouse was busy on every core rather than short of memory, but 8 GB is close to the edge: in two runs of a dozen, with other work running beside ClickHouse, a few of the 12-month boxes ran out of memory. Give it 14 GB or more for room to spare. With 4 GB, a year of 10 million visits opened over 7 and 30 days, but the channel, country, device, and campaign boxes failed over 12 months.
Each event in ClickHouse carries its visit’s source, campaign, place, and device, so a filter on one of those reads the events alone. A filter on a page reads every pageview in the range to find the visits that saw it, which makes it the slowest view over a year of the biggest site. Short ranges are quick, filtered or not.
Disk
ClickHouse used about 0.4 GB for every million visits with their events, compressed: 3.7 GB for 10 million and 75 GB for 180 million. Visits with more events a visit take more.
Hits
One server recorded 600 hits a second and answered each within 0.3 seconds, with ClickHouse busy on about 9 cores. At 800 a second, ClickHouse was busy on all 14, and hits waited and timed out. A year of 180 million visits at four events each averages about 25 hits a second, so one server has room for peaks many times the average. Run more copies of the server behind a load balancer for more.
When ClickHouse is down, busy, or restarting, the server keeps each hit in SQLite, Postgres, or MySQL, with no address and no user agent, and sends them in the order they came once ClickHouse answers again. While any wait, new hits queue behind them, so a visitor’s hits stay in order. A hit waits a day at most, and at most two million wait, about a gigabyte. Past either, hits are lost and logged as "could not record an event", so watch the log and ClickHouse’s own health.
Set it up
Set CLICKHOUSE_URL to the ClickHouse to use, with its user, password, and a database name, and start the server.
bash
CLICKHOUSE_URL=https://runlight:password@clickhouse.example.com:8443/runlight npx @runlight/serverThe server makes the database if it is not there yet, creates its tables, and logs where the visits go. If ClickHouse cannot be reached, the server stops with the reason rather than dropping visits.
Use https:// when ClickHouse is on another machine, since the password goes with every request. Inside one Docker network, as below, http:// is fine. The server warns when it starts if the address is http:// and could be across the internet.
The ClickHouse user needs these grants on its database. It needs CREATE DATABASE as well only if the database is not there yet.
sql
GRANT CREATE TABLE, CREATE VIEW, INSERT, SELECT, ALTER DELETE, ALTER UPDATE, OPTIMIZE, TRUNCATE ON runlight.* TO runlight;
GRANT SELECT ON system.mutations TO runlight;The second line is optional. With it, a big delete (a site, or old visits past their retention) runs in ClickHouse’s background while the server waits for it, however long it takes. Without it, the server waits on the request, which gives up after five minutes.
With Docker Compose
This runs Runlight and ClickHouse side by side, each with its data in a volume.
compose.yaml
services:
runlight:
image: ghcr.io/runlightsh/runlight
ports: ["127.0.0.1:3000:3000"]
volumes: ["runlight:/data"]
environment:
CLICKHOUSE_URL: http://runlight:change-me@clickhouse:8123/runlight
depends_on:
clickhouse:
condition: service_healthy
clickhouse:
image: clickhouse/clickhouse-server:25.8
environment:
CLICKHOUSE_USER: runlight
CLICKHOUSE_PASSWORD: change-me
ulimits:
nofile: { soft: 262144, hard: 262144 }
volumes: ["clickhouse:/var/lib/clickhouse"]
healthcheck:
test: ["CMD", "clickhouse-client", "--user", "runlight", "--password", "change-me", "-q", "SELECT 1"]
interval: 5s
volumes:
runlight:
clickhouse:ClickHouse is reached only from inside the Compose network here, so it needs no port of its own.
With ClickHouse Cloud
Make a service, then copy its HTTPS address and password from Connect. The address goes in CLICKHOUSE_URL with the user, the password, and a database name added.
bash
CLICKHOUSE_URL=https://default:password@abc123.eu-west-1.aws.clickhouse.cloud:8443/runlightMove an existing install
Add CLICKHOUSE_URL to a server that already has visits and restart it. On that first start it copies the visits of the last two days into ClickHouse before it counts anything, so a visit going on at the time carries on. That takes a few seconds on a busy site. From then on every hit goes to ClickHouse.
The rest is copied in the background, newest first, a day at a time, so the reports people look at most are right almost at once. A million visits take under a minute on a laptop. Until it is done, the dashboard says how far back the reports reach so far, and imports of visits and links are turned away, since an import then could count the same visits twice.
The copy records how far it got after each day, so a restart or a dropped connection carries on from there without counting a visit twice. When several copies of the server share one database, one of them does the copying and the others carry on counting. If that one stops, another takes over within two minutes.
To have it all done before the server takes any visits, stop the server, set CLICKHOUSE_URL, and run the copy yourself. Then start the server.
bash
npx @runlight/server clickhouse copyIn Docker, run the commands on this page inside the container, as for the password command.
bash
docker exec runlight node node_modules/.bin/runlight.sh clickhouse statusSee how far it has got
bash
npx @runlight/server clickhouse statusIt says how many visits and events ClickHouse holds, how far back the copy has reached, and whether the old tables still hold visits.
Empty the old tables
Once the copy is done, the old visits stay in SQLite, Postgres, or MySQL, and nothing reads them. Empty them when you are happy with ClickHouse.
bash
npx @runlight/server clickhouse drop-oldIt says what it will delete and asks you to type yes. Add --yes to skip the question in a script. It refuses while the copy is still going.
It empties only the visits that started before ClickHouse took over, which are all in ClickHouse. A server still running without CLICKHOUSE_URL after the switch, such as one not yet restarted, writes its visits to the old tables, where ClickHouse never sees them. status counts those, and drop-old leaves them in place.
Go back
To stop using ClickHouse, stop every server and copy everything back.
bash
npx @runlight/server clickhouse copy --backThis replaces the visits and events in SQLite, Postgres, or MySQL with everything ClickHouse holds, including the visits since the switch, then empties ClickHouse. It asks first, and refuses while a server is still taking hits. A server that stopped without shutting down counts as gone a minute and a half after it was last heard from. Then remove CLICKHOUSE_URL and start the server, which builds the daily rollups again as it runs. Switching on ClickHouse again later copies everything afresh.
If it stops part way, run it again. Once the old database holds everything, it records that before it empties ClickHouse, so a second run finishes the job rather than copying from an empty ClickHouse. Until it is finished, a server will not start with CLICKHOUSE_URL.
In your own app
A Node app that runs Runlight as a library can do the same. Wrap the store it already has.
ts
import { runlight } from "@runlight/sdk";
import { postgres } from "@runlight/sdk/postgres";
import { clickhouse } from "@runlight/sdk/clickhouse";
export const rl = runlight({
store: clickhouse({ url: process.env.CLICKHOUSE_URL!, store: postgres({ url: process.env.DATABASE_URL! }) }),
});The copy runs in the app’s process, as it does in the server. clickhouse() takes these options beside url and store.
| Option | Default | What it does |
|---|---|---|
maxQueries |
2 | The most report queries sent to ClickHouse at once; the rest wait their turn. A hit’s own reads and writes have lanes of their own. |
queryTimeout |
120000 | The longest one report query may run, in milliseconds. 0 means no limit. |
copyInBackground |
true | False leaves the copy for store.copy(), run from a script of your own. The last two days are copied before the store is ready either way. |
What changes
Reports read the visits themselves in ClickHouse, so there are no daily rollups to build. Unfiltered views over a long range take a little longer than with rollups, about 0.4 seconds for a year of a million visits, and filtered ones are far quicker.
Visitors over several days are counted from the visits, where the rollups add up each day’s. The two differ only when a visit runs past midnight and the same person comes back the next day, so ClickHouse can show a few fewer.
Visitors are counted exactly up to about 4,000 in any one number, and estimated beyond that, to within about 1%. An exact count of a year’s visitors for each page of a big site needs several gigabytes of memory, and the estimate needs a fraction of that. An estimate is never shown above the visits, pageviews, or conversions it counts the visitors of.
Nothing new is stored. The columns are the ones the other databases keep, with no IP address and no full user agent, and the daily salts stay in SQLite, Postgres, or MySQL. A deleted visit is hidden from reports at once, and ClickHouse rewrites the parts of the table that held it within a day, so it leaves the disk too.