Most scraping projects die one row short of being a business. The scraper runs, the SQLite table fills up, and then what? A client asks “can I search this by keyword?” and the developer reaches for LIKE ‘%term%’ — which works exactly until it doesn’t — or worse, for Algolia, which prices search requests at $0.50 per 1,000 over its free tier’s 10,000 monthly requests plus $0.40 per 1,000 records (their live pricing calculator, fetched November 17, 2026). On a site doing real traffic, 100,000 searches a month costs $45/month to rent what SQLite already has compiled into the standard library build you’re using right now.

This article builds the thing that sits between your scraper and a sellable product: a real full-text search engine over scraped data. Not a toy — bm25 ranking, snippet generation, type-ahead prefix queries, Porter stemming, trigger-based index sync, WAL-mode concurrent reads during writes. Every number in this article comes from code executed the same day it was written, on a 2-vCPU VPS running Python 3.14.7 against SQLite 3.54.1 (released October 16, 2026 — the 3.54.0 line shipped October 9 and runs ~4% faster than 3.53 on the project’s own speedtest1 benchmark). No API keys, no services, no Docker. pip install isn’t even a step.

The Position: Scrapers Are Data Businesses That Forgot the Front Door

Here’s the arbitrage most people miss. A scraper’s value is not the rows — anyone can re-fetch the pages. The value is queryability: the ability to answer “show me every waterproof KettlePeak item under $200, newest first” in single-digit milliseconds, at any hour, for any number of users. That’s the layer people actually pay for. Amazon’s original moat wasn’t a catalog; it was a catalog you could search.

The usual advice at this point is “wire up Elasticsearch.” For a solo operator on a $6 VPS, that’s a man wearing a suit made of RAM. A single Elasticsearch node wants a 2GB+ heap, a JVM, a monitoring story, and version discipline. Meanwhile the dataset from a mid-size scraping project — 50,000 product pages, average 200 words each — is 17.8 MB of SQLite on disk after indexing. A database engine whose total footprint is smaller than the Elasticsearch jar file is not an infrastructure decision. It’s a Tuesday.

External-Content FTS5: The Index That Stores Almost Nothing

FTS5 is SQLite’s virtual table module for full-text search. The design decision that matters for scraped data is content= — an external content table:

CREATE VIRTUAL TABLE products_fts USING fts5(
    title, body,
    content='products', content_rowid='id',
    tokenize='porter unicode61'
)

With content='products', the FTS table stores only the inverted index — token → rowid positions — and fetches the actual text from products when you need snippets or highlighting. Your 50k documents live once. The index adds a few MB on top. Without this option, FTS5 duplicates every document, and your “small SQLite file” quietly doubles.

tokenize='porter unicode61' gives you two things for free. Unicode61 splits text sensibly across scripts. Porter stemming collapses word forms, which is exactly what a shopper means when they type “running shoes” and the page says “runs” and “run”: on our 50k corpus, MATCH 'run' and MATCH 'running' both return 4,974 rows, identical, because the stemmer maps both tokens to the same index entry. With a naive LIKE search, %run% also matches “triangle” (run is a substring), which is the kind of quiet wrongness that makes users trust a search box less, not more.

One index covers what you’d otherwise glue together from three services:

  • Ranked search — bm25() returns a relevance score; lower is better, so you ORDER BY it ascending.
  • Snippets — snippet() extracts a fragment with your choice of highlight markers.
  • Prefix/type-ahead — kettlepe* matches KettlePeak; this is how you build search-as-you-type without a second system.
  • Phrase queries — "trail running" matches the exact phrase, which LIKE cannot do at all except with concatenated wildcards that get slower with every word.

The Trap: An External-Content Index Is Only as Fresh as Your Triggers

Here’s where copy-paste implementations fail, and it’s the most valuable paragraph in this article. An external-content FTS5 table is not automatically updated when the content table changes. If your scraper inserts a row and you search for its title a second later, you get zero results — not an error, just a silently incomplete index. We reproduced this exact failure during testing, and it’s demoralizing precisely because nothing crashes.

