I've come across a situation where I need to shard a database (Oracle, but that doesn't particularly matter).

The gist of the problem is I have written a large-scale system in a fairly standard TDD-style, with repositories and services hidden behind interfaces. Dependency injection is used to implement particular versions of the services and repositories at runtime, with this functionality allowing for crazy client requirements (for example we migrated from MSSQL to Oracle in under a week, which would not have been possible if the architecture was not properly decoupled!).

The problem now arises that I need to shard off a large portion of the data into a separate archival system, for performance reasons. The table structure has to remain the same due to time constraints, and I would like the purity of the architecture to remain the same, with no one section of the system having knowledge of the inner workings of another. The system will need to be switched from one shard to another dynamically via a user input (what input this is is yet to be finalised).

So, the two ways I can think of to approach this issue are:

1 - Pass in the required connection string on creation of a repository. This is not ideal as it means that the service layer, or worse, the UI, needs to know about the underlying sharding. This will make the design very inflexible in future, and require things like the sit-in-front caching layer (a write-through cache implementation on top of the IService) to be updates so their methods take a connection string as a parameter, which just seems wrong.

2 - Create a new IConnectionProvider subsection of the system that abstracts the connection information for the repository. This would be more ideal, as the repositories would remain self-contained, but I cannot think of a good way to switch this implementation without having to specify the relevant interface as a parameter to each method call, which goes back to the service layer specifying which connection to use.

Does anyone have any experience with this issue, or any opinions on a preferably low-labor yet still fairly pure implementation in this situation?

  • Are you sure you can't use the CREATE PARTITION/CREATE PARTITION SCHEME approach in SQL Server or the equivalent Oracle partitioning mechanism? docs.oracle.com/cd/E18283_01/server.112/e16541/toc.htm
    – JasonTrue
    Commented Feb 12, 2012 at 18:38
  • Not positive, I'm not overly familiar with Oracle's partitioning, but I don't think this is a particularly good candidate just to do the volume of data (which is very high). I will certainly ask one of the DBAs (they work for a different company for the client, or I would have asked them already!), however, I'm still interested in an answer to the architectural question.
    – Ed James
    Commented Feb 12, 2012 at 19:01
  • Understood, it's just that your described use case is essentially the canonical motivating scenario for such partitioning schemes. I wouldn't want to waste a lot of time trying to come up with a messy OO hack if I could make the sharding essentially transparent to the application.
    – JasonTrue
    Commented Feb 12, 2012 at 19:22
  • Option 2: stackoverflow.com/questions/1943576/… Commented Feb 14, 2012 at 3:17

1 Answer 1


I think it depends on how well your entities that you are persisting are segregated. If they are coupled and you are relying on cross-table joins to bring some arbirary read (+projection) state up and then deserialize it back, then you probably have a problem.

On the other hand, if you can load an entity or aggregate root + value objects from your repository without having to pull up a complete cross-entity-boundary object graph and have that be consistent in a transaction, then there's definitely the possibility there to shard in the repository.

In my world, the repository is the actual thing that implements sharding - it's a coherent 'bag of entities by id'.

That said; what are your sharding on? Entity type? Then, e.g. with Windsor, use an IHandlerSelector implementation that looks at the type of the repository (meaning type of entity requested) and chooses the right instance depending on whether it's a 'hot' entity, or a 'cold' one.

Similarly, for ranges of keys or based on entity data/attributes, you could do a handler selector that looks at some thing on the entity.

An alternative is to start publishing an event stream from operations on your entities and have those events create a read model that is denormalized already; avoiding the join-problems that might be the root cause of your scalability problems (this is speculative from my side based on experience).

  • That IHandlerSelector thingy sounds like what I'm looking for, but I'm using Unity for DI, and I don't know if there's an equivalent. I'll have to check that out.
    – Ed James
    Commented Feb 12, 2012 at 20:53
  • No idea. I would avoid Unity if I could.
    – Henrik
    Commented Feb 12, 2012 at 21:48
  • Why's that? I find that I prefer the Property-injection rather than Constructor-injection pattern, and that the container is fairly simple and performant.
    – Ed James
    Commented Feb 12, 2012 at 22:01
  • Well, it's not thread-safe nor has many features; features one needs after the initial meeting...
    – Henrik
    Commented Feb 13, 2012 at 9:20
  • Thread safety is not something I require as I have yet to come across a situation where I need to change the registrations after initialisation. Features, on the other hand, is a good point, but I get the impression they're improving it fairly quickly.
    – Ed James
    Commented Feb 13, 2012 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.