Modernizing Real Estate Analytics: A 10x Speed Journey from Legacy Monolith to Event-Driven Architecture

Overview

Our client, a prominent real estate technology company operating across 15 metropolitan markets, had built their analytics capabilities on a legacy stack that served them well during early growth but began showing its age as data volumes exploded. Their on-premises PostgreSQL database, while reliable, was struggling to handle the 50 million+ property records, 2 million+ transactions, and millions of daily user interactions that now flow through their platform.

The tipping point came when their quarterly business review revealed that critical dashboard queries were taking 45+ minutes to load during peak hours-unacceptable for a platform where real estate agents make time-sensitive decisions based on market intelligence. The client engaged us to modernise their entire analytics infrastructure while ensuring zero disruption to their active operations.

The Challenge

The existing analytics system presented multiple interconnected challenges that demanded a comprehensive solution:

Performance Bottlenecks: Their primary PostgreSQL instance, hosted on aged hardware, was processing over 100,000 queries daily with an average latency of 12 seconds-and spiking to 45+ seconds for complex market analysis reports. The slow query log showed hundreds of queries taking longer than 30 seconds, causing timeouts and frustrated users.

Scalability Constraints: The monolithic architecture meant that scaling required vertical growth-bigger, more expensive servers-rather than the horizontal scaling needed for their explosive data growth. Each market they entered added 3-5 million new records, and the system was approaching its hard limits.

Data Freshness Issues: With batch processing running only twice daily, the analytics dashboard showed information that was often 12+ hours old. In fast-moving real estate markets, this delay meant agents were working with outdated pricing data and stale inventory information.

Infrastructure Costs: The dedicated PostgreSQL cluster required expensive enterprise licensing, 24/7 DBA monitoring, and hardware upgrades every 18 months. The total cost of ownership had grown 340% over three years while performance degraded. The hardware refresh cycle alone cost $180,000 per upgrade, and licensing fees added another $48,000 annually.

Developer Frustration: Beyond user-facing issues, the engineering team struggled with a sluggish development environment. CI/CD pipelines took 90+ minutes for database-dependent tests, delaying deployments and frustrating developers. Schema changes required coordinated late-night deployments with extensive rollback procedures. The team had essentially stopped making dashboard enhancements because the overhead was too high.

Goals

We established clear, measurable objectives aligned with the client's business priorities:

• Reduce average query response time from 12 seconds to under 1.5 seconds (minimum 8x improvement)
• Enable real-time data updates with sub-minute latency for new listings and price changes
• Achieve 60% reduction in infrastructure costs while handling 3x current data volume
• Implement horizontal scaling to support future expansion to 50+ markets
• Maintain 99.95% uptime during migration with zero data loss

Approach

Our approach combined careful planning with incremental delivery, ensuring the client could validate each phase before committing to the next:

Phase 1: Discovery and Architecture Design

We spent three weeks conducting deep technical discovery, analyzing six months of query logs, interviewing 15 power users, and mapping data flow patterns. This revealed that 73% of queries were analytical aggregations (averages, counts, rankings) while only 27% required point lookups-suggesting a columnar store would dramatically outperform their row-based PostgreSQL.

We designed an event-driven architecture centred on Apache Kafka for data streaming, ClickHouse for analytical storage, and a Redis layer for hot data. The design embraced the streaming architecture pattern, where data flows continuously rather than in batch windows. This meant every listing update, price change, and transaction would immediately flow through the pipeline, available for analysis within seconds rather than hours.

Choosing ClickHouse over alternatives like Amazon Redshift or TimescaleDB required careful evaluation. We conducted proof-of-concept tests with real production data, comparing query performance across representative workloads. ClickHouse outperformed alternatives by 3-10x on aggregation queries while offering better compression (40% smaller storage) and simpler operations. The decision was data-driven, not trend-driven.

Phase 2: Data Pipeline Implementation

Rather than a big-bang migration, we built a dual-write pipeline that fed both the existing PostgreSQL and new ClickHouse cluster simultaneously. This allowed us to validate data consistency in production without risking downtime. We implemented comprehensive reconciliation checks comparing row counts, checksums, and query results between systems.

Phase 3: Query Layer Modernisation

We rebuilt the API layer to intelligently route queries: simple lookups to Redis cache, aggregations to ClickHouse, and complex joins to either system based on query patterns. This hybrid approach preserved existing application code while delivering new performance. A Smart Query Router (SQR) learned from query patterns and automatically optimised routing over two weeks.

Implementation

The technical implementation required solving several complex challenges unique to their scale and requirements:

Event Streaming Infrastructure

We deployed Apache Kafka on Kubernetes with a three-broker cluster across three availability zones. Each database change triggered an event that flowed through Kafka Connect to ClickHouse. The streaming pipeline processes approximately 15,000 events per second with sub-second end-to-end latency.

Key implementation details include:

• Kafka brokers: 3 nodes, 8-core CPU, 32GB RAM each
• Topic partitioning: 32 partitions per topic for parallel processing
• Replication factor: 3 for fault tolerance
• Retention: 7 days (168 hours) for replay capability
• Schema registry: Confluent Schema Registry for Avro schemas

ClickHouse Cluster Configuration

The ClickHouse deployment used a 6-node cluster with 3 replica sets for high availability. We configured MergeTree tables with custom sorting keys optimized for their common query patterns-market to property type to price range. Materialised views handled automatic aggregations for frequently-accessed reports.

