Forem: Sylvain Artois

Full-Text Search on a 2 GB PostgreSQL Instance

Sylvain Artois — Tue, 12 May 2026 08:46:55 +0000

AFK.live aggregates headlines from 250+ news sources. I needed search. I had a 2 GB database and no budget for Elasticsearch.

Fair warning: this article goes deep into PostgreSQL internals — query plans, GIN indexes, trigram scoring. I've added links throughout for context. If you're not familiar with these concepts, the links are there for you. If you are, skip them and enjoy the war stories.

The Constraint

AFK runs on a Scaleway managed PostgreSQL instance — the smallest tier they offer:

DB-PLAY2-PICO: 1 vCPU, 2 GB RAM
Cost: roughly 70% of the entire AFK hosting budget (50€)

No PGVector. No Elasticsearch. No Typesense. Just PostgreSQL 16 with the pg_trgm and unaccen extensions, which Scaleway includes by default on managed instances.

The headlines table has grown past 1 million rows. I needed fuzzy search that handles French accents, returns results in under 300ms, and doesn't bring the tiny instance to its knees.

Here's how I got there — and the bugs I hit along the way.

Building the Foundation: A Materialized View

Searching directly against the headlines table would mean joining with sources on every query, and computing lower(unaccent(title)) at query time for every candidate row. On a 1 vCPU machine, that's not going to work.

The solution is a materialized view that pre-computes the normalized search field:

CREATE MATERIALIZED VIEW IF NOT EXISTS headlines_search AS
SELECT
    h.id,
    h.title,
    h.url,
    h.published_at,
    h.source_slug,
    lower(unaccent(h.title)) as search_title
FROM headlines h
JOIN sources s ON h.source_slug = s.slug
WHERE
    s.exclude_from_search = false
    AND h.published_at IS NOT NULL;

search_title is pre-lowercased and unaccented at materialization time, not at query time. The view also filters out sources I've flagged as excluded (far-right sources).

On top of this, three indexes:

-- GIN trigram index for fuzzy text matching
CREATE INDEX idx_headlines_search_title_gin
ON headlines_search USING gin(search_title gin_trgm_ops);

-- For chronological ordering and date range filters
CREATE INDEX idx_headlines_search_published_at
ON headlines_search(published_at DESC);

-- For source filtering
CREATE INDEX idx_headlines_search_source_slug
ON headlines_search(source_slug);

The GIN trigram index is the core of the search: it breaks every search_title into 3-character grams and indexes them, enabling fuzzy matching without full table scans.

Refreshing the View

A materialized view is a snapshot. New headlines are invisible until you refresh it. AFK's CI/CD pipeline deploys every two hours, so I added the refresh as the first step of the deployment script:

# deploy.legacy.sh — runs before Astro build
env PGPASSWORD="${SCALEWAY_DB_PASSWORD}" psql \
  -h "${SCALEWAY_DB_HOST}" \
  -p "${SCALEWAY_DB_PORT}" \
  -U "${SCALEWAY_DB_USER}" \
  -d "${SCALEWAY_DB_NAME}" \
  -c "REFRESH MATERIALIZED VIEW headlines_search;" \
  && echo "headlines_search refreshed successfully" \
  || echo "headlines_search refresh failed, continuing with stale data"

A note on REFRESH MATERIALIZED VIEW CONCURRENTLY: it exists, and I have the unique index it requires. But over the remote connection to Scaleway's managed instance, the concurrent refresh kept hitting SSL EOF errors on longer operations. The non-concurrent variant works reliably — but it locks the view for the duration of the refresh. During that lock, any search query hitting the view will fail. We deploy during business hours, so this is a real trade-off: a few seconds of downtime every two hours. On a site with a few concurrent users, that's acceptable. At larger scale, you'd want to schedule refreshes overnight and accept 24 hours of staleness — or fix the SSL issue and use CONCURRENTLY.

Bug #1: The View That Was Never Refreshed

The search page went live. It worked. Then a user pointed out that no results appeared after November 2025.

The materialized view had never been refreshed since its creation. It had 357K rows. The headlines table had over 1 million. Two-thirds of the articles were invisible to search.

