Data Types Best Practices
Type choices are schema decisions you live with for years.
How to Use This List
- Decide at table design; avoid silent ALTER widening.
- Document in ADR for money and timezone.
A - Scalars
- Use numeric for money. Exact decimals.
- Prefer text over varchar unless constrained. Same performance.
- bigint for surrogate keys at scale. Avoid overflow.
B - Time and IDs
- timestamptz for events. UTC storage.
- uuid or bigint identity for PKs. Pick one per table.
- Avoid timestamp without time zone. Ambiguous local times.
C - Semi-structured
- jsonb for optional facets. Not whole entity.
- GIN only for proven query paths. Index cost is real.
- CHECK jsonb_typeof when shape matters. Catch bad writes early.
FAQs
Can I change enum labels easily?
Renaming is OK; removing values is hard. Prefer lookup tables if labels churn.
Related
- Data Types Basics - tour
- Schema Design Best Practices - constraints
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.