Data Migration Strategy

The migration ran over four weekends in incremental phases:

1. Weekend 1: Historical data dump (2.3 years, 180 million records) to ClickHouse using parallel COPY operations
2. Weekend 2: Dual-write enablement with reconciliation
3. Weekend 3: Query routing gradual rollout (10% to 50% to 100%)
4. Weekend 4: PostgreSQL read-only mode and final reconciliation

Total downtime during migration: 47 minutes (planned maintenance window).

Smart Query Router

The SQR component analyseed incoming queries and automatically selected the optimal backend. Machine learning models trained on query performance data learned to predict optimal routing. Results showed:

• 89% of queries routed to ClickHouse (aggregation-heavy)
• 8% served from Redis cache (recent lookups)
• 3% required PostgreSQL (complex joins, historical reasons)

Results

The migration delivered results that exceeded our original projections:

Performance Improvements

Average query response time dropped from 12.3 seconds to 0.8 seconds-a 15x improvement. The fastest gains came in market analysis dashboards, which now load in 2-4 seconds compared to the previous 20+ minutes.

Specific query type improvements:

• Market trend reports: 1.2s (down from 847s) - 705x faster
• Property search aggregations: 0.6s (down from 18s) - 30x faster
• Agent performance rankings: 3.1s (down from 234s) - 75x faster
• Price analytics: 2.8s (down from 156s) - 55x faster

Data Freshness

Real-time streaming reduced data latency from 12 hours to under 45 seconds. New listings and price changes now appear in dashboards within a minute of being committed-a transformation for agents responding to market changes.

Cost Optimisation

Infrastructure costs reduced by 62% in the first year:

• Eliminated PostgreSQL enterprise licensing: $48,000/year savings
• Hardware reduction (fewer/smaller servers): $67,000/year savings
• Reduced DBA requirements (managed service): $36,000/year savings
• Added: Kafka/ClickHouse cluster costs: $52,000/year
• Net savings: $99,000/year

Business Impact

The faster, fresher analytics directly impacted business metrics:

• Agent dashboard usage increased 340% (more agents checking daily)
• Average time-on-site increased 8 minutes (engaging with data)
• Support tickets related to slow reports dropped 89%
• Two new markets launched successfully on the new infrastructure

Key Metrics Summary

The transformation was measurable across every dimension we tracked:

Metric	Before	After	Improvement
Average query time	12.3s	0.8s	15x faster
Data freshness	12 hours	45 seconds	960x fresher
Infrastructure cost	$159k/year	$60k/year	-62%
Market report load time	847s	1.2s	705x faster
Dashboard usage	340 sessions/day	1,490 sessions/day	+340%
Support tickets (reports)	156/month	17/month	-89%
Uptime	99.2%	99.97%	+0.77%
CI/CD pipeline time	92 minutes	11 minutes	-88%
Data storage (compressed)	8.4 TB	5.1 TB	-39%

Lessons Learned

Several insights emerged from this engagement that inform our approach to similar migrations:

1. Start with query analysis, not technology selection

Our discovery phase proved invaluable. Understanding that 73% of queries were analytical aggregations-information easily extracted from PostgreSQL logs-pointed us toward columnar storage before we ever discussed technology options. Always let the data and query patterns guide the solution.

2. Dual-write enables low-risk migration

Running both systems in parallel during migration was essential for building confidence. Being able to compare results in production, without impacting users, allowed the client stakeholders to validate the new system thoroughly before committing.

3. Hybrid query routing preserves investment

Rather than requiring a complete application rewrite, the Smart Query Router let us incrementally adopt new infrastructure. This pattern applies broadly-route what makes sense to the new system while preserving existing investments in application logic.

4. Real-time is relative

The client's requirement was "real-time," which we achieved at 45-second latency. Defining specific SLAs (sub-minute for listings, sub-second for cached queries) helped manage expectations and focus engineering effort appropriately.

5. Budget for operational learning curve

The new stack required new skills. We invested in two weeks of knowledge transfer with the operations team, covering Kafka maintenance, ClickHouse tuning, and debugging distributed systems. This upfront investment paid dividends in smooth operations.

Conclusion

This migration demonstrates how thoughtful architecture redesign can transform both technical performance and business outcomes. The event-driven approach not only solved immediate performance challenges but positioned the client for scalable growth. Their infrastructure now handles 3x the data volume at 62% lower cost, with query speeds 15x faster.

The real estate platform now processes analytics workloads that would have required a $500k hardware investment under the old architecture-achieving this with cloud-managed services at a fraction of the cost. More importantly, their 2,000+ agents make decisions based on fresh, fast-loading market intelligence that genuinely supports competitive advantage.

For organizations facing similar legacy data challenges, this engagement validates that modern, event-driven analytical architecture is achievable without disruptive big-bang migrations. The dual-write pattern, combined with intelligent query routing, enables gradual migration with managed risk.

Looking ahead, the client has since expanded to eight additional markets with minimal infrastructure changes. The horizontal scaling capabilities of Kafka and ClickHouse mean they can add new regions simply by adjusting partition counts and adding nodes to the cluster. What once required six months of hardware procurement and deployment now happens in days through infrastructure-as-code templates.

This engagement exemplifies our approach: deeply understanding client needs, selecting the right technology for the specific problem, and executing with minimal risk through incremental delivery. The results speak for themselves-a 15x performance improvement, 62% cost reduction, and a platform ready for the next phase of growth.