There is a classier way but you still have to deal with the fk constraints and their related performance issues. If you were using Oracle that probably wouldn't be an issue but with Postgres, I'm not sure really.
Technically, the most efficient way to delete records is to do so by id so you don't don't lock tables or ranges which could be a problem if let say you had a fk to the user table and your app would read a lot from this table. At least in MySQL with innodb, if you would to do what you suggested, you would (read/write) lock 3 tables while deleting a user: users => gardens => crop You might be thinking that MySQL is dumb and you would be right, but at the same time, you are forcing the constraints and the DB is trying to protect its data. Fk constraints should only be used for data integrity protection and only when really needed because of the obvious performance issues related. That's why I would recommend to not use Fk constraints and do the dirty job in a callback or async. - Matt On Mon, Oct 25, 2010 at 9:09 AM, Erik Pukinskis <[email protected]>wrote: > Postgres actually... but yeah. Thanks for the warm welcome. :) > > The async job is a good idea. It should work OK for my current needs. > It seems odd there isn't a classier solution though. > > Erik > > On Mon, Oct 25, 2010 at 8:58 AM, Matt Aimonetti <[email protected]> > wrote: > > Welcome to DB hell, I assume you are using MySQL. Using foreign keys and > cascading deletion will create giant locks causing major performance issues > if your DB is under heavy load. Your reads will lock and everything can > potentially fall apart :( > > > > Mu suggestion if you want to a our crappy performance is to remove the fk > constraints and create an asynchronous job to delete the related records on > deletion. > > Delete your user and trigger a job with the player id as a reference and > let the job delete the associated records & data. > > > > Of course that's not the ideal solution but it should help. > > > > - Matt > > > > Sent from my iPhone > > > > On Oct 25, 2010, at 8:17, Erik Pukinskis <[email protected]> > wrote: > > > >> Hey all, > >> > >> I've been trying find a way to set up relatively fast cascading of > >> deletions across multiple model associations with no luck. It seems > >> like most people just throw :dependent => :destroy into their models, > >> but that's dog slow, especially if you've got a big table with lots of > >> rows (I have a 100k item table joined to a 1m item table, and I need > >> to delete a large number of those). > >> > >> So obviously I need to do this in the database, but I can't really > >> find any way to do it. I've got a User who has a Garden who has many > >> Crops. And I've got the following in my migrations: > >> > >> add_foreign_key :gardens, :user_id, :users, :id, :on_delete => > >> :cascade, :name => "gardens_user_fkey" > >> add_foreign_key :crops, :garden_id, :gardens, :id, :on_delete => > >> :cascade, :name => "crops_garden_fkey" > >> > >> That's using the redhillonrails_core plugin > >> (http://github.com/weplay/redhillonrails_core). I thought that would > >> do it, but when I delete a user, the garden and crops stick around. > >> > >> There's not much documentation on the web for this issue.... seems > >> like most people are only deleting a row or two in their apps, and > >> just use the ActiveRecord facilities and accept that it'll take a > >> little bit of time. > >> > >> Anyone else have any experience with this? > >> > >> Best, > >> Erik > >> > >> -- > >> SD Ruby mailing list > >> [email protected] > >> http://groups.google.com/group/sdruby > > > > -- > > SD Ruby mailing list > > [email protected] > > http://groups.google.com/group/sdruby > > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby > -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
