Reporting looks like a query problem until concurrency, freshness, and product definitions enter the discussion. My work spans PostgreSQL-backed assessment workflows and analytical reporting with ClickHouse.
My contribution
As a hands-on Tech Lead, I contributed to architecture, query and index analysis, backend implementation, and coordination with data and infrastructure teams. I helped evaluate workload placement and tradeoffs between performance, freshness, and operating cost.
Problem and constraints
Transactional operations and broad analytical queries have different access patterns. A dashboard may aggregate many records while students and school staff keep updating operational data. A fast single query is insufficient if multiple users compete for the same resources.
Assessment API
PostgreSQL
Transactional records
Transformation
Freshness checks
Reconciliation
Reporting API
ClickHouse
Analytical aggregates
Decisions and alternatives
Query rewrites and suitable indexes are the first alternatives when PostgreSQL can still meet the workload. A read replica isolates some resource use but preserves row-oriented query characteristics and introduces lag. A cache helps when staleness and invalidation are acceptable. An analytical store introduces a second data model and synchronization responsibilities.
I evaluate representative queries, realistic concurrency, percentile latency, resource use, and freshness. Keeping ClickHouse for analytical workloads means accepting those operating responsibilities rather than assuming the engine solves them automatically.
Correctness before speed
A report must define its denominator, filters, and time boundary. A success percentage over enrolled students differs from a distribution over students who answered. Both can be valid, but presenting them without labels can make accurate calculations look inconsistent.
Results and evidence
This case documents architecture and decision process. I have not published an externally reproducible production benchmark or attributed a percentage improvement to a single change. A comparison should include query text, data shape, concurrency, cache state, and measurement period.
What I would improve next
I would keep a versioned workload suite with correctness assertions and a freshness budget. Performance regressions should be evaluated alongside changes in metric definitions.