Hacker News Reader

Comment by hamandcheese | Hacker News Reader

Pretty much every soft-delete implementation I've seen involves modifying a tables schema to add a deleted_at column.

Invariably, this becomes a rather annoying thorn in my side when I need to do more advanced indexing, like unique indexes for upserts. Nothing impossible, but I'm quickly swimming upstream against whatever ORM I happen to be using (I'm looking at you, Prisma).

What I haven't seen much of is a trigger-based system which moves the deleted record into an entirely new table. Is it possible to make a deletions table that always mirrors the source tables schema? I know table inheritance is a thing but I don't know if that applies to future schema changes.

Anyways, I know deleted_at quite well, but I long for something better.

View on HN

I wonder if this could be solved with partitioning on the deleted_at column; you’d only need two partitions: one for live data (deleted_at is null) and one for deleted data. Then, in Postgres at least, you could just query the live partition directly when you want to skip deleted records.

You can just build an index with a condition, without even.partitioning the table.

Except, the index doesn't help with avoiding the WHERE clause

Yeah, but Ecto does. You can implement the Repo.prepare_query/3 callback to make sure the query has WHERE deleted_at IS NOT NULL. In fact, that's the exact example in the documentation:

https://hexdocs.pm/ecto/Ecto.Repo.html#c:prepare_query/3

Now I have two problems

You don't have to reply to a comment if you don't really have anything else to say. What's the point of saying "Now I have two problems" without saying what the new problem is? So what is it? It can't be that an ORM has been added to the mix, since Ecto isn't an ORM.

The problem is you have to manage indexes in the database and keep that in sync with each application that queries it. Ecto is one of those applications.

This is a very interesting idea. I didn't know you could query a partition directly!

> What I haven't seen much of is a trigger-based system which moves the deleted record into an entirely new table.

I've used https://github.com/nearform/temporal_tables before for that, has been pretty solid. Still need to manually keep the schema in sync, though.

I like this (1). Like anything related to triggers, you need to be mindful of tables with heavy throughput and maybe move the logic to "code" so you can batch or skip things, but for most tables it works like a charm.

1. https://brandur.org/fragments/deleted-record-insert

Author here.

You are right, the main downside of the approach in the article is that you have to remember to filter the deleted_at column on constraints and consider how foreign keys cascade.

About triggers, we do link at the bottom to an article that uses them: https://brandur.org/fragments/deleted-record-insert

Since I am exploring a sync engine, having a separate table for deletes means I’d need to run additional queries when catching up, which led me to explore the single table approach. Let’s see how it evolves.

> trigger-based system

You read my mind. Yes, this is possible. I haven’t done it in prod, but I made a PoC. As to table inheritance, yes, you can also do that, and yes, in PG anyway it automatically propagates schema changes.

There are quite a few gotchas [0], though, so think carefully before doing so.

The below assumes you have a user/customer table named customer, and another copy named inactive_customer. One other note: if you use an IDENTITY for the PK in the main table, you’ll want to drop that first in the inactive_customer table, so the ids match. Example below.

    ALTER TABLE inactive_customer ALTER COLUMN id DROP IDENTITY;

    CREATE FUNCTION move_inactive_customer()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $function$
    BEGIN
        INSERT INTO inactive_customer(<schema>) —-replace schema with yours
        VALUES (OLD.<col> for col in cols) —-replace this pseudo-loop with your schema
        ON CONFLICT (id) DO NOTHING —-modify for your table constraints
        RETURN OLD;
    END;
    $function$;

    CREATE TRIGGER redirect_customer
    BEFORE DELETE
    ON customer
    FOR EACH ROW
    EXECUTE PROCEDURE move_inactive_customer();

[0]: https://www.postgresql.org/docs/current/ddl-inherit.html

> unique indexes for upserts

Would simple "create unique index on .... where deleted_at is null" work for you?

I mean, Prisma is just awful, that's hardly a problem of soft deletes.

At my previous job we used prisma, and what a terrible system, the amount of problems I saw there. Some have been fixed since, but it shouldnt take 3 major versions of an ORM to use an actual upsert operation instead of doing

if select() then update() else insert() at the client side, which is all sorts of wrong. Or the 50ms of latency it introduced per query by starting a separate process that generated the queries, or the lack of schema flexibility, or the idiotic new join features that transforms rows into jsons in a destructive way.

It's just bad software

I've hit a lot of rough edges with it too. A few off the top of my head:

- in memory joins (until they added the preview relationalJoin feature)

- no partial indexes

- no select for update

- no table partitioning (though if you do this out of band it all works fine)

- use of create type enum instead of check constraints makes maintenance of enumerated columns more challenging (IMO)

- little support for less common (but useful!) data types like geospatial, intervals, ltree, etc

- the reliance on wasm/rust makes debugging what it's doing difficult

- the connection pooling defaults are surprising (never reaps idle connections, AFAIK not cgroups aware and over provisions the pool size based on host CPU count)

For simple use cases it's appealing, and I suspect it's probably a better experience with nosql databases, but when you're used to taking full advantage of postgres' feature set it gets frustrating quickly.

There's issues in the tracker for basically everything above, and some may have even been solved by now - in a few more years it might be a better experience.

> in memory joins (until they added the preview relationalJoin feature)

This, to me, was the single issue that made me forever not take Prisma seriously. I get that “ship now, fix later” is a mindset, but you simply cannot ship an RDBMS wrapper that doesn’t handle JOINs. That’s an integral part of the system.

> the connection pooling defaults are surprising (never reaps idle connections, AFAIK not cgroups aware and over provisions the pool size based on host CPU count)

Tbf I have yet to find a piece of software that hasn’t messed up cgroupsv1. That’s not really an excuse – read docs – but at least everyone seems to make the same mistake.

What about foreign keys referencing the row you moved?

I suppose "on delete cascade" would fire appropriate triggers in the downstream tables, and "on delete set null" will take care of upstream and peer tables.

If this is not sufficient, and a live record in one table may legitimately refer to a "dead" record in another table, then well, maybe "soft delete" is what you need, and your data just has a deactivated state.

Yes it can be done with rules, inheritance, triggers, and updatable views.

See my answer on stack overflow

https://stackoverflow.com/a/78694335/4188138

However, in my experience, it's more easily maintainable to use normal deletes, have a trigger to move it to a schemaless table (jsonb column type that contains all of the data at the time of delete), and have manual restore logic via a good UX.

Couldn't you apply all the indices as partial where deleted at is null?

For Django, django-simple-history [1] is killer at this. It doesn't track perfectly in the same way as temporal tables (if a DB operation doesn't trigger a save hook, it won't be tracked) but the ergonomics for Django work is pretty unbeatable. Great integration into the ORM + auth + admin. History tables automatically derived from model definitions with sane nullability defaults, sane migration strategy, and hackability where needed.

It's made "can we get an audit table / undo delete for XYZ?" feature requests really easy.

[1] https://github.com/jazzband/django-simple-history

Uh. Why? Deleted at is the last ModifiedOn timestamp.

Nothing should modify it anymore after that.

Reply on news.ycombinator.com