ClickHouse for Symfony Developers - SymfonyCon 2025

While most PHP applications rely on traditional row-oriented databases, many modern applications require processing massive amounts of data for analytics, logging, and metrics – areas where conventional databases struggle. This becomes even more apparent when you need real-time performance at scale.

ClickHouse is a powerful column-oriented database that can save you significant time when aggregating data in real-time, all without leaving the comfort of your technology stack.

In this talk we'll explore :
- Why column-oriented databases are game-changers for analytical workloads
- How to integrate ClickHouse with Symfony applications using recommended practices
- Real-world architecture patterns that effectively combine traditional databases with ClickHouse
- How to deploy ClickHouse with just a few lines of code
- Key ClickHouse features for building real-time dashboards
- Concrete aggregation examples with benchmarks comparing ClickHouse to other solutions

We'll also cover advanced features like cold storage, replication, and practical optimization tricks learned from production experience.

Whether you're working with e-commerce applications, logs, user events, IoT data, or business metrics, this talk will equip you with the knowledge to implement ClickHouse as a powerful addition to your PHP development toolkit.

Transcript

1. ClickHouse for Symfony developers SymfonyCon 2025 1
2. ▪ Code is available at https://github.com/romainneutron/clickhouse-symfony-symfonycon2025/ ▪ Slides include links ▪ If you don’t take notes that’s fine, you’ll get slides $ git clone git@github.com:romainneutron/clickhouse-symfony-symfonycon2025.git $ cd clickhouse-symfony-symfonycon2025 $ upsun project:create $ upsun push -y About this Presentation 2
3. Romain Neutron Staff Engineer at Upsun ▪ Former Symfony Core Team ▪ Joined Blackfire in 2014 ▪ Joined Upsun in 2021 following Blackfire acquisition ▪ Observability Expert #0 - Who I am https://github.com/romainneutron 3
4. Focus on what really matters. 👀 Monitoring 🚀 Deploying 🏗 Provisioning 📦 Packaging 🧪 Testing 💻 </> 🔐 Security 📈 Data services 🎮 Operating system 🎼 Container orchestration 🛠 CDN, APIs, Services 💽 Servers 🛜 Networking ♾ CI/CD 🔮 Virtualization 🏃 Runtimes And automate the rest. The code. #0 - Who I am https://upsun.com/ 4
5. #0 - Who I am https://blackfire.io/ 5
6. #0 - Who I am https://upsun.com/ 6
7. ▪ One Observability stack ▪ 25 components in PHP and Golang ▪ 17 geographic regions ▪ ClickHouse is the pivot #0 Who I am Observability at Upsun 7
8. ClickHouse for Symfony developers Realtime SQL-like database 8
9. ◼ Observability presentation ◼ Introduction to Columnar Databases ◼ Use Cases ◼ Clickhouse + Symfony & PHP ◼ ClickHouse on Upsun ◼ Q&A Agenda 9
10. What we do at Observability #1 - Observability Container CPU / RAM / Pressure UI CLI APIs HTTP Traffic Browser Blackfire Logs Observability Pipeline Realtime ~ few sec. 🌡Measures 🤓 Usage 10 ClickHouse
11. How we used to do it #1 - Observability 11
12. How we used to do it #1 - Observability OpenSearch cluster N https://docs.opensearch.org/latest/aggregations/ 12 Node 1 Node 2 Node 3 Map + Reduce
13. How we used to do it, and their limitations #1 - Observability Pre-aggregating data for faster queries on lowest time interval https://docs.opensearch.org/latest/aggregations/ 13
14. #1 - Observability Raw data Datetime Value 2024-02-04T14:01:04.234Z 27 2024-02-04T14:01:07.237Z 28 2024-02-04T14:01:08.514Z 25 2024-02-04T14:01:11.101Z 26 Derived index - 5 sec window aggregations Datetime Average Percentiles 2024-02-04T14:01:00.000Z 27 Binary Histogram 2024-02-04T14:01:05.000Z 26.5 Binary Histogram 2024-02-04T14:01:10.000Z 26 Binary Histogram Derived index - 30 sec window aggregations Datetime Average Percentiles 2024-02-04T14:00:00.000Z 26.5 Binary Histogram Query data with at least 5 sec. points interval Query data with at least 30 sec. points interval https://docs.opensearch.org/latest/aggregations/ 14
15. It did not scale with affordable budget Periodic triggers Unoptimized architecture, limits of ES as a storage for numbers #1 - Observability https://docs.opensearch.org/latest/aggregations/ 15
16. What brought us to ClickHouse Trends and intensive testing #1 - Observability https://trends.google.fr/trends/explore?date=2017-10-20%202025-11-20&geo=FR&q=clickhouse&hl=en 16
17. Integers Ingestion benchmark #1 - Observability $ bin/console benchmark:fill-tables --datetime-from=-1days --quantity=10_000_000 --batch-size=10_000 Total Data Points: 10,000,000 | Batch Size: 10,000 +---------------+---------------+------------+----------------+ | Engine | Duration (ms) | Points/sec | vs Fastest | +---------------+---------------+------------+----------------+ | postgresql | 95,058.47 | 105,198 | 44% | | mariadb | 131,588.98 | 75,994 | 32% | | mysql | 132,054.10 | 75,727 | 32% | | elasticsearch | 513,924.78 | 19,458 | 8% | | clickhouse | 41,739.29 | 239,582 | 100% (fastest) | +---------------+---------------+------------+----------------+ https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 17
18. Integers Ingestion benchmark #1 - Observability $ bin/console benchmark:fill-tables --datetime-from=-1days --quantity=2_000_000 --batch-size=1_000 Total Data Points: 2,000,000 | Batch Size: 1,000 +---------------+---------------+------------+----------------+ | Engine | Duration (ms) | Points/sec | vs Fastest | +---------------+---------------+------------+----------------+ | postgresql | 32,527.73 | 61,486 | 18% | | mariadb | 26,032.18 | 76,828 | 23% | | mysql | 27,772.76 | 72,013 | 21% | | elasticsearch | 32,860.44 | 60,863 | 18% | | clickhouse | 5,883.82 | 339,915 | 100% (fastest) | +---------------+---------------+------------+----------------+ https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 18
19. Integers Query benchmark #1 - Observability $ bin/console benchmark:query-avg --datetime-from=-2days +---------------+---------------+--------------+----------------+ | Engine | Duration (ms) | Rows Scanned | vs Fastest | +---------------+---------------+--------------+----------------+ | postgresql | 172.90 | 2,000,000 | 10% | | mariadb | 683.93 | 2,000,000 | 3% | | mysql | 615.10 | 2,000,000 | 3% | | elasticsearch | 268.54 | 2,000,000 | 7% | | clickhouse | 18.10 | 2,000,000 | 100% (fastest) | +---------------+---------------+--------------+----------------+ https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 19
20. Integers Query benchmark #1 - Observability $ bin/console benchmark:query-avg --datetime-from=-2days +---------------+---------------+--------------+----------------+ | Engine | Duration (ms) | Rows Scanned | vs Fastest | +---------------+---------------+--------------+----------------+ | postgresql | 976.93 | 12,000,000 | 4% | | mariadb | 4,150.59 | 12,000,000 | 1% | | mysql | 3,664.12 | 12,000,000 | 1% | | elasticsearch | 382.29 | 12,000,000 | 9% | | clickhouse | 34.57 | 12,000,000 | 100% (fastest) | +---------------+---------------+--------------+----------------+ https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 20
21. Integers Query benchmark #1 - Observability $ bin/console benchmark:query-avg --datetime-from=-2days +---------------+---------------+--------------+----------------+ | Engine | Duration (ms) | Rows Scanned | vs Fastest | +---------------+---------------+--------------+----------------+ | postgresql | 862.54 | 12,000,000 | 11% | | mariadb | 6,356.97 | 12,000,000 | 1% | | mysql | 6,303.42 | 12,000,000 | 1% | | elasticsearch | 1,413.44 | 34,000,000 | 7% | | clickhouse | 92.45 | 34,000,000 | 100% (fastest) | +---------------+---------------+--------------+----------------+ https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 21
22. Integers Query benchmark #1 - Observability $ bin/console benchmark:query-avg --datetime-from=-2days +---------------+---------------+--------------+----------------+ | Engine | Duration (ms) | Rows Scanned | vs Fastest | +---------------+---------------+--------------+----------------+ | postgresql | 920.80 | 12,000,000 | 12% | | mariadb | 6,327.89 | 12,000,000 | 2% | | mysql | 6,240.45 | 12,000,000 | 2% | | elasticsearch | 1,868.77 | 44,000,000 | 6% | | clickhouse | 114.01 | 44,000,000 | 100% (fastest) | +---------------+---------------+--------------+----------------+ https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 22
23. What are columnar databases #2 - Introduction to Columnar Databases 23
24. Relational ▪ PostgreSQL ▪ MySQL ▪ MariaDB Columnar ▪ ClickHouse ▪ BigQuery ▪ Redshift Relational VS Columnar #2 - Introduction to Columnar Databases 24
25. Relational Good at ▪ Transactional Usage ▪ ACID compliance ▪ Frequent Insert / Update / Delete ▪ Complex relationships Bad at ▪ Large scale queries ▪ Too much columns Columnar Good at ▪ Aggregations Queries - OLAP ▪ Filtering, Reporting ▪ Data warehousing ▪ Hundreds of columns Bad at ▪ Modifying data ▪ Record lookup Relational VS Columnar - what they do #2 - Introduction to Columnar Databases 25
26. Relational ▪ Row-based Storage ▪ B-tree indexes - O(log n) lookups ▪ Buffer pool caching Columnar ▪ Column-oriented Storage ▪ Aggressive compression ▪ Data skipping ▪ Late decompression Relational VS Columnar - how they do #2 - Introduction to Columnar Databases 26
27. ◼ Open Source ◼ MergeTree engine - Data stored in immutable parts, asynchronously merged in background ◼ Specialized compression codecs per column ◼ Materialized views as incremental pre-aggregation engine (not cached queries) ClickHouse Specificity #2 - Introduction to Columnar Databases https://clickhouse.com/docs/intro 27
28. ClickHouse data Table engines ▪ MergeTree family High-performance engines for production workloads. Fast inserts with background data processing, supports replication, partitioning, and secondary indexes. Your go-to for serious data storage. ▪ Log family Write many small tables quickly, read them whole. Minimal features, minimal overhead. ▪ Integration Engine family Connect to external systems (MySQL, Kafka, S3, PostgreSQL, MongoDB, etc.). Query external data directly or stream from message queues. ▪ Special Engines family Utility engines for specific use cases: in-memory tables (Memory), views (View), joining data (Merge), mocking data (Null), testing (GenerateRandom), etc. #2 - Introduction to Columnar Databases https://clickhouse.com/docs/engines/table-engines 28
29. ClickHouse data Sort matters CREATE TABLE events ( timestamp DateTime, user_id UInt32, event_type String ) ENGINE = MergeTree() PARTITION BY toYYYYMM(timestamp) ORDER BY (user_id, timestamp) ▪ Physical layout - Rows are stored on disk sorted by ORDER BY key ▪ Query optimization - Range queries on ORDER BY columns are extremely fast (sequential reads) ORDER BY (user_id, timestamp) all events for user_id=123 are stored contiguously, enabling fast user-specific queries. #2 - Introduction to Columnar Databases https://medium.com/datadenys/how-clickhouse-primary-key-works-and-how-to-choose-it-4aaf3bf4a8b9 https://medium.com/datadenys/improving-clickhouse-query-performance-tuning-key-order-f406db7cfeb9 29
30. ClickHouse data Materialized views ▪ ClickHouse key feature ▪ Pre-compute metrics on insert - realtime aggs ▪ Trigger mechanism ▪ Raw data → MV → Aggregated tables ▪ Rollups, denormalization, filtering ▪ Queries from seconds to milliseconds #2 - Introduction to Columnar Databases https://clickhouse.com/docs/materialized-views 30
31. ClickHouse data Materialized views #2 - Introduction to Columnar Databases https://clickhouse.com/docs/materialized-views downloads MergeTree .inner.download_daily_mv AggregatingMergeTree download_daily_mv Materialized View Select Select Insert Insert Trigger 31
32. ClickHouse Data Materialized views <!-- Create a table --> CREATE TABLE downloads ( when DateTime, userid UInt32, bytes Float32 ) ENGINE=MergeTree PARTITION BY toYYYYMM(when) ORDER BY (userid, when) <!-- Insert data --> INSERT INTO downloads SELECT now() + INTERVAL number / 100 SECONDS as when, 25, rand() % 100000000 FROM system.numbers LIMIT 50000000 #2 - Introduction to Columnar Databases https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-2 32
33. SELECT toStartOfDay(when) AS day, userid, formatReadableQuantity( count()) as downloads, formatReadableSize( sum(bytes)) AS totalBytes, formatReadableSize(round( avg(bytes))) AS avgBytes, arrayMap(x -> formatReadableSize(x), quantiles( 0.5, 0.99)(bytes)) AS percentilesBytes FROM downloads GROUP BY userid, day ORDER BY userid, day ┌─────────────────day─┬─userid─┬─downloads────┬─totalBytes─┬─avgBytes──┬─percentilesBytes──────────┐ 1. │ 2025-11-20 00:00:00 │ 25 │ 5.06 million │ 229.79 TiB │ 47.64 MiB │ ['47.68 MiB','94.15 MiB'] │ 2. │ 2025-11-21 00:00:00 │ 25 │ 8.64 million │ 392.53 TiB │ 47.64 MiB │ ['47.91 MiB','94.34 MiB'] │ 3. │ 2025-11-22 00:00:00 │ 25 │ 8.64 million │ 392.38 TiB │ 47.62 MiB │ ['47.73 MiB','94.28 MiB'] │ 4. │ 2025-11-23 00:00:00 │ 25 │ 8.64 million │ 392.24 TiB │ 47.60 MiB │ ['48.01 MiB','94.32 MiB'] │ 5. │ 2025-11-24 00:00:00 │ 25 │ 8.64 million │ 392.58 TiB │ 47.65 MiB │ ['48.65 MiB','94.42 MiB'] │ 6. │ 2025-11-25 00:00:00 │ 25 │ 8.64 million │ 392.48 TiB │ 47.63 MiB │ ['47.72 MiB','94.29 MiB'] │ 7. │ 2025-11-26 00:00:00 │ 25 │ 1.74 million │ 79.15 TiB │ 47.64 MiB │ ['47.37 MiB','94.30 MiB'] │ └─────────────────────┴────────┴──────────────┴────────────┴───────────┴───────────────────────────┘ 7 rows in set. Elapsed: 0.220 sec. Processed 50.00 million rows, 600.00 MB (227.72 million rows/s., 2.73 GB/s.) Peak memory usage: 1.08 MiB. #2 - Introduction to Columnar Databases https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-2 33
34. ClickHouse data Materialized views INSERT INTO download SELECT now() + number * 60 as when, 25, rand() % 100000000 FROM system.numbers LIMIT 50000000 Query: 34724 rows in set. Elapsed: 0.696 sec. Processed 50.01 million rows, 600.06 MB (71.82 million rows/s., 861.89 MB/s.) Peak memory usage: 304.55 MiB. #2 - Introduction to Columnar Databases https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-2 34
35. ClickHouse data Materialized views CREATE MATERIALIZED VIEW downloads_daily_mv ENGINE = MergeTree PARTITION BY toYYYYMM(day) ORDER BY (userid, day) POPULATE AS SELECT toStartOfDay(when) AS day, userid, countState() as downloads, sumState(bytes) AS totalBytes, avgState(bytes) AS avgBytes, quantilesState( 0.5,0.99)(bytes) AS percentilesBytes FROM downloads GROUP BY userid, day #2 - Introduction to Columnar Databases https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-2 35
36. #2 - Introduction to Columnar Databases SELECT day, userid, formatReadableQuantity(countMerge(downloads)) as downloads, formatReadableSize(sumMerge(totalBytes)) AS totalBytes, formatReadableSize(round(avgMerge(avgBytes))) AS avgBytes, arrayMap(x -> formatReadableSize(x), quantilesMerge( 0.5,0.99)(percentilesBytes)) AS percentilesBytes FROM downloads_daily_mv GROUP BY userid, day ORDER BY userid, day ┌─────────────────day─┬─userid─┬─downloads────┬─totalBytes─┬─avgBytes──┬─percentilesBytes──────────┐ 1. │ 2025-11-20 00:00:00 │ 25 │ 5.06 million │ 229.79 TiB │ 47.64 MiB │ ['48.82 MiB','94.29 MiB'] │ 2. │ 2025-11-21 00:00:00 │ 25 │ 8.64 million │ 392.53 TiB │ 47.64 MiB │ ['48.88 MiB','94.37 MiB'] │ 3. │ 2025-11-22 00:00:00 │ 25 │ 8.64 million │ 392.38 TiB │ 47.62 MiB │ ['47.30 MiB','94.43 MiB'] │ 4. │ 2025-11-23 00:00:00 │ 25 │ 8.64 million │ 392.24 TiB │ 47.60 MiB │ ['47.48 MiB','94.47 MiB'] │ 5. │ 2025-11-24 00:00:00 │ 25 │ 8.64 million │ 392.58 TiB │ 47.65 MiB │ ['48.23 MiB','94.42 MiB'] │ 6. │ 2025-11-25 00:00:00 │ 25 │ 8.64 million │ 392.48 TiB │ 47.63 MiB │ ['46.55 MiB','94.44 MiB'] │ 7. │ 2025-11-26 00:00:00 │ 25 │ 1.74 million │ 79.15 TiB │ 47.64 MiB │ ['48.22 MiB','94.48 MiB'] │ └─────────────────────┴────────┴──────────────┴────────────┴───────────┴───────────────────────────┘ 7 rows in set. Elapsed: 0.014 sec. https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-2 36
37. ClickHouse data Materialized views #2 - Introduction to Columnar Databases https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-2 downloads MergeTree .inner.download_daily_mv AggregatingMergeTree download_daily_mv Materialized View Select Select Insert Insert Trigger 37
38. Common use cases Immutable data #3 Use-Cases 38
39. ◼ Application Monitoring ◦ Controller response time ◦ SQL / Redis queries ◦ Memory consumption ◦ Worker analytics ◼ Business Analytics ◦ product/category/region revenue real time dashboard ◦ Conversion funnel ◦ A/B testing ◼ User behavior Analytics ◦ Pages views and user journey ◦ UI heatmaps ◦ Session / bounce rate ◼ Logs ◼ Marketing / SEO Common use cases Immutable data #3 Use-Cases 39
40. ClickHouse + Symfony Minimal app monitoring Let’s build a minimal app monitoring system Will report Memory, Status Code and Response Time per controller #3 Use-Case: minimal app monitoring https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 40
41. ◼ Native binary protocol on port 9000. Not supported by PHP yet ◼ HTTP Interface on port 8123 $ composer req symfony/http-client ClickHouse interfaces #3 Use-Case: minimal app monitoring https://clickhouse.com/docs/interfaces/overview https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 41
42. # config/packages/framework.yaml framework: http_client: scoped_clients : clickhouse.client : base_uri: '%env(CLICKHOUSE_URL)%' ClickHouse interfaces #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/http_client.html#scoping-client https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 42
43. ClickHouse + Symfony Minimal app monitoring $ composer require symfony/messenger \ symfony/doctrine-messenger #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/messenger.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 43
44. ClickHouse + Symfony Minimal app monitoring # config/packages/messenger.yaml framework: messenger: transports: async: '%env(MESSENGER_TRANSPORT_DSN)%' routing: 'App\Messenger\Message\MonitoringData' : async #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/messenger.html#transport-configuration https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 44
45. ClickHouse + Symfony Minimal app monitoring Let’s Store data #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/messenger.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 45
46. namespace App\Messenger\Message; readonly class MonitoringData { public function __construct( public float $duration, public int $peakMemoryUsage, public string $controller, public string $uri, public int $statusCode, ) { } } #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/messenger.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 46
47. class RequestMonitoringListener implements EventSubscriberInterface { // ... public function onKernelRequest (RequestEvent $event): void { $this->requestStartTime = microtime (true); } public function onKernelTerminate (TerminateEvent $event): void { $this->messageBus ->dispatch (new MonitoringData( duration: microtime (true) - $this->requestStartTime , peakMemoryUsage: memory_get_peak_usage (true), controller: $this->getControllerName ($event->getRequest ()), uri: $event->getRequest ()->getRequestUri (), statusCode: $event->getResponse ()->getStatusCode () )); } #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/messenger.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 47
48. #[AsMessageHandler] class MonitoringDataHandler { // ... public function __invoke(MonitoringData $message): void { $this->buffer[] = [ 'timestamp' => date('Y-m-d H:i:s'), 'duration' => $message->duration, 'peak_memory_usage' => $message->peakMemoryUsage, 'controller' => $message->controller, 'uri' => $message->uri, 'status_code' => $message->statusCode, ]; if (count($this->buffer) >= self::BUFFER_SIZE) { $this->flush(); } } #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/messenger.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 48
49. #[AsMessageHandler] class MonitoringDataHandler { //... public function flush(): void { $query = 'INSERT INTO monitoring_data FORMAT JSONEachRow'; $body = implode("\n", array_map(fn($data) => json_encode($data, JSON_THROW_ON_ERROR), $this->buffer)); $this->clickhouseClient->request('POST', '?database='.$this->clickhouseDb.'&query='.urlencode($query), [ 'body' => $body ]); $this->buffer = []; } } #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/messenger.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 49
50. #[AsMessageHandler] class MonitoringDataHandler { //... public function flush(): void { $query = 'INSERT INTO monitoring_data FORMAT JSONEachRow'; $body = implode("\n", array_map(fn($data) => json_encode($data, JSON_THROW_ON_ERROR), $this->buffer)); $this->clickhouseClient->request('POST', '?database='.$this->clickhouseDb.'&query='.urlencode($query), [ 'body' => $body ]); $this->buffer = []; } } INSERT DONE #3 Use-Case: minimal app monitoring $ bin/console messenger:consume https://symfony.com/doc/current/messenger.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 50
51. ClickHouse + Symfony Minimal app monitoring Let’s Retrieve data #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/http_client.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 51
52. $sql = <<<EOSQL SELECT hour, controller, status_code, countMerge(request_count) AS total_requests, round(avgMerge(duration_avg), 4) AS avg_duration, arrayMap(x -> round(x, 4), quantilesMerge( 0.5, 0.95)(duration_quantiles)) AS duration_percentiles, round(avgMerge(memory_avg) / 1048576, 2) AS avg_memory_mb FROM monitoring_data_hourly WHERE hour >= now() - INTERVAL 24 HOUR GROUP BY hour, controller, status_code ORDER BY hour DESC LIMIT 50 FORMAT JSON EOSQL; #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/http_client.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 52
53. $data = $this->clickhouseClient ->request('POST', '?database='.$this->clickhouseDb, [ 'body' => $sql, ])->toArray()['data']; dump($data); #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/http_client.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 53
54. ^ array:2 [ 0 => array:12 [ "hour" => "2025-11-18 09:00:00" "controller" => "App\Controller\RootController::root" "status_code" => 200 "total_requests" => "27" "avg_duration" => 0.0007 "min_duration" => 0.0005 "max_duration" => 0.0016 "duration_percentiles" => array:2 [ 0 => 0.0005 1 => 0.0014 ] "avg_memory_mb" => 3.78 "min_memory_mb" => 2 "max_memory_mb" => 10 "memory_percentiles_mb" => array:2 [ 0 => 2 1 => 10 ] ] ... QUERY DONE #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/http_client.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 54
55. ┌────────────────hour─┬─controller────────────────────────────────────────────────────────────────────────┬─total_requests─┬─avg_duration─┬─avg_memory_mb─┐ 1. │ 2025-11-23 13:00:00 │ AdminPipeline\Controller\AgentApi\ConfigurationController::🐕configuration │ 1541 │ 0.0266 │ 4.1 │ 2. │ 2025-11-23 13:00:00 │ AdminPipeline\Controller\Api\ServerController::topTransactions │ 1095 │ 0.0672 │ 6 │ 3. │ 2025-11-23 13:00:00 │ AdminPipeline\Controller\RootController::root │ 120 │ 0.0086 │ 4.1 │ 4. │ 2025-11-23 13:00:00 │ AdminPipeline\Controller\Admin\DashboardController::login │ 90 │ 0.0113 │ 4.18 │ 5. │ 2025-11-23 13:00:00 │ AdminPipeline\Controller\Api\BillingGroupsStateController::getBillingGroupsStates │ 12 │ 0.0915 │ 4 │ 6. │ 2025-11-23 13:00:00 │ AdminPipeline\Controller\Api\ServerController::transactionUriDistribution │ 7 │ 0.0582 │ 4 │ 7. │ 2025-11-23 13:00:00 │ AdminPipeline\Controller\Api\ServerController::serverTransactionAverageWt │ 7 │ 0.0512 │ 4.29 │ 8. │ 2025-11-23 14:00:00 │ AdminPipeline\Controller\AgentApi\ConfigurationController::🐕configuration │ 1544 │ 0.0266 │ 4.11 │ 9. │ 2025-11-23 14:00:00 │ AdminPipeline\Controller\Api\ServerController::topTransactions │ 1104 │ 0.0681 │ 6 │ 10. │ 2025-11-23 14:00:00 │ AdminPipeline\Controller\RootController::root │ 120 │ 0.0086 │ 4.13 │ 11. │ 2025-11-23 14:00:00 │ AdminPipeline\Controller\Admin\DashboardController::login │ 90 │ 0.0113 │ 4.13 │ 12. │ 2025-11-23 14:00:00 │ AdminPipeline\Controller\Api\BillingGroupsStateController::getBillingGroupsStates │ 12 │ 0.0902 │ 4 │ 13. │ 2025-11-23 14:00:00 │ AdminPipeline\Controller\Api\ServerController::transactionUriDistribution │ 3 │ 0.0588 │ 4 │ 14. │ 2025-11-23 14:00:00 │ AdminPipeline\Controller\Api\ServerController::serverTransactionAverageWt │ 3 │ 0.0492 │ 4 │ #3 Use-Case: minimal app monitoring https://symfony.com/doc/current/http_client.html https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 55
56. ◼ Batch inserts (1 000 lines per batch is common) ◼ Do some tests with your own data ◼ Prepare Materialized View per usage ◼ Compress your data using codecs Datetime => DoubleDelta + ZSTD String => Codec Use Altinity Useful Queries to check ◼ Use appropriate type : UInt, LowCardinality(), FixedString, … ClickHouse key points #3 Performance https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema https://kb.altinity.com/altinity-kb-schema-design/codecs/ https://clickhouse.com/docs/data-compression/compression-modes https://clickhouse.com/docs/sql-reference/data-types https://kb.altinity.com/altinity-kb-useful-queries/altinity-kb-database-size-table-column-size/ 56
57. ClickHouse scalability TTL, Tiered Storage and horizontal scaling #5 - Scalability 57
58. Compute ◼ Scale vertically first, then horizontally Storage ◼ High IOPS to ease reading data Data lifecycle ◼ Use TTL to delete data ◼ Or Use TTL to move data to warm storage ClickHouse scalability Recommendations #5 - Scalability https://clickhouse.com/docs/guides/developer/ttl https://clickhouse.com/docs/guides/sizing-and-hardware-recommendations 58
59. Delete data using condition CREATE TABLE IF NOT EXISTS table_name ( `date` DateTime, ... ) ENGINE = MergeTree() PARTITION BY toYYYYMM(date) TTL toDate(date) + toIntervalDay( 30) SETTINGS ttl_only_drop_parts = 1 ClickHouse scalability Use TTL to delete data #5 - Scalability https://clickhouse.com/docs/guides/developer/ttl https://clickhouse.com/docs/guides/sizing-and-hardware-recommendations 59
60. ClickHouse is Upsun native # .upsun/config.yaml services: clickhouse: type: clickhouse:25.3 apps: php_app: type: 'php:8.4' relationships: clickhouse: #6 - Bonus https://docs.upsun.com/add-services/clickhouse.html 60
61. ClickHouse is Upsun native $ upsun tunnel:single -r clickhouse_native -y Are you sure you want to open an SSH tunnel to the relationship clickhouse_native on the environment main (type: production)? [Y/n] y SSH tunnel opened to clickhouse_native at: clickhouse://native:81e02af4b748326debe9c94340f6cee8@127.0.0.1:30000/main #6 - Bonus $ docker run --rm -it --network host clickhouse/clickhouse-server:25.3 clickhouse-client -h localhost --port 30000 --user native --password 81e02af4b748326debe9c94340f6cee8 ClickHouse client version 25.3.8.23 (official build). Connecting to localhost:30000 as user native. Connected to ClickHouse server version 25.3.8. clickhouse.0 :) https://docs.upsun.com/add-services/clickhouse.html 61
62. Power features ◼ Infinite scaling ◼ S3 Remote storage ◼ Lifecycle events Powerful connectors ◼ PostgreSQL as source ◼ Kafka as source ◼ OpenTelemetry Collector So much more #6 - Bonus https://clickhouse.com/docs/integrations/kafka https://clickhouse.com/docs/engines/table-engines/integrations/postgresql https://clickhouse.com/docs/engines/database-engines/mysql https://clickhouse.com/blog/clickhouse-and-open-telemtry 62
63. GitHub Repository ◼ https://github.com/romainneutron/clickhouse-symfony-symfonycon2025 Useful links and readings ◼ https://altinity.com/blog/ ◼ https://kb.altinity.com/ ◼ https://clickhouse.com/docs ◼ https://symfony.com/doc/current/messenger.html ◼ https://symfony.com/doc/current/http_client.html Resources #6 - Bonus 63
64. ▪ Code is available at https://github.com/romainneutron/clickhouse-symfony-symfonycon2025/ ▪ All slides include links ▪ If you dont take notes that’s fine, you’ll get slides $ git clone git@github.com:romainneutron/clickhouse-symfony-symfonycon2025.git $ cd clickhouse-symfony-symfonycon2025 $ upsun project:create $ upsun push -y #0 Who I am About this Presentation 64
65. Thank you. 65