Denormalization - When to Trade Integrity for Performance

▶

Steps

setup

Initialize empty B-tree

We start with an empty B-tree of order 3 (max 2 keys per node). The root node is created with no keys and no children.

💡 Initializing the tree sets the foundation for all insertions and shows the starting point before any data is added.

Line:

class BTreeNode:
    def __init__(self, is_leaf):
        self.keys = []
        self.children = []
        self.is_leaf = is_leaf

💡 The tree starts empty, ready to accept keys and grow as needed.

insert

Insert first key 10

Insert key 10 into the root node, which is currently empty and a leaf.

💡 Inserting the first key shows how keys are added to an empty node without splitting.

Line:

if root.is_leaf:
    root.keys.append(key)
    root.keys.sort()

💡 Keys are inserted directly into leaf nodes if there is space.

insert

Insert second key 20

Insert key 20 into the root node, which has space for one more key.

💡 Adding a second key shows how keys are kept sorted within a node.

Line:

root.keys.append(key)
root.keys.sort()

💡 Keys within a node are maintained in sorted order for efficient search.

insert

Insert third key 30 triggers split

Insert key 30 into the root node, which is full. This triggers a node split.

💡 When a node is full, inserting another key requires splitting the node to maintain B-tree properties.

Line:

root.keys.append(key)
root.keys.sort()
if len(root.keys) == order:
    split_root()

💡 Splitting nodes keeps the tree balanced and maintains order constraints.

insert

Insert key 25 into subtree

Insert key 25 by traversing from root to the right child node (keys > 20).

💡 Traversal shows how the tree directs insertions to the correct leaf node.

Line:

if key > root.keys[0]:
    insert_in_child(root.children[1], key)

💡 B-tree traversal uses keys to decide which child to descend into.

insert

Insert key 25 into leaf node

Insert key 25 into the right child leaf node which currently has key 30.

💡 Inserting into a leaf node with space shows normal insertion without splitting.

Line:

child.keys.append(key)
child.keys.sort()

💡 Leaf nodes accept keys until full, maintaining sorted order.

insert

Insert key 5 into left subtree

Insert key 5 by traversing to the left child node (keys < 20).

💡 Traversal to left child demonstrates how smaller keys are routed.

Line:

if key < root.keys[0]:
    insert_in_child(root.children[0], key)

💡 Traversal decisions maintain tree order and balance.

insert

Insert key 5 into left leaf node

Insert key 5 into the left child leaf node which currently has key 10.

💡 Inserting smaller key into leaf node shows keys remain sorted.

Line:

child.keys.append(key)
child.keys.sort()

💡 Keys in leaf nodes are always sorted for efficient search.

insert

Insert key 15 triggers split in left leaf

Insert key 15 into left leaf node which is full (has 2 keys). This triggers a split of the leaf node.

💡 Splitting leaf nodes maintains B-tree balance and order constraints.

Line:

child.keys.append(key)
child.keys.sort()
if len(child.keys) == order:
    split_child()

💡 Splitting redistributes keys and promotes middle key to parent.

traverse

Final B-tree structure after insertions

The B-tree is balanced with root keys [10, 20] and three leaf children holding keys [5], [15], and [25, 30].

💡 The final balanced tree shows how denormalization organizes data for efficient queries.

Line:

return root

💡 Balanced trees maintain performance by limiting height and ensuring sorted keys.

class BTreeNode:
    def __init__(self, is_leaf):
        self.keys = []  # STEP 1
        self.children = []
        self.is_leaf = is_leaf

class BTree:
    def __init__(self, order=3):
        self.root = BTreeNode(True)  # STEP 1
        self.order = order

    def insert(self, key):
        root = self.root
        if len(root.keys) == self.order - 1:
            new_root = BTreeNode(False)  # STEP 4
            new_root.children.append(root)
            self.split_child(new_root, 0)  # STEP 4
            self.root = new_root
            self.insert_non_full(new_root, key)  # STEP 4
        else:
            self.insert_non_full(root, key)  # STEP 2,3,5,6,7,8,9

    def insert_non_full(self, node, key):
        i = len(node.keys) - 1
        if node.is_leaf:
            node.keys.append(key)  # STEP 2,3,6,8
            node.keys.sort()  # STEP 2,3,6,8
        else:
            while i >= 0 and key < node.keys[i]:
                i -= 1
            i += 1
            if len(node.children[i].keys) == self.order - 1:
                self.split_child(node, i)  # STEP 4,9
                if key > node.keys[i]:
                    i += 1
            self.insert_non_full(node.children[i], key)  # STEP 5,7

    def split_child(self, parent, index):
        order = self.order
        node = parent.children[index]
        mid = order // 2
        new_node = BTreeNode(node.is_leaf)  # STEP 4,9
        new_node.keys = node.keys[mid+1:]  # STEP 4,9
        node.keys = node.keys[:mid]  # STEP 4,9
        if not node.is_leaf:
            new_node.children = node.children[mid+1:]  # STEP 4,9
            node.children = node.children[:mid+1]  # STEP 4,9
        parent.keys.insert(index, node.keys[mid])  # STEP 4,9
        parent.children.insert(index+1, new_node)  # STEP 4,9

