The challenges of soft delete • Hacker News Board

Re: The challenges of soft delete

#121

Post by taeric » Wed, Jan 21, 2026, 2:29 PM UTC

postexitus wrote: post #98

MaxGabriel wrote: post #13

This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…

Soft deletes in banking are just a Band-Aid to the much bigger problem of auditability. You may keep the original record by soft deleting it, but if you don't take care of amends, you will still lose auditability. The correct way is to use EventSourcing, with each change to an otherwise immutable state being recorded as an Event, including a Delete (both of an Event and the Object). This is even more problematic from…

Isn't this, essentially, backing into double-entry accounting for all things banking? Which, fair, it makes sense.

Re: The challenges of soft delete

#122

Post by moring » Wed, Jan 21, 2026, 2:39 PM UTC

Both the article and many comments here seem to miss that UPDATE deletes data -- the previous value of the field being updated -- which is a serious problem if soft-delete is your tool to keep old data. If you actually want historical data, you'll need logs or go straight to event sourcing.

Re: The challenges of soft delete

#123

Post by ctxc » Wed, Jan 21, 2026, 2:40 PM UTC

MarginalGainz wrote: post #107

The hidden cost we battle in e-commerce isn't just DB storage/performance, it's Search Index Pollution. We treat 'availability' as a complex state machine (In Stock, Backorder, Discontinued-but-visible, Soft Deleted). Trying to map this logic directly into a Postgres query with WHERE deleted_at IS NULL works for CRUD, but it creates massive friction for discovery. We found that strict CQRS/Decoupling is the only way…

And why would one do that? For marginal gainz?

Re: The challenges of soft delete

#124

Post by pif » Wed, Jan 21, 2026, 2:51 PM UTC

KronisLV wrote: post #82

nine_k wrote: post #18

Earlier quoted context omitted.

> DELETEs are likely fairly rare by volume for many use cases All your other points make sense, given this assumption. I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. > Undoing is really easy Depends on whether undoing even happens, and whether the act of deletion and undeletion require audit records anyway. In short, there are cases when soft-deletion works well, and i…

> I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. I think we largely need support for "soft deletes" to be baked into SQL or its dialects directly and treated as something transparent (selecting soft deleted rows = special case, regular selects skip those rows; support for changing regular DELETE statements into doing soft deletes under the hood). https://news.ycombinat…

> I think we largely need support for "soft deletes" to be baked into SQL

I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page.

Re: The challenges of soft delete

#125

Post by ndr » Wed, Jan 21, 2026, 2:52 PM UTC

arnsholt wrote: post #104

MaxGabriel wrote: post #13

This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…

The core system at my previous employer (an insurance company) worked along the lines of the solution you outline at the end: each table is an append only log of point in time information about some object. So the current state is in the row with the highest timestamp, and all previous stars can be observed with appropriate filters. It’s a really powerful approach.

This is also a recurring pattern when using bigtable.

Re: The challenges of soft delete

#126

Post by iamleppert » Wed, Jan 21, 2026, 2:56 PM UTC

There is another solution I use all the time: move deleted records to their own table. You probably don't need to do this for all tables. It allows you to not pepper your codebase with where clauses or statuses, everything works as intended, and you can easily restore records deleted by mistake, which is the original intent anyways. You can easily set this up by using a trigger at the database level in almost every database, that just works.

Re: The challenges of soft delete

#127

Post by ndr » Wed, Jan 21, 2026, 2:58 PM UTC

arter45 wrote: post #112

gleenn wrote: post #43

Earlier quoted context omitted.

If you're implementing immutable DB semantics maybe you should consider Datomic or alternatives because then you get that for free, for everything, and you also get time travel which is an amazing feature on top. It lets you be able to see the full, coherent state of the DB at any moment!

My understanding is that Datomic uses something like Postgres as a storage backend. Am I right? Also, it doesn't support non-immutable use cases AFAIK, so if you need both you have to use two database technologies (interfaces?), which can add complexity.

Datomic can use various storage services. Yes, pg is one option, but you can have DynamoDB, Cassandra, SQLServer and probably more.

> Also, it doesn't support non-immutable use cases AFAIK

What do you mean? It's append only but you can have CRUD operations on it. You get a view and of the db at any point in time if you so wish, but can support any CRUD use case. What is your concern there?

It will work well if you're read-heavy and the write throughput is not insanely high.

I wouldn't say it's internally more complex than your pg with whatever code you need to make it work for these scenarios like soft-delete.

From the DX perspective is incredibly simple to work on (see Simple Made Easy from Rich Hickey).

Re: The challenges of soft delete

#128

Post by ndr » Wed, Jan 21, 2026, 3:06 PM UTC

ndr wrote: post #127

arter45 wrote: post #112

Earlier quoted context omitted.

My understanding is that Datomic uses something like Postgres as a storage backend. Am I right? Also, it doesn't support non-immutable use cases AFAIK, so if you need both you have to use two database technologies (interfaces?), which can add complexity.

Datomic can use various storage services. Yes, pg is one option, but you can have DynamoDB, Cassandra, SQLServer and probably more. > Also, it doesn't support non-immutable use cases AFAIK What do you mean? It's append only but you can have CRUD operations on it. You get a view and of the db at any point in time if you so wish, but can support any CRUD use case. What is your concern there? It will work well if you're…

Also good real-world use case talk: https://www.youtube.com/watch?v=A3yR4OlEBCA

Re: The challenges of soft delete

#129

Post by redman25 » Wed, Jan 21, 2026, 3:09 PM UTC

da_chicken wrote: post #50

nine_k wrote: post #18

Earlier quoted context omitted.

> DELETEs are likely fairly rare by volume for many use cases All your other points make sense, given this assumption. I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. > Undoing is really easy Depends on whether undoing even happens, and whether the act of deletion and undeletion require audit records anyway. In short, there are cases when soft-deletion works well, and i…

> I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. At that point you should probably investigate partitioning or data warehousing.

Exactly, partition the table vertically by month. Surprised no one else seems to be mentioning this.

Re: The challenges of soft delete

#130

Post by dagss » Wed, Jan 21, 2026, 3:17 PM UTC

I just long for DBs to evolve from "stateful" to "stateless". CQRS at the DB level.

* All inserts into append only tables. ("UserCreatedByEnrollment", "UserDeletedBySupport" instead of INSERT vs UPDATE on a stateful CRUD table)

* Declare views on these tables in the DB that present the data you want to query -- including automatically maintained materialized indices on multiple columns resulting from joins. So your "User" view is an expression involving those event tables (or "UserForApp" and "UserForSupport"), and the DB takes care of maintaining indices on these which are consistent with the insert-only tables.

* Put in archival policies saying to delete / archive events that do not affect the given subset of views. ("Delete everything in UserCreatedByEnrollment that isn't shown through UserForApp or UserForSupport")

I tend to structure my code and DB schemas like this anyway, but lack of smoother DB support means it's currently for people who are especially interested in it.

Some bleeding edge DBs let you do at least some of this efficient and user-friendly. I.e. they will maintain powerful materialized views and you don't have to write triggers etc manually. But I long for the day we get more OLTP focus in this area not just OLAP.