I often face an annoying problem: I would like to restore a record that has been deleted or see how it looked before the last update. There are of course many ways to implement it manually and thoroughly by creating additional tables to keep deleted records and all changes, but frankly, just thinking taking care of it tires me out and I forget about it.
I have heard of people who have never
DELETE records, instead of defining a
deleted_at timestamp or whatever, then always take that into account in the app. However, I find this kind of "hack" at once messy and fragile. I am not comfortable with such a mechanism, no matter how often. Just the idea of not really using the
DELETE The SQL keyword to delete a message seems wrong to me.
Is there a way to tell PostgreSQL?
Hello, PostgreSQL. Do you see this table called "important stuff"? Well, I would like you to keep an eye on any DELETED or UPDATED record in this table, and only delete these "event records" for one year after they happened. This will give me a year window to go back to the changed / deleted data, without storing it forever and without having to code my own system. I would like to be able to access this data in a meaningful way.
I hope it exists, because it would be nice to just be able to mark a table as "important" and then have PG keep an eye on it for me. Let's be frank: if I have to do it myself, it probably won't happen, and I'll be 100% "blind" to everything that has changed in my tables.
Please do not make me install additional software or strange extensions. I hate that.