March 16, 2026
PostgreSQL for Structured Route Data
Context
No Scratch Nav needed to store users, routes, route requests, upvotes, and replies — data with clear relationships — and search across country, city, and keywords. The database choice affected query performance, search, and development complexity.
Decision
Chose PostgreSQL for relational integrity and full-text search, with Redis for caching and rate limiting.
Alternatives Considered
MongoDB
- Flexible schema
- Easy to start with
- No native relational joins
- Eventual consistency adds complexity
- Weaker full-text + flexible-field story than Postgres JSONB
SQLite
- Zero configuration
- Lightweight
- No concurrent writes
- Not production-ready for a multi-service API
Reasoning
The domain is relational — requests belong to users, upvotes belong to routes, replies belong to requests. PostgreSQL gives ACID integrity, full-text search, and JSONB for flexible fields (like route waypoints) in one place, and it pairs naturally with Redis for caching and rate limiting. AI tooling also has extensive Postgres training data, which made AI-assisted iteration faster.