PostgreSQL Search Engineering Lab

Build search.
Understand why it works.

An evolving product search engine built from PostgreSQL primitives — beginning with naive ILIKE substring matching and progressively adding full-text retrieval, indexes, relevance ranking, fuzzy matching and caching.

wireless mechanical keyboard ⌘ K
100,000 products PostgreSQL FastAPI Psycopg Raw SQL
Explore the evolution ↓
SEARCH EVOLUTION

Six versions.
One database.

Each version keeps the previous problem visible, then introduces one meaningful architectural improvement.

V1 BASELINE

ILIKE Search

The intentionally naive baseline. Case-insensitive substring matching across product fields using ILIKE '%query%'.

ILIKE Sequential Scan Baseline
Open V1 →
V2 RETRIEVAL

Full-Text Search

Move from raw substring matching to PostgreSQL linguistic retrieval using tokens, lexemes, stemming and tsquery.

tsvector tsquery @@
Open V2 →
V3 PERFORMANCE

FTS + GIN

Keep full-text retrieval while adding a GIN-backed search vector so PostgreSQL can locate candidates through an index.

GIN Search Vector EXPLAIN
Open V3 →
V4 RESILIENCE

Fuzzy Fallback

Recover from misspellings and failed searches using PostgreSQL trigram similarity and typo-tolerant fallback.

pg_trgm similarity() Suggestions
Open V4 →
V5 CACHE

Redis Caching

Cache repeated normalized searches and compare database execution with cache hits, TTLs and cache-key design.

Redis TTL Cache Hit
Open V5 →
BENCHMARK

Same query.
Different architecture.

Every version is measured independently so application latency and PostgreSQL execution behavior can be compared without hiding what changed underneath.

Ready. Versions run sequentially against the same query.

benchmark.log

query —
V1 ILIKE———Waiting
V2 Full-text———Waiting
V3 FTS + GIN———Waiting
V4 Fuzzy———Waiting
V5 Cached———Waiting
MEASUREMENT

Don't just call it fast.
Measure what happened.

SearchLab keeps application measurements separate from PostgreSQL execution diagnostics.

01

Application latency

Python's perf_counter() measures application-observed elapsed time around the database operation.

02

Python memory

tracemalloc records current and peak tracked Python allocations during the search.

03

PostgreSQL execution

EXPLAIN ANALYZE exposes execution time, planning time, rows processed and the chosen plan.

04

Buffer behavior

BUFFERS shows whether database pages were already available in PostgreSQL's shared cache or needed to be read.

THE POINT
Don't memorize that GIN and Redis are fast. Build the bad version first, inspect the query planner, measure the failure mode, and understand why the architecture had to evolve.

SearchLab is not meant to hide PostgreSQL behind abstractions. Each version keeps the SQL, execution plan, application measurements and limitations visible.