4 ms·

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 annoyin

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.

pikdum 2y ago
> 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 https://github.com/nearform/temporal_tables before for that, has been pretty solid. Still need to manually keep the schema in sync, though.
out_of_protocol 2y ago
> unique indexes for upserts Would simple "create unique index on .... where deleted_at is null" work for you?
atonse 2y ago
What about foreign keys referencing the row you moved?
- nine_k 2y ago
  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.
fiddlerwoaroof 2y ago
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.
- nine_k 2y ago
  You can just build an index with a condition, without even.partitioning the table.
  - fiddlerwoaroof 2y ago
    Except, the index doesn't help with avoiding the WHERE clause
    - dqv 2y ago
      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 https://hexdocs.pm/ecto/Ecto.Repo.html#c:prepare_query/3
      - fiddlerwoaroof 2y ago
        Now I have two problems
        dqv 2y ago
        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.
        2y ago
MarceColl 2y ago
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
- mnahkies 2y ago
  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.
  - sgarland 2y ago
    > 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.
Fire-Dragon-DoL 2y ago
Couldn't you apply all the indices as partial where deleted at is null?
kristiandupont 2y ago
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 https://brandur.org/fragments/deleted-record-insert
NicoJuicy 2y ago
Uh. Why? Deleted at is the last ModifiedOn timestamp. Nothing should modify it anymore after that.
code_biologist 2y ago
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 https://github.com/jazzband/django-simple-history
josevalim 2y ago
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 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.
sgarland 2y ago
> 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 https://www.postgresql.org/docs/current/ddl-inherit.html
samanator 2y ago
Yes it can be done with rules, inheritance, triggers, and updatable views. See my answer on stack overflow https://stackoverflow.com/a/78694335/4188138 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.

pikdum 2y ago

> 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 https://github.com/nearform/temporal_tables before for that, has been pretty solid. Still need to manually keep the schema in sync, though.

out_of_protocol 2y ago

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

atonse 2y ago

What about foreign keys referencing the row you moved?

nine_k 2y ago
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.

fiddlerwoaroof 2y ago

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.

nine_k 2y ago
You can just build an index with a condition, without even.partitioning the table.
- fiddlerwoaroof 2y ago
  Except, the index doesn't help with avoiding the WHERE clause
  - dqv 2y ago
    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 https://hexdocs.pm/ecto/Ecto.Repo.html#c:prepare_query/3
    - fiddlerwoaroof 2y ago
      Now I have two problems
      - dqv 2y ago
        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.
        2y ago

MarceColl 2y ago

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

mnahkies 2y ago
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.
- sgarland 2y ago
  > 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.

Fire-Dragon-DoL 2y ago

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

kristiandupont 2y ago

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 https://brandur.org/fragments/deleted-record-insert

NicoJuicy 2y ago

Uh. Why? Deleted at is the last ModifiedOn timestamp. Nothing should modify it anymore after that.

code_biologist 2y ago

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 https://github.com/jazzband/django-simple-history

josevalim 2y ago

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 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.

sgarland 2y ago

> 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 https://www.postgresql.org/docs/current/ddl-inherit.html

samanator 2y ago

Yes it can be done with rules, inheritance, triggers, and updatable views. See my answer on stack overflow https://stackoverflow.com/a/78694335/4188138 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.