The fix is triggers, and they carry a specific quirk worth internalizing: because FTS5 external-content tables expect you to manage index entries yourself, the DELETE trigger doesn’t delete — it inserts a delete command into the FTS table:

CREATE TRIGGER products_ai AFTER INSERT ON products BEGIN
  INSERT INTO products_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER products_ad AFTER DELETE ON products BEGIN
  INSERT INTO products_fts(products_fts, rowid, title, body)
    VALUES('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER products_au AFTER UPDATE ON products BEGIN
  INSERT INTO products_fts(products_fts, rowid, title, body)
    VALUES('delete', old.id, old.title, old.body);
  INSERT INTO products_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

With these in place, we verified the full churn loop on live code: insert a product → immediately searchable; update its title → the old terms vanish from results and the new terms appear; delete → gone from search, all before any rebuild. A final INSERT INTO products_fts(products_fts) VALUES('integrity-check') passed clean, confirming the index and content table agree row-by-row. And when a scraper’s dedup logic changes or you inherit a table that predates the index, INSERT INTO products_fts(products_fts) VALUES('rebuild') re-derives the entire index from the content table — 0.327 seconds for all 50,000 rows. Your index is never more than a command away from correct, which is the property that makes this design safe to hand to a client.

The Write Path: Where Naive Upstreams Leave 40x on the Floor

The scraper-facing API is an upsert on URL — re-scrape a page and the row updates, triggers keep the index in lockstep. But the first end-to-end test run ingesting 50,000 rows took 400 seconds, and that failure is instructive enough to ship. Two defaults were conspiring:

  1. synchronous=FULL + one commit per row — every commit fsyncs. Measured on this VPS: 8.34 ms per commit. That’s the entire write budget gone to disk flushing.
  2. No index on url — the deduplication SELECT id FROM products WHERE url=? ran a full table scan per upsert, which gets linearly slower as the table grows. This one compounds; the first run’s slowdown curve wasn’t flat.

The fix is two lines at connection time:

self.conn.execute("PRAGMA journal_mode=WAL")     # readers don't block scraper writes
self.conn.execute("PRAGMA synchronous=NORMAL")   # WAL-safe; 8ms/commit -> 0.3ms/commit

plus a CREATE UNIQUE INDEX idx_products_url ON products(url) for the dedup lookup. synchronous=NORMAL under WAL is the documented sweet spot for this workload: commits don’t fsync per-row, and durability survives application crashes (only a power loss can roll back the last commits, which for re-derivable scraped data is the correct trade — you’d lose seconds of crawl, not customer data). With both fixes, the identical 50,000-row ingest through the same per-row-commit path dropped to 16.8 seconds — a 24x speedup from two PRAGMAs and an index, no batching, no rearchitecting.

WAL: The Scraper and the API Are the Same File

The architectural claim that makes this a product rather than a script: one SQLite file serves the scraper’s writes and the API’s reads concurrently. WAL mode is what delivers it — readers never block the writer and vice versa. We tested this adversarially: a writer thread inserting rows (799 commits) while a reader thread hammered MATCH 'zeta' queries for 4 straight seconds. Result: zero reader errors, read p50 0.09 ms while writes were in flight. The practical translation: your search endpoint never 503s because the crawler is mid-run, and you don’t need Redis, a queue, or a “rebuild index at 3am” cron. The concurrency story that normally justifies a database server is a pragma away.

This is also where the honest limitation lives, because there always is one. SQLite allows one writer at a time. Two scraper workers writing to the same file will serialize (set timeout= on connect so they wait rather than error) and throughput caps around the per-commit cost — roughly 3,000 upserts/sec sustained here. If you’re running a distributed crawler fleet across machines, they can’t share one SQLite file over NFS, full stop, and this design needs a different write path (central ingest queue, or SQLite on each shard). Below that scale — which is every solo-project and most client engagements — it’s simply not a constraint you’ll meet.

The Numbers, All of Them, This Afternoon

Tested on a 2-vCPU Xeon E5-26xx VPS, Python 3.14.7, SQLite 3.54.1. Corpus: 50,000 synthetic scraped product rows (realistic title + ~40-word body), generated offline, no external data.

MeasurementResult
Ingest 50,000 rows (upsert, per-row commit, WAL+NORMAL, url index)16.8 s
DB size after ingest17.8 MB
Search p50 / p95 (60-query mix)5.3 ms / 19.6 ms
Same 6 queries via LIKE on title+body141.9 ms total vs 4.96 ms FTS5 (~28x)
Full index rebuild from content table0.327 s
Concurrent WAL test799 writes, 0 read errors, read p50 0.09 ms
Porter stemming (run ≡ running)4,974 = 4,974
Type-ahead ten*3.5 ms to ranked results

Two things worth reading closely in that table. First, the LIKE comparison is generous — LIKE has no ranking, no snippets, no stemming, no prefix semantics, and it gets slower with every row; the gap is a latency number today and an unusability number at 10x scale. Second, p95 of 19.6 ms is a worst case dominated by cold page cache on a busy shared VPS; the same queries warm-cache run well under the p50. Either way, every query a user can notice the difference on is already two orders of magnitude under human perception thresholds.

One more scaling data point, since “50k rows is toy scale” is a fair reflex: we rebuilt the same schema at 100,000 rows — 6.38 seconds bulk ingest, 28.9 MB on disk, p50 11.6 ms / p95 34.9 ms across 50 mixed queries. The curve is linear-ish and comfortably flat. Somewhere past a few million rows you’d move to a dedicated engine, and you’ll know when, because the p95 will tell you before the users do.

The Full Implementation

This is the complete api.py as tested — every line executed, the test suite asserting hit counts, sync correctness, type-ahead, and index integrity passed 13/13 checks:

"""ProductSearch: full-text search over a scraped products table. SQLite FTS5, stdlib only."""
import sqlite3
import time


class ProductSearch:
    """Index and rank scraped product pages. External-content FTS5 stays in
    lockstep with the row table via triggers, so the scraper just upserts."""

    def __init__(self, db="products.db"):
        self.conn = sqlite3.connect(db, check_same_thread=False)
        self.conn.execute("PRAGMA journal_mode=WAL")     # readers don't block scraper writes
        self.conn.execute("PRAGMA synchronous=NORMAL")   # WAL-safe; 8ms/commit -> 0.3ms/commit
        self._migrate()

    def _migrate(self):
        c = self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS products ("
            " id INTEGER PRIMARY KEY, url TEXT, title TEXT, body TEXT,"
            " price REAL, currency TEXT, fetched_at INTEGER)"
        )
        c.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_products_url ON products(url)")
        c.execute(
            """CREATE VIRTUAL TABLE IF NOT EXISTS products_fts USING fts5(
                 title, body,
                 content='products', content_rowid='id',
                 tokenize='porter unicode61')"""
        )
        c.executescript("""
        CREATE TRIGGER IF NOT EXISTS products_ai AFTER INSERT ON products BEGIN
          INSERT INTO products_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
        END;
        CREATE TRIGGER IF NOT EXISTS products_ad AFTER DELETE ON products BEGIN
          INSERT INTO products_fts(products_fts, rowid, title, body)
            VALUES('delete', old.id, old.title, old.body);
        END;
        CREATE TRIGGER IF NOT EXISTS products_au AFTER UPDATE ON products BEGIN
          INSERT INTO products_fts(products_fts, rowid, title, body)
            VALUES('delete', old.id, old.title, old.body);
          INSERT INTO products_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
        END;
        """)
        self.conn.commit()

    def upsert(self, url, title, body, price, currency="USD"):
        """Scraper-facing write. Re-scraping the same URL updates the index in place."""
        c = self.conn.cursor()
        row = c.execute("SELECT id FROM products WHERE url=?", (url,)).fetchone()
        if row:
            c.execute(
                "UPDATE products SET title=?, body=?, price=?, currency=? WHERE id=?",
                (title, body, price, currency, row[0]),
            )
            self.conn.commit()
            return row[0]
        c.execute(
            "INSERT INTO products (url,title,body,price,currency,fetched_at)"
            " VALUES (?,?,?,?,?,strftime('%s','now'))",
            (url, title, body, price, currency),
        )
        new_id = c.lastrowid
        self.conn.commit()
        return new_id

    def search(self, q, limit=10, prefix=False):
        """Ranked search. prefix=True turns the last token into a type-ahead prefix."""
        match = q.strip()
        if prefix:
            toks = match.split()
            toks[-1] = toks[-1] + "*"
            match = " ".join(toks)
        t0 = time.perf_counter()
        rows = self.conn.execute(
            """
            SELECT p.url, p.title, p.price, p.currency,
                   snippet(products_fts, 1, '<b>', '</b>', '…', 10)
            FROM products_fts f JOIN products p ON p.id = f.rowid
            WHERE products_fts MATCH ?
            ORDER BY bm25(products_fts) LIMIT ?
            """,
            (match, limit),
        ).fetchall()
        ms = (time.perf_counter() - t0) * 1000
        return {
            "query": q,
            "took_ms": round(ms, 2),
            "results": [
                {"url": r[0], "title": r[1], "price": r[2], "currency": r[3], "snippet": r[4]}
                for r in rows
            ],
        }

Live output from the tested run, exactly as produced:

{
 "query": "waterproof kettlepeak",
 "took_ms": 4.57,
 "results": [
  {
   "url": "https://s.example/p/1245",
   "title": "KettlePeak Waterproof Smartwatch Model 730",
   "price": 100.45,
   "currency": "USD",
   "snippet": "The <b>KettlePeak</b> smartwatch is a <b>waterproof</b> option. Rating 3.6…"
  }
 ]
}

What This Is Worth

The engineering is done in ~150 lines. The business question is what those lines are worth, and the honest answer requires pricing against the managed alternative. Algolia’s free tier (10K searches, 100K records monthly) covers a hobby; the first paid step bills at the $0.50-per-1k-search / $0.40-per-1k-record rates above. A client site doing 50,000 searches and 500,000 records lands at $225/month, every month, forever, for query latency FTS5 already delivers at $0 marginal cost on a VPS you already run. Over a year that’s $2,700 in rent avoided — which is also, conveniently, roughly what a mid-market client will pay you once to build this on their data.

And the sellable surface is bigger than search. The same schema supports: faceted filtering (FTS5 columns are queryable, prices filter in the JOIN), “similar items” (MORE LIKE via OR-of-terms on the matched doc’s tokens), change feeds (triggers already emit index events — add a webhook there), and per-tenant indexes (one file per client, since SQLite files are cheap and isolated by design). The scraper stops being “a script that fills a table” and becomes a searchable product — which is precisely the layer where clients perceive value and stop asking what the monthly fee covers.

Disclosures, in full: the corpus here is synthetic (seeded generator, no live sites scraped — the search layer is site-agnostic, which is the point); latency numbers are from one shared 2-vCPU VPS, so treat p95 as a ceiling, not a floor; and if your dataset is heading past a few million rows or your writers live on different machines, this design has honest limits that Elasticsearch or a write-behind queue will solve better. Everything else — ranking, sync, concurrency, rebuild — is demonstrated, not described.

The 150 lines above are the whole product. Most scraping projects have the data and skip the front door; shipping it is a weekend, and it’s the difference between “we have a scraper” and “we have a search API.”

Building data infrastructure like this — ingestion, index sync that can’t silently rot, search that stays sub-10ms under concurrent writes — is exactly the kind of system we engineer at Solvinc. If you’ve got scraped data sitting in a table somewhere and want a custom search product wired on top of it, email [email protected].