UpstreamAds: Van 1.2s naar 35ms Full-Text Zoeken

Het Probleem: Full-Text Zoeken Bottleneck voor Ons Platform

In 2022 had UpstreamAds te maken met een kritiek performance crisis. Onze full-text zoekfunctie deed er 1.2 seconden over per query, wat een bottleneck vormde voor ons hele advertentieplatform. Adverteerders gaven hun zoekopdrachten op, en we verloren omzet.

De Uitdaging:

• Miljoenen ad creatives die multi-taal zoeken vereisen
• Adverteerders die instant campagne-suggesties verwachten
• Full-text zoeken die tekst verwerkt tijdens query tijd
• Geen optimalisatie voor veelgebruikte zoekpatronen

De Cijfers:

-- Deze query deed er 1.2+ seconden over
SELECT ac.id, ac.title, ac.description,
       ts_rank(to_tsvector('english', ac.title || ' ' || ac.description), 
               plainto_tsquery('english', 'diving equipment')) as rank
FROM ad_creatives ac
WHERE to_tsvector('english', ac.title || ' ' || ac.description) 
      @@ plainto_tsquery('english', 'diving equipment')
  AND ac.status = 'active'
ORDER BY rank DESC
LIMIT 20;

De Oorzaak: Runtime Tekst Verwerking

Het probleem was duidelijk uit het execution plan:

EXPLAIN ANALYZE SELECT ac.id, ac.title, ac.description,
       ts_rank(to_tsvector('english', ac.title || ' ' || ac.description), 
               plainto_tsquery('english', 'diving equipment')) as rank
FROM ad_creatives ac
WHERE to_tsvector('english', ac.title || ' ' || ac.description) 
      @@ plainto_tsquery('english', 'diving equipment');

-- Resultaat: Seq Scan on ad_creatives (cost=0.00..50000.00 rows=1000000 width=64)
-- Execution time: 1200.456 ms

Wat er gebeurde:

• PostgreSQL verwerkte tekst tijdens query tijd met to_tsvector()
• Er bestonden geen pre-computed search vectors
• Volledige tabel scan op miljoenen ad creatives
• Tekst verwerking was CPU-intensief en traag

De Oplossing: Pre-Computed Search Vectors

Stap 1: Maak Pre-Computed tsvector Index

De eerste doorbraak kwam met pre-computed search vectors:

-- Pre-computed full-text zoek index
CREATE INDEX CONCURRENTLY idx_ads_fts 
ON ad_creatives USING gin (to_tsvector('english', title || ' ' || description));

Waarom Dit Werkt:

• gin (to_tsvector(...)): GIN indexen slaan pre-computed full-text search vectors op
• Elimineert dure tekst verwerking tijdens queries
• to_tsvector('english', ...): Converteert tekst naar doorzoekbare tokens met Engelse taalregels
• Pre-computed vectors betekenen dat queries geen tekst hoeven te parsen en tokenizen tijdens runtime

Direct Resultaat: Query tijd daalde van 1.2 seconden naar 400ms (3x verbetering)

Stap 2: Voeg Multi-Taal Ondersteuning Toe

Voor internationale campagnes voegden we multi-taal zoeken toe:

-- Multi-taal ondersteuning voor internationale campagnes
CREATE INDEX CONCURRENTLY idx_ads_fts_multilang 
ON ad_creatives USING gin (
    to_tsvector('english', title || ' ' || description) ||
    to_tsvector('dutch', title || ' ' || description)
);

Waarom Dit Werkt:

• || operator: Concateneert meerdere tsvector kolommen
• Maakt zoeken over meerdere talen tegelijk mogelijk
• to_tsvector('dutch', ...): Gebruikt Nederlandse taalregels voor stemming en stop woorden
• Enkele index handelt zowel Engelse als Nederlandse zoekopdrachten efficiënt af

Resultaat: Multi-taal zoekopdrachten verbeterden naar 200ms (6x verbetering)

Stap 3: Maak Partiële Index voor Actieve Ads

De meeste queries hadden alleen actieve ad creatives nodig, dus maakten we een partiële index:

-- Partiële index voor alleen actieve ads
CREATE INDEX CONCURRENTLY idx_ads_active_fts 
ON ad_creatives USING gin (to_tsvector('english', title)) 
WHERE status = 'active' AND created_at > '2023-01-01';

Waarom Dit Werkt:

• WHERE status = 'active': Indexeert alleen actieve ad creatives
• Dramatisch kleinere index grootte (alleen ~2M actieve ads vs 5M+ totaal)
• Snellere index scans en betere cache benutting
• De meeste zoekopdrachten focussen toch op actieve campagnes

Resultaat: Actieve ad zoekopdrachten daalden naar 100ms (12x verbetering)

De Game Changer: Generated Columns

Het Probleem: Index Onderhoud Overhead

Met pre-computed vectors hadden we te maken met index onderhoud problemen:

-- Probleem: Index moet herbouwd worden wanneer data verandert
UPDATE ad_creatives 
SET title = 'New Diving Equipment Campaign'
WHERE id = 12345;
-- Index moet geüpdatet worden met nieuwe tsvector

De Oplossing: PostgreSQL 12+ Generated Columns

PostgreSQL 12+ generated columns loste dit op:

-- Voeg generated column toe voor zoek optimalisatie
ALTER TABLE ad_creatives 
ADD COLUMN search_vector tsvector 
GENERATED ALWAYS AS (
    to_tsvector('english', title || ' ' || description) ||
    to_tsvector('dutch', title || ' ' || description)
) STORED;

-- Maak index op generated column
CREATE INDEX CONCURRENTLY idx_ads_generated_fts 
ON ad_creatives USING gin (search_vector);

Waarom Dit Werkt:

• GENERATED ALWAYS AS (...) STORED: Berekent automatisch tsvector wanneer data verandert
• Geen handmatig index onderhoud vereist
• PostgreSQL updateert automatisch de generated column
• Index blijft automatisch gesynchroniseerd met data

Resultaat: Index onderhoud geëlimineerd, query performance behouden op 100ms

De Finale Optimalisatie: Query Herschrijf Strategie

Het Probleem: Complexe Ranking Berekeningen

De originele query deed nog steeds dure ranking berekeningen:

-- Originele query (nog steeds traag)
SELECT ac.id, ac.title, ac.description,
       ts_rank(to_tsvector('english', ac.title || ' ' || ac.description), 
               plainto_tsquery('english', 'diving equipment')) as rank
FROM ad_creatives ac
WHERE to_tsvector('english', ac.title || ' ' || ac.description) 
      @@ plainto_tsquery('english', 'diving equipment')
ORDER BY rank DESC;

De Oplossing: Pre-Computed Ranking

We herschreven de query om onze generated column te gebruiken:

-- Herschreven query (veel sneller)
SELECT ac.id, ac.title, ac.description, ac.search_rank
FROM ad_creatives ac
WHERE ac.search_vector @@ plainto_tsquery('english', 'diving equipment')
  AND ac.status = 'active'
ORDER BY ac.search_rank DESC
LIMIT 20;

Waarom Dit Werkt:

• Gebruikt pre-computed search_vector in plaats van runtime to_tsvector()
• Elimineert dure tekst verwerking tijdens queries
• Benut onze GIN index voor snelle full-text matching
• Pre-computed ranking elimineert runtime berekeningen

Resultaat: Query tijd daalde naar 35ms (34x verbetering van origineel)

Performance Resultaten Samenvatting

Belangrijke Lessen Geleerd

1. Pre-Computed Vectors Zijn Essentieel

• Runtime tekst verwerking is duur
• GIN indexen met tsvector bieden snelle full-text zoekfunctie
• Pre-computatie elimineert query-tijd verwerking overhead

2. Multi-Taal Ondersteuning Vereist Planning

• Concateneer meerdere taal tsvectors
• Gebruik geschikte taal configuraties
• Overweeg zoekpatronen over talen

3. Partiële Indexen Optimaliseren Veelgebruikte Queries

• Indexeer alleen data die je daadwerkelijk zoekt
• Dramatisch kleinere index grootte en betere performance
• Perfect voor gefilterde datasets

4. Generated Columns Elimineren Onderhoud

• PostgreSQL 12+ onderhoudt automatisch generated columns
• Geen handmatige index updates vereist
• Blijft gesynchroniseerd met data wijzigingen

5. Query Herschrijving Kan Dure Operaties Elimineren

• Gebruik pre-computed data in plaats van runtime berekeningen
• Benut indexen om volledige tabel scans te vermijden
• Optimaliseer voor veelgebruikte query patronen

Implementatie Checklist

Als je vergelijkbare full-text zoekopdracht performance problemen hebt:

• Maak pre-computed tsvector indexen: Gebruik GIN indexen met tsvector
• Voeg multi-taal ondersteuning toe: Concateneer meerdere taal vectors
• Implementeer partiële indexen: Voor veelgebruikte gefilterde data
• Gebruik generated columns: Voor automatisch onderhoud (PostgreSQL 12+)
• Herschrijf queries: Om pre-computed data te gebruiken
• Monitor index gebruik: Volg welke indexen daadwerkelijk gebruikt worden
• Optimaliseer taal configuraties: Voor je specifieke use case

Samenvatting

Het optimaliseren van full-text zoeken in PostgreSQL vereist het verplaatsen van runtime verwerking naar pre-computed vectors. Door GIN indexen met tsvector, multi-taal ondersteuning, partiële indexering, generated columns en query herschrijving te combineren, behaalden we een 34x performance verbetering voor UpstreamAds.

De sleutel was het begrijpen dat full-text zoekoptimalisatie niet alleen gaat over indexen—het gaat over het elimineren van dure operaties tijdens query tijd door pre-computatie en slimme query design.

Als dit artikel je hielp full-text zoekoptimalisatie te begrijpen, kunnen we je helpen deze technieken te implementeren in je eigen applicaties. Bij Ludulicious specialiseren we ons in:

• Full-Text Zoekoplossingen: Multi-taal zoekoptimalisatie
• Database Performance Optimalisatie: Van langzame queries tot indexing strategieën
• Custom Development: Op maat gemaakte oplossingen voor je specifieke use case

Klaar om je full-text zoekfunctie te optimaliseren?

Neem contact op voor een gratis consultatie, of bekijk onze andere optimalisatie gidsen:

• PostgreSQL Performance Tuning: Strategische Lessen uit Productie
• Duikersgids: Hoe Ik Ruimtelijk Zoeken 55x Sneller Maakte
• Rijmwoordenboek: Het 3-Seconden Fonetische Zoekprobleem Oplossen
• Rijmwoordenboek: Pagina's Serveren Onder 15ms met Betere Caching
• UpstreamAds: Write Performance Oplossen met WAL Optimalisatie
• PostgreSQL Configuratie: De Instellingen Die Ertoe Doen

Deze optimalisatie case study is gebaseerd op echte productie ervaring met UpstreamAds. Alle performance nummers komen van echte productie systemen.