title: Making sharding simple with Django
body: <p>I have a <a href="https://www.djangoproject.com/" rel="noreferrer">Django</a> project based on multiple <a href="http://www.postgresql.org/" rel="noreferrer">PostgreSQL</a> servers.</p>
  
  <p>I want users to be <a href="http://en.wikipedia.org/wiki/Shard_%28database_architecture%29" rel="noreferrer">sharded</a> across those database servers using the same <a href="http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram" rel="noreferrer">sharding logic used by Instagram</a>:</p>
  
  <p>User ID => logical shard ID => physical shard ID => database server => schema => user table</p>
  
  <ul>
  <li>The logical shard ID is directly calculated from the user ID (13 bits embedded in the user id).</li>
  <li>The mapping from logical to physical shard ID is hard coded (in some configuration file or static table).</li>
  <li>The mapping from physical shard ID to database server is also hard coded.  Instagram uses <a href="http://pgfoundry.org/projects/pgbouncer/" rel="noreferrer">Pgbouncer</a> at this point to retrieve a pooled database connection to the appropriate database server.</li>
  <li>Each logical shard lives in its own <a href="http://www.postgresql.org/docs/8.1/static/ddl-schemas.html" rel="noreferrer">PostgreSQL schema</a> (for those not familiar with PostgreSQL, this is <em>not</em> a table schema, it's rather like a namespace, similar to <a href="http://dev.mysql.com/doc/refman/5.6/en/create-database.html" rel="noreferrer">MySQL 'databases'</a>).  The schema is simply named something like "shardNNNN", where NNNN is the logical shard ID.</li>
  <li>Finally, the user table in the appropriate schema is queried.</li>
  </ul>
  
  <p>How can this be achieved as simply as possible in Django ?</p>
  
  <p>Ideally, I would love to be able to write Django code such as:</p>
  
  <p><strong>Fetching an instance</strong></p>
  
  <pre><code># this gets the user object on the appropriate server, in the appropriate schema:
  user = User.objects.get(pk = user_id)
  </code></pre>
  
  <p><strong>Fetching related objects</strong></p>
  
  <pre><code># this gets the user's posted articles, located in the same logical shard:
  articles = user.articles
  </code></pre>
  
  <p><strong>Creating an instance</strong></p>
  
  <pre><code># this selects a random logical shard and creates the user there:
  user = User.create(name = "Arthur", title = "King")
  # or:
  user = User(name = "Arthur", title = "King")
  user.save()
  </code></pre>
  
  <p><strong>Searching users by name</strong></p>
  
  <pre><code># fetches all relevant users (kings) from all relevant logical shards
  # - either by querying *all* database servers (not good)
  # - or by querying a "name_to_user" table then querying just the
  #   relevant database servers.
  users = User.objects.filter(title = "King")
  </code></pre>
  
  <p>To make things even more complex, I use <a href="http://wiki.postgresql.org/wiki/Streaming_Replication" rel="noreferrer">Streaming Replication</a> to replicate every database server's data to multiple slave servers.  The masters should be used for writes, and the slaves should be used for reads.</p>
  
  <p>Django provides support for <a href="https://docs.djangoproject.com/en/dev/topics/db/multi-db/#automatic-database-routing" rel="noreferrer">automatic database routing</a> which is probably sufficient for most of the above, but I'm stuck with <code>User.objects.get(pk = user_id)</code> because the router does not have access to the query parameters, so it does not know what the user ID is, it just knows that the code is trying to read the User model.</p>
  
  <p>I am well aware that sharding should probably be used only as a last resort optimization since it has limitations and really makes things quite complex.  Most people don't need sharding: an optimized master/slave architecture can go a very long way.  But let's assume I <em>do</em> need sharding.</p>
  
  <p>In short: <strong>how can I shard data in Django, as simply as possible?</strong></p>
  
  <p>Thanks a lot for your kind help.</p>
  
  <p><strong>Note</strong></p>
  
  <p>There is an <a href="https://stackoverflow.com/questions/4481435/django-data-sharding">existing question</a> which is quite similar, but IMHO it's too general and lacks precise examples.  I wanted to narrow things down to a particular sharding technique I'm interested in (the Instagram way).</p>