Soft delete | Postgres.FM 103 |

[ 🇬🇧_🇺🇸 Check out the subtitles - we now edit them, ChatGPT+manually! You can also try KZbin's auto-translation of them from English to your language; try it and share it with people interested in Postgres!]
Nikolay and Michael discuss soft deletion in Postgres - what it means, several use cases, some implementation options, and which implementations suit which use cases.
Here are some links to things they mentioned:
* Soft Deletion Probably Isn't Worth It (blog post by Brandur) brandur.org/so...
* Easy, alternative soft deletion (blog post by Brandur) brandur.org/fr...
* Our episode on auditing postgres.fm/ep...
* CREATE FUNCTION … SECURITY DEFINER (docs) www.postgresql...
* Principle of least privilege en.wikipedia.o...
~~~
What did you like or not like? What should we discuss next time? Let us know in the comments, or by tweeting us on @postgresfm / postgresfm , @samokhvalov / samokhvalov and @michristofides / michristofides
~~~
Postgres FM is produced by:
- Nikolay Samokhvalov, founder of Postgres.ai postgres.ai/
- Michael Christofides, founder of pgMustard pgmustard.com/
~~~
This is the video version. Check out postgres.fm to subscribe to the audio-only version, to see the transcript, guest profiles, and more.

[@Neoshadow42]

Subtitles are incredible, thanks guys!

[@kirkwolak6735]

I loved this one! I love how PG allows the entire record to be easily encoded and stored. We implemented an audit feature like this in Oracle. It was way too much code. We stored the OLD and NEW record. When I saw how easy it was for a single table in PG... I started falling in love... For us, the table, and the timestamp was always attached. To Answer the question: "How do you show this to the Manager" (the record changes. Assuming you stored the table_name, table_id columns with it. Then you would create a visible link that pointed to that record if there was an updated record in existence. And if it is a deleted recorded you will either need to merge it into the results, or show them NEAR the messages of the rough similar timestamp. You don't have to show them. Just show that they exist, with an easy way to get to them. FWIW, Day 1 in training. We showed users that all of their edits were stored. And Deletes were Stored as well. we only had to recover a couple of times.

[@drasticfred]

i always do add a reserve "flag column" to my tables, usually type int, no matter what the table serves for, it comes very handy, gives a flexibility that glue it any other table, service or logic etc.