Postgres FM - Soft delete

Episode Date: June 28, 2024

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) https://brandur.org/soft-deletionEasy alternative soft deletion (blog post by Brandur) https://brandur.org/fragments/deleted-record-insertOur episode on auditing https://postgres.fm/episodes/auditingCREATE FUNCTION … SECURITY DEFINER (docs) https://www.postgresql.org/docs/current/sql-createfunction.htmlPrinciple of least privilege https://en.wikipedia.org/wiki/Principle_of_least_privilege~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:Jessie Draws for the elephant artwork 

Hello, this is PostgresFM. I don't remember the number of episodes. This is Nikolai Samokhvalov from Postgres.ai and my co-host as usual, Michael Christofidis, PgMaster. Hi, Michael. Hello, Nikolai. I think this is 103. Okay, not bad. So, we discussed what? discuss what yeah so it's one of those things that's come up in discussion a few times and we said we should do an episode on that one day and so we finally got to it and it's soft deletes which is a pattern that a lot of us have come across whilst working with databases more generally it's not specific to postgres but it's the idea of instead of deleting data, not deleting it, but hiding it from the application in some way. Let me explain it for regular, like how it feels

since I've built three social networks in the past. I used soft deletes a lot, of course. And then like Postgres is not good with deletes due to how MVCC is organized, vacuum and so on. Deletes are not good, right? Like it's like old, old idea. Although each update consists of delete and insert in physical level, right? Unless it's a hot update.

So how it feels in the eyes of millions of users, I know. And it's not only about the projects I've built. Almost any social media uses it. You go and try to unregister your account. You press all the buttons, say, delete me or something, like everything. Or sometimes you need to write a support or something. But usually good services have this button ready for you. So I want to delete my account fully.

And then you find out that, for example, depending on implementation, then you find out that your page is, I don't know, some small artifacts. You can see and feel that your page was not deleted, or your account was not fully deleted. One of these artifacts not artifacts are signs that soft delete is used is you can actually restore it right? Right? You can, for example, Facebook, try to delete yourself on Facebook, it will give you many chances to restore and even after you delete it, you still can restore it. So you can, like one month later, you can just press a button and

restore. What users want when they are angry about some service? They don't want to deal with it completely. Something can happen in life, right? They can see having an account as a problem, for example. They think it's a mistake to post such pictures or to do some conversations, doesn't matter. I want to delete it. I'm a new person right now. It's there, right? So delete. But then the server says, you can restore. Did you delete my data or no? And then compliance, we have also interesting points. In some cases, we have mandatory demand to store data sometimes, right? We have both sides, right? Yeah, we have laws that demand we have to store data for a certain amount of time in some environment. And vice versa.

Sometimes if user decides, the service must delete data. So delete, the word delete has many meanings, right? It's not delete SQL statement, delete. When you delete your account, it does mean the service executed actual delete. Most likely, they just executed updates or delete with triggers and so on. We will discuss and dive into details. But for users, it's really annoying to feel lack of ability to truly delete.

I must say, like I saw it and I was on the other side of i i was on both sides many times actually yeah and i i know how it feels when you want to delete for sure like 100 i won't delete my data it's my data deleted no you can restore it means it was not actually deleted yeah for sure well i i think we can come back to the user side. Well, I think there's so many parts of this that are there are different implementations i think various companies and organizations are implementing features around deletion around kind of making sure people know exactly what they're doing before hitting delete you know write the words of your account name in everyone's come across these kind of make sure you type out yes i'm sure i really want to delete my account

and I will not beg support to undelete it later. We give you one month to reconsider, right? Exactly. Or we can recover it, but only in the next 30 days. Yeah, exactly. I remember another way, another sign that you can see that soft delete was applied is instead of having 404 error, 404 error, you go to your X page like you deleted it, but

