Skip to main content
ALTCHA®

ClickHouse

Sentinel can write request logs to ClickHouse instead of the main database. Use it when you log many requests, or want raw records kept for weeks or months instead of the default 72 hours, without growing the main database.

Prerequisites: ClickHouse 23.3 or later, reachable over its HTTP interface, and an existing database for Sentinel's tables.

When to use it

Main database (default)ClickHouse
Raw records (_search)[request_logs] retention, default 72h[clickhouse] raw_retention, default 90d (1d–5y)
Hourly statistics (_stats)Kept indefinitelyKept 5 years
Load on the main databaseEvery logged request is a rowNone from request logs

Everything else stays the same: the API, the admin UI Dashboard and Logs pages, and the record fields.

The store is chosen once, at startup. With [clickhouse] url set and the clickhouse feature licensed, request logs go to ClickHouse; without the feature, Sentinel logs [clickhouse] is configured but not licensed; request logs go to the database and keeps them in the main database. A newly installed license takes effect at the next restart. Logs already in the other store aren't moved.

Configure it

[clickhouse]
url = "https://clickhouse.example.com:8443"
database = "altcha_sentinel"          # must already exist
username = "sentinel"
password = "${file:/run/secrets/clickhouse_password}"
raw_retention = "30d"                 # raw records for _search; hourly stats are kept 5 years

The url must start with http:// or https://. See [clickhouse] for every key and default.

At startup, Sentinel connects to ClickHouse and creates or checks its tables. If ClickHouse can't be reached, startup fails:

Error: error sending request for url (http://clickhouse:8123/)

Caused by:
    0: client error (Connect)
    …

TLS

With an https:// url, Sentinel verifies the server certificate against the system CA store, the top-level tls_extra_ca_certs, and [clickhouse] tls_ca. For a client certificate, set tls_cert and tls_key together, both PEM:

[clickhouse]
url = "https://clickhouse.example.com:8443"
tls_ca = "${file:/etc/sentinel/clickhouse-ca.pem}"        # private CA of the server
tls_cert = "${file:/etc/sentinel/clickhouse-client.pem}"  # client chain, leaf first
tls_key = "${file:/run/secrets/clickhouse_client_key}"    # PKCS#1, PKCS#8 or SEC1

The tls_* keys need an https:// url; with http://, startup fails with clickhouse TLS options need an https:// url. A value that isn't PEM fails too, for example clickhouse.tls_ca: no PEM certificate found.

Tables and rollups

Sentinel keeps four objects in the configured database:

ObjectHolds
request_logsRaw records, partitioned by month, deleted after raw_retention
request_stats_hourlyHourly aggregates per key, kept 5 years, which _stats reads
request_stats_hourly_mvMaterialized view that rolls each new record into request_stats_hourly
request_stats_stateRollup progress and the applied retention

By default ([clickhouse] auto_migrate = true) Sentinel creates and upgrades them at startup. To have an administrator apply the schema instead, set auto_migrate = false and use altcha-sentinel schema sql --clickhouse; see Schema migrations.

When Sentinel starts on an existing request_logs table, it rolls up the stored records in the background, a day at a time, then applies raw_retention. Until that backfill finishes, _stats also reads raw records for the older hours, so the numbers are complete throughout.

Retention

raw_retention is a TTL on request_logs. A background job, run by one replica every 5 minutes, applies a changed value once the backfill above is done and logs raw request log retention applied. ClickHouse drops whole monthly partitions, so raw records can stay up to about a month longer than raw_retention.

With auto_migrate = false, Sentinel doesn't change the TTL. It logs a warning that raw_retention changed and keeps the old retention until you apply the script of altcha-sentinel schema sql --clickhouse.

Search and statistics

With ClickHouse, POST /v2/request-logs/_search reads request_logs and POST /v2/request-logs/_stats reads request_stats_hourly. The requests, filters, and responses are the same as with the main database; see Request logs & analytics. The differences:

  • _search finds records for raw_retention instead of [request_logs] retention.
  • Statistics are kept 5 years, so _stats covers its full 366-day range limit for a year after you start.
  • A search or statistics query that takes longer than 60 seconds is stopped. If ClickHouse is unavailable, both endpoints return 500 INTERNAL_ERROR.

Writes are best-effort as with the main database: a batch that ClickHouse rejects is dropped and logged as request log flush failed. Dropped records are counted in altcha_sentinel_request_logs_dropped_total{reason="write_failed"}; see Monitoring & metrics.

Health and doctor checks

When ClickHouse is the request-log store, every replica probes it in the background:

  • GET /.health includes a clickhouse check, and returns 503 with "status": "unhealthy" while ClickHouse is unreachable. Without ClickHouse, the check is absent.
  • The doctor storage.clickhouse check reports ClickHouse is reachable., or fails with the error and the hint Check the [clickhouse] url, credentials and database. It's skipped when request logs are in the main database.
  • license.features warns Configured but not licensed: clickhouse (request logs go to the database). when url is set without the license feature.

Run the checks from the CLI before you start the server:

docker run --rm \
  -v "$PWD/sentinel.toml:/etc/sentinel/sentinel.toml:ro" \
  -v "$PWD/secrets:/run/secrets:ro" \
  ghcr.io/altcha-org/sentinel:2.0.0-beta.1 --config /etc/sentinel/sentinel.toml doctor
  FAIL  storage.clickhouse    ClickHouse is unreachable: error sending request for url (http://clickhouse:8123/): client error (Connect): …
                              hint: Check the [clickhouse] url, credentials and database.

See the CLI reference for the doctor options.

Request logs from v1

v2 writes to a new request_logs table and doesn't read the v1 logs table; to keep v1's ClickHouse logs, copy them inside ClickHouse as described in Migrating data.

Start typing to search...

↑ ↓ Navigate ↵ Select