When we started using DuckDB more heavily at Altertable, we quickly discovered the NetQuack (community-maintained) extension for URL parsing. While functional, its performance was... disappointing. Extracting domains from URLs was taking 37 seconds for 30,000 URLs. For a database that prides itself on analytical performance, this felt wrong.
After digging into the code, the culprits were obvious: heavy reliance on std::regex for parsing and database queries to validate TLDs against the Public Suffix List. Inspired by how ClickHouse handles URL parsing, we rewrote the entire extension. This was one of 17 upstream contributions we made in 90 days. The results? Up to 4000x performance improvements across all functions.
What Was Wrong
The original NetQuack implementation had two major performance killers. First, every URL component extraction used std::regex:
Second, TLD extraction required querying a database table populated from the Mozilla Public Suffix List:
This meant every domain extraction triggered multiple database queries. For analytical workloads processing thousands of URLs, this was a disaster.
The ClickHouse-Inspired Solution
We completely rewrote the extension using character-by-character parsing instead of regex:
This approach uses std::string_view for zero-copy operations, processes one character at a time with simple switch statements, and avoids regex compilation overhead entirely.
Then, the bigger win was eliminating database dependencies. We use gperf to generate a perfect hash function from Mozilla's Public Suffix List:
This creates a collision-free hash function compiled directly into the extension binary. Now TLD lookups are O(1) memory operations instead of database queries:
The perfect hash table adds only ~500KB to the binary size but eliminates runtime PSL downloads, database table storage, query execution overhead, and memory allocations for temporary strings.
The Results
We benchmarked both versions against 30,000 URLs. The improvements were staggering:
| Function | Before | After | Improvement |
|---|---|---|---|
extract_schema | 0.069s | 0.010s | 6.9x faster |
extract_host | 0.086s | 0.008s | 10.7x faster |
extract_port | 0.533s | 0.009s | 59.2x faster |
extract_path | 0.127s | 0.008s | 15.8x faster |
extract_query_string | 0.202s | 0.004s | 50.5x faster |
extract_domain | 37.503s | 0.012s | 3125.2x faster |
extract_subdomain | 35.831s | 0.010s | 3583.1x faster |
extract_tld | 36.003s | 0.009s | 4000.3x faster |
extract_extension | 0.329s | 0.007s | 47.0x faster |
The TLD-dependent functions saw the most dramatic improvements because they eliminated both regex parsing AND database queries. The architecture change was fundamental:
- Before:
URL Input → Regex Parsing → Database Query → Result - After:
URL Input → Character Parsing → Perfect Hash Lookup → Result
The old implementation had O(n) regex compilation per URL plus O(log n) database queries with disk I/O. The new implementation has O(n) single-pass character scanning with O(1) perfect hash lookups in memory.
And despite the complete rewrite, all existing tests pass and edge cases are handled correctly.
Key Takeaways
Profile before optimizing. The 37-second domain extraction time immediately pointed to the real bottlenecks. Always measure first.
Eliminate I/O in hot paths. Database queries in URL parsing functions were the primary performance killer. Moving to compile-time data structures eliminated this entirely.
Character parsing beats regex for simple patterns. For structured data like URLs, character-by-character parsing is often faster and more predictable than regex.
Perfect hashing works great for static data. When you have a known, static dataset (like TLDs), perfect hash functions provide optimal lookup performance.
Zero-copy string operations matter. Using std::string_view eliminated unnecessary string copying, especially important for analytical workloads.
Try It Yourself
The optimized NetQuack extension is currently under review in Pull Request #13. While we wait for the maintainer to review and hopefully merge these changes, you can:
-
Test the optimized version by building from our branch:
-
Run the benchmarks yourself to see the performance improvements:
-
Check out the source code on GitHub to see the detailed implementation.
This optimization journey reminded us why performance matters in analytical databases. When you're processing millions of URLs, the difference between 37 seconds and 0.012 seconds isn't just a nice-to-have—it's the difference between interactive analysis and waiting for coffee to brew.
Sometimes the best optimization is throwing out the old approach entirely and starting fresh with the right architectural principles. In this case, ClickHouse showed us the way.
