Skip to content

About

Testing PostgreSQL Full Text Search performance on a travel catalogue of 10k products

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

2 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

PostgreSQL Full-Text Search POC

Production-ready full-text search using only PostgreSQL - No Elasticsearch required!

PostgreSQL License: MIT

⭐ Star this repo if you find it useful!

🎯 What This Proves

For most applications with <100K products, you don't need Elasticsearch!

PostgreSQL handles:

  • ✅ 10,000+ products with complex schemas
  • ✅ Sub-100ms query times (cached <50ms)
  • ✅ Fuzzy search with typo tolerance
  • ✅ Multi-field search (title, description, variants, reviews)
  • ✅ 3-tier search strategy (EXACT → UNION → FUZZY)

Savings: $0 infrastructure cost, no sync mechanisms, no data consistency issues.

📊 Performance

Search Term Time (ms) Match Type
Cricket World Cup 15.8 🟢 EXACT
Football 44.1 🟢 EXACT
creket (typo) 84.8 🟡 FUZZY

Database: 10,000 products, 20,000 variants, ~3,000 reviews (33 MB + 14 MB indexes)

🚀 Quick Start

Prerequisites

Required Software

For Local Setup (setup-local.sh):

  • ✅ PostgreSQL 14+ installed and running
  • ✅ PostgreSQL client tools: psql, createdb, dropdb
  • ✅ bash 3.2+ or compatible shell (zsh, dash)

For Docker Setup (setup-docker.sh):

  • ✅ Docker 20.10+ or Docker Desktop
  • ✅ Docker Compose (plugin v2 or standalone v1)
  • ✅ bash 3.2+ or compatible shell

Supported Operating Systems

macOS:

  • ✅ Intel (x86_64): macOS 10.14+ (Mojave and later)
  • ✅ Apple Silicon (ARM64): macOS 11+ (Big Sur and later) - M1, M2, M3, M4
  • 📦 Package Manager: Homebrew
  • 🔧 Install: brew install postgresql@14 && brew services start postgresql@14

Linux Distributions:

  • ✅ Ubuntu: 18.04+ (x86_64, ARM64, ARMv7)
  • ✅ Debian: 10+ (x86_64, ARM64, ARMv7)
  • ✅ Fedora: 32+ (x86_64, ARM64)
  • ✅ RHEL/CentOS: 8+ (x86_64, ARM64)
  • ✅ Arch Linux: Latest (x86_64, ARM64)
  • ✅ Manjaro: Latest (x86_64, ARM64)
  • ✅ openSUSE: 15+ (x86_64, ARM64)
  • ✅ SLES: 15+ (x86_64, ARM64)

Installation Examples:

# Ubuntu/Debian
sudo apt-get install postgresql-14 postgresql-contrib-14
sudo systemctl start postgresql

# Fedora/RHEL/CentOS
sudo dnf install postgresql14-server postgresql14-contrib
sudo systemctl start postgresql-14

# Arch Linux/Manjaro
sudo pacman -S postgresql
sudo systemctl start postgresql

Verification

Check PostgreSQL is installed:

psql --version  # Should show PostgreSQL 14.0 or higher

Check PostgreSQL is running:

psql -l  # Should list databases without errors
# Or
pg_isready  # Should return "accepting connections"

Check Docker (for Docker setup):

docker --version  # Should show Docker 20.10+
docker compose version  # Should show Docker Compose version

📖 Detailed System Support: See Supported Systems Guide for complete compatibility matrix, troubleshooting, and platform-specific instructions.

One-Command Setup

Local PostgreSQL:

./scripts/setup-local.sh

Docker:

./scripts/setup-docker.sh  # Prompts to install Docker if missing

That's it! The script creates the database, inserts 10,000 products, builds indexes, and runs benchmarks (~2 minutes).

🎮 Try It

psql search_poc
-- Basic search
SELECT * FROM search_products_poc('Cricket World Cup', 10);

-- Fuzzy search (typo tolerance)
SELECT * FROM search_products_poc('creket', 10);

-- Multi-word search
SELECT * FROM search_products_poc('luxury champagne', 10);

🏗️ Architecture

3-Tier Search Strategy:

  1. EXACT - All words present (AND logic)
  2. UNION - Any word present (OR logic)
  3. FUZZY - Typo tolerance using trigrams

Database Schema:

categories → products → venues
                      → variants
                      → reviews

📊 ER Diagram:

Database Schema

📁 Project Structure

postgres-search-poc/
├── scripts/              # Setup scripts
│   ├── setup-local.sh
│   └── setup-docker.sh
├── sql/                  # SQL files
│   ├── migration.sql     # Schema & seed data
│   └── test-queries.sql  # Test queries
├── docs/                 # Documentation
│   ├── DOCKER.md
│   ├── TEST_RESULTS.md
│   └── erdiagram.png
├── README.md
├── LICENSE
└── docker-compose.yml

📚 Learn More

🎯 Use Cases

Perfect for: E-commerce catalogs (<100K products), CMS, document search, event ticketing, hotel booking.

Consider Elasticsearch for: Massive scale (>1M products), real-time analytics, distributed search.

🤝 Contributing

Found a bug? Have an optimization? PRs welcome!

📄 License

MIT License - See LICENSE file

⭐ Star this repo if you find it useful!

Ninad Sutrave

About

Testing PostgreSQL Full Text Search performance on a travel catalogue of 10k products

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages