Many a performance problem as a system struggles to support growing demand can be pointed to the data layer. The database can easily become a bottleneck and can be the hardest to scale after the fact because it manages state (in contrast, stateless components are easy to scale).

Typically, we don’t have any real control over the forces applied to the products or solutions we are designing and building. We can speculate and design with certain limits in mind, however what if we come across a situation where those limits are shattered? In the design, we need to think of a way of how we can support this and yet reduce the pressure on the entire system.
Of course, the problem may be solved by throwing more hardware at the problem – i.e. upgrading the database server by increasing memory and/or CPU horsepower. However, it is prudent to think about scalability before rather than after the fact.
First, let us consider some elementary physics:
pressure = force / area
For a given force, if we reduce the area to which the force is supplied the pressure increases. Try for example, standing on tip-toes like a ballerina to get a sense of what I’m talking about. The force i.e. your body weight is constant, but we are reducing the surface area to which the force is applied resulting in increased pressure.
Likewise, if we increase the surface area the pressure is reduced. This is how a person can lay on a bed of nails without puncturing the skin – there needs to be of course enough nails.
One approach is to use horizontal and vertical portioning techniques in the design of the data layer. Both these approaches have the effect of increasing the surface area and thus reducing pressure points as the force (or load) on the system increases compared to a single monolithic data layer (i.e. single database server).
Horizontal partitioning is where the rows of a single logical table are spread over multiple physical databases. For example, customers whose last name begins with A-F may be stored in one database server and customers whose last name begins with G-L are stored in another database server and so on. It is a popular technique used by several large dot-coms as a way to spread the load.
Vertical portioning is a similar concept but involves storing different tables in different physical databases. For example, purchase orders may be stored in one database server whereas invoices may be stored in another.
To learn more about horizontal and vertical partitioning, take a look at http://en.wikipedia.org/wiki/Partition_(database)
Conceptually, the approaches are very straightforward however if you want to leverage them in your design there are several things to look out for.
For horizontal paritioning, you want to strive for even distribution across the partitions. For example, partitioning based on the first letter of the last name may not provide even distribution - V through Z for example may be quite light in the number of records. Hashing techniques may provide a good approach.
For vertical partitioning, references across data models may be tricky. In particular, forget about foreign key constraints. If the two data models have a high degree of coupling between them, then I would go back to the drawing board – perhaps you haven’t found the right boundary. It’s ok to go to one data model, find what you are looking for, then use the result from that query, to find what you ultimately need from the second data model. Avoid distributed transactions (XA/2PC) that span more than one data model if at all possible. Ask yourself, do you really need referential integrity across two data models at all times? If so, then again you should revisit your data design and carve out the right boundaries.
When designing data models at the highest level, strive for high-cohesion (if it changes together, then it stays together) and loose-coupling between the data models. In other words, apply some of the same desirable properties for code design to your data design.
