PostgreSQL
Production database administration, schema design, JSONB querying, indexing strategies, and multi-tenant data isolation.
1 / The Default Database
PostgreSQL is the database I reach for by default. Across Conclave, Foundry, Phoenix, StudyLink, Trajectory, and Vigil, it served as the primary data store — and in several cases, as the vector search engine via pgvector.
2 / Beyond Relational Basics
My PostgreSQL work goes beyond basic table definitions. In Phoenix, PostgreSQL handled JSONB document fields alongside vector embedding columns, combining document metadata queries with cosine similarity searches in single statements. In Trajectory, pessimistic locking (`SELECT FOR UPDATE`) prevented race conditions in concurrent pipeline state updates. In Foundry, PostgreSQL-backed LangGraph state checkpointers persisted agent decision histories for review and resumption.
3 / Indexing Discipline
Indexing strategy was one of the practical lessons that came from production experience rather than theory. Adding indexes on foreign keys from the first Flyway migration script prevents the slow query surprises that appear under load. HNSW indexes on pgvector columns are essential for retrieval performance. GIN indexes on JSONB columns enable efficient document metadata filtering.
4 / Multi-Tenant Isolation
Several projects required data isolation between users or tenants. The approach was row-level filtering scoped to the authenticated user's context — every query included a tenant predicate, and the application layer enforced this consistently. PostgreSQL's row-level security policies offer database-enforced isolation, though my implementations relied on application-level enforcement through Spring Security and Django middleware.