“

Series: S14 Engineering Stories · Article 2 | Level: Intermediate | Reading Time: 15 min

Why We Abandoned ClickHouse

#TL;DR

During coomia-dip's storage selection, we initially chose ClickHouse as the OLAP engine, forming an "OLTP + OLAP" dual-engine architecture with PostgreSQL. After 6 months of real-world usage, we made a painful decision — abandon ClickHouse and migrate entirely to Apache Doris. This ADR (Architecture Decision Record) documents the background, evaluation process, migration plan, and post-mortem reflections. Core lesson: at small-to-medium scale, "one good-enough engine" beats "two theoretically optimal engines."

#1. Background: Why We Needed OLAP

#1.1 coomia-dip Query Scenarios

As an ontology-driven decision platform, coomia-dip faces two fundamentally different query demands:

Transactional Queries (OLTP):

• Single Object lookup by ID
• Create/update/delete Object operations
• State checks during Action execution
• Permission verification

Analytical Queries (OLAP):

• Aggregation analysis across large Object sets (e.g., "yield rate trends by production line over the past 30 days")
• Multi-dimensional drill-down (grouping by time, region, product line)
• Complex JOINs (aggregations after linking Objects via LinkTypes)
• Time-series analysis (device sensor data trends and anomaly detection)

In the v1 phase, we used PostgreSQL for all queries. For small data volumes (100K scale), this was perfectly adequate. But when test data grew to the millions, analytical queries became unacceptable — a typical aggregation query took 10-30 seconds.

#1.2 Why We Initially Chose ClickHouse

In our mid-2024 technology evaluation, ClickHouse had several notable advantages:

1. Ultimate columnar storage performance: ClickHouse consistently ranked first in OLAP benchmarks
2. Active community: 30k+ GitHub stars, active Chinese-language community
3. MergeTree engine: Native support for time-series data and real-time aggregation
4. Mature ecosystem: Established integrations with Kafka, Spark, and Flink
5. SQL compatibility: Standard SQL support, low learning curve

Based on these reasons, we introduced ClickHouse in our v2 architecture, forming a three-layer storage architecture: "PostgreSQL (OLTP) + ClickHouse (OLAP) + MinIO (object storage)."

#2. Problems ClickHouse Brought

#2.1 Data Synchronization Nightmare

The first problem after introducing ClickHouse was data synchronization. When a user modified an Object through an Action, the change needed to:

1. Write to PostgreSQL (guaranteeing transactionality)
2. Sync to ClickHouse (guaranteeing data freshness for analytical queries)

We tried three synchronization approaches:

Approach A: Dual Write Write to both PostgreSQL and ClickHouse simultaneously in the Action Engine. Problem: no distributed transaction guarantee between the two databases — if ClickHouse write failed, data became inconsistent.

Approach B: CDC (Change Data Capture) Capture PostgreSQL WAL logs via Debezium and sync to ClickHouse in real-time. Problem: introduced new middleware (Debezium + Kafka), increasing operational complexity; sync latency could reach several seconds under high load.

Approach C: Scheduled Batch Sync Full or incremental sync every 5 minutes. Problem: data latency too high — users couldn't see newly created Objects in analytics reports immediately.

We ultimately chose Approach B (CDC), but the operational burden it brought far exceeded expectations.

#2.2 Schema Change Pain

In coomia-dip, users can dynamically add properties to ObjectTypes. In PostgreSQL, we use JSONB columns for dynamic properties — schema changes have virtually zero cost.

In ClickHouse, the situation was entirely different:

• ClickHouse's ALTER TABLE ADD COLUMN is very time-consuming on large tables
• ClickHouse doesn't support JSONB (although it has an experimental JSON type, performance and stability were unsatisfactory)
• Every time a user added a new property, DDL needed to be executed in ClickHouse

We had to maintain a complex "Schema Synchronizer" that needed to:

1. Listen to Schema Registry change events
2. Map coomia-dip types to ClickHouse column types
3. Execute ALTER TABLE statements
4. Handle various edge cases (type incompatibility, column name conflicts, etc.)

This Schema Synchronizer exceeded 3,000 lines of code, was bug-prone, and became the most unstable component in the entire system.

#2.3 JOIN Limitations

ClickHouse's JOIN support is limited. While it supports various JOIN syntax, performance degrades sharply in these scenarios:

• Multi-table JOINs: A typical OQL query might involve 3-5 ObjectType joins; ClickHouse performance drops significantly beyond 3-table JOINs
• Large table JOIN large table: When both tables exceed a million rows, ClickHouse's memory consumption can cause OOM
• Frequent small queries: ClickHouse is optimized for large batch queries; single-query latency (~50-100ms) is an order of magnitude higher than PostgreSQL (~1-5ms)

#2.4 Windows Development Environment Compatibility

This issue seems minor but had a significant real-world impact. Our development team primarily uses Windows + WSL2. ClickHouse running in WSL2 had the following issues:

• Memory allocator (jemalloc) occasionally crashed in WSL2
• File system performance degraded dramatically on Windows directories mounted in WSL2
• Docker Desktop running ClickHouse consumed 2-3x the memory of native Linux

This meant developers frequently encountered random ClickHouse-related failures when running integration tests locally.

#2.5 Operational Complexity

After introducing ClickHouse, our operational checklist grew to include:

• ClickHouse cluster deployment and monitoring
• Debezium + Kafka deployment and monitoring
• Schema Synchronizer deployment and monitoring
• Data consistency check scripts
• ClickHouse backup and recovery

For a 4-person team, this operational burden was unsustainable.

#3. Evaluating Alternatives

#3.1 Evaluation Criteria

We established 7 evaluation criteria:

#3.2 Candidate Solutions

We evaluated the following:

Option 1: Continue PostgreSQL + ClickHouse Maintain status quo; invest more effort optimizing sync and schema management.

Option 2: PostgreSQL + StarRocks StarRocks is a ClickHouse competitor, claiming better JOIN support.

Option 3: Apache Doris Doris is a MySQL-protocol-compatible MPP database supporting both OLTP and OLAP.

Option 4: DuckDB (embedded) DuckDB as an embedded OLAP engine requires no standalone deployment.

#3.3 Evaluation Results

Doris won with 7.6 points. Its core advantage: "good-enough OLAP performance + sufficient OLTP capability + simple operations."

#4. Why Doris Won

#4.1 Unified Engine, Eliminating Data Sync

Doris's greatest advantage is supporting both OLTP and OLAP queries simultaneously. This meant we no longer needed to maintain data synchronization between two databases. Data is written once and can serve both transactional and analytical queries.

This single advantage eliminated 30% of our operational workload and all 3,000 lines of Schema Synchronizer code.

#4.2 MySQL Protocol Compatibility

Doris is compatible with the MySQL protocol, meaning:

• Numerous existing client libraries work out of the box
• Developers can use familiar MySQL SQL syntax directly
• Migration costs are significantly reduced

#4.3 Excellent JOIN Capability

Doris's MPP architecture is naturally suited for distributed JOINs. In our benchmarks, 3-table JOIN queries were 2-3x faster on Doris than ClickHouse, and 5-table JOINs were 5-10x faster.

#4.4 Dynamic Schema Support

Doris 2.x introduced the Variant type, similar to PostgreSQL's JSONB, supporting dynamic schemas. This perfectly matched coomia-dip's requirement — users can dynamically add properties without executing DDL.

#4.5 Lightweight Deployment

In development environments, Doris can run in single-node mode with far less resource consumption than ClickHouse. In production, Doris's FE+BE architecture is much simpler than a ClickHouse cluster + Debezium + Kafka.

#5. Migration Process

#5.1 Migration Strategy

We adopted a "gradual migration" strategy:

Phase 1 (2 weeks): Set up Doris environment, complete data migration scripts Phase 2 (2 weeks): Gradually switch read queries to Doris (writes still go to PostgreSQL) Phase 3 (1 week): Switch write operations to Doris Phase 4 (1 week): Decommission PostgreSQL and ClickHouse

#5.2 Data Migration

The core challenge was type mapping. coomia-dip ObjectType properties have 12 types that needed mapping to Doris column types:

The migration script ran for approximately 4 hours (total data volume ~500GB), with no data loss.

#5.3 Query Layer Adaptation

The OQL query engine needed to change from "PostgreSQL + ClickHouse dual routing" to "Doris single routing." This was actually a simplification — approximately 2,000 lines of routing and adapter code were deleted.

#5.4 Test Coverage

During migration, we wrote 200+ migration-specific test cases covering:

• Read/write for all 12 data types
• Boundary values (NULL, empty strings, max values, min values)
• Concurrent reads and writes
• Bulk imports
• OQL query compatibility

Only after all 200+ tests passed did we execute the formal migration.

#6. Post-Migration Results

#6.1 Performance Comparison

Doris was slightly inferior to the PG+CH combination for single-row queries and simple aggregations, but had overwhelming advantages in JOIN scenarios. Given that coomia-dip queries are JOIN-heavy (Objects always have relationships), Doris was the better choice.

#6.2 Operational Simplification

#6.3 Code Reduction

Deleting 7,600 lines of code meant eliminating 7,600 lines of potential bugs.

#7. Post-Mortem Reflections

#7.1 Where We Went Wrong

Looking back at this decision, we made several mistakes:

Mistake 1: Blinded by Benchmarks ClickHouse's performance in standard OLAP benchmarks (TPC-H, ClickBench) was indeed impressive. But the benchmark scenarios (single-table large-scale aggregation) were far from our actual use case (multi-table JOINs + dynamic schemas).

Mistake 2: Underestimated "Additional Component" Costs Introducing ClickHouse wasn't just introducing a database — it brought Debezium, Kafka, Schema Synchronizer, and a series of "satellite components." Their combined operational costs far exceeded ClickHouse itself.

