Using Boolean value as a basis for soft deletion judgements.

[ramzeng]

select * from posts where user_id in (10, 20, 30) and deleted_at is null;

select * from posts where user_id in (10, 20, 30) and is_deleted = false;

As stated above, I believe that using a Boolean field as the basis for soft deletion and creating a composite index provides better sorting and querying performance.

[Sophist-UK]

@ramzeng Can you explain why you closed this?

[ramzeng]

Because i think that changing the index columns may not improve performance.

Moving deleted rows to another table might be the best way to improve performance.

Do you think so?

[Sophist-UK]

Firstly, we would need some proper benchmarks with each of the supported databases to know what the various performance differences would be i.e. for filtered selects and for inserts/updates.

Secondly, adding a new field for new versions might create some substantial backwards-compatibility issues which would outweigh most performance gains unless they were substantial. (And of course an extra field will increase the row size slightly.)

But there is a really major issue with indexes and the use of null. In the SQL definition (and in most *but not all SQL implementations) every value null is different from every other value null. Yes, genuinely null <> null!!!!

Supposing you have a customer table that contains e.g. customer_number, customer_name, phone_number and deleted_at, and you want to ensure that phone numbers are unique for each active customer. So every phone_number must be unique for customers which have not been deleted (deleted_at is null) but you can have zero or one records where deleted_at is null and as many records where deleted_at has a unique deletion time in it.

This definition of null means that you cannot use a unique composite index of (say) phone_number and deleted_at in order to ensure that records are not duplicated because (1, null) and (1, null) are (amazingly) considered by SQL to be different values. Equally you cannot have a unique index only on phone_number if you want to allow a record to be deleted and then to create a new record for a new customer that has been issued the same phone_number as the previous customer.

In other words, (if we take as a given the stupid decision by the SQL folks to make null <> null then) using null to indicate that a record is not deleted is a somewhat bad choice, and using an is_deleted boolean field would be better. Then again, setting deleted_at to a default non-null datetimestamp value (like 1900-01-01 00:00:00.0000) for non-deleted records would be a better choice because this would allow use of a single index whilst avoiding the null issue completely.

I even created a Laravel package to handle this issue: https://github.com/Tranzakt/Laravel-SoftDeletesUnique / https://packagist.org/packages/tranzakt/laravel-softdeletesunique

[ramzeng]

A composite unique index with nullable column is really a bad idea, and your package works well, thanks.

[Sophist-UK]

Ah - a first user. I created it as a learning exercise, and have not (yet) used it myself. Hence the beta nature.