The frustrating part: I had planned for this. The git history tells the story. The initial commit (October 2025) didn't just create the view — it also created a refresh_headlines_search() PL/pgSQL function and configured two Ofelia scheduler jobs in scheduler-config.ini: one for weekdays (refreshing nine times a day, from 6:15 to 22:15 CET) and one for Sundays (five times a day). The infrastructure was designed correctly.

But the scheduler jobs referenced environment variables (${SCALEWAY_DB_ADMIN_PASSWORD}) that were never injected into the container. The jobs ran on schedule, failed silently, and nobody noticed for five months. The refresh_headlines_search() function I carefully wrote in the migration? Never called once.

Twenty years of engineering experience, and I forgot to check that my cron jobs actually worked. The fix was the deploy script change above — crude but reliable, because it runs in the same environment that already has the database credentials.

The lesson: materialized views are a pit of success when they work, and a silent disaster when they go stale. There's no built-in warning. If you use them, monitor the row count or the most recent published_at — don't assume your refresh mechanism works just because it exists.

Bug #2: The 4.4-Second Query

After the refresh brought the view to ~966K rows, the next problem appeared immediately. Searching "donald trump" took 4.4 seconds and triggered an HTTP 504 timeout. (The short timeout is intentional — I'd rather show an error than let a slow query go unnoticed. If search is too slow, I want to know about it before users start complaining.)

Here's what the original query looked like:

SELECT hs.id, hs.title, hs.url, hs.published_at,
       s.name, s.logo, s.url, s.slug,
       similarity(hs.search_title, lower(unaccent($1))) as score
FROM headlines_search hs
JOIN sources s ON hs.source_slug = s.slug
WHERE hs.search_title % lower(unaccent($1))
ORDER BY hs.published_at DESC, score DESC
LIMIT 50;

The % operator is pg_trgm's similarity operator. It uses the GIN index. Sounds efficient. Let's look at EXPLAIN ANALYZE:

Bitmap Index Scan on idx_headlines_search_title_gin
  Index Cond: (search_title % lower(unaccent('donald trump')))
  rows=55508

Bitmap Heap Scan on headlines_search hs
  Rows Removed by Index Recheck: 55351
  Heap Blocks: exact=32113
  Execution Time: 4443ms

The GIN index returned 55,508 candidates. After the recheck filter, only 157 survived — 99.7% noise. PostgreSQL had to load 32,113 heap blocks (a heap block is an 8 KB page of actual table data — 32K blocks means ~250 MB of disk reads) to evaluate the similarity predicate on each candidate. On a 2 GB instance, that's a significant chunk of available memory spent on I/O.

The problem is how GIN trigram indexes work: they find all rows that share any trigrams with the query. For a common term like "donald trump", that's a lot of rows.

The Fix: `strict_word_similarity`

PostgreSQL's pg_trgm extension offers three similarity functions, and they behave very differently:

Function	What it measures	Operator
`similarity(a, b)`	How similar the full strings are	`%`
`word_similarity(a, b)`	Whether `a` matches any word in `b`	`<%`
`strict_word_similarity(a, b)`	Whether `a` matches a contiguous word sequence in `b`	`<<%`

similarity compares the entire query against the entire title — terrible for short queries against long titles. word_similarity is more lenient but still too broad. strict_word_similarity requires the query to match a contiguous sequence of words in the title. That's exactly what you want when someone searches for a proper noun like "donald trump" or "réchauffement climatique".

The <<% operator also changed the query plan. Instead of the GIN bitmap scan, PostgreSQL used an incremental sort on the published_at index — scanning from most recent to oldest and stopping once it had enough results:

SELECT hs.id, hs.title, hs.url, hs.published_at,
       s.name, s.logo, s.url, s.slug,
       strict_word_similarity(lower(unaccent($1)), hs.search_title) as score
FROM headlines_search hs
JOIN sources s ON hs.source_slug = s.slug
WHERE lower(unaccent($1)) <<% hs.search_title
ORDER BY hs.published_at DESC, score DESC
LIMIT 50;

The results:

Version	Execution time	Index used
Original (`%` + similarity)	4,444ms	GIN (55K candidates)
Raised threshold to 0.3	836ms	GIN (55K candidates)
`word_similarity` `<%`	765ms	published_at
`strict_word_similarity` `<<%`	221ms	published_at

From 4.4 seconds to 221ms. The 2 GB instance can handle that.

The trade-off is real, though: strict_word_similarity matches contiguous word sequences, not approximate ones. It won't forgive typos the way similarity does — "donlad trump" returns nothing. You're trading fuzzy tolerance for speed and precision. For a news search where users type proper nouns they already know how to spell, that's the right call. For a general-purpose search box, it might not be.

The Relevance Problem

Fast isn't useful if the results are wrong. A user reported that searching "emmanuel macron" returned headlines about Emmanuel Bellanger, Emmanuel Grégoire, and Emmanuel-Philibert de Savoie mixed in with actual Macron results.

I tested with synthetic data to understand the scoring:

Title	`strict_word_similarity` score	Matches?
"emmanuel macron annonce une réforme"	1.0	yes
"emmanuel bellanger critique le gouvernement"	0.5625	yes
"emmanuel grégoire quitte la mairie de paris"	0.5625	yes
"macron reçoit biden à l'élysée"	0.4375	no

The issue: "emmanuel" alone is 8 characters out of the 16-character query. That's enough trigram overlap to produce a score of 0.5625 — well above the default pg_trgm.strict_word_similarity_threshold of 0.3. Any "Emmanuel X" headline passes the filter.

On the real dataset, the score distribution confirmed it:

Score	Count	What they are
1.00	4,246	Actual "Emmanuel Macron" headlines
0.56	977	"Emmanuel [someone else]" noise

19% noise. And the problem is specific to queries containing common first names — "réchauffement climatique" worked perfectly because both words are long and distinctive.

Threshold Tuning: SET vs WHERE

Two ways to raise the bar:

Option A — SET the threshold before the query:

SET pg_trgm.strict_word_similarity_threshold = 0.8;
SELECT ... WHERE lower(unaccent($1)) <<% hs.search_title ...

Option B — add a WHERE clause:

SELECT ... WHERE lower(unaccent($1)) <<% hs.search_title
  AND strict_word_similarity(...) >= 0.8 ...

They produce the same results but perform very differently. The SET approach tells the GIN index to use 0.8 as its scan threshold — it returns 4,295 candidates. The WHERE approach uses the default 0.3 for the index scan (8,460 candidates), then discards rows in the heap.

-- SET approach: 166ms, 4,295 index candidates
-- WHERE approach: 348ms, 8,460 index candidates

The SET is 2x faster because it pushes the selectivity into the index. The catch is that SET is a session-level command — it changes the threshold for the entire database connection, not just one query. If you're using a connection pool (and you should be), that modified connection goes back into the pool after your request. The next request that picks it up inherits the 0.8 threshold, which might not be what it expects.

How we handle this is covered in the next section.

The API Endpoint

Everything comes together in a single Astro SSR endpoint at /api/search/headlines. Here's the core flow:

// Get a dedicated client from the pool
const client = await databaseService.getClient();
try {
  // Set threshold only when there's a text query
  if (hasQuery) {
    await databaseService.executeQueryWithClient(
      client,
      "SET pg_trgm.strict_word_similarity_threshold = 0.8",
    );
  }

  // Execute search and count queries on the same client
  results = await databaseService.executeQueryWithClient(client, query, params);
  const countResult = await databaseService.executeQueryWithClient(
    client,
    countQuery,
    countParams,
  );
  totalCount = parseInt(countResult[0]?.total_count || "0", 10);
} finally {
  client.release();
}

The SET command only fires when there's a text query. If the user is just browsing by source or date — no text, no threshold change.

The finally block is where we handle the session-level SET problem. When client.release() returns the connection to the pool, it carries the 0.8 threshold with it. In our case, this is fine: the search endpoint is the only consumer of strict_word_similarity, so every text search sets the threshold before querying. No other code path depends on the default 0.3. If it did, we'd need to SET pg_trgm.strict_word_similarity_threshold = DEFAULT before releasing — or use SET LOCAL inside a transaction, which scopes the setting to that transaction only.

The query is built dynamically based on which filters are present:

// Text search: strict_word_similarity with <<% operator
if (hasQuery) {
  conditions.push(`lower(unaccent($${paramCounter})) <<% hs.search_title`);
}

// Optional source, date_from, date_to filters
if (source_slug) {
  conditions.push(`hs.source_slug = $${paramCounter}`);
}

The scoring column adapts too: strict_word_similarity(...) when there's a text query, 0 otherwise. Results are ordered by published_at DESC, score DESC — most recent first, highest relevance as tiebreaker.

Pagination uses LIMIT/OFFSET with a separate COUNT(*) query for the total. Responses get a 5-minute Cache-Control header since the view only changes at deploy time anyway.

The Frontend

The search page is an Astro SSR page with an Alpine.js component handling all client-side interaction. No framework overhead — Alpine weighs ~15 KB and feels right for a form with a few filters.

The component manages four concerns:

Source autocomplete: Sources are loaded from an Astro content collection into a JSON data island at build time. The autocomplete filters client-side — no API call needed. Selecting a source shows it as a chip with the outlet's favicon.

Date range filters: Two date inputs that map directly to date_from and date_to query parameters. Simple, but together with the source filter they allow powerful browsing: "show me everything from Le Monde in March 2026".

URL state sync: Every search updates the browser URL via history.replaceState() — ?q=macron&source_slug=lemonde&page=2. Bookmarkable, shareable. On page load, init() reads the URL params back and re-executes the search.

Pagination: Page state is managed in Alpine and synced to the URL. The API returns total_count, Alpine computes totalPages, and the UI shows Previous/Next controls. Page changes trigger a new API call and smooth-scroll back to the top.

async executeSearch() {
  const params = new URLSearchParams();
  if (this.query) params.set('q', this.query);
  params.set('limit', String(this.pageSize));
  params.set('offset', String((this.currentPage - 1) * this.pageSize));

  if (this.selectedSource) params.set('source_slug', this.selectedSource.slug);
  if (this.dateFrom) params.set('date_from', this.dateFrom);
  if (this.dateTo) params.set('date_to', this.dateTo);

  // Update browser URL without reload
  history.replaceState(null, '', `?${browserParams.toString()}`);

  const response = await fetch(`/api/search/headlines/?${params.toString()}`);
  // ...
}

No debounce, no search-as-you-type. The user submits the form, sees results. Fast enough that it feels instant.

What a 2 GB Database Can Do

The final stack:

Materialized view with pre-computed lower(unaccent(title)) — avoids runtime text normalization on 966K rows
GIN trigram index — handles fuzzy matching with French accents
strict_word_similarity with <<% operator — 20x faster than similarity for multi-word queries
Session-level threshold at 0.8 — pushes selectivity into the index, eliminates 19% noise on name queries
Astro SSR + Alpine.js — server-side API, lightweight client
5-minute cache — the view only changes at deploy time anyway

Average query time: ~200ms for text search, faster for filter-only browsing. On the smallest managed PostgreSQL instance Scaleway offers.

The lesson is boring but worth repeating: before reaching for a search service, check what your existing database can do. PostgreSQL's pg_trgm isn't Elasticsearch — there's no stemming, no typo tolerance beyond trigram overlap, no field boosting. But for searching a million news headlines in a single language, it's more than enough.

The real optimization work wasn't about choosing the right tool. It was about reading EXPLAIN ANALYZE output, understanding how GIN indexes actually scan, and tuning a single threshold value.

BERTopic Hyperparameter Tuning: Building a Speed-Optimized Grid Search System

Sylvain Artois — Fri, 03 Apr 2026 14:28:27 +0000

NLP clustering isn't exactly intuitive for a senior developer dipping their toes into machine learning waters. Unlike debugging a server issue or fixing a constantly re-rendering React component, I have zero intuition about what parameter values might work. And honestly? The literature doesn't always help.

So I built myself a hyperparameter tuner for BERTopic, designed to test the maximum number of parameter combinations in the shortest possible time. It's essentially a race against the clock: how can we make experimentation fast enough to explore a vast parameter space?

The source code is available on GitLab for anyone who wants to experiment. The project is packaged as a proper Python package — install it with pip install -e . and use the bertopic-tune and bertopic-model CLI commands.

The Challenge: From Manual Intuition to Systematic Search

When you're working with BERTopic, you're actually dealing with multiple algorithms working in concert:

UMAP for dimensionality reduction
HDBSCAN for clustering
BERTopic itself for topic extraction

Each has its own parameters, and they interact in non-obvious ways. The documentation might tell you that n_neighbors in UMAP typically ranges from 5 to 50, but what works for your specific dataset? That's where systematic testing comes in.

Setting Up the Data Pipeline

First, I formalized a data format (detailed in the README). The idea is to automate everything: SQL export of collected data, manual clustering or clustering done by an LLM. You could easily imagine an n8n workflow that runs this script every two weeks or monthly to track semantic evolution in your data.

The ground truth is a simple JSON with cluster IDs as keys and lists of text IDs as values:

{
  "7": [288657, 304729, 314406],
  "6": [308809, 309885, 311726, 312668],
  "4": [
    /* ...*/
  ]
  //...
}

The script expects a parameter grid to test:

{
  "umap_n_neighbors": [12, 14, 16],
  "umap_n_components": [7, 8, 9, 10, 11],
  "umap_min_dist": [0.1],
  "hdbscan_min_cluster_size": [3],
  "hdbscan_min_samples": [8, 9, 10],
  "hdbscan_cluster_selection_epsilon": [0.3, 0.5],
  "bertopic_top_n_words": [8, 10],
  "nr_topics": ["none"],
  "n_gram_max": [2, 3],
  "embedding_model": ["dangvantuan/sentence-camembert-large"],
  "remove_stopwords": [false]
}

This is just a refinement grid - I'd already run the script once on this dataset and was narrowing down the search space. Initial runs often have much wider parameter ranges.

The Speed Optimization: Pre-Computing Embeddings

Once the dataset is ready (examples are available in the repo), the first optimization is pre-calculating embeddings. While not computationally heavy, this step is crucial because it allows us to bypass GPU usage during the actual parameter search, enabling massive parallelization.

def precalculate_embeddings(
    texts_dict: dict[str, list[str]],
    embedding_models: list[str],
    pooling_mode: str,
    device: str = "cuda",
) -> None:
    """
    Pre-calculate all embeddings for all combinations of models and stopword settings.
    This prevents multiple parallel processes from trying to compute embeddings simultaneously.
    """
    logger.info("Pre-calculating embeddings for all model/stopword combinations...")

    for model_name in embedding_models:
        model_path = ensure_custom_embedding_model(model_name=model_name, pooling_mode=pooling_mode)

        for remove_stopwords in [True, False]:
            texts_for_embeddings = texts_dict["processed"] if remove_stopwords else texts_dict["original"]

            get_cached_embeddings(
                texts_for_embeddings,
                model_path,
                pooling_mode,
                remove_stopwords,
                device=device,
            )

            if torch.cuda.is_available():
                torch.cuda.empty_cache()

The embeddings are cached to disk using Python's pickle format (.pkl files), which allows for fast serialization and deserialization of numpy arrays. Each cache file is uniquely identified by a combination of the model name, pooling mode, stopword removal setting, and a hash of the corpus - ensuring we never accidentally use the wrong embeddings.

Parallel Execution with ProcessPoolExecutor

The CLI entry point main_tuner() in bertopic_tuner/cli.py iterates through each possible combination using ProcessPoolExecutor:

    # Pre-calculate all embeddings before parallel processing
    precalculate_embeddings(texts_dict, unique_embedding_models, args.pooling_mode)

    # Prepare all parameter combinations
    tasks = []
    for i, params in enumerate(param_combinations):
        ts = datetime.now().strftime('%Y%m%d_%H%M%S_%f')[:-3]
        params["run_name"] = f"ParameterTuning_{i+1}_{ts}"
        params["manual_clusters_path"] = manual_clusters_path
        tasks.append((batch_id, params, experiment_name, texts_dict))

    max_workers = args.max_workers
    print(f"Running {len(tasks)} tasks with {max_workers} parallel processes...")

    with concurrent.futures.ProcessPoolExecutor(max_workers=max_workers) as executor:
        future_to_task = {
            executor.submit(run_berttopic_with_params, *task): i
            for i, task in enumerate(tasks)
        }

        for future in concurrent.futures.as_completed(future_to_task):
            # Process results...

Each worker function run_berttopic_with_params retrieves the cached embeddings and calls modeler.run() directly as a Python function — no subprocess spawning, no temporary files.

Why ProcessPoolExecutor? It's the perfect tool for CPU-bound parallel tasks in Python. Since we've pre-computed embeddings, each BERTopic run is purely CPU-intensive (UMAP and HDBSCAN calculations). The Global Interpreter Lock (GIL) doesn't affect separate processes, so we get true parallelism. With threads, we'd be limited by the GIL; with processes, we can use all available CPU cores effectively.

MLflow Integration for Experiment Tracking

Inside bertopic_tuner/modeler.py, we use MLflow to track each experiment:

def setup_model_with_logging(
    config: dict, model_class, prefix: str, exclude_keys: list[str] | None = None
):
    """Setup a model with MLflow parameter logging."""
    if exclude_keys is None:
        exclude_keys = []

    mlflow_params = {f"{prefix}_{k}": v for k, v in config.items() if k not in exclude_keys}
    mlflow.log_params(mlflow_params)

    return model_class(**config)

We build BERTopic parameters based on the received configuration:

umap_config = {
    "n_neighbors": params.get('umap_n_neighbors'),
    "n_components": params.get('umap_n_components'),
    "min_dist": params.get('umap_min_dist'),
    "metric": 'cosine',
    "random_state": 42,
}

The key is to run BERTopic without any representation models. While these are perfect in production for refining clusters, they're unnecessary here - we just want to measure how well a parameter combination matches our ground truth.

topic_model = BERTopic(
    umap_model=umap_model,
    hdbscan_model=hdbscan_model,
    vectorizer_model=vectorizer_model,
    top_n_words=bertopic_top_n_words,
    verbose=True,
    nr_topics=nr_topics,
    low_memory=False,
    n_gram_range=(1, params.get("n_gram_max", 1)),
)

Evaluation Metrics: Measuring Success

In bertopic_tuner/evaluation.py, we calculate various metrics, but I primarily focus on two:

A_f1_score_assignment: Measures how well the predicted clusters match the ground truth assignments
A_ARI (Adjusted Rand Index): A chance-corrected measure of clustering similarity

For a deeper dive into these metrics, check out my blog post on supervised clustering evaluation.

Analyzing Results in MLflow

Once experiments are complete, MLflow provides powerful tools for analysis:

Parallel Coordinates Plot: Visualize how different parameter combinations affect metrics

Representative Documents: Review actual text samples from each cluster to assess quality

Metric Comparisons: Sort and filter experiments by any logged metric

The MLflow UI (accessible at http://localhost:12001 in the repo) becomes your command center for understanding which combinations work best.

Key Insights and Conclusions

After building and using this system extensively, here are my main takeaways:

Speed Through Smart Optimization

By pre-computing embeddings and parallelizing experiments, I can test hundreds of parameter combinations in just tens of minutes. The embedding cache and ProcessPoolExecutor combination is the secret sauce here.

Discovering Unexpected Configurations

The system revealed parameter combinations I never would have tried manually. For instance, I now have a production configuration running with:

{
  "umap": {
    "n_neighbors": 25,
    "n_components": 15
  }
}

No tutorial or LLM ever suggested parameters this high, but for my specific dataset, they work brilliantly.

The Human Element Remains Critical

While I'm automating the downstream pipeline (dataset generation, automated experimentation launches), I haven't automated the final configuration export - and I'm not sure it would be desirable. Often, the top-performing combinations are very close in metrics, and I need to:

Analyze the results carefully
Test them in production
Make a final decision based on real-world performance

The best configuration on paper isn't always the best in practice. Sometimes a slightly lower-scoring configuration produces more interpretable or stable topics in production.

Looking Forward

This system has transformed how I approach NLP clustering. Instead of guessing parameters based on vague intuition or generic recommendations, I can systematically explore the parameter space and find what actually works for my data.

The combination of BERTopic's flexibility, MLflow's tracking capabilities, and Python's parallel processing creates a powerful experimentation platform. Whether you're clustering news headlines, customer feedback, or any other text corpus, this approach can help you find the optimal configuration for your specific use case.

Feel free to explore the source code and adapt it for your own projects.