large_serilog_3.png

medium_serilog_3.png

small_serilog_3.png

thumbnail_serilog_3.png

serilog_3.png

large_serilog_2.png

medium_serilog_2.png

small_serilog_2.png

thumbnail_serilog_2.png

serilog_2.png

Our ClickStack instance is pre-configured with a data source pointing at our `logs.app_logs` table and a pre-built dashboard that lights up as soon as the demo generates traffic.

![](/uploads/serilog_4_4fa0e50c00.png)

The dashboard includes five tiles that demonstrate what you get out of the box with structured log data in ClickHouse:

* **Error Rate** — A stacked bar chart showing `countIf(level='Error') / count()` per time bucket, giving you an at-a-glance view of error spikes.
* **Log Volume by Level** — Total log count grouped by severity level. Useful for spotting shifts in logging patterns (e.g., a sudden flood of warnings).
* **Response Time (p50 / p95 / p99)** — Quantile line charts over `properties.Elapsed` (the millisecond duration captured by Serilog's request logging middleware). This is possible because the sink writes `Elapsed` as a typed column, not buried in a JSON blob.
* **Requests by Endpoint** — Log count grouped by `RequestPath`, so you can see which endpoints are getting the most traffic.
* **Warnings and Errors** — A searchable log table filtered to `level:"Warning" OR level:"Error"`, showing timestamp, level, and message.

<h3 id="sql-queries">SQL queries</h3>

Here are some queries to try once you've generated traffic:

**Error rate per minute:**

<pre><code type='click-ui' language='sql'>
SELECT
    toStartOfMinute(timestamp) AS minute,
    countIf(level = 'Error') AS errors,
    count() AS total,
    round(errors / total * 100, 2) AS error_rate_pct
FROM logs.app_logs
WHERE timestamp > now() - INTERVAL 1 HOUR
GROUP BY minute
ORDER BY minute;
</code></pre>

**Slow request detection:**

<pre><code type='click-ui' language='sql'>
SELECT timestamp, message, StatusCode, properties.Elapsed
FROM logs.app_logs
WHERE properties.Elapsed > 1000
ORDER BY properties.Elapsed DESC
LIMIT 10;
</code></pre>

And because the data is just SQL tables, you can `JOIN` log data with other ClickHouse tables, build materialized views for real-time aggregations, or export results to any tool that speaks SQL.

<h2 id="production-considerations">Production considerations</h2>

<h3 id="batching">Batching</h3>

The sink batches log events before writing to ClickHouse. In ClickHouse, each insert creates a new [table part](https://clickhouse.com/docs/parts) (an immutable data segment that ClickHouse later merges in the background), and it's recommended to insert large batches of data at once instead of doing small, frequent inserts. If small inserts are necessary, you should enable [async inserts](https://clickhouse.com/docs/optimize/asynchronous-inserts).

The key parameters:

* **`batchSizeLimit`** — Maximum events per batch (default: 10,000). Increase for high-throughput services.
* **`flushInterval`** — Time between flushes (default: 5 seconds). Lower for near-real-time visibility.
* **`queueLimit`** — Maximum events buffered in memory (default: 100,000). Back-pressure when ClickHouse is slow.

<h3 id="table-engines-partitioning-indexes">Table engines, partitioning, indexes</h3>

ClickHouse gives you a lot of control over how your data is stored and queried. For most log workloads, the default `MergeTree` engine with time-based partitioning is a solid starting point:

<pre><code type='click-ui' language='sql'>
ENGINE = MergeTree
PARTITION BY toMonday(timestamp)
ORDER BY (timestamp);
</code></pre>

This keeps data organized by time, which aligns well with typical log queries and retention policies.

You can further optimize performance by adding secondary indexes on frequently filtered columns like `level` or `CorrelationId`, allowing ClickHouse to skip large portions of data during queries. You also get per-column compression controls, allowing you to configure delta encoding and compression algorithms.

For text search, ClickHouse supports both lightweight bloom filter indexes and full-text inverted indexes. Full-text indexes deliver roughly 10x faster queries than bloom filters in real-world benchmarks, turning multi-second full-scan queries into sub-second lookups. The cost is a significantly larger index on disk. But for most deployments, that's a good trade-off: as noted in the [text index GA blog](https://clickhouse.com/blog/full-text-search-ga-release), in ClickHouse Cloud, the cost of storage is often a small fraction of overall infrastructure spend.

If you want to go deeper on tuning storage layout, partitioning strategies, and index types, check out the [ClickStack Performance Tuning guide](https://clickhouse.com/docs/use-cases/observability/clickstack/performance_tuning).

<h3 id="log-retention-with-ttl">Log retention with TTL</h3>

ClickHouse supports automatic data expiration:

<pre><code type='click-ui' language='sql'>
ALTER TABLE logs.app_logs
MODIFY TTL timestamp + INTERVAL 30 DAY
SETTINGS ttl_only_drop_parts = 1;
</code></pre>

After 30 days, old partitions are automatically dropped. The `ttl_only_drop_parts` setting ensures ClickHouse drops entire data parts rather than rewriting them to remove expired rows, which is much more efficient for log workloads where all rows in a partition expire together. No cron jobs, no manual cleanup.

<h3 id="failure-handling">Failure handling</h3>

**On the server side**, [**ClickHouse Cloud**](https://clickhouse.com/cloud) handles the heavy lifting: automatic replication, failover, scaling, and backups. Your sink points at a connection string and you don't manage infrastructure.

**On the client side**, even with a perfectly healthy ClickHouse cluster, network blips happen: a DNS hiccup, or a brief connectivity drop between your service and the database. The sink handles this by buffering log events in memory and retrying on the next flush cycle. The maximum queue size, flush interval, and batch size are all configurable. For most services, the defaults give you a reasonable window.

Note that this is an in-memory buffer, not a durable queue. If the service itself restarts while the buffer is full, those events are gone. If you need guaranteed delivery, you'll want to add a fallback.

To know when any of this is happening, wire up the sink's callbacks to your alerting system. `onBatchFailed` is the important one: if log writes are failing, you want to know.

```csharp
.WriteTo.ClickHouse(
    connectionString: "...",
    tableName: "app_logs",
    onBatchWritten: (count, elapsed) =>
        Console.WriteLine($"Wrote {count} events in {elapsed.TotalMilliseconds}ms"),
    onBatchFailed: (exception, count) =>
        Console.Error.WriteLine($"Failed to write {count} events: {exception.Message}"));
```

<h3 id="real-time-alerting-with-materialized-views">Real-time alerting with materialized views</h3>

ClickHouse [materialized views](https://clickhouse.com/docs/use-cases/observability/clickstack/materialized_views) transform data on insert: you can aggregate error rates in real-time without any external pipeline. For example, to track error counts per minute:

<pre><code type='click-ui' language='sql'>
CREATE MATERIALIZED VIEW logs.error_counts_per_minute
ENGINE = SummingMergeTree
ORDER BY minute
AS
SELECT
    toStartOfMinute(timestamp) AS minute,
    level,
    count() AS count
FROM logs.app_logs
WHERE level IN ('Error', 'Fatal')
GROUP BY minute, level;
</code></pre>

Every time a batch of logs is inserted, ClickHouse incrementally updates the aggregate. You can then query `error_counts_per_minute` for a real-time error dashboard or alert when counts spike. These views can then be used inside ClickStack, which has [built-in support for alerting](https://clickhouse.com/docs/use-cases/observability/clickstack/alerts).

<h2 id="resources">Resources</h2>

* [Demo source code on GitHub](https://github.com/ClickHouse/clickhouse-serilog-demo/tree/main)
* [Serilog.Sinks.ClickHouse on GitHub](https://github.com/ClickHouse/Serilog.Sinks.ClickHouse)
* [ClickHouse Cloud](https://clickhouse.com/cloud) — Managed ClickHouse with a free tier
* [Serilog Documentation](https://serilog.net/)
* [ClickStack Performance Tuning](https://clickhouse.com/docs/use-cases/observability/clickstack/performance_tuning) — Optimizations for production observability workloads
* [Full-Text Search in ClickHouse — Now GA](https://clickhouse.com/blog/full-text-search-ga-release) — Inverted indexes for sub-second log search
* [How Trip.com Built a 50PB Logging Solution with ClickHouse](https://clickhouse.com/blog/how-trip.com-migrated-from-elasticsearch-and-built-a-50pb-logging-solution-with-clickhouse) — Migrating from Elasticsearch, 4x data capacity on same hardware
* [Building a Logging Platform with ClickHouse and Saving Millions over Datadog](https://clickhouse.com/blog/building-a-logging-platform-with-clickhouse-and-saving-millions-over-datadog) — 200x cost reduction vs SaaS, 17x compression ratio

<h2 id="conclusion">Conclusion</h2>

We've built a .NET service that produces structured log events via Serilog, writes them directly to ClickHouse, and lets us query our logs using SQL.

The key takeaways:

* **Drop-in for Serilog users**: If your .NET app already uses Serilog, the ClickHouse sink is one `WriteTo.ClickHouse()` call away.
* **Cost and performance**: ClickHouse handles log workloads at a fraction of the cost of traditional logging platforms, with 10–20x compression and sub-second analytical queries over billions of rows.
* **Full schema control**: You decide how logs are stored. Typed columns, secondary indexes, partitioning, and compression codecs, all through the fluent schema builder in C#.
* **Full-text search**: ClickHouse's now-GA inverted indexes bring sub-second log search to your existing tables — no separate search engine required.
* **No frontend lock-in**: Your logs are standard ClickHouse tables. Query them with SQL in the Play UI, connect [Grafana](https://clickhouse.com/docs/integrations/grafana) (via the ClickHouse data source plugin), [Metabase](https://www.metabase.com/data_sources/clickhouse), [ClickStack](https://clickhouse.com/docs/en/observability/clickstack), or any other tool that speaks SQL.

The entire demo runs with a single `docker compose up`. Clone the repo, generate some traffic, and see what structured logs look like in ClickHouse. When you're ready to try it with a managed deployment, get started with ClickHouse Cloud.

Interested in seeing how ClickHouse works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.

Get started today

Learn how to send structured .NET logs directly to ClickHouse using Serilog — with full schema control, full-text search, and SQL   queries over your log data.

Alex Soffronow Pagonidis

Blog posts by Alex Soffronow Pagonidis

Structured Logging in .NET with Serilog and ClickHouse