tylerhannan.com

My static site has a database. It’s ClickHouse’s

Or…spelling, meaning, and a 256 KB budget…oh my!

2026-11-15

TL;DR

  • The ClickHouse playground takes SQL over HTTP from any website, with a read-only user and no password.
  • Send JSON along with each query using values(). Congrats, your data becomes tables.
  • Add vectors from aiEmbed. Hey presto, you get semantic search.
  • Iff0 the whole query fits in 256 KB.

I've ignored my blog for years. It ran on WordPress and mattered back when I was an evangelist in the payments industry. Not surprisingly, it went the way most old WordPress sites do. One day the page was blank, probably some broken PHP. And when I finally looked in the database, it was full of spam.

So I started again, this time as a folder of HTML and JSON files: no backend, no database. Then I was looking for a talk that I had given, and I couldn't find it.

Well, now I need search. Yay? How do I build search on a static site?

I work for ClickHouse, so…

This began an exploration that ended up with every query box on this site being real SQL, and you can run it. The ClickHouse playground takes SQL over HTTP from any website, with a read-only user and no password. I send my site's JSON along with each query using the values() table function, and ClickHouse treats that data as tables.

The client is one fetch() call:

const res = await fetch('https://play.clickhouse.com/?user=play', {
  method: 'POST',
  body: 'SELECT version() FORMAT JSONCompact',
});
console.log((await res.json()).data);  // [["26.10.3.7"]]

The playground has rules: no DDL, no inserts. So I can't put my data on the server. Nobody said I couldn't bring it with me.

That's what the values() table function is for. It turns a list of rows into a table, and wrapping it in a CTE gives the table a name. Want to try it? This runs in the playground exactly as it is:

WITH talks AS (
    SELECT * FROM values('date Date, title String, event String',
        ('2026-10-06', 'Host and MC', 'ClickHouse Open House Munich'),
        ('2026-09-30', 'Keynote: AI Is Redrawing the Database Market', 'ClickHouse Open House London'),
        ('2025-06-12', 'My Favourite Features', 'ClickHouse Meetup Berlin'))
)
SELECT toYear(date) AS year, count() AS talks
FROM talks
GROUP BY year
ORDER BY year DESC

On the site, a little script called run.js1 does that for every query box. It looks at which tables your query mentions. It loads that JSON. It turns each one into a CTE and sticks it on the front of your SQL. Then it sends the lot to the playground.

It also caps every result at 501 rows, because I know myself, and sooner or later I'd try to pull a million rows into a browser tab.

And because the boxes are real SQL, you can edit them. Group my talks by year, or ignore my data completely and query the playground's own datasets instead. SELECT count() FROM github_events counts about 11.2 billion rows. Go ahead, try it on the home page, I'll wait. Back? Beauty. That's 11 billion rows, counted from what is basically a folder of HTML files, and I'm still a little amazed that works.

The talks query edited to count the playground's github_events table, with the result underneath: about 11.2 billion rows, run on play.clickhouse.com.

Yes, I wrote about GeekTool

Then I had search, lightweight search. It was compelling, but it wasn't enough. A 404 page and broken WordPress links that match by spelling do not a useful site search make.

This is the query behind the 404 page (you can run it here if you like):

WITH 'geektool implementation' AS words SELECT round(ngramSearchCaseInsensitive(title, words), 2) AS match, title FROM pages ORDER BY match DESC LIMIT 3

Old WordPress links tend to show up with slightly wrong paths. So when one lands on the 404 page, the page turns the missing path into words and asks ngramSearchCaseInsensitive which titles look closest. Try /2012/04/geektool-implementation/ and it finds "GeekTool: Implementation Detail" at 0.85.2

The 404 page for /2012/04/geektool-implementation/: the spelling query, and its top result, "GeekTool: Implementation Detail", at 0.85.

And that's fine for most broken links. It's no help at all when someone types an idea instead of a title.

Ideas aren't titles: ClickHouse AI functions

ClickHouse has released AI functions, so I was interested in aiEmbed. I wanted semantic search on my website, and for that I needed three things: a vector for every page, a vector for whatever someone types, and a way to compare them. ClickHouse handles the first one and the last one.

The 404 page for /credit-card-fees, with the spelling query and results on top and the meaning query and results below, including "Cost of Accepting Credit Cards".

Here's what that looks like on the 404 for /credit-card-fees. Spelling finds the titles with "credit card" in them, which is fine. Meaning also finds Cost of Accepting Credit Cards, and that one doesn't say "fees" anywhere in the title. That's the bit I wanted.

Embedding the pages

aiEmbed has been in ClickHouse since 26.6, and it's still beta. You hand it some text, it sends that to an embedding model, and you get an Array(Float32) back. Anything that speaks the OpenAI API works, and you tell aiEmbed where to find it with a named collection. I used Ollama running EmbeddingGemma 2 on my laptop. Here's the heart of embed.py, which runs in clickhouse-local:

CREATE NAMED COLLECTION site_embed AS
    provider = 'openai', endpoint = 'http://localhost:11434/v1/embeddings';

SELECT path,
       arraySlice(aiEmbed('title: ' || title || ' | text: ' || text, 'embeddinggemma-2:270m-bf16-text',
                          map('credentials', 'site_embed')), 1, 384) AS v
FROM file('docs.jsonl', JSONEachRow)

On my laptop that does all 325 pages, talks, demos, photo captions, and posts I wrote for other sites in about 8 seconds. The 'title: … | text: …' bit is the prompt EmbeddingGemma expects for documents. Then the script squashes each vector down to Int8, base64-encodes it, and saves the lot as embeddings.json, right alongside the rest of the site's JSON.

Embedding whatever you type

The playground can't help with this one. aiEmbed needs a named collection on the server, and the play user, very reasonably, doesn't get one. So I wrote a small Cloudflare Worker that runs the same EmbeddingGemma 2 model on Workers AI and hands back the same Int8 format.

You may be thinking, Tyler, who cares which model does the embedding? Fair question. It matters a lot, because vectors from two different models don't line up, and then search gives you nonsense. So I use the same model on both sides, and I checked: for identical text, the vectors from Ollama and from Workers AI come out at 0.9995 cosine similarity or better. Pages embedded on my laptop and words embedded by Cloudflare really can be compared.

Ranking

Whatever you typed becomes a one-row table called request. From there, ranking every page is one query with cosineDistance:

WITH
    (SELECT embedding FROM request) AS words
SELECT round(1 - cosineDistance(embedding, words), 2) AS similarity, title, path
FROM pages
ORDER BY similarity DESC, path
LIMIT 1 BY ifNull(event, path)
LIMIT 10

The LIMIT 1 BY is there because, before I added it, the nearly identical "ClickHouse release calls" talks took four of the ten results for "streaming data into ClickHouse". I do a lot of release calls, apparently enough to crowd out everything else.

In practice, your browser does the same math itself from embeddings.json, which is why results show up right away. If you press run, the exact query goes off to the playground instead, and you get the same rows back. I use that a lot to make sure my JavaScript and ClickHouse agree.

256 KB is a budget

Bringing your own data does have a limit, though. max_query_size on the playground is 262,144 bytes, and every byte of data I send along counts toward it. More dimensions usually means better search…but it also means more bytes. EmbeddingGemma gives you 768 dimensions, and it's trained so you can chop off the end and the first part still works on its own.

So I started small, with 128 dimensions. Then I searched "neon" and got a photo of playing cards first, an old blog post second, and an actual neon sign third, which is not what anyone searching for neon wants.

Granted, that's one search, which is hardly science, but it was enough to send me back for more dimensions.

DimensionsTop results for "neon" (the first EmbeddingGemma)Fits in one query?
128Wall of Playing Cards, an old blog post, LB's Record BarYes
384LB's Record Bar, Never Stop ExploringYes, about 200 KB
768LB's Record Bar, Never Stop ExploringNo

The photos page searched for "neon": the grid reordered with lit signs first, LB's Record Bar and Never Stop Exploring.

So I went with 384. That's one Int8 byte per dimension, base64-encoded, and the whole pages table comes to about 200 KB. ClickHouse unpacks it right inside the CTE:

SELECT * EXCEPT (e),
       arrayMap(i -> toFloat32(reinterpretAsInt8(substring(base64Decode(e), i, 1))),
                range(1, length(base64Decode(e)) + 1)) AS embedding
FROM values('path String, title String, e String', ...)

And because cosine distance doesn't care how long a vector is, squashing it to Int8 only costs a little precision.

Then, right before I published this, I found out Cloudflare was retiring that model. Its replacement, EmbeddingGemma 2, finds three lit signs for "neon" even at 128 dimensions. Of course it does. I kept 384 anyway, because it still tracks the full 768 more closely across the other searches I tried, and the screenshot above is EmbeddingGemma 2's answer.

That was plenty of room until I went looking for old talks and posts. Every search sends the whole pages table along with it, and it's now about 208 KB of the 256 KB. I'll have to try smaller vectors soon, or speak and write less. run.js now checks the size before it sends anything, so a query that's too big gets a plain "not sent" instead of an error from the playground.

Things I learned the hard way

First, remember the playground is shared. There are quotas, and every visitor's query counts against them, including mine. That's why my queries are small and run.js caps results at 501 rows.

Also, aiEmbed is still in beta, so before you copy my SQL, check the argument order for your version. In 26.11 it's aiEmbed(text, model[, params]).

Then there's clickhouse-local, which remembers things. It keeps your tables and named collections between runs. Older versions threw everything away, so if you're used to that, a script that runs CREATE twice will fail the second time. It all lives in ~/Library/Application Support/clickhouse-local on macOS (~/.local/share/clickhouse-local on Linux), and --tmp gets you the old throwaway behaviour back.

And if your browser is doing the ranking, like mine is, the JavaScript has to round, order, and break ties exactly the way the query does. Otherwise the run button (text, css element, whatever) gives you different answers than the page. When I'm not sure, I press it and compare.

Oh, and check how much of each page you're actually embedding. I was cutting every post at 2,000 characters, which felt like plenty. It wasn't. 120 of my 193 old posts are longer than that, so search never saw how most of them ended. So I took a sentence from the end of 30 long posts and searched for it. With the cut, the right post came back first 8 times, and its median rank was 20th. With the whole post, it came back first 12 times, and the median rank was 2nd. Thirty sentences is hardly science, but I embed the whole thing now. The vectors are the same size either way, so it costs nothing against the 256 KB.

Finally, the search box on the writing page only covers the old posts that live on this site. The posts I wrote for other sites are listed too, but all I have for them is a title. So /search/ can match them by title, not by what they actually say.

One small Worker

So that's it. The site is still a folder of files, and every query on it runs on the playground. You can do all of this right now. Please go break it.

The code is all on GitHub: static files and one small Worker.

There's still a script on my laptop, and a second copy of the model behind the Worker. I'd like to get rid of both. It could all be SQL, and I'd like that.

0 Yes, iff. If and only if. That's maths, not database talk, but formal proofs and relational algebra are close cousins, so I'm keeping it.3

1 "Little" is doing some work there. It's 170 lines.

2 It was 2012. Don't judge me.

3 Yes, a footnote on a footnote. And yes, I zero-index.

Published 2026-11-15. The code as this post describes it is the playground-search tag.

:) WITH (SELECT embedding FROM posts WHERE path = '/2026/11/my-static-site-has-a-database/') AS this_post SELECT round(1 - cosineDistance(embedding, this_post), 2) AS similarity, title, path FROM posts WHERE path != '/2026/11/my-static-site-has-a-database/' ORDER BY similarity DESC, path LIMIT 5;
similaritytitlepath
0.69“The best laid schemes…/2009/01/“the-best-laid-schemes…/
0.69Syntax Highlight: a test/2010/07/syntax-highlight-a-test/
0.69Medieval Art, Collective Intelligence, and Language Abuse/2010/11/medieval-art-collective-intelligence-and-language-abuse/
0.68Chris Anderson: The Economics of Abundance/2008/02/chris-anderson-the-economics-of-abundance/
0.68A return to blogging/2009/11/a-return-to-blogging/

5 rows in set. Each post's embedding is from EmbeddingGemma 2, made with ClickHouse's aiEmbed.

Contact

tyler@tylerhannan.com linkedin x / twitter