User-based partitioning divides a logical dataset horizontally so that each user’s records occupy a separate partition. It suits workloads where users mostly access their own data: independent access can reduce synchronization overhead and latency, and partitions can be placed on different physical nodes. section 5.1 section 5.2
Orders by customer
Consider an online bookstore where customers view and place orders while an order processor handles pending orders. One shared Orders table requires coordination across concurrent access. With a table per customer, each table still holds order rows, but all its rows belong to that customer. Customers can access their own tables without coordinating with one another; the order processor still needs access to many tables. section 5.1
The independence comes with operational costs:
- The application must manage partitions and route each request to the right database instance. Finding a user’s partition can itself add latency when there are many users. section 5.1 section 5.3.3
- A partition per user can mean a very large number of partitions. Scanning across users becomes costly because records are scattered among small datasets. section 5.3.3
- Transactions spanning independent partitions are a poor fit. A transfer between bank accounts, for example, needs a transaction covering both accounts. section 5.1 section 5.3.3
Choose this layout when access by user is common enough to justify those costs. For aggregate queries, one possible compromise is to keep a separate vertically partitioned copy of selected user data while retaining per-user partitions for interactions. That adds a consistency problem between copies. User-based partitioning is also a form of workload partitioning and can overlap with geographical partitioning when users usually access data from the same location. section 5.3.3 section 5.3
