Database scalability

Vertical database scaling implies that the database system can fully exploit maximally configured systems, including typically multiprocessors with large memories and vast storage capacity. Such systems are relatively simple to administer, but may offer reduced availability. However, any single computer has a maximum configuration. If workloads expand beyond that limit, the choices are either to migrate to a different, still larger system, or to rearchitect the system to achieve horizontal scalability.[10]

Horizontal database scaling involves adding more servers to work on a single workload. Most horizontally scalable systems come with functionality compromises. If an application requires more functionality, migration to a vertically scaled system may be preferable.[10]

Databases run on individual hardware ranging in capacity from smartwatches to supercomputers to multiple transparently reconfigurable server farms.[2] Databases also scaled vertically to run on 64-bit microprocessors, multi-core CPUs, and large SMP multiprocessors, using multi-threaded implementations.

Fully exploiting a hardware configuration requires a variety of locking techniques, ranging from locking an entire database to entire tables to disk blocks to individual table rows. The appropriate lock granularity depends on the workload. The smaller the object that is locked, the less the chance of database requests blocking each other, while the hardware idles. Typically row locks are necessary to support high volume transaction processing applications at the cost of processing overhead to manage the larger number of locks.[3]

Further, some systems ensure that a query sees a time-consistent view of the database by locking data that a query is examining to prevent an update from modifying it, stalling work. Alternatively, some databases use multi-version read consistency to avoid (blocking) read locks while still providing consistent query results.[11]

Another potential bottleneck can occur in some systems when many requests attempt to access the same data at the same time. For example, in OLTP systems, many transactions may attempt to insert data into the same table at the same time. In a shared nothing system, at any given moment, all such inserts are processed by the single server that manages that partition (shard) of the table, possibly overwhelming it, while the rest of the system has little to do. Many such tables use a sequence number as their primary key that increases for each new inserted row. The index for that key can also experience contention (overheat) as it processes those inserts. One solution for this is to reverse the digits of the primary key. This spreads the inserts into both the table and the key across multiple parts of the database.[12]

A basic technique is to split large tables into multiple partitions based on ranges of values in a key field. For example, the data for each year could be held on a separate disk drive or on a separate computer. Partitioning removes limits on the sizes of a single table.

Replicated databases maintain copies of tables or databases on multiple computers. This scaling technique is particularly convenient for seldom or never-updated data, such as transaction history or tax tables.[6]

A variety of approaches are used to scale beyond the limits of a single computer. HP Enterprise's NonStop SQL uses the shared nothing architecture in which neither data nor memory are shared across server boundaries. A coordinator routes database requests to the correct server. This architecture provides near-linear scalability.

The widely supported X/Open XA standard employs a global transaction monitor to coordinate distributed transactions among semi-autonomous XA-compliant transaction resources.

Oracle RAC uses a different model to achieve scalability, based on a "shared-everything" architecture. This approach incorporates the shared disk approach that allows multiple computers to access any disk in the cluster. Network-attached storage (NAS) and Storage area networks (SANs) coupled with local area networks and Fibre Channel technology enable such configurations. The approach includes a "shared" logical cache in which data that has been cached in memory on server is made available to other servers without requiring them to again read the data from disk. Each page is moved from server to server to satisfy requests. Updates generally happen very quickly so that a "popular" page can be updated by multiple transactions with little delay. This approach is claimed to support clusters containing up to 100 servers.[13]

Some researchers question the inherent limitations of relational database management systems. GigaSpaces, for example, contends that space-based architecture is required to achieve performance and scalability. Base One makes the case for extreme scalability within mainstream relational database technology.[14]