And this is why we use things like database users that don't have delete permission, and row-level security so users can delete things that don't belong to them.

I have learned this from a very similar experience.

And why it’s often best to mark a record deleted and then have a reaper remove the records at a later point. But you must make sure all normal queries don’t see deleted items.

That's over engineering. At this point, just rely on PITR. FWIW, postgres does have a "reaper" via vacuum, but not for the purpose of safety. but rather to allow for mvcc.

Hi, if I may, what does PITR stand for?

Thanks!

There are other advantages for soft-deletes, like not having to worry about FKs.

>One night in bed I realized that if someone hit submit on the delete screen without filling in any criteria it would just delete the whole database.

how

Lack of validation?

I.e if no criteria, it could be sending a DELETE message with no where clause in SQL land.

i remember sitting next to someone who screamed after realising he just did a ’DELETE FROM company' without a WHERE clause. Our database was way too big to backup, so we only had production, but luckily I had rolled out database logging in the inyerface that recorded all UPDATE and DELETEs a few weeks before the event.

I had a DBA call me on my way back from lunch absolutely sobbing after fat fingering a semicolon before the where clause while logged in to Prod as root.

we got things restored and back online in a couple of hours. I let her go home afterwards, heh she had suffered enough.

I was taught to also use a transaction and then check how many rows were affected before committing.

As was Tom Scott: https://youtu.be/X6NJkWbM1xk

I wonder if archive.org was around enough back then to capture any of it.

Many mistakes were made prior in order to get her in the situation to begin with. The semicolon was just the icing on the cake.

When your database is too big to backup that's a lot like a bank being too big to fail. Unwise long term strategy.

Terabytes across 25+ databases not co-located and all being masters replicating to each other, in 2009. Not sure how you would have done it.

I got myself into the habit of opening transactions before doing anything that could alter data by hand. Huge panic-saver.

Even earlier, I got into the habit of typing the where clause before typing the from clause. Not much of a change, but it feels more fail-safe.

in one web shop i worked with, developers who were fanaticslly using constructions "WHERE user LIKE '%$user%' "

we've had some really heated debates, but nobody would listen to me. and then one day DELETE happened where $user was empty. they spent half day restoring the database, but nobody made any conclusions and nothing changed...

Easy: no form input no condition on the delete

aaand it’s gone