Centralized vs. Distributed: Trade-Offs and Query Optimization

Why this matters: distribution is not free, and the cost shows up somewhere specific — in cross-site latency on every query that spans shards. This lesson quantifies it.

Key takeaway

Sharding requires adding more nodes; vertical scaling increases resources on existing nodes. The choice must support growth, maintain availability, and minimize latency — and in a distributed database, query optimization is the difference between 0.1 seconds and 4.24 seconds for the same result.

Centralized databases

	Advantages	Disadvantages
Centralized	Maintenance (updates and backups) is straightforward · Stronger consistency and ACID compliance · Simpler programming model · Efficient for small datasets that fit on one node	High latency once query volume approaches single-node limits · A single point of failure risks total inaccessibility

Distributed databases

	Advantages	Disadvantages
Distributed	Faster access by retrieving from the nearest or most frequently used shard · Supports different levels of distribution transparency · Complex queries split into subqueries for parallel processing	Cross-site retrieval increases latency · Joins across partitioned nodes are expensive and complex to reconstruct · Consistency requires additional complexity · Synchronization for updates and backups is slower

Query optimization: a worked example

Transaction speed depends on query type, shard count, network speed, and hardware. Consider a query touching three tables across two sites:

Store    :    10,000 tuples  at site A
Product  :   100,000 tuples  at site B
Sales    : 1,000,000 tuples  at site A

The query:

sql

SELECT Store_key
FROM (Store JOIN Sales JOIN Product)
WHERE Region = 'East' AND Brand = 'Wolf';

Estimated cardinalities for the intermediate results:

Rows matching brand Wolf: 10
Stores in the East region: 3,000 (out of 10,000)

Communication assumptions:

Data rate     = 50,000,000 bps   (50M bps)
Access delay  = 0.1 s per site
Tuple size    = 200 bits  (25 bytes)

The model

T = a + v / b

  a = total access delay
  b = data rate
  v = total data volume

Three approaches

Approach 1 — move Product to site A and process there.

T = 0.1 + (100,000 * 200) / 50,000,000
  = 0.1 + 0.4
  = 0.5 seconds

Approach 2 — move Store and Sales to site B and process there.

T = 0.2 + ((10,000 + 1,000,000) * 200) / 50,000,000
  = 0.2 + 4.04
  = 4.24 seconds

Note the access delay is 0.2 here — two tables are being moved, so two site accesses.

Approach 3 — filter Brand = 'Wolf' at site B first, then move only the result to site A.

T = 0.1 + (10 * 200) / 50,000,000
  = 0.1 + 0.00004
  ~= 0.1 seconds

The result

Approach	Data moved	Time	Relative
Move Product to A	100,000 tuples	0.5 s	5x slower than best
Move Store + Sales to B	1,010,000 tuples	4.24 s	42x slower
Filter at B, move result to A	10 tuples	~0.1 s	Best

The third option offers the lowest latency. Filtering at the source minimizes data transfer volume compared to moving full tables — which highlights the critical role of query optimization in distributed systems.

Conclusion

Distributing data across nodes — vertically and horizontally — aims to improve:

Reliability (fault tolerance)
Performance
Balanced storage capacity and dollar costs

...provided the queries are optimized. Centralized and distributed databases each involve trade-offs, and the choice should be driven by the application's consistency, availability, scalability, and operational requirements.