Nathan Ostgard

Foo.objects.get(keywords__contains='bar') will make your database cry when you run it against a 30 million row table. At times it becomes necessary to segment a large table into many smaller tables.

Say you have these models:

class City(models.Model):
  city = models.CharField(maxlength=255)
  state = models.USStateField()

class Listings(models.Model):
  name = models.CharField(maxlength=100)
  street = models.CharField(maxlength=100)
  city = models.ForeignKey(City)
  zip = models.CharField(maxlength=10)
  keywords = models.CharField(maxlength=255)

The listings table has gotten huge. Users only search within a single city, so you want to break the listings table into a table for each city. Instead of yourapp_listing, you now have yourapp_listing_1, yourapp_listing_2, etc. All the listings are in the matching table for their city.

I couldn’t find a documented way to make db_table dynamic in Django. So, how do you get Django to use the right table when you’re querying for listings? Here’s how I did it:

class ListingManager(models.Manager):
  def get_table_for(self, city):
    '''
    someapp_listing if city == None
    someapp_listing_012 if city.id == 12
    '''
    table = '_'.join((self.model._meta.app_label,
                      self.model._meta.module_name))
    if city:
      table += '_%03d' % city.id
    return table

  def in_city(self, city):
    self.city = city
    self.model._meta.db_table = self.get_table_for(city)
    return self

class Listing(models.Model):
  city = models.ForeignKey(City)
  objects = ListingManager()

  def delete(self):
    Listing.objects.in_city(self.city)
    super(Listing, self).delete()

  def save(self):
    Listing.objects.in_city(self.city)
    super(Listing, self).save()

It’s simple to use:

>>> city = City.objects.get(id=2)
>>> Listings.objects.in_city(city).all()

Edit 2007/08: Simplified the code. When I first wrote this I replaced db_table with a subclass of str that called ListingManager.get_table_for. It was overkill, and didn’t always work as expected (e.g., 'foo' + Listing._meta.db_table didn’t work).