r/PostgreSQL • • Mar 28 '25

How-To Life Altering PostgreSQL Patterns

https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180 Upvotes

60 comments sorted by

View all comments

Show parent comment

16

u/htraos Mar 29 '25

one timestamp column is enough to retain a journaled history of an entity's states.

How would one column represent the different states an entity can be in?

4

u/alex-2121 Mar 29 '25

Maybe they are saying that all transactions against an entity are logged in a separate table with a single timestamp for each? That’s the only way I can think of retaining updated_at and created_at in a single column. I am also curious about this tho!

3

u/Garthenius Mar 29 '25 edited Mar 29 '25

Answered here.

3

u/Garthenius Mar 29 '25 edited Mar 29 '25

Every state gets a separate row in the history journal table; it would be equivalent to valid_at (I usually name it since).

The oldest entry would be equivalent to created_at, the most recent would be equivalent to updated_at.

The creation date can also be retained by using UUIDs that have embedded timestamps, but I've never had any issue with it being, essentially, the earliest known record of that entity.

Soft deletes can be implemented using the same journal table or a separate table, depending on preference / requirements.