Embedded Analytics for SaaS Without Overloading the Product Database

CSV exports are a product symptom

Customer-facing analytics often begins as an export button. The product stores the events, an administrator downloads a CSV, and someone tries to answer the real question in a spreadsheet. This can be a sensible first release. It becomes a product problem when the same customers repeatedly need filters, comparisons and definitions that the export leaves them to reconstruct.

That was the starting point at Teachbase, a B2B learning platform. Its customers could export learning data, but the files were difficult to interpret as a working management view. Broader reporting requests also returned to the product team as manual exports and development work.

At that point the decision is not simply whether to embed a dashboard. The company is deciding whether analytics is part of the product: which questions it will answer, what freshness it promises, how customers are separated and who owns a metric when its meaning changes.

Product workloads and analytical workloads are different

A transactional database is designed around the operations that keep the application running: create an account, assign a course, save progress, update a payment or fetch one customer record. Customer-facing analytics usually asks for a different access pattern: scan history, group it by several dimensions, compare periods and let many users change filters interactively.

Those analytical queries can compete with the application for CPU, memory and I/O. That does not mean PostgreSQL is incapable of analytics, or that a source system with ten million rows automatically needs ClickHouse. Dataset size without query shape, concurrency and service-level expectations is not a migration criterion.

A narrow report may work well on tuned PostgreSQL, a read replica or a dedicated reporting instance. A separate OLAP layer becomes more attractive when repeated scans and aggregations are central to the customer experience, product traffic must be protected from them, and the expected query pattern has been tested rather than assumed.

• What queries will customers run, across how much history and how many dimensions?
• How many customers may query at the same time, and can one tenant become a noisy neighbour?
• What delay between a product event and the dashboard is actually useful?
• Can a replica meet the requirement without creating more operational work than an OLAP service?
• Who will own pipelines, access controls, metric changes and incident response after launch?

Define the analytical product before choosing the stack

The first Teachbase task was methodological, not technical. A phrase such as “course completed” sounds obvious until the underlying product states are inspected. Does completion mean that the deadline passed, every assignment was submitted, a passing score was reached, or the course both started and ended inside the selected period? Different answers produce different numbers from the same database.

One concept such as progress could appear across several tables and fields. The team therefore had to agree definitions, locate the source events and preserve the hierarchy between programmes, courses and assignments before building the analytical layer.

This work is part of the product contract. Before architecture, define the metric semantics, supported dimensions, tenant boundary, drill-down level, update frequency and reconciliation method. Otherwise the system may be fast and still give each customer a different interpretation of the same label.

Build the minimum viable analytical layer

The final Teachbase flow was deliberately narrower than the source application. Fifty legacy source tables were reduced to eight prepared views used for extraction. Once a day, the required data moved to ClickHouse. Customer-specific marts fed DataLens dashboards embedded into the product experience.

Daily refresh was a product decision, not a technical limitation. The team agreed that one update a day was sufficient for this reporting use case, so the MVP did not add streaming machinery it did not need. Streaming, incremental replication or CDC should enter the design when freshness, source-load or recovery requirements justify the added complexity.

Field note: why ClickHouse was a good fit for this SaaS use case

Teachbase had more than ten million rows in its source system, but volume was only part of the context. Customers needed repeated aggregation and filtering across learning history, while the live product database had to remain protected. The client was already using managed Yandex Cloud services, so Managed Service for ClickHouse, Yandex Data Transfer, Yandex Cloud Functions and DataLens formed an operationally realistic stack for the MVP.

Metric calculations were moved into the data layer and the dashboard consumed prepared results. In that implementation, each customer who used the analytical product received a separate physical mart and dashboard. This made the isolation boundary explicit for a limited MVP, but it is not a universal multi-tenant architecture.

The outcome was a customer-facing analytics layer that Teachbase could offer as part of its product. The defensible result is product capability: Teachbase gained an interactive analytical interface it could offer instead of leaving customers to rebuild the view from exported files. We do not attribute an unmeasured change in retention, conversion or revenue to the database choice.

