What do you use instead of Semrush for rank tracking on a large site?

I've actually benchmarked this exact scenario for an internal project last year. The trap is thinking there's a "middle ground" store that solves both the scaling and the dashboard problem neatly. In my experience, there isn't one.

You need two layers: a time-series database for the raw data (I used ClickHouse on a single node for 20k keywords, it handled the cardinality fine) and a separate analytical OLAP store or even a denormalized PostgreSQL table for the dashboard. The dashboard queries should never hit the high-cardinality time-series store directly; you run a daily aggregation job that flattens the latest rankings into a table optimized for joins and filtering by client, region, or keyword group. This adds pipeline complexity, yes, but it's the only way to get dashboards that aren't a misery of slow queries and disconnected gauges.

The real question becomes whether you're willing to build and maintain that two-tier aggregation system. If not, you're stuck with a product like Semrush, where you pay for them to have solved it already.

Your two-layer approach mirrors what we ultimately implemented, but the performance bottleneck surprised us. The aggregation job itself became a significant point of failure as the dataset grew. Transforming high-cardinality time-series data into a denormalized dashboard table is a heavy operation, and scheduling it during low-traffic periods becomes a operational constraint.

We sidestepped this by using ClickHouse's materialized views to perform continuous incremental aggregation. This moves the compute cost from a daily batch job to the insertion time, which spreads the load. The trade-off is increased storage for the aggregated data, but it eliminated the recurring dashboard timeout issues we had with the scheduled job approach. The key was designing the materialized view to pre-join dimension tables (like client, keyword group) at insertion, so the final table is truly query-ready.

So while I agree completely with the architectural split, I'd add that the implementation of the aggregation layer is critical. A batch job might work initially, but it doesn't scale elegantly.

This is such a great, practical point about the aggregation layer. Moving the compute to insertion time via materialized views is a clever way to smooth out that operational cost. I've seen similar bottlenecks with daily batch jobs that start taking six hours and eat into the reporting window.

The storage trade-off is interesting. We faced a similar decision and found that pre-joining dimensions, like you did, actually *reduced* our overall costs. The raw time-series store was cheaper per GB, but the sheer volume of repeated dimension data across billions of rows was massive. By creating a query-ready aggregate table, we shrank the dataset the dashboard actually hit by about 70%, which made our BI tool way happier. The cost just shifted from compute headaches to slightly pricier storage for the aggregated layer, which was a win for us.

One caveat we learned the hard way: if your dimension tables change frequently (like keyword groups or client mappings), you need a robust process to refresh or version those materialized views. We had a week where old client names were serving in the dashboard because the view wasn't rebuilt after a CRM sync.

The dimension refresh problem is the critical detail everyone misses until it breaks. Materialized views lock in a snapshot of those joins. If your source dimension tables are volatile, your aggregated data becomes stale and wrong.

You need to treat those views as part of your CI/CD, not just a set-and-forget schema object. Any merge to the dimension source should trigger a rebuild or a versioned cutover. Otherwise you're trading a batch performance problem for a data integrity problem.

You've nailed the core data model issue. A keyword rank is more of an event than a classic gauge. Shoving it into Prometheus feels like putting a square peg in a round hole just because the hammer is handy.

That said, a simple relational setup can get painful fast if you're tracking historical movements for thousands of keywords. The query to find a keyword's rank on a specific date across all tracked days becomes a real drag. A time-series db built for analytics (like Timescale) can handle that time-slicing much more gracefully.

So maybe the answer is "neither" - or that you need both, as the later posts hint at with the two-layer approach.

The amortization math is seductive but often built on spreadsheet fiction. You're assuming the pipeline's fixed cost stays fixed. It doesn't.

>maintaining the data quality SLA, which requires its own set of monitoring and alerting

You've just described a second, parallel pipeline whose cost isn't linear. Every new client site or keyword group adds new edge cases and verification overhead, not just more rows to the same table. The moment you add a new geographic market or search vertical, your "amortized" engineering cost spikes again.

The real break-even isn't when your average cost per keyword dips below Semrush's rate. It's when the total cost of your engineering team's unplanned maintenance work stops exceeding the subscription invoice. I've yet to see a team actually track that.

You're spot on about the unplanned maintenance work. We tried tracking that once, and the spreadsheet column for "data fire drill hours" always dwarfed the forecasted engineering time. It's the silent killer of any DIY cost model.

The new geographic market example hits home. Adding a new region isn't just a new data source - you're suddenly debugging why rank data for Sydney is returning zeros, and that's a whole new verification layer. Your "amortized" cost per keyword resets to zero with each new edge case.

That's why our team's rule of thumb became: if you can't quantify the ongoing cost of *unknown* unknowns, you haven't priced it correctly. The subscription invoice is the ceiling of your cost. A DIY pipeline's cost floor is just the first estimate.