My assessment-reporting work involves PostgreSQL and ClickHouse. I begin a database decision by specifying what the workload must do while operational systems remain usable.
Operational and analytical workloads
Define a representative workload
A synthetic benchmark can include school aggregates, student detail, and cross-event trends. Fix the dataset, filters, and denominator. Test realistic concurrency, separate cold and warm runs, and check results before comparing speed.
queries: [school_summary, student_detail, event_trend]
concurrency: [1, 5, 20]
cache_states: [cold, warm]
checks: [same_filters, same_denominator, same_result]
measures: [p50_ms, p95_ms, errors, cpu, data_age_seconds]This is an example test plan, not a production result. Concurrency one does not establish behavior when many users open dashboards together.
Compare alternatives
| Option | Benefit to test | Responsibility |
|---|---|---|
| Query rewrite or index | Less PostgreSQL work | Write overhead and plan sensitivity |
| Read replica | Separate read load | Lag and query cost |
| Cache | Repeated requests | Invalidation and staleness |
| Analytical store | Large aggregations | Synchronization and another model |
Correctness has a freshness budget
A fast answer from yesterday may be wrong for the current workflow. Define acceptable data age, late-update behavior, and the reporting period. Reconcile business totals and dimensions, not only row counts.
Plans before intuition
Inspect row estimates, actual rows, scan choices, join behavior, and buffers. EXPLAIN ANALYZE executes a statement; use an appropriate environment and precautions, particularly for writes. An index is a hypothesis about an access path, not a universal optimization.
A decision I would reverse
If an analytical store meets latency goals but repeatedly violates freshness or demands disproportionate operating effort, revisit the design. If tuned PostgreSQL meets concurrency and cost targets, an additional store may not be justified.
Related case: assessment reporting
References and further reading
Primary sources for the technical concepts in this article. The examples and decisions above are my synthesis, not quotations from these sources.
- PostgreSQL documentationUsing EXPLAIN
Inspect estimates, actual work, and query plans.
- ClickHouse documentationWhat is ClickHouse?
Analytical workloads and column-oriented storage.