About

This site stores the entire ClickHouse documentation in a ClickHouse database and queries it live. A practical reference for anyone contributing to the ClickHouse docs — useful for quickly mapping content structure, understanding coverage, and planning updates.

How This Works

Every page of the ClickHouse documentation — 2,000+ pages and 12,000+ H2 sections — has been scraped and loaded into a live ClickHouse Cloud database (GCP, us-east1). The schema has three tables:

Every page on this site is a Next.js server component that queries ClickHouse directly via @clickhouse/client. There is no intermediate API layer between the UI and the database — a read-only credential is scoped to SELECT only and never reaches the browser. Static pages (Browse, Badges, Schema) are pre-rendered at build time with daily ISR. The Search page streams results live. The SQL Explorer runs arbitrary user queries against the live database.

How I Built This

Scraping the built HTML, not the source

The ClickHouse documentation is written in MDX and assembled from two GitHub repositories: English source lives in ClickHouse/ClickHouse, and the site shell lives in ClickHouse/clickhouse-docs. English content is fetched and copied into the build at compile time and is gitignored from the docs repo entirely.

Rather than parsing MDX source — which mixes JSX component imports, shared snippet includes, remark plugins, KaTeX math, and auto-generated index pages — I build the Docusaurus site and scrape the rendered HTML. This means autogenerated settings pages and API reference, snippet-resolved content, and fully rendered math all come out cleanly. The scraper is a Python script using BeautifulSoup and markdownify.

Storing content as Markdown

Despite scraping HTML, section content is stored as reconverted Markdown to preserve elements such as backtick-wrapped code indicators, fenced code blocks with language tags, and H3/H4 heading structure within sections, while still being compact and compressing well. Pre-extracted code_blocks and code_languages columns sit alongside the Markdown for efficient querying without RegEx.

Schema design: clarity over performance

ClickHouse is columnar — a query only reads the columns it selects, regardless of how many columns the table has. Therefore, separating metadata and content into two tables was a design choice, and not required for performance optimization. The table design keeps conceptual roles clear (pages as index, sections as content store), and putting content in smaller per-section rows makes the bloom filter index more precise than it would be on full-page blobs.

Stack choices

Each scrape also produces a scrape_delta.json comparing the new snapshot against the previous one — pages added or removed, metadata changes (title, breadcrumb, badges, H2 headings), and section-level content changes identified by MD5 hash. This makes it easy to see what actually changed in the ClickHouse docs between builds.

Claude Code helped substantially with the web app — in particular the Browse page's tree-building algorithm, the SQL Explorer's drill-down and column-width logic, and several non-obvious edge cases in a docs 'index page' detection heuristic. The scraper, schema design, and data pipeline were developed more manually.

What I Learned About the Docs

The site is assembled from two separate repos

English documentation source lives in ClickHouse/ClickHouse under docs/en/. The site shell, config, and translations live in ClickHouse/clickhouse-docs. At build time, a script shallow-clones the main repo, copies the English folders in, and deletes the clone. Those folders are gitignored from the docs repo.

The sidebar is injected at runtime, not in the HTML

The left navigation panel is rendered client-side from JS bundles, not embedded in the static HTML. You can't extract the nav tree by scraping the rendered sidebar. Instead, nav hierarchy comes from two sources: breadcrumb HTML (embedded as schema.org structured data on every page) for the path, and sidebars.js — the Docusaurus sidebar config file — for sibling ordering. sidebars.js is evaluated via Node to produce a complete ordered list of all pages in nav sequence; this is the only reliable source, as the per-page previous/next JSON fields have URL inconsistencies and are absent entirely from newer sections of the docs.

Many index pages are autogenerated

A significant number of section landing pages — for aggregate functions, table engines, settings, integrations, and others — are not hand-authored. A Python script walks each directory, reads child page frontmatter, and writes a Markdown table into an index.md template between comment tags. These pages have essentially no unique prose; their content is a link-and-description table pointing to their children.

URL paths and nav positions are often unrelated

Many pages live at URLs that bear no relationship to their breadcrumb position. A page at /cloud/manage/backups appears under "Cloud > Guides > Backups" in the nav, while its child pages live at /cloud/manage/backups/.... Building the Browse view required a three-priority heuristic to reliably detect which page is the index for each section — URL prefix matching alone is not sufficient.

The nav order has more hidden structure than it appears

Docusaurus strips numeric prefixes (01_, 02_) from filenames and directory names when computing page identifiers — so cloud/onboard/02_migrate/ becomes cloud/onboard/migrate/ in every URL and reference. Some pages also have a slug frontmatter field that makes their URL completely unrelated to their file path (e.g. a file at cloud/onboard/migrate/migration_guides/bigquery/loading-data.md is served at /docs/migrations/bigquery/loading-data). The Knowledge Base is a separate Docusaurus blog plugin at a different directory entirely (knowledgebase/ at the repo root), not part of the main docs sidebar at all — it generates its own pagination and tag index pages alongside the actual articles.

The badge ecosystem is broader than it might appear

The docs use 11 distinct badge types: experimental, beta, private_preview, cloud_only, not_cloud, enterprise, scale, community, clickhouse_supported, partner, and deprecated. A handful of badge types account for most tagged pages, and the vast majority of pages carry no badge at all. The Badges page lets you slice by type and see exactly where each label appears in the nav hierarchy and within a given page.

Image handling requires careful scraper work

The site uses @docusaurus/plugin-ideal-image for responsive lazy-loading. Each <img> in the built HTML has a tiny placeholder in src (sometimes 48px wide) and the full responsive set in srcset. The scraper swaps each src to the last (largest) srcset entry and makes it absolute before Markdown conversion — otherwise stored content would have broken image links.

What I Learned About ClickHouse

MATERIALIZED columns do more work than I expected

Several derived values are stored as MATERIALIZED columns — computed automatically at insert time and kept in sync without any application logic: breadcrumb_depth (length of the breadcrumb array), nav_title (last element of the breadcrumb array, i.e., the sidebar label), section_path (page path concatenated with the section anchor), and word_count (whitespace-split token count). The scraper never computes these — they derive themselves.

ngrambf_v1 is the right bloom filter for substring search

ClickHouse offers two bloom filter index types: tokenbf_v1 indexes whole tokens (good for exact word matches) and ngrambf_v1 indexes character n-grams (good for substring matches like LIKE '%term%'). For searching documentation — where users type partial function names, mid-word substrings, or code fragments — character n-grams are the right choice. The index lets ClickHouse skip entire data granules that can't possibly match, making full-text substring search fast without a tool like Elasticsearch.

arrayElement(arr, -1) is more stable than arrayLast

In ClickHouse 26, arrayLast changed its signature: it now requires a predicate lambda as its first argument, like arrayFirst. The zero-argument form that previously returned the last element no longer works. Using arrayElement(breadcrumb_array, -1) (negative index means last element) is equivalent and stable across versions.

CREATE OR REPLACE TABLE for schema-safe reloads

When the schema needs to change — adding a column, updating a MATERIALIZED expression, changing an index — the right pattern is CREATE OR REPLACE TABLE. It atomically replaces the table definition (including all MATERIALIZED columns and indexes) and starts fresh, without a separate DROP followed by CREATE. The data pipeline uses this on every load run, so schema migrations happen automatically without manual intervention.

ARRAY JOIN makes per-badge analytics clean

Each page in docs_pages has a badges Array(String) column. Counting pages per badge type with a regular GROUP BY would require awkward subqueries or string splitting. With ARRAY JOIN, each badge value is unnested into its own row before aggregation — one clause handles the flattening that would take a window function or lateral join in other databases:

SELECT badge, count() AS page_count
FROM docs_pages
ARRAY JOIN badges AS badge
GROUP BY badge
ORDER BY page_count DESC

The Basic tier auto-idles

ClickHouse Cloud's Basic tier automatically suspends after a period of inactivity. The first query after an idle period adds a few seconds of cold-start latency while the service resumes. For a portfolio site with intermittent traffic, this is the right cost/availability tradeoff — the SQL Explorer shows a "Waking service, please wait..." message after three seconds of loading so visitors understand what's happening.

splitByWhitespace for word count

Word count is stored as a MATERIALIZED column computed via length(splitByWhitespace(section_content)). It's an approximation — punctuation-attached words count as one token, hyphenated words as two — but it's accurate enough for relative comparisons ("which pages are long vs. short?") and costs nothing to maintain.