Mistake 3: Insufficient PoC During selection, we ran performance benchmarks but didn't do sufficient integration testing. If we had spent two weeks on a complete PoC (including data sync, schema changes, JOIN queries), we might have discovered the problems in month two.

#7.2 Where ClickHouse Shines

ClickHouse isn't a bad product — it remains the best choice for:

• Log analytics (fixed schema, high write volume, single-table queries)
• Time-series analysis (IoT sensors, monitoring data)
• Wide-table reports (pre-aggregated data warehouses)

But it wasn't right for our use case — dynamic schemas, frequent JOINs, and coexisting transactional and analytical queries.

#7.3 The Value of ADRs

This experience gave us a deep appreciation for ADRs (Architecture Decision Records). We didn't write an ADR when making the ClickHouse decision — if we had, we would have explicitly listed assumptions, constraints, and risks, potentially discovering problems earlier.

After this experience, we mandated that all major technical decisions must have an ADR, including:

• Decision context and problem statement
• Evaluated options and criteria
• Chosen option and rationale
• Known risks and mitigations
• Reviewers and approval date

#8. Advice for Readers

#8.1 Storage Selection Checklist

If you're also making storage decisions, this checklist may help:

1. Clarify your query patterns: Primarily OLTP, OLAP, or mixed?
2. Assess JOIN requirements: How many relationships exist in your data model?
3. Consider schema change frequency: Is the schema fixed or dynamic?
4. Calculate total operational cost: Not just the database itself — include sync, monitoring, and backup components
5. PoC with your real scenarios: Don't just look at benchmarks
6. Consider developer experience: Can the team conveniently run it locally?
7. Reserve a migration path: If you choose wrong, what's the migration cost?

#8.2 "Good Enough" Beats "Theoretically Optimal"

This is the deepest lesson from this experience. At small-to-medium scale (data < 10TB), a "good enough" unified engine almost always beats two "theoretically optimal" specialized engines. Because it:

• Reduces data synchronization complexity
• Reduces operational workload
• Reduces code complexity
• Reduces the technology stack the team needs to master

Of course, at massive scale (data > 100TB), a specialized engine's performance advantage may outweigh a unified engine's simplicity. But before making that judgment, first confirm you actually have that much data.

#9. Migration Technical Details

#9.1 Zero-Downtime Migration Plan

To achieve zero-downtime migration, we designed a "shadow write" mechanism:

Phase 1: Shadow Write
├── All writes → PostgreSQL (primary)
├── All writes → Doris (shadow)
└── All reads ← PostgreSQL

Phase 2: Read Switch
├── All writes → PostgreSQL (primary)
├── All writes → Doris (shadow)
└── All reads ← Doris (gradual switch)

Phase 3: Write Switch
├── All writes → Doris (primary)
└── All reads ← Doris

Phase 4: Decommission Legacy
├── Stop PostgreSQL
├── Stop ClickHouse
└── Stop Debezium + Kafka

#9.2 Data Consistency Verification

At the end of each Phase, we ran data consistency verification scripts:

• Compare row counts between the two databases
• Randomly sample 1,000 rows for field-by-field comparison
• Verify all ObjectType properties were completely migrated
• Run full OQL regression tests

#9.3 Rollback Plans

Each Phase had a corresponding rollback plan:

• Phase 1 rollback: Stop Doris shadow writes
• Phase 2 rollback: Switch reads back to PostgreSQL
• Phase 3 rollback: Switch writes back to PostgreSQL
• Phase 4 rollback: Restore PostgreSQL from backup

During the actual migration, we didn't use any rollback plans — everything went smoothly.

#10. Conclusion

Abandoning ClickHouse was one of the most painful technical decisions in the coomia-dip project — not because ClickHouse was bad, but because we paid 6 months of time for this incorrect selection. Yet this experience also taught us: there are no silver bullets in technology selection, only "the most appropriate choice for your scenario."

If we could do it over, we would have chosen Doris on Day 1. But without those 6 months of ClickHouse experience, we might never have truly understood why Doris was the better choice.

#Key Takeaways

1. Benchmarks do not equal real scenarios: ClickHouse was fast in standard tests but unsuitable for our multi-table JOIN + dynamic schema use case
2. Total operational cost > single component cost: ClickHouse itself wasn't expensive, but adding Debezium + Kafka + Schema Synchronizer tripled the cost
3. "Good enough" unified engine > "theoretically optimal" specialized engine combo: For small-to-medium scale (< 10TB)
4. PoC must cover real scenarios: Especially data sync, schema changes, and complex JOINs
5. Major technical decisions need ADRs: Explicitly document assumptions, constraints, and risks

#Next Article

Next: S14-03 Merging 8 Layers into 3 Processes — We'll describe why 8 Layers was too heavy during development and how we merged them into 3 processes while maintaining architectural clarity.

Tags: #coomia-dip #ClickHouse #Doris #StorageSelection #ADR #ArchitectureDecision #DatabaseMigration