2

We have an Accounts table in a Postgres DB that has a unique index on user_id and product. Users can have many accounts, there's no product table. Occasionally users will create an account accidentally, and we will need to add that account back to their original User record, after they've collected some information on it that we can't just toss.

Because of the unique index on product and user_id, we need to do some serious linking/unlinking on the Account record, and record data from one account record into a JSONB field on the other record before connecting it to the proper User record.

We have a deleted_at column on the Accounts table that is moderately used, and I've proposed adding it to the (user_id, product_id) index. That way, any record that has a NULL deleted_at for a given user_id, product_id is the "active" account, and accounts can be marked deprecated/inactive by just setting a deleted_at.

This would require some re-writes in our lookups, but they wouldn't necessarily be extensive, because our ORM provides a scoping feature we can apply to all queries.

I've gotten pushback on the idea, but nothing related to performance impact or technical drawbacks, only statements along the lines of "Do people really do that?" or "I've never seen that before." I swear I have seen companies do this, but it would be hard to provide proof.

Can anyone provide a performance or modeling criticism of this approach? Has anyone seen this sort of approach? If not, have you ever fixed this sort of problem?

Improve this question

2 Answers 2

Reset to default
1

Doing that for a typical B-tree index is probably not going to be terribly helpful and potentially harmful due to the extremely low cardinality in the deleted_at column (it can only ever be true or false). In most cases you would never want to query on this column and almost always would want to filter out records where this is true (deleted).

A partitioned table is a good approach here. You can partition the table on the valid values of this column such that blocks of your tablespace data are organized such that deleted and non-deleted data is located together. If you query now with a local index and you filter on your partition column then you will only perform index scans or table scans in the partition you are interested in.

This has the added benefit of record insertion being faster as well.

Improve this answer
4
  • Interesting. How would you handle other records foreign keys that point at this record that's been moved into this partition?
    – candied_orange
    Commented Dec 8, 2018 at 11:08
  • @candied_orange The foreign key constraint is against the table itself and not the partition of the table. That means that data integrity is preserved to your related tables even when the deleted column is true.
    – maple_shaft ♦
    Commented Dec 10, 2018 at 15:20
  • Just to be clear, deleted_at would be a timestamp, so wouldn't that instead make it very high cardinality? You are correct that yes, the plan would be to impose a default scope on most queries that enforces that deleted_at IS NULL in the query.
    – kidCoder
    Commented Dec 10, 2018 at 16:53
  • @kidCoder The IS NULL filter can be problematic when it comes to using an index. The deleted_at column will not be good for an index nor a partition column as a TIMESTAMP. I suggest adding an additional column IsDeleted that is a boolean type then partitioning on that. Then you can ensure that all of your indexes are local indexes that maintain separate trees for each partition so that there is less searching for records where you filter on deleted = false.
    – maple_shaft ♦
    Commented Dec 10, 2018 at 17:49
1

You're toying with the idea of working immutably. Nothing is ever destroyed. You just change things by adding new things. This non-destructive approach is disorienting to people used to the CRUD mentality. But so long as you have the storage capacity it works. Just look at wikipedia. Changes all the time and nothing is ever really lost.

With this implementation you'll run into an issue if you allow them to mark a record as deleted and then create a nearly identical one when you have uniqueness constraints on fields. You'll need to decide if you want to prevent that or somehow tie uniqueness to deletetion status.

Like any change made to a table you have to be sure that all uses of that table are updated when you make the change. But yes, you certainly can stop actually deleting records just because some user made a typo.

Now if a lawyer comes at you with a court order you'd better have your SQL tools handy.

Improve this answer
1
  • "You'll need to decide if you want to prevent that or somehow tie uniqueness to deletetion status." <- this is essentially the plan, where only one record for a given User+Product can have deleted_at be NULL, and the rest have a timestamp that creates uniqueness. Are you saying this is a strategy you've seen before / don't see issues with?
    – kidCoder
    Commented Dec 10, 2018 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.