What is the correct way to deal with unnecessary records in the database - delete or mark them with the "deleted" flag?

Question

MySQL

Removing unnecessary entries can be done as follows:

1. Delete physically. Periodically do OPTIMIZE TABLE.
2. Do not delete. Assign a flag, for example, "deleted", and in the future do not select records with this flag

How is it more correct and optimal to do it if records are deleted a little, but regularly?

Answer 1

2. Do not delete. Assign a flag, for example, "deleted", and do not select records with this flag in the future

2. do not delete, assign a flag, and then overwrite by removing the “deleted” flag

Answer 2

depends on the size of the database, of course.
If the base is large I would combine flag + cron delete + OPTIMIZE during downtime at night.

Answer 3

physical meaning.
The DB record becomes, roughly speaking, in a file. If the record is physically removed from the file, it is necessary to perform an operation to truncate the file and rewrite it, which on many file systems and DBMS is a resource-intensive (and dangerous - what if it crashes?) operation, so they try to avoid it. Plus, often when rewriting (optimizing) the database becomes unavailable at all or for some operations, which is also not good. This is where the practice of marking “deleted” came from. Decide for yourself, based on the task and need.
I once met an alternative option: periodically draining the database into a dump without “marked” records and replacing it with the old database. Due to the fact that the dump was read without stopping work, and the upload of a new one was fast enough, the solution lived and was acceptable. The by-product is backups.

Answer 4

Depends on whether you need these records in the future, if 100% is not needed, I would set the flag for deletion, and delete it by cron.

Answer 5

It all depends on the type of table from which records are deleted.
If it's MySQL, then mark it as deleted and delete it later.
If innodb, then delete physically.

Answer 6

Ответы очевидны.
Помечать флагом — растет база, удалять — понижение производительности (фрагментация, сама операция удаления может оказаться дорогой) и часто усложнение бизнеслогики.
Я бы не рекомендовал удалять данные из сложных баз данных, особенно если это справочники, например выставив в freign key — RESTRICT, чтобы разрешить удаление только для 'свободных' записей. Удаление записей в сложных базах данных, обычно очень сложная операция, обычно перед этим приходится проводить кучу проверок и изменений для связанных данных, поэтому флаг 'deleted' используется как упрощение или даже часть механизма для введения временной составляющей в хранение данных (база данных может являться как средством для хранения текущего состояния, так и для хранения лога изменений данных во времени)
Depending on the tasks:
1. If the deletion occurs relatively rarely, i.e. if the overhead on the size of the base is insignificant, then it is better to mark it with a flag.
2. If deletions and creations of records are very frequent (as a result, the database does not grow very much), then it is better to delete, the advantages of the absence of fragmentation may not be enough to kill the growth of indexes (by the way, the cache in RAM will be busy in vain).
3. If both speed and data are critical, then it is better to flag and delete by cron, and in order to minimize the likelihood of delays for the maintenance time, divide the table into clusters (you can 'manually' in the database class) and clear each piece separately, and the division to do in order to separate frequently used data from rarely used ones.

Answer 7

Compromise. Mark for deletion. And delete the oldest entries in small portions of the crown.