The first architecture was useful—and too complicated

The early approach copied the required source tables into an intermediate PostgreSQL database and prepared analytical structures there before moving data onward. The intention was to protect production, but the design added another database and another transfer. A revised version worked, yet still carried more infrastructure than the first product scope ultimately needed.

The PostgreSQL dashboard prototype still did something valuable: it produced feedback. Customers showed which data and learning objects mattered. After testing the load on the product database, the next version retained tests and assignments while leaving out less useful content types, prepared a smaller filtered source layer and moved the required data to ClickHouse for final marts.

The lesson is not that prototypes are waste. It is that the first analytical product should be built to reveal the right scope. An architecture that is technically elegant but difficult to change can preserve the wrong assumptions longer than a simpler prototype would.

Tenant isolation is an architecture decision, not a dashboard filter

Customer-facing analytics introduces a boundary that internal BI can often avoid: one customer must never see another customer’s data. The boundary has to exist in the data model, access path and operating process—not only as a tenant filter appended by the interface.

ClickHouse supports several patterns. Tenants with a common schema can share a table with tenant identity in the ordering key and read access restricted through roles and row policies. Application authentication and write-side tenant validation remain separate controls. A smaller number of large or structurally different tenants may justify separate tables, databases or services. Stronger isolation usually brings more infrastructure and operational overhead.

Teachbase used separate physical marts and dashboards in its MVP. A SaaS product with thousands of small tenants would probably evaluate a shared-table design instead. Choose the model from tenant count, schema variation, data volumes, regional requirements and acceptable failure radius; do not copy an MVP pattern without those constraints.

Freshness and cost belong in the same product decision

Freshness is an end-to-end promise. Source commits, extraction delay, insert batching, transformations, scheduled recomputation and caches can all contribute to the age of the number a customer sees. Calling the database real time does not define that promise.

Tighter freshness targets also have a cost: more frequent data movement, more inserts, more monitoring and more ways for partial updates to appear. Incremental pre-aggregation shifts computation to insert time and stores prepared results; scheduled recomputation shifts it to refresh time. Every shortcut transfers cost somewhere else.

For Teachbase, the team determined that a daily batch was sufficient for the intended reporting use case. A fraud product, infrastructure monitor or live marketplace might require seconds. Write the required freshness in user terms first—“visible by the next morning” or “visible within five minutes”—then design and test the pipeline that can meet it.

When ClickHouse is—and is not—the right choice

ClickHouse belongs behind embedded analytics when its columnar query model solves a measured product problem and the team is prepared to own the surrounding system. It should not be a reflexive answer to a large table, a slow unexamined query or a desire to add an impressive component to the architecture.

1. 01

   Stay on PostgreSQL when the workload is still modest

   If the first reports are narrow, concurrency is low and a tuned query or read replica meets the customer promise, the simpler system may be the better product decision.

2. 02

   Add an OLAP layer when analytical access is the product

   ClickHouse becomes attractive when customers repeatedly scan and aggregate growing history, interactive queries compete with live transactions, and a tested schema can exploit the common filters and dimensions.

3. 03

   Account for the system around the database

   Include ingestion, backfills, tenant isolation, schema changes, quotas, observability, reconciliation and support. The managed-service bill is only one part of the cost.

4. 04

   Prove the query pattern before committing

   Use representative data, filters and concurrency. Compare the simplest viable PostgreSQL path with the OLAP design, then choose from evidence rather than a row-count rule.

The result is a product capability, not a database migration

The useful outcome of the Teachbase project was not “we installed ClickHouse”. It was a defined customer-facing analytics layer: agreed learning metrics, an analytical workload separated from the product database, an explicit tenant boundary and a dashboard the SaaS company could offer to customers.

That distinction keeps architecture subordinate to the product. Start with the customer question and the service promise. Keep PostgreSQL where it is sufficient. Add ClickHouse when the analytical workload has earned a separate system. Then judge the release by whether customers can answer the intended question reliably—not by how modern the data stack looks.