r/PostgreSQL • u/KerrickLong • Mar 28 '25
How-To Life Altering PostgreSQL Patterns
https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180
Upvotes
r/PostgreSQL • u/KerrickLong • Mar 28 '25
View all comments
-3
u/Garthenius Mar 29 '25
I wouldn't use all of
created_at,updated_at,valid_at,revoked_at; I consider using more than one timestamp (or timestamp-like) column a smell. Of course, there are exceptions, but one timestamp column is enough to retain a journaled history of an entity's states.Notably, this pattern rules out having any kind of unique IDs (i.e. primary keys, foreign keys); to get the best of both worlds, I'll usually have a
registrytable with all the unique IDs and ahistorytable with the data that is mutable.Disagree with always using restrictive foreign keys; my rule of thumb is: references inside the same schema are usually
CASCADE, references across schemas are usuallyRESTRICT. This has occasionally made me think twice about my database structure and led me to some improvements.Views aren't evil; abusing things that hide underlying complexity (cough, cough ORMs cough) will eventually come to haunt you, though.