ENGINEERING NOTE / 08
PostgreSQL data modeling
A useful data model captures the rules that must stay true even when several requests arrive together. PostgreSQL gives those rules a durable place close to the data.
In this note 05 questions
What belongs in the database schema?
Use relationships, nullability, uniqueness, and check constraints for invariants the application must never violate. Application validation improves feedback, while database constraints protect the final write.
How do you choose an index?
Start from real query filters, joins, and ordering. An index can speed reads but costs storage and write work, so measure the query plan before adding one for every column.
When is a transaction necessary?
Use a transaction when several writes form one business action and must succeed or fail together. Keep it short and avoid waiting on external services while database locks are held.
How should migrations be deployed?
Review schema changes with the application version that uses them. For disruptive changes, add the new shape first, migrate existing data, switch readers and writers, then remove the old shape in a later release.
What changes for chat history?
Conversations, participants, messages, and read state have different lifetimes and access rules. A relational model helps preserve message history while membership checks control which records a caller can retrieve.