# Usage example:
# btree = BTree(order=3)
# for key in [10, 20, 30, 25, 5, 15]:
#     btree.insert(key)  # STEP 2-9

📊

Denormalization - When to Trade Integrity for Performance - Watch the Algorithm Execute, Step by Step

Watching the B-tree insertion process step-by-step reveals how data is organized for fast access, illustrating the trade-offs in denormalization strategies.

insert

leaf

Initialize empty B-tree with root node.

insertKey: 10

10

leaf

Insert key 10 into root node.

insertKey: 20

1020

leaf

Insert key 20 into root node.

splitKey: 30↑ promoted: 20

20

10

leaf

30

leaf

Split root node and promote middle key 20.

insertKey: 25

20

10

leaf

30

leaf

Traverse to right child to insert key 25.

insertKey: 25

20

10

leaf

2530

leaf

Insert key 25 into right child leaf node.

insertKey: 5

20

10

leaf

2530

leaf

Traverse to left child to insert key 5.

insertKey: 5

20

510

leaf

2530

leaf

Insert key 5 into left child leaf node.

splitKey: 15↑ promoted: 10

1020

5

leaf

2530

leaf

15

leaf

Split left leaf node and promote key 10 to root.

traverse

1020

5

leaf

2530

leaf

15

leaf

Final balanced B-tree after all insertions.

Key Takeaways

✓ B-tree insertions maintain balance by splitting full nodes and promoting middle keys.

This insight is hard to see from code alone because the recursive splitting and promotion logic is complex and subtle.

✓ Traversal decisions based on key comparisons direct insertions to the correct leaf node.

Visualizing traversal paths clarifies how the tree maintains sorted order and efficient search.

✓ Denormalization via B-tree structure trades some data redundancy for faster read performance by reducing join operations.

Seeing the tree structure evolve helps understand the performance benefits and trade-offs of denormalization.

Practice

1. You have a relation with multi-valued attributes and repeating groups. At which normalization step do you first ensure atomicity of attributes and eliminate repeating groups?

easy

A. When converting the relation into First Normal Form (1NF)

B. When removing partial dependencies in Second Normal Form (2NF)

C. When eliminating transitive dependencies in Third Normal Form (3NF)

D. When enforcing stricter dependency conditions in Boyce-Codd Normal Form (BCNF)

Solution

1. Step 1: Understand 1NF's role in normalization

   1NF requires that all attributes be atomic and that there are no repeating groups or arrays. This is the first step in normalization to ensure the relation is well-structured.

2. Step 2: Differentiate from later normal forms

   2NF and 3NF focus on removing partial and transitive dependencies, respectively, but assume atomic attributes already exist. BCNF further refines dependency conditions but does not address atomicity.

3. Final Answer:

   Option A -> Option A

4. Quick Check:

   Atomicity and elimination of repeating groups is the hallmark of 1NF, not later forms.

Hint: Atomicity and no repeating groups -> 1NF

Common Mistakes:

• Confusing 1NF with 2NF or 3NF responsibilities
• Thinking BCNF addresses atomicity
• Assuming partial dependencies relate to atomicity

2. Trace the sequence of steps the query optimizer takes when generating an explain plan for a query with a filter predicate on a large table. What happens to the cardinality estimate after applying the filter?

easy

A. The optimizer first estimates the total rows, then reduces cardinality based on filter selectivity before choosing access paths

B. The optimizer ignores filter predicates during cardinality estimation and applies them only at execution time

C. The optimizer assumes filter predicates reduce cardinality to zero if any predicate exists

D. The optimizer estimates cardinality after choosing the access path, not before

Solution

1. Step 1: Initial cardinality estimation

   The optimizer starts by estimating the total number of rows in the table.

2. Step 2: Apply filter selectivity

   It adjusts cardinality by applying the estimated selectivity of the filter predicate, reducing the row count.

3. Step 3: Use adjusted cardinality for access path selection

   The reduced cardinality informs the choice of index scans or full scans.

