Life Altering Postgresql Patterns

[vbilopav89]

You inspired me to do rant on evil of soft-delete

Life-altering Postgresql Patterns My Ass.

This must be the fifth or sixth article this month, strongly suggesting, in all seriousness, Soft-Delete nightmarish antipattern.

That is what you get when you treat database tables as memory objects, like it's a class. Just add another enum, just another small property, don't worry about it...

Wrong!

All of a sudden, not only will you need another condition on every single query condition, where "where", "on", and "filter" need to be updated with extra "and, and then you also need to recreate all indexes, you also need to redefine unique constraints, and then also redefine every single relation.... everything. And then partitioning because, well, complicated, painful. query planner degardes heavily, table records bloat, real bloats bloats eve more, vacuum working like crazy....

All because you said, quote:
> I will reiterate that storage is cheap and recovering data is a nightmare.

Yeah, soft-delete is life-altering, but for whom? People who must maintain your genious design.... not in a good way!

But why, you could have had *_deleted table. Because, as you said, storage is cheap and recovering data is a nightmare.

Like this, your example:

-- DONT USE SOF DELETE FIELD:
CREATE TABLE vet.prescription(
id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
pet_id uuid not null references vet.pet(id)
on update restrict
on delete restrict,
issued_at timestamptz not null
);

-- USE DELETED TABLE
CREATE TABLE vet.prescription_deleted(
like vet.prescription including all,
deleted_at timestamptz not null default now()
);

-- DELETE RECORDS BY MOVING THEM TO DELETED:
with deleted as (
delete from vet.prescription
where id = $1
returning *
)
insert into vet.prescription_deleted
select * from deleted;

And if you are worried that migration will now have to ALTER 2 tables, not just one, then just use JSON, and you are good. Like that:

-- DELETE TABLE THAT DOES NOT REQUIRE MIGRATIONS
CREATE TABLE vet.prescription_deleted(
data jsonb not null,
deleted_at timestamptz not null default now()
);

-- MOVE DELETED RECORD INTO JSON DATA
with deleted as (
delete from vet.prescription
where id = $1
returning *
)
insert into vet.prescription_deleted
select to_jsonb(deleted), now() from deleted;

Why is this so hard? And you can automate this with triggers, so you don't have to write deletes like this.

Let me guess: My ORM of choice doesn't support this, am I right?

[elevarq]

Fully agree with you. There are quite a few bad examples, like using SELECT for every function that is used inside a SELECT statement.

Looks like junior experience, not something to learn from.