openstack team mailing list archive

[Federico Innocenti <federico.innocenti@xxxxxx>]

+1 to the design proposed here.

Even without embracing anything nova specific but simply from a database perspective the soft-delete approach is proven to be a poor solution to most of the problems it promises to solve.

In addition to what Stan already pointed out, let me recap something that you may already know but so that we have a complete picture, this time solely from a db point of view:

- restoring a record is more than doing set deleted=0. It is about recovering the all graph of references in the database. Beside the complexity of a restore procedure, it makes not possible to selectively recover just the information we want.

- tables grow also when alive data (deleted=0) are a small percentage of the total. This takes more space/time for backups and maintenance operations.

- all queries require for every table involved an additional filter on deleted=0 and thus an additional scan, even though likely most DBMS are able to optimize queries discriminating on a binary flag.

- using unique constraints and foreign keys is impossible. The nova database schema is now holding more foreign keys than in the old days, but unless normal deletions are performed they are worthless as they cannot protect the database from inconsistencies. And in Nova we saw a certain number of inconsistencies arising in a large usage context.


What the soft delete approach tries (badly) to do is in practice to keep an archive of historical data. The best archiving solution could be left to the choice of the single vendor for the time being (until a more comprehensive notification system is in place), since every major DBMS provides its own facilities to implement it. In MySQL as in many other databases you can write db triggers which insert the row being deleted to a shadow table in the same db or in another db.

Cheers,
Federico Innocenti