Postgres Database

Connection Configuration

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession

DATABASE_URL = "postgresql+asyncpg://user:pass@localhost:5432/dbname"
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=10, pool_pre_ping=True, pool_recycle=300)

Docker Setup

docker run -d --name geo-postgres \
  -e POSTGRES_USER=geo_user -e POSTGRES_PASSWORD=geo_password -e POSTGRES_DB=geo_chat \
  -p 5432:5432 -v geo_postgres_data:/var/lib/postgresql/data postgres:16-alpine

Common Operations

docker exec geo-postgres pg_isready -U geo_user -d geo_chat       # Health check
docker exec -it geo-postgres psql -U geo_user -d geo_chat         # Connect
docker exec geo-postgres pg_dump -U geo_user geo_chat > backup.sql # Backup

Environment

# backend/.env
DATABASE_URL=postgresql+asyncpg://geo_user:geo_password@localhost:5432/geo_chat

Performance Tips

1. Always use connection pooling with asyncpg
2. Index all foreign key columns
3. Use JSONB over JSON for indexing support
4. Use deleted_at soft deletes for audit trail
5. Use selectinload() for relationships to prevent N+1

For full schema reference, see references/schema.md.