IB Computer Science Topic A3.4.4: Features of Distributed Databases Notes

Distributed Databases: One Logical Database Spread Across Many Machines

1. A distributed database stores its data across multiple physical locations, which may be servers in different buildings, different countries, or different cloud regions.
2. To the user and to the application it behaves as one single database, and a query does not name the site the data sits on.
3. Everything difficult about a distributed database follows from that gap: the system must keep a single, consistent picture out of parts that fail and communicate over a slow, unreliable network.

Homogeneous and Heterogeneous Systems

1. Homogeneous
   1. All sites use the same DBMS, schema, and OS.
   2. Queries and updates behave identically at every site, so the system is far easier to manage.
2. Heterogeneous
   1. Sites may run different DBMSs, different schemas and different operating systems, often because separate organisations merged.
   2. A translation layer must convert between them, which adds cost and is a common source of errors.

Fragmentation: Splitting the Database Between Sites

1. Fragmentation divides the database into parts, and each site stores and manages one fragment.
2. Horizontal fragmentation splits by row, for example each country's branch holding its own customers.
3. Vertical fragmentation splits by column, for example payroll data held separately from contact details.
4. Fragmentation pays off when local data is the data accessed most often, because most queries are then answered without touching the network.
5. The other method of spreading data is replication, which copies data to several sites instead of dividing it, and is covered as a feature below.

Why Consistency Is Hard Once Data Is Spread Out

1. Data consistency means every user sees the same data whichever site they reach, and it is the hardest guarantee to keep in a distributed system.
2. Network latency: an update takes measurable time to travel to the other sites, so there is always a window in which sites disagree.
3. Concurrent access: two users at different sites can update the same item at the same moment, and neither site knows about the other's change yet.
4. Partial failure: one site can go down while the rest keep running, which no single-machine database ever has to handle.

ACID Across Sites: The Same Four Guarantees, a Harder Job

1. ACID stands for Atomicity, Consistency, Isolation, and Durability.
2. The four properties are defined and worked through in A3.3.6, so what matters here is what each one costs once the transaction spans several machines.
3. Atomicity across sites is enforced by the two-phase commit protocol.
   1. In the prepare phase, a coordinator asks every participating site whether it can commit, and each site replies with a vote and locks the data it would change.
   2. In the commit phase, the coordinator sends commit only if every site voted yes, and sends abort if a single site voted no or failed to reply.
   3. The cost is that each site holds its locks while it waits, so a coordinator that crashes between the phases leaves those sites blocked.
4. Consistency across sites means a constraint may depend on data the local site does not hold, so checking it requires a round trip before the transaction can complete.
5. Isolation across sites requires locks to be coordinated between machines, which creates the possibility of a distributed deadlock that no single site can detect on its own.
6. Durability across sites means a committed transaction must survive the loss of a whole site, so the commit is written to more than one site before it is acknowledged.

Concurrency Control: Managing Simultaneous Transactions

1. Concurrency control lets many transactions run at once without leaving the data inconsistent.
2. Pessimistic concurrency control (PCC) locks a resource before touching it, which prevents conflicts but makes other transactions wait.
3. Optimistic concurrency control (OCC) assumes conflicts are rare, allows the work to proceed, and checks for a conflict at commit time, aborting if one is found.

Consistency Models: How Quickly Sites Must Agree

1. Strong consistency: an update is visible at every site before the transaction is reported as complete.
2. Eventual consistency: an update spreads over time, so sites may disagree for a period but converge if no further updates arrive.
3. Causal consistency: updates that depend on one another are seen in the same order everywhere, while unrelated updates may arrive in any order.

Data Partitioning: Dividing Data for Performance

1. Data partitioning divides a large table into smaller sections that can be stored and queried separately.
2. Range partitioning splits on value ranges, such as one partition per year.
3. Hash partitioning applies a hash function to a key to spread rows evenly across partitions.
4. List partitioning assigns rows by a specific value, such as one partition per region.
5. Round-robin partitioning places each successive row in the next partition, giving an even spread with no relationship to the data.

Data Security: More Sites Means More Ways In

1. A distributed database has multiple access points, so an attacker only has to find the weakest site.
2. Authentication verifies who is making a request, and must be applied consistently at every site.
3. Encryption protects data both in transit between sites and at rest on each site's storage.
4. Validated Input: Ensuring data meets predefined rules.

Distribution Transparency: Hiding the Distribution from the User

1. Distribution transparency means the system behaves as one database, so users write queries without knowing how the data is spread.
2. Access transparency: data is requested in the same way whether it is local or remote.
3. Location transparency: a user can reach data without knowing which site physically holds it, so data can be moved between sites without rewriting any query.
4. Replication transparency: users are unaware that copies exist, and never choose which copy to read.
5. Failure transparency: the system keeps working when a site fails, serving requests from the sites that remain.
6. Concurrency transparency: many users work at once without seeing each other's partial results.

Fault Tolerance: Staying Up When a Site Goes Down

1. Fault tolerance keeps the system operational despite the failure of a site, a disk or a network link.
2. Redundancy stores multiple copies of the data, so no single loss destroys it.
3. Automatic failover switches requests to a working site when a failure is detected, without the user acting.
4. Fault tolerance is why distributed databases are chosen for banking, healthcare and air traffic control, where downtime is not acceptable.

Global Query Processing: Planning a Query Across Sites

1. Global query processing works out how to answer a query that needs data from several sites.
2. Local optimization decides how each site finds its own part of the answer.
3. Global optimization decides which site does which work and in what order, so that as little data as possible is sent across the network.
4. Moving a large table across the network to join it with a small one is the classic mistake, since sending the small table the other way is far cheaper.

Replication: Keeping Copies at Several Sites

1. Replication stores copies of data at more than one site, which improves both availability and read performance.
2. Full replication copies the entire database to every site, giving the fastest reads and the highest cost to keep consistent.
3. Partial replication copies only the most critical or most read data.
4. No replication gives each site unique data, which is fragmentation on its own.
5. Master-slave: one site accepts all writes and the others copy from it, which keeps consistency simple.
6. Multi-master: several sites accept writes and reconcile them, which improves availability but creates conflicts to resolve.
7. Peer-to-Peer: All nodes can act as both master and slave.

Scalability: Growing by Adding Sites

1. Scalability is the ability to handle more data and more users by adding nodes rather than by replacing hardware with something bigger.
2. This is the main reason organisations choose a distributed design, and it is why NoSQL and distributed systems are often discussed together.

Advantages of Distributed Databases

1. Faster performance where queries are answered by local access rather than over the network.
2. Growth by adding sites, without replacing existing hardware.
3. Improved fault tolerance and reliability, since one site failing does not stop the system.
4. Local autonomy, letting each site manage its own data while still sharing it.

Disadvantages of Distributed Databases

1. More complex to design and manage than a single-site database.
2. Consistency and concurrency control require extra network round trips, which cost time on every transaction.
3. Harder to secure, because there are more access points to defend.
4. Translation problems between systems in a heterogeneous setup.

Real-World Applications

1. Hotel chains keeping availability at each property while head office reports across all of them.
2. Air traffic control and military systems, where fault tolerance matters more than cost.
3. Corporate systems with departments in different countries needing local speed and global reporting.
4. Pharmaceutical companies tracking treatments and trial data across many national sites.