Querying a web crawl from a small app

You can query Common Crawl from an ordinary small app: DuckDB reads the published Parquet and text files over HTTPS, so there is no cluster, no data warehouse and no local copy. The work is choosing the right files, keeping the query inside the container's memory, and treating a multi-minute scan as a background job rather than a web request.

This page is about a website that searches Common Crawl and then asks who links to a result. The same shape applies to any app that wants web data for building RAG indexes, sampling pages, or analysing links.

What Common Crawl publishes

Two separate datasets: a URL index in Parquet, and a hyperlink graph in text files. They are published on data.commoncrawl.org and are free to read.

The URL index is "a tabular/columnar index (Parquet format) to the Common Crawl archives", partitioned by two columns, crawl and subset, where subsets include warc, robotstxt and crawldiagnostics (Common Crawl, index table, checked 2026-11-09). It carries the URL, host and domain columns, fetch time and status, detected MIME type, language fields, and the location of the record inside the WARC file. Common Crawl's announcement of the format says "the index table of a single monthly crawl has about 300 GB" (Common Crawl blog, checked 2026-11-09). You read a few of those gigabytes, not all of them.

The hyperlink graph comes in host-level and domain-level releases. Hostnames are stored "in reverse domain name notation"; all link types are counted, including links to images and scripts; and "the domain graph is built by aggregating the host graph at the pay-level domain (PLD) level" using the public suffix list (Common Crawl, web graphs, checked 2026-11-09). The machine-readable list of releases, newest first, is https://index.commoncrawl.org/graphinfo.json.

The graph and each crawl are immutable once published. That matters later: it makes every answer cacheable.

Read it without copying it

DuckDB's httpfs extension turns an HTTPS URL into a readable file, so a query can open a Parquet part without downloading it first. The extension provides "a file system that allows reading remote/writing remote files", and for plain HTTP(S) "only file reading is supported" (DuckDB docs, httpfs, checked 2026-11-09). It loads itself on first use.

Two practical details:

In SQL, the shape is:

SELECT url, fetch_status, content_mime_detected, fetch_time
FROM read_parquet($files, hive_partitioning = true)
WHERE subset = 'warc'
  AND url_host_tld = $tld
  AND url_host_registered_domain = $domain
LIMIT $limit;

Bind the file list and filters as parameters rather than pasting them into the string.

Keep the query inside a small memory limit

Set memory_limit below the container's memory and raise it only when a query needs it. DuckDB's limit defaults to 80% of the machine's RAM, which on a shared host can be far more than the container is allowed; SET memory_limit = '256MB' makes the database spill or fail instead of being killed. threads defaults to the number of CPU cores and can be lowered the same way (DuckDB docs, configuration, checked 2026-11-09).

The failure to recognise is not "the query is slow" but an out-of-memory error on a join. A query that joins a huge lookup table to a filtered result makes DuckDB build a hash table for the whole lookup side, even though the answer needs a handful of rows. In this project, joining the graph's edge table to its full vertex table exhausted a 256 MB limit; the same data fitted once the query stopped doing that.

The fix is a two-step query:

  1. Aggregate first. Group the large table down to the few identifiers you actually need — for example, the top 200 sites linking to one domain — and keep only ids and counts.
  2. Resolve the small set second. Look up names for just those ids with WHERE id IN (...), accepting a scan of the name table but holding only a couple of hundred rows in memory.

This "aggregate, then resolve" pattern works whenever the lookup table is bigger than the answer: spend one pass counting, then one bounded lookup, instead of a hash join over everything.

Make a slow scan a job, not a request

A remote scan can run for minutes, so start it, return an id, and let the page poll for progress. Browser and proxy timeouts are measured in seconds; a DuckDB query over the graph is not. Running the query inside the request handler also makes cancelling impossible, because nobody is listening.

A small in-process job manager is enough: one running job at a time, a status of running, done, error or cancelled, a stage number, a human message, and elapsed time. The page polls GET /api/jobs/:id once a second and draws a progress bar. For cancellation, keep the DuckDB connection and call interrupt(); check the job's cancelled flag between stages so a cancel that lands between queries is not lost.

Say what the wait means. "Scanning the graph edge file. This can take several minutes" tells a visitor more than an animated bar with no words.

Cache finished answers by release

Because a crawl and a graph release never change, a result keyed by release and query stays correct forever. Write each finished answer to its own JSON file, keyed by a hash of crawl | domain | limit for searches and release | domain for link lookups, and write atomically (temporary file, then rename) so a crash cannot leave half a record. On a host with a volume, put that directory on the volume so it survives redeploys.

The first query for a domain pays the scan; every repeat returns immediately. A small admin page that lists entries with sizes and can delete them is worth the hour it takes, especially when the volume is small and an oversized cache should be trimmed rather than rediscovered.

What to watch

See also

Glossary terms used on this page: Container, Host, RAG.

Sources

All checked on 2026-11-09.


First version written with AI assistance, from a session that built a Common Crawl search app in a small container, hit the join's out-of-memory error, and replaced it with an aggregate-then-resolve query, and checked against the sources on 2026-11-09. Found a mistake? Fix it or open an issue. Text licensed under CC BY 4.0.