Consider using a dedicated "deleted" column instead of relying on "deleted_at IS NULL"
For various tables we use soft deletions. A row is considered to be deleted when the column deleted_at
is set to a non-NULL value. This however makes filtering less efficient than it potentially be. This is because a query such as WHERE deleted_at IS NULL
will not use an index, resulting in a regular row filter being applied. If we were to instead use a column such as deleted
(true or false) we can use indexes efficiently given it doesn't have any NULL values.
In other words, instead of this:
SELECT * FROM issues WHERE deleted_at IS NULL;
We'd use this:
SELECT * FROM issues WHERE deleted IS FALSE;
In this setup we can either keep deleted_at
without an index (so we can track when a row was removed), or we can remove the row alltogether.