Skip to content

Query optimization#950

Merged
goldflag merged 3 commits intomasterfrom
query-optimization
Mar 22, 2026
Merged

Query optimization#950
goldflag merged 3 commits intomasterfrom
query-optimization

Conversation

@goldflag
Copy link
Copy Markdown
Collaborator

@goldflag goldflag commented Mar 22, 2026 •
edited by coderabbitai bot
Loading

Summary by CodeRabbit

  • New Features

    • Added lazy loading for metrics sections — Referrers, Pages, Devices, Countries, Events, Weekdays, Network, and SearchConsole now render only when visible in viewport, improving initial page load performance.

  • Refactor

    • Optimized analytics queries for faster metric calculations and improved responsiveness when retrieving dashboard data.

goldflag added 3 commits March 22, 2026 13:09
@goldflag
Enhance analytics queries for improved performance and accuracy
1528c3b 
- Added logic to handle cases with and without filters in getMetric, getOverview, and getOverviewBucketed, optimizing SQL queries for session metrics and bounce rate calculations.
- Refactored getOutboundLinks to simplify the query structure, focusing on URL counts and last clicked timestamps.
- Introduced inner filter statements in getSessions to improve event-level data accuracy while maintaining overall query correctness.
- Updated getFilterStatement to support filtering on raw event-level columns, enhancing query efficiency.
@goldflag
Restore query log
fa45bfa
@goldflag
reduce number of queries
8afb7d5
@vercel
Copy link
Copy Markdown

vercel bot commented Mar 22, 2026

The latest updates on your projects. Learn more about Vercel for GitHub.

Project Deployment Actions Updated (UTC)
rybbit Ready Ready Preview, Comment Mar 22, 2026 10:08pm

Request Review

@coderabbitai
Copy link
Copy Markdown
Contributor

coderabbitai bot commented Mar 22, 2026 •
edited
Loading

Caution

Review failed

The pull request is closed.

ℹ️ Recent review info
⚙️ Run configuration

Configuration used: defaults

Review profile: CHILL

Plan: Pro

Run ID: 953a8541-ebd4-4b9e-aa3e-813a7253c2a1

📥 Commits

Reviewing files that changed from the base of the PR and between 0d6b841 and 8afb7d5.

📒 Files selected for processing (8)
  • client/src/app/[site]/main/page.tsx
  • docker-compose.cloud.yml
  • server/src/api/analytics/events/getOutboundLinks.ts
  • server/src/api/analytics/getMetric.ts
  • server/src/api/analytics/getOverview.ts
  • server/src/api/analytics/getOverviewBucketed.ts
  • server/src/api/analytics/sessions/getSessions.ts
  • server/src/api/analytics/utils/getFilterStatement.ts
📝 Walkthrough

Walkthrough

This PR optimizes analytics performance through client-side lazy-loading of metric sections, enables ClickHouse query logging, refactors server-side aggregations to execute at the database level rather than in application code, and extends filtering capabilities with event-level safety constraints for use in database query CTEs.

Changes

Cohort / File(s) Summary
Client-side Lazy Loading
client/src/app/[site]/main/page.tsx		 Introduced LazySection component using intersection observer to defer rendering of metrics sections until visible; wrapped Referrers, Pages, Devices, Countries, Events, Weekdays, and conditionally Network/SearchConsole sections with lazy-loading containers.
ClickHouse Configuration
docker-compose.cloud.yml		 Enabled query logging by defining explicit <query_log> destination targeting system.query_log table with 7500ms flush interval, and enabled log_queries in the default user profile.
Event Aggregation Refactoring
server/src/api/analytics/events/getOutboundLinks.ts		 Migrated URL aggregation from application code to ClickHouse query: now extracts url from props, computes COUNT(*) and MAX(timestamp), groups by URL, and limits to 1000 results.
Metric Query Optimization
server/src/api/analytics/getMetric.ts, server/src/api/analytics/getOverview.ts, server/src/api/analytics/getOverviewBucketed.ts		 Added conditional query branching based on filter presence: unfiltered queries use single-scan aggregation; filtered queries retain multi-CTE structure. Replaced COUNT(CASE...) patterns with countIf() and centralized repeated variable computations (e.g., fillClause, tzEscaped).
Session Query Enhancement
server/src/api/analytics/sessions/getSessions.ts		 Introduced event-level filtering inside the AggregatedSessions CTE to reduce aggregated row volume before outer filtering.
Filter Utility Extensions
server/src/api/analytics/utils/getFilterStatement.ts		 Added includeOnly option to FilterStatementOptions for restricting which filters generate SQL; introduced INNER_SAFE_PARAMS constant and new getEventLevelFilterStatement() helper for safe event-level filtering in CTEs.

Estimated code review effort

🎯 4 (Complex) | ⏱️ ~45 minutes

Possibly related PRs

  • Track outbound events #536 — Directly refactors getOutboundLinks.ts to move aggregation from application code to ClickHouse queries, using the same pattern as this PR's metric query optimizations.
  • better filters #817 — Extends getFilterStatement.ts with session/event-level filtering configuration, complementing this PR's introduction of INNER_SAFE_PARAMS and getEventLevelFilterStatement().
  • Returning #763 — Modifies getOverviewBucketed.ts query and result shape, overlapping with this PR's metric query optimization changes.

Poem

🐰 Lazy sections hop into view with care,
Queries now aggregate with database flair,
Filters refined for the session's deep nest,
ClickHouse logs flowing—analytics blessed! ✨

✨ Finishing Touches
📝 Generate docstrings
  • Create stacked PR
  • Commit on current branch
🧪 Generate unit tests (beta)
  • Create PR with unit tests
  • Commit unit tests in branch query-optimization

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

Comment @coderabbitai help to get the list of available commands and usage tips.

Tip

You can customize the tone of the review comments and chat replies.

Configure the tone_instructions setting to customize the tone of the review comments and chat replies. For example, you can set the tone to Act like a strict teacher, Act like a pirate and more.

@goldflag goldflag merged commit 8aaf763 into master Mar 22, 2026
6 of 7 checks passed
@coderabbitai coderabbitai bot mentioned this pull request Apr 3, 2026
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

1 participant

@goldflag