Database Patterns¶
Common PostgreSQL patterns for real-world applications.
Overview¶
| Pattern | Use Case | Key Technique |
|---|---|---|
| Soft Deletes | Preserve deleted data | deleted_at column + partial indexes |
| Audit Trails | Track all changes | Trigger-based logging |
| Full-Text Search | Text search without external service | tsvector + GIN indexes |
When to Use These Patterns¶
Soft Deletes¶
Use when: - Legal/compliance requires data retention - Users may want to restore deleted items - Referential integrity prevents hard deletes
Avoid when: - Data privacy requires true deletion (GDPR) - Table size is a concern - Queries must be fast and simple
Audit Trails¶
Use when: - Compliance requires change history - Debugging requires understanding "how did we get here?" - Users need to see version history
Consider alternatives: - Event sourcing for event-heavy systems - Application-level logging for simpler needs
Full-Text Search¶
Use when: - Simple search requirements - Don't want external dependencies (Elasticsearch) - Combined SQL and text queries
Consider alternatives: - Elasticsearch for complex relevance tuning - Typesense/Meilisearch for typo-tolerance - Algolia for hosted search
See Also¶
- Database Patterns (SQLAlchemy) -- Model definitions, mixins, and relationship loading used by these patterns