Optimizing PostgreSQL Full-Text Search to Under 15ms

How Sub-15ms Search Speeds Transformed an Enterprise Web App

The Search Bottleneck: In an enterprise inventory catalog containing over 250,000 products, warehouse employees were complaining that search queries took 3 to 5 seconds per keystroke. The search box was running a standard WHERE name LIKE '%term%' query, which completely bypasses standard B-Tree indexes and forces a full scan of all 250,000 rows on every search.

“Users feel the difference between 400ms and 15ms instantly. Speed is a feature that directly drives user productivity.”

The Solution: PostgreSQL Trigram GIN Indexes

We introduced PostgreSQL trigram indexing (pg_trgm) and Generalized Inverted Indexes (GIN):

-- Step 1: Enable Trigram extension in PostgreSQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Step 2: Create GIN Trigram Index
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- Step 3: Lightning-fast sub-12ms fuzzy search
SELECT id, name, sku, price FROM products WHERE name ILIKE '%search_term%' LIMIT 20;

The Immediate Impact

• Query Execution Time: Dropped from 3,800ms to just 12ms.

• Database CPU Load: Decreased from 82% to an idle 9% during peak business hours.

• User Satisfaction: Customer support tickets related to slow catalog searches dropped to zero on day one.

Accelerate Your Web Application With WorldWebTree

At WorldWebTree, we optimize slow database queries, implement instant full-text search, and modernize legacy web applications to run at peak performance.

Explore our database tuning and optimization services at WorldWebTree and view our performance case studies.

Work Together: Is your database search running slowly? Contact Umar Farooq for database tuning or view my background on

GitHub Follow my software engineering posts on LinkedIn and explore my open-source code on