4. Step 4: Why other options are incorrect

   The optimizer ignores filter predicates during cardinality estimation and applies them only at execution time: Ignoring filters during estimation leads to poor plans.
   The optimizer assumes filter predicates reduce cardinality to zero if any predicate exists: Assuming zero cardinality is unrealistic and would discard valid plans.
   The optimizer estimates cardinality after choosing the access path, not before: Cardinality estimation precedes access path selection to guide it.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   Filter predicates reduce estimated rows before access path decisions.

Hint: Filter predicates reduce cardinality estimates before access path selection.

Common Mistakes:

• Thinking filters are ignored until execution
• Assuming filters eliminate all rows
• Believing cardinality is estimated after access path choice

3. Which of the following statements about enforcing Isolation levels in a database is TRUE regarding performance trade-offs?

medium

A. Serializable isolation can cause significant performance degradation due to locking overhead

B. Snapshot isolation eliminates all concurrency anomalies without any trade-offs

C. Read uncommitted isolation prevents dirty reads without any performance cost

D. Higher isolation levels always improve throughput by reducing conflicts

Solution

1. Step 1: Understand isolation levels and trade-offs

   Higher isolation levels like Serializable prevent anomalies but increase locking and reduce concurrency.

2. Step 2: Analyze options

   A is false because higher isolation often reduces throughput. B is false; snapshot isolation can have anomalies like write skew. C is false; read uncommitted allows dirty reads.

3. Final Answer:

   Option A -> Option A

4. Quick Check:

   Serializable isolation trades performance for correctness.

Hint: Serializable isolation = correctness at performance cost

Common Mistakes:

• Believing higher isolation always improves throughput
• Assuming read uncommitted prevents dirty reads

4. Given a set of functional dependencies where some attributes can be NULL, how does the presence of NULL values affect the application of Armstrong's Axioms in inferring new dependencies?

hard

A. Armstrong's Axioms assume attributes are non-nullable, so NULLs invalidate inference results

B. NULL values do not affect Armstrong's Axioms since they are logical inference rules independent of data instances

C. Augmentation fails in presence of NULLs because adding attributes with NULL breaks dependency preservation

D. Transitivity cannot be applied if any attribute in the chain can be NULL

Solution

1. Step 1: Understand Armstrong's Axioms nature

   They are inference rules based on attribute sets and dependencies, independent of actual data values.

2. Step 2: Analyze NULL impact

   NULLs affect data instance semantics but do not change logical inference of dependencies.

3. Step 3: Evaluate options

   A is incorrect; axioms do not assume non-nullability.
   B is correct; inference is logical and data-agnostic.
   C is incorrect; augmentation is a logical rule, not data-dependent.
   D is incorrect; transitivity applies regardless of NULL presence.

4. Final Answer:

   Option B -> Option B

5. Quick Check:

   Armstrong's Axioms operate on schema-level dependencies, not data values.

Hint: Armstrong's Axioms = logical rules, unaffected by NULLs

Common Mistakes:

• Confusing logical inference with data instance behavior
• Assuming NULLs break functional dependencies at schema level
• Believing augmentation or transitivity depend on data completeness

5. Your system uses sharding with a hash-based shard key. Suddenly, one shard becomes a hotspot due to a sudden surge in traffic for a specific key range. What advanced strategy can you apply to mitigate this issue without redesigning the entire shard key?

hard

A. Switch to vertical partitioning to isolate the hotspot columns.

B. Implement consistent hashing with virtual nodes to better distribute load across shards.

C. Convert asynchronous replication to synchronous replication to reduce latency.

D. Increase the replication factor of the hotspot shard to spread reads.

Solution

1. Step 1: Identify the hotspot cause

   A surge in traffic for a specific key range causes uneven load on one shard.

2. Step 2: Evaluate vertical partitioning

   Vertical partitioning splits columns, not rows, so it won't address row-based hotspots.

3. Step 3: Consider consistent hashing with virtual nodes

   This technique allows finer-grained distribution of keys across shards, reducing hotspots without changing the shard key.

4. Step 4: Assess replication changes

   Increasing replication factor helps read scalability but does not reduce write hotspots; synchronous replication affects consistency, not load distribution.

5. Final Answer:

   Option B -> Option B

6. Quick Check:

   Consistent hashing with virtual nodes mitigates hotspots by spreading keys more evenly.

Hint: Virtual nodes in consistent hashing smooth out hotspots [OK]

Common Mistakes:

• Confusing vertical partitioning with horizontal load balancing
• Thinking replication reduces write hotspots
• Assuming replication mode affects load distribution