Centralized vs. Distributed: Trade-Offs and Query Optimization

In one line: 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.

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 design minimizes data transfer volume compared to moving full tables — which highlights the critical role of query optimization in distributed systems.

The principle worth carrying out of this

Move the computation to the data, not the data to the computation.

Approach 3 transfers 10 tuples instead of a million because the filter runs where the data already lives. The transfer time drops to essentially nothing and the whole query cost becomes the fixed access delay — you have hit the floor.

This is why distributed query planners push predicates down toward storage, why SELECT * across shards is so expensive, and why an interviewer asking "how would you make this query faster" is usually asking "what can you filter before it crosses the network."

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.

Key takeaway

Distribution buys capacity and fault tolerance and charges you in coordination — most visibly at joins. Whether that trade pays off depends almost entirely on whether your queries can be answered without moving data between shards.

Interview signal by level

Level	What a strong answer sounds like
L4	"Distributed databases scale better than a single one."
L5	Names the cost: "cross-shard joins get expensive, so I'd shard so related data stays together."
Staff+	Quantifies it: "filtering at the source instead of moving the table is a 40x difference on this query — push predicates down and move computation to the data. And I'd check which term dominates: if it's fixed access delay, the fix is fewer round trips, not less data."

Next: all of it applied to one design, end to end.