r/Database • • 17d ago

Thinking of adding soft delete (deleted_at)? Decide these 5 things first

  1.  Unique constraints.  A deleted user still "uses" their username. Use a partial unique index (`where deleted_at is null`).

  2.  Every query must filter deleted rows.  Miss one, and deleted data shows up in a report or API response.

  3.  Foreign keys.  What happens to orders that belong to a soft-deleted customer?

  4.  Real deletion.  Privacy laws may require you to actually erase data, so soft delete can't be the only option.

  5.  Restoring.  What if the username was taken again while the old account was deleted?

Do you use soft delete, or did you move to archive tables instead?

77 Upvotes

21 comments sorted by

19

u/Imaginary__Bar 17d ago

Type 2 SCD table and have a valid-from and valid-to date/time

3

u/Beautiful-Hotel-3094 17d ago

Basically this. All you need is SCD and I recommend a mix rather than type 2. Valid from, valid to and an is_active flag that you can index on.

2

u/elkazz 17d ago

Say more

10

u/xylarr 17d ago

An SCD Type 2 (Slowly Changing Dimension Type 2) table is a data warehousing technique that tracks the complete historical timeline of changes to dimension data (such as a customer's address or an employee's department) by inserting a brand-new row for each update rather than overwriting the existing data.

11

u/shayhtfc 17d ago

You can use database views that filter out the deleted rows, and then reference those from your app instead of the underlying tables

1

u/n_dev_00 10d ago

this seems like best option. i just read that we could even write to views. so, once we write to delete field, it will also dissapear from view.

3

u/wedgelordantilles 17d ago

Record immutable events project views

2

u/tehbilly 14d ago

Why would you model anything in computing as a sequence of events?! /s

2

u/Khmerrr 17d ago

1 unique filtered indexes are ok

2 views

3 it depends on business rules

4 ok

5 fix your design bugs to cover this scenario

Archived data is not meant to support deletion.

2

u/coderarun 17d ago

Columnar databases do this internally so you don't have to. They also handle all of this stuff just to pass ACID.

1

u/EconomixTwist 16d ago

That’s not what a columnar database is

1

u/coderarun 13d ago

Do you know of a columnar database which does NOT do this? Because of how they're stored, random deletes are expensive.

2

u/TomaRavelli 17d ago

I moved to archive tables a long time ago. Soft deletes get messy fast with unique constraints and cascading foreign keys. And having to add a deleted_at filter to every query is how ghost data ends up in prod reports. Moving deleted rows to a separate archive schema keeps the active tables small and the scans fast, and there’s no risk of old rows leaking into your app logic.

3

u/Disastrous-Waltz493 17d ago

stop polluting your live tables with tombstones. Move the data to an archive schema immediately after the delete action triggers. Soft deletes are a lazy design that forces every single query to remember a where clause and breaks index selectivity over time. If you need audit history, use event sourcing or temporal tables, not a deleted_at column that turns your database into a graveyard of zombie rows.

1

u/No_Witness_5295 17d ago

1. active vs inactive

4. mainly just need to delete data that could be used to identify that user

5. unique id, or dont allow username reuse even if deleted

1

u/raks1991 10d ago

Two Postgres gotchas with point 1 that tend to show up later.

Upserts: ON CONFLICT (username) won't infer a partial unique index unless you repeat the predicate:

INSERT INTO users (username, email)
VALUES ($1, $2)
ON CONFLICT (username) WHERE deleted_at IS NULL
DO UPDATE SET email = EXCLUDED.email;

Without the WHERE, inference finds no matching arbiter and the INSERT errors out.

Foreign keys: an FK has to reference a primary key, a unique constraint, or a non-partial unique index, so REFERENCES users (username) can't use the partial one. Keep FKs on the surrogate id.

On the view idea further down: a plain WHERE deleted_at IS NULL view is auto-updatable, but if you add WITH CHECK OPTION, soft-deleting through the view gets rejected because the updated row would no longer be visible through it.

-1

u/EconomixTwist 17d ago

Every query must filter deleted rows.  Miss one, and deleted data shows up in a report or API response

What do you mean... Miss one

-1

u/ShotgunPayDay 17d ago

Soft delete? Do you mean a tombstone?

-2

u/mcspuder 17d ago

Just move to a better database. Datomic handles this much better

2

u/Zardotab 17d ago edited 17d ago

Such products are usually tradeoffs. Doing Need 1 and Need 2 better doesn't necessarily mean a product also does 3 through 20 better. I'd like to see a report card that fits my shop's domain and needs. Established RDBMS are chalk of features that a start-up has a hard time competing against, by the way.

There are add-ons for full audit trails, by the way. Some RDBMS have that feature built in.

2

u/Few_Committee_6790 17d ago

Moving to a different technology is an option but in reality not a solution. Try getting upper management to to move databases for this. Get a clue