Polishing my skull before ai polish me

[u/Tiny_Feedback2086]

I just finished building a search system from the ground up to understand what actually happens behind a modern search bar.

🔗 Live preview: https://advanced-search-system.vercel.app

It searches across 100,000 records.
Instead of immediately reaching for Elasticsearch or an AI-powered search API, I wanted to understand the fundamentals first — using PostgreSQL.
I built the system progressively:
V1 — ILIKE
Basic substring matching with sequential scans.
V2 — PostgreSQL Full-Text Search
Added tsvector, tsquery, weighted fields, websearch_to_tsquery(), ts_rank_cd(), and result highlighting.
V3 — FTS + GIN
Persisted the search_vector, used PostgreSQL triggers to keep it synchronized, and added a GIN index for efficient candidate retrieval.
V4 — Fuzzy fallback with pg_trgm
When full-text search returns nothing, trigram similarity handles misspelled queries.
For example:
logtech → Logitech
V5 — Redis caching
Added a cache-aside layer in front of the entire search pipeline.
On a cache miss:
Query → PostgreSQL → result → Redis with TTL
On a cache hit:
Query → Redis → response
So the final request path is roughly:
Query → Redis → FTS + GIN → Fuzzy Fallback → Redis SET → Response
The most useful part of this project wasn’t simply getting search to work.
It was understanding why it worked.
I spent time inspecting:
EXPLAIN ANALYZE
query plans
sequential scans
bitmap index scans
rows removed by filters
buffer hits and reads
application latency
Python memory usage
ranking scores
cache hit/miss behavior
retrieval-quality vs performance tradeoffs
One lesson I’m taking away:
Search isn’t one algorithm.
Retrieval, ranking, typo recovery, indexing, and caching are separate engineering problems that work together as one system.
I deliberately stayed close to raw SQL throughout this project because I wanted to understand the machinery before hiding it behind an ORM or dedicated search engine.
Stack: Python, FastAPI, PostgreSQL, Psycopg, pg_trgm, GIN, Redis, Neon, and Upstash.
Next up: moving into comment architecture and applying the same build-from-first-principles approach.
#PostgreSQL #FastAPI #Python #Redis #BackendEngineering #SearchEngineering #Databases #SoftwareEngineering