[Openstack] Discussion / proposal: deleted column marker

Joe Gordon jogo at cloudscaling.com
Wed Oct 3 17:35:17 UTC 2012


+1 to the design proposed here

Looks like there is already a session proposed for Grizzly summit:
http://summit.openstack.org/cfp/details/63

best,
Joe

On Wed, Oct 3, 2012 at 10:07 AM, Federico Innocenti <
federico.innocenti at hp.com> wrote:

>
> +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
>
>
>
> ______________________________**_________________
> Mailing list: https://launchpad.net/~**openstack<https://launchpad.net/~openstack>
> Post to     : openstack at lists.launchpad.net
> Unsubscribe : https://launchpad.net/~**openstack<https://launchpad.net/~openstack>
> More help   : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack/attachments/20121003/580c2619/attachment.html>


More information about the Openstack mailing list