you see something like account deleted. It means that records still exist in database, right? Just changed its status. So status like active, deleted, right? Just changed its status. So status like active, deleted, right? Pending, like pending, pending, it means you need to activate it, like email activation or something, phone activation. Then it's active and then it's deleted. Life cycle, right? In my mind, that's kind of like a bug though, right? Like that's just a poorly implemented soft deletion.ion yeah but it's so common

and i also i actually implemented i implemented soft deletes in many various ways but honestly this morning thinking like walking my dog by the way hello to everyone who is running walking dog like i don't know like riding bicycle uh yeah i know we promised to keep 30 minutes, but it's good for you to run longer and ride bicycle longer and for your dog as well. So walking my dogs, I was thinking, soft delete, soft delete. I also chatted with our boss to like to brainstorm what to discuss. And I realized that if I needed to implement it right now, looking back to my 20 years of experience through social networks and many other systems and

our customers, all those ways to implement soft delete, what would I choose now? I don't know. Maybe by the end of this discussion, I will have some clarity. But it's so difficult. It's not an easy topic, actually. I expected a simple topic today. I think it's simple if you only consider a single case at a time. If you want to give generic advice, I think it becomes more complicated. And we also dove into the most complicated version,

which is deleting a whole account which has multiple things. Yeah, let's talk about only one table. Yeah, propagation is a different topic. Consider only one table. We have many choices. For example, one I already discussed, like status. So you have pending, active, deleted, something, archived. Or it can be a different column is deleted.

By default, it's false. You can set it to true. Both are very similar approaches. Both I don't like at all. I quite like deleted at, which is with it being null by default, and then a timestamp as to when it was deleted. I would not choose these approaches, neither of them, today.

I chose them in the past, but today, no, thank you. Why? Because then you need to deal, like, optimizing your queries, thinking about indexes. Most of the time, queries work with active data. So you need to keep all your indexes probably partial with the where clause having this filter to deal only with active records. And you accumulate like non-active data set in the same table,

which is not useful at all, right? And also foreign keys. Yeah, unique constraints. There's so many downsides. That's complexity. Well, and you need to adapt your application to always view things where deleted app is not. Right.

Because otherwise people will see deleted data. So there is definitely complexity to that. But implementation-wise, I think it's on the simpler side. I think there are some advantages. I maybe would choose it only if I know that deletes will be much less than 1% of all data. But who knows in advance, right? Maybe we will need to delete a lot.

Sometimes from social media experience, but who knows in advance right maybe we will need to delete a lot like sometimes for like from social media experience spammers like in ways they come they can register million accounts and you need to delete them at the same time to follow your procedures you developed if you develop soft deletes approach you deleting fake accounts is also deletes right so you need to still keep them in the same table and it's like a lot of records which will sit on the disk and most importantly in memory in caches let's talk about the use cases for it because i think they're different enough that i think it will then become clearer when we would use different ones like for example I think the primary use case I the only use case I've ever used soft deletes for in the past I've always I've always been at companies and working on products where we

haven't done soft deletes by default I've only ever had it implemented it once for an application because several times a year we were restoring data that had been deleted accidentally or prematurely. So it was... And you didn't have DatabaseLab to go back in time easily.

We could... This was actually on Microsoft SQL Server database at the time and we would actually restore from a backup. That's what we'd end up doing. It takes many hours. The larger the database, the longer it takes. And it's painful and manual.

And we were doing this often enough that we decided to implement soft deletes, but with a slight difference. I think sometimes people describe soft deletes as if it's like permanently going to be in that soft deletion state. We would do it and then have a, like a cron job that anything older than, so people would delete something by mistake and very quickly email support and say,

I've deleted the wrong project in this case. We need to restore. Yeah. And restore procedures complex. Yeah. But it was always quickly. It was always within a day or two. It's softer, it's quicker. No, sorry.

So in this case, with this application, people that deleted things accidentally, they knew pretty, like almost straight away that they had done it and that they had regretted doing it. So we knew we could, for example, have this data in a soft deleted fashion with deleted app with a timestamp and then we could have a cron job hard delete or actually delete data that was more than like that was deleted at more than 30 days ago so in that case

it meant we could keep the the structure simple only one column, update the application only in a few places to view data that wasn't deleted yet. So it was a relatively simple case where we were having to restore data from time to time. It's a good point. So I took extreme case when we discuss accounts, and it's sensitive to personal feelings are affected. But you're describing basically the same thing as emails in Gmail

and the recycle bin or how it's called, trash folder. Yeah, like on a desktop, yeah. Right, so you put it there basically when you do deletes and it sits there 30 days or so, and then automatically hard deleted, actually. Exactly. And you can restore it any time, and users usually appreciate this feature

and considering it as good if you can undo your action. I agree. When we talk about some objects inside the account, right? So some data. The only difference in this case is we didn't implement the ability to view what's in the like in those two cases but we could have done and that's actually potentially would have been nice for users yeah actually in this case this approach now i'm thinking yeah in this case i would probably choose some like status approach because

it's actually the same data we just don't show it in one place but we show it in another place so like it's just some category that's it right you're right so in this case i would prefer actually status maybe timestamp of changes but maybe i would put timestamps to special different like audit like log style append only table to track actions when some action was done. Yeah, but you're right, actually, yeah. Maybe this case, let's call it in place of delete when we keep data in the same table and so on. I was thinking use case-wise,

that's if you're implementing it to avoid or to make restoration easier or undelete easier. And viewing deleted data, in some cases, like trash data, if you want to allow users to review it, why not? Yeah, well, that's the other, like, there's another use case for soft deletes that I've heard and seen people write about, which is to give you the ability to examine it or to audit it or to look through why it was done or who did what. So I think there are some. Exactly.

And I know we've done a whole episode on audit, but it could be not necessarily for regulatory purposes, but it could be for debugging purposes. And there we can have a hard delete button for users as well. Only there, not in main place, but if object is already soft deleted, we could allow users to actually delete. But let's talk about Postgres, because This is UX discussion. What else? Compliance. Sometimes you do need to delete data for sure,

keeping it only in some backups and also have some policy to delete there as well. I mean, backups should be deleted as well at some point. Sometimes you do need to keep information and soft deletes help to comply some policies and regulations. Depending on law, you need to follow. It's hard. But honestly, I prefer, speaking of law, I prefer when I need to delete everything. But for convenience and as a business owner, it's actually much more convenient if you still have data all the time and you can review what happened and restore it.

Well, sometimes, depending on the nature of your business, I see more and more guys follow, it's like some compliance rules, but I see more and more companies implement the rule that Slack deletes messages after some time and like everything is deleted I don't know like maybe it's because of course maybe it's some compliance actually I don't know well I think there was an age where this going back to kind of philosophical side of things but I think there was a time where people I think I remember

reading headlines like data is the new oil and things like that. And people thought hoarding as much data or gathering as much data as they could would be valuable in the future for analysis reasons or for mining, that kind of thing. And I feel like with a lot of data breaches, especially, sorry, I think I've got a siren in my background. With data breaches, I feel like things flipped and people started to think of having data as a liability and the more data they held or the more personal it was the more at risk they are of or the the bigger target they become for for attacks i feel like that's changed even within the last well even within my career of how people view holding data if you don't really need it. Yeah. Also performance reasons.

If you keep everything, it's hard. I see many successful e-commerce companies, they do need to actually delete data. And having soft deletes, they at some point decide to clean up and actually delete data. So in place of soft delete, you just have a column or a couple of columns, and then you have a lot of partial indexes, obviously, maybe. Maybe, maybe not. It depends on the distribution and selectivity you have. And how could we improve this? What about having a special partition

or set of partitions and include this column to partition key? It would be transparent for application if we update status setting from active to deleted. In this case, the row will be deleted in the main partition, like the actual working partition, and it will go to this kind of archive partition, right? Yeah, it would be like moved. So I guess it's similar

in terms of write-ahead log impact. But actually, could it be higher? I guess you're forcing no hot updates, but I'm guessing you're not going to get hot updates anyway because it's the delete to that sort basically yeah so you cannot have had a hot update here so zero chances because it's it's actually deleted actual insert and yeah so and i guess hot updates is a good thing to have but in this case you should avoid partial back to a single table in just a column if you want hot updates to be used when you update this

column you cannot use this column in partial indexes in the where clause of those indexes, right? You must avoid it. It works well only if you have, as I said, only low part of your table. Like deletes are rare. Yeah, yeah, yeah. So another solution I see mentioned that I've not used is using a view. So defining a view where deleted that has no value, for example. Yeah, I don't know.

You know, like when you're in love with something many years and then this love is converted to hate. This is my relationship with views because my thesis, bachelor or master, I don't remember, was about updatable views and then updatable XML views. And I spent so much time and I worked with them so much time.

Now the only views I can deal with now these days, myself in projects where I have full control, only postgres views oh that's nice okay everywhere else i avoid them because i don't want to deal with dependency hell later yeah because it's just a lot of overhead you need to you just change something or add column or drop column you need to recreate your views and logs. And logs, when you issue DDL, I don't want this. But yes, views can be used in many cases, but you cannot create an index on regular view.

Again, I'm speaking of performance. Just views will hide it, right? And that's it. I mean, hide some rows. Exactly. But it also makes the implementation a little bit easier because you don't have to update. You don't have to make the application aware

of your soft delete implementation. Well, yeah. You could just point the application at the view now. The application doesn't see those rows at all, right? Should we move on to some other, like... Approach, separate table approach. Yes. So straightforward approach.

Let's just have the table with same structure, right? Maybe not the same set of indexes because we don't need all of them, maybe, right? And just insert to that table maybe using some trigger when delete happens. Yeah, so the two approaches I've heard are a shadow table for each table that you want to... Yeah, I don't know if that's the correct term. I had this. And I also hated this. Yeah. Because DDL, maintaining schema

but I did see a neat alternative to this in a blog post by Brander who's got a couple of blog posts so let's discuss why it's bad in detail, just to clarify first of all you need to create, if you have many tables where you want soft delete to be

implemented you always need additional table it's just overhead and also you need every time you create a column for example or your name column or something you also need not to forget to do it in the shadow table yeah and living with such schema many years in one project, I must admit, it's a huge headache. People always forget it. You can try to invent some procedures and automation not to forget it, but still deployments to production when shadow table DDL was forgotten, quite significant risk. So it's a lot of objects to deal with.

Of course, you can put it to some, like, say, schema named shadow, for example, and keep the table names exactly the same as in the original schema, public or something new, if it's a new project, right? So public schema is not popular anymore, right? So, and it's good because it means convenience because exactly the same table names. But it introduces a lot of overhead, it requires good culture of automation, like tooling,

not to be always in consistent state. Yeah, I think there are a lot of difficulties as part of this. Obviously, it does come with some benefits, like easier to restore than the alternative way of doing this. Let me guess about alternative way. I also implemented it. I didn't read this post by Brando, but I can guess. I also implemented it. I didn't read this post by Brandor, but I can guess because I also implemented it.

I think we walked on the same paths, right? Sounds like it. So you create a single table with probably some ID, maybe some surrogate ID, then table name, who deleted, timestamp, and then just JSONson or json b column where you just put all the data from any table right and you can exactly okay why not preserving column names maybe even right so you yes i think they used like a toJSONB function.

Right. And you do everything in triggers, so everything, again, is transparent. When a delete statement happens, your row from any table which has this trigger is packed and sent to this table. Very convenient. I like this schema. It's super. So, again, this is optimized for, like like this is harder to restore from right because it's now in a difficult format and especially if like the scheme has changed in between well this is a price you you like you have trade-off. Either you survive any schema changes easily or you need to deal with some mutations during restore.

You need to understand, okay, this delete happened a year ago. We had different schema. What to do? You do need to do this. Or you need to maintain mirror schema changes all the time i would prefer actually deal with like since restore depending on the project of course if restore is not super common i would prefer this schema super convenient super universal yeah me too i think there's a lot of benefits here not for the one case I did implement soft deletes in the past, where the whole point was to make restoration easier in a very small number of cases. This case is very different to that. But yeah, I do think there are some tricky parts of this, though.

I think, for example, the compliance thing is tricky here. It's another place that you need to remember to delete data from in the future. If you get a GDPR request, you can't just search your normal tables for that data. You also have to search this delete log. And it might be tricky to search because it's JSONB data unstructured. So I think there are downsides. Right. Is it fair to call this soft delete?

Maybe it should be called just archiving? Good point. I mean, there's no definition of soft delete that I know. It's not part of the SQL standard. So I think it's more around what's it for? And maybe we've lost sight of why like, why are we implementing this feature? If it's for easy undeletes, maybe this isn't the right solution. But if it's for debugging or if it's for different purposes, then yeah, archiving makes some sense. But I guess it is,

it's deleting it in a way that it's not deleted. You mentioned as a user seeing some signs that it wasn't deleted. This avoids a lot of those, right? It's not in the same table anymore. Yeah, application can... If we define the trigger function with security definer, we can revoke or just do not grant any permissions to application user to deal with schema or this table. And that's it.

So application won't be able to reach the data at all. At very low, like at database level, database guarantees, it's not possible. But at the same time, trigger can still insert it because trigger function has security definer, meaning that it has permissions function has security definer meaning that it has permissions to run queries which are inside the function using the level of access of the user who defined this function cool i didn't know you could do that that's very cool yes it's called security definer you just when you create a function, trigger function, for trigger, you need to create a function. You just say Security Definer. And I like it because you can encapsulate some actions,

which normal user, application user, when I say application user, I mean database user, which is used by your application, right? And usually there are some tables that this user cannot access. For example, this, like a shadow table or shadow schema with multiple tables. But I still prefer single table. But you can still allow,

for example, specific insert encapsulated in this function and just grant permissions for specific action indirectly via the security definer. Nice. I like it. I use it all the time for many cases.

So, yeah. Better control what can be done, what cannot be done. So, basically, it's good for security reasons. It's good to forbid everything as first thing and then just allow some specific action in a very controlled manner. Intriguing. I feel like there's a phrase for this, isn't it?

Like security by minimum permission or something. I can't remember what the phrase is. It's like a security principle of least permission. Give things the least permission they need to do what they need to do. Right. Well, in former life, we called it blacklisting, whitelisting. I don't know why now it's not possible to tell this, right? But it means that you will just say nothing is possible

except this, right? And yeah, it's good. It's good. So yeah, it's a good approach, but you are right, restoration will be tricky. You need to develop specific procedures, deal somehow with schema evolution. And in this case, I would say if we talk about some account deletion, I would do this. If we talk about something like we have some objects user can create, modify, or delete, and we want support undo for deletion, like in Gmail with emails, when you just delete email, it goes to trash and then you can undo or delete forever, right? There's a button delete forever. This is hard delete. In case i would prefer maybe maybe still uh in place like in the same like just a column in the same table status or something nice right right both are we can say both are soft deletes

with their own pros and cons yeah right seems very reasonable Do you think they can coexist in the same project? Not because decisions were made by different persons, different developers. Well, I genuinely think at the end of Brando's blog post, he mentioned switching to this in all but a couple of tables. So I think they are currently a good example of a project that has both. You mean switching to JSONB shadow table? Yes, instead of you calling it in place. I think they still implement in place in a couple of places.

The best thing about this single shadow table with JSONB colon is that it's so easy to attach it to any table. It's just a trigger. That's it. It will slow down, delete a little bit. That's it. Yeah, because of the triggers. You could use it for the hard delete and then have it around for a while longer. We discussed about compliance and so on,

and I mentioned that for business owners, there are fears that something was deleted, hard to recover. It's very good for audit purposes. For example, you have some account in your application and there are some admins or owners, managers, and there are some regular users. Some users do something, they deleted something,

and you just need to not only see and undo it, but see when it was done by whom like i saw another person in a hacker news thread talking yeah not just audit but also what really happened so i saw somebody who had implemented a chat system let's say it's a support business to business support system and one of your your internal works for your company users said something they regret saying as part of this chat and then deleted one of your internal works for your company users said something they regret saying as part of this chat and then deleted one of those messages, it's really useful for an admin to see the whole history.

Or, you know, you mentioned social media. A lot of social media sites let you edit a message, keep it in the old version. Update, not delete. So this leads us to time travel, basically. We cannot name it soft updates right but tracking all versions it actually also possible they are the same approach and i think i did it yeah so when you have delete with this trigger with security security defin our function trigger function which just archives previous record in the trigger

function you use old key name it's this is just record right yeah and you you convert it to jsonb insert right absolutely the same trigger can be used for updates with any update maybe i would have some checks if some actual data was changed, not just something small was changed, right? But actual data like message was changed, right? Title, message, new.title, does equal old.title, and so on. Just to avoid spam archiving, which are useless records in this shadow table. So if I see something valuable has been changed with this update,

again, I pack old to JSONB and insert the same trigger, basically. Right? Yeah, I'm not sure in that case. It depends how often you need to be viewing those older ones, because it might be tricky to show the admin those.

How are you going to select that data back out to show them the old versions? It's a tricky thing. By the way, no, for updates, I think this is what I did actually. For updates, I would archive probably both old and new records because just to be able to show diff. Yes, it's like too much data to store but why not in this case i would extend shadow table with additional jsonb column like delete uh the records related to the deletes won't have it like it they would have only nows in that column. For updates, I would have two JSONB values.

Interesting. Old and new, basically. And then we can build some diff if needed, right? Maybe. I think this is getting beyond the scope of what... But this is interesting. It's useful and easy to implement and super universal. You can, again, attach it to any table

and have simple audit like that but what i what i don't like or understand is because this is a single shadow table across multiple how like what are you querying like how are you uh looking for that in a let's say it was a month ago in your how are you efficiently finding that in that shadow table what are you searching by well depending on the application right i would probably in this shadow table i would probably have some columns which would allow me to search faster or i would deal with searching inside jsonb why not genetics well yeah. This slows down updates. Yeah. There's a lot of, I think it's a tricky one because if it's a shadow table for all tables,

like having user ID or like having other columns that you could search by might not make sense for some of those. You can have null there. True, true, true. Well, yeah, I agree with that. you can have now there true true true well yeah i agree that especially if you add gin insert this implicit insert which happens during delete or update it will be not fast at all already because of gin so yeah overhead this is the price you have it yeah i like that it's another trade off like it depends what you want and it depends how much data you have and depends what proportion ever going to be deleted or what proportion ever ever going to be restored or looked into.

But yeah, I think it's really cool. I'll link up the blog post we mentioned. Any last things you wanted to add? Actually, you know what? During this discussion, I was curious what we have in PostgreSQL system, and actually we have exactly this approach I just described. We have a shadow table, single one for many tables to track deletes and updates. It's called audit log, and we have data before and data after two JSONV columns.

Yeah, and actually, a lot of additional stuff. This is just to be able to show admins of organizations what happened. In PgMuster, we have hard deletes. It's slightly different because it's just query plans, right? And you can always submit them again. But if you delete them, they're gone. So we decided not to at all. Yeah, and now an application cannot reach this part of database at all.

It's only possible like implicitly, as I described, security definer. So quite a good approach. Very reliable. Nice. All right. Well,

thanks so much, Nicola. Catch you next week. Thank you. Good chat. Bye-bye.