Soft deletion probably isn't worth it

* hot and cold do not churn each others indexes or tables, you effectively have only one set of indexes (and data) that's actually churning and the others are stable.

* hot and cold can be treated differently - you can perform more aggressive indexing on data once you know it's out of the hot-set, or partition your hot data onto a tablespace with dedicated hardware while cold data is on bulk hardware, etc. Since queries are planned onto each partition individually, postgres can often select "the right way" for each partition.

* "deleted_at" is a special case of cold table. Dropping any partition is basically free, so if you partition into date ranges, then at the end of your data retention period you just drop the partition for that date range, which doesn't churn indexes or induce vacuuming

If data can never exit the cold-data state, then it's effectively an append-only table too, it just exists as a supplement to your online/hot-data table but it doesn't require special attention/etc. So we're in agreement on that point, that's a good design feature if you can swing it!

(note that for audit logging, I think it's simpler to just do the separate table. But the partition strategy does have some advantages for "cold" or "dead" data as a more generic concern imo)

I mean I guess I can imagine a scenario where you want to know _when_ a record was logically deleted in addition to a flag that says is_deleted, but maybe that should be two different columns.

Or at the very least, `deleted_at` is a misleading name in that case. Interpreting `deleted_at` as "is deleted after but not before" is definitely a reasonable _enough_ interpretation. I agree it doesn't unambiguously mean that, but it's at best ambiguous enough to make for a bad column name if `IS NOT NULL` is the way you intend to check for a logical delete.

> you could to get the comparison flipped by accident, and if it's only in one place out of many that bug could go unnoticed for a while.

That's exactly why I proposed a database view as the proper solution to both whole `deleted_at IS NULL` "code leakage" issue. If you have a table that uses a logical-delete idiom that you intend to query from a bunch of different places in the code, a database view that filters out the logically deleted rows is a much more appropriate solution. It's a universally DRY solution. The single simple view covers all tech stacks, all services, etc., up to and including interacting with the database via some sort of REPL console.

If you need to add `deleted_at IS NULL` to, say, 90% of the queries against the `customer` table and you're afraid someone is going to forget to do that somewhere somehow, it seems pretty obvious kind of "extract common WHERE clause" refactoring is warranted. This is especially true in the actual "business meaning" context here. You are only going to be interested in the logically deleted rows in small number of specific circumstances. Having some way to extract away that detail from the 90% of the use cases where "logical deleted" is meant to be functionally equivalent to "actually deleted" seems extremely warranted. It's a perfect counter-example to overcome an aversion to using database views. I get that the `active_customer` abstraction is probably awkward to implement in a lot of application code contexts (depending on your ORM/DB-query framework I guess), but the database view is a simple and elegant way to address that really clearly defined need.

You can’t just declare that it take years to delete data, GDPR sets reasonable limits on how long a company can delay true deletion.

    Nonsense like a "deleted" column on your main data table just seems silly.

Sure, you have to be conscious of adding "where deleted_at = NULL" (or the opposite) to all your queries. If you're going through an ORM this is usually trivial e.g. in ActiveRecord it can be a default scope on your model.

    On a proper database you can do your "undeleted" with triggers 
    and it's relatively trivial. 

If I was retrofitting an existing application with soft-deletes I would definitely favor this solution.

There are some obvious advantages over a "deleted" column on your main table for sure.

One drawback is that you now have to keep two table definitions in sync. You also have to add conditional logic in a lot of places to handle whether a given piece of code is looking at a normal user from the "users" table or a deleted user from the "users_deleted" table. Instead of messing with the WHERE clause on every query, now you're messing with the table name on every query. So that's a wash IMO.