Storage
Hindsight uses PostgreSQL as its sole storage backend.
Why PostgreSQL?
PostgreSQL provides all capabilities required for a semantic memory system in a single database:
| Capability | Implementation |
|---|---|
| Vector search | pgvector extension with HNSW indexes |
| Full-text search | Built-in tsvector with GIN indexes |
| Relational data | Native PostgreSQL |
| JSON documents | JSONB with indexing |
| Graph queries | Recursive CTEs |
Reduced System Dependencies
Building exclusively for PostgreSQL simplifies deployment and operations:
- Single connection string to configure
- Single backup and restore strategy
- Single monitoring target
- ACID transactions across all data types
- Single upgrade path
No Storage Abstraction
Hindsight does not abstract storage behind a generic interface. This is a deliberate trade-off.
We believe PostgreSQL is becoming the standard database API. Its popularity, extension ecosystem, and modularity mean that PostgreSQL-compatible interfaces are appearing everywhere—from serverless offerings to distributed databases. Building for PostgreSQL today means compatibility with a growing ecosystem tomorrow.
Supporting multiple databases would increase flexibility but conflict with our core goals: Hindsight is fully open source and designed to be as simple as possible to run and use. Adding database abstractions introduces complexity in code, testing, documentation, and operations—complexity that we pass on to users.
By committing to PostgreSQL, we keep the system simple:
- One set of deployment instructions
- One set of performance characteristics to understand
- One codebase optimized for one backend
- No configuration decisions about which database to use
Development with pg0
For local development, Hindsight uses pg0—an embedded PostgreSQL distribution.
What is pg0?
pg0 is a single binary containing:
- PostgreSQL server
- pgvector extension (pre-installed)
- Automatic initialization
Behavior
When no DATABASE_URL is configured, Hindsight:
- Downloads the pg0 binary for the current platform (macOS ARM, Linux x86_64/ARM64, Windows)
- Starts an embedded PostgreSQL instance on port 5555
- Initializes the schema
- Stores data in
~/.hindsight/pg0/
Environments
| Environment | Database | Configuration |
|---|---|---|
| Development | pg0 (embedded) | Automatic |
| Production | PostgreSQL 15+ | DATABASE_URL environment variable |
Requirements
- PostgreSQL 15 or later
- pgvector 0.5.0 or later
Any PostgreSQL instance that satisfies these requirements should work. If you encounter issues with a specific setup, open a GitHub issue.
Tested Managed Services
- AWS RDS (PostgreSQL 15+)
- Google Cloud SQL
- Azure Database for PostgreSQL
- Supabase
- Neon