My Tech Learnings: System Design : Databases

https://www.youtube.com/watch?v=6GebEqt6Ynk

TWO TYPES OF INDICES

LSM-based databases

Log strucured Merge Tree :

Designed for high write throughput.
Use a log-structured merge-tree to manage data by first writing to memory (MemTable)
Then flushing sorted, immutable files (SSTables) to disk.
Common examples include RocksDB, Apache Cassandra, LevelDB, InfluxDB, and ScyllaDB, which are frequently used in NoSQL and time-series applications.

Avoids getting locks when reading immutable table = performance benefits

B-trees based databases

Like Postgres & MySQL

When searching

LSM may search in memory and disk hence read maybe slower
Btree : always search by going to single location (single search) on disk.

https://www.linkedin.com/pulse/multi-leader-replication-handling-write-conflicts-saurav-prateek/

Yes, multi-leader SQL replication inherently has write conflicts because multiple nodes can independently accept and commit writes to the same data simultaneously. These conflicts are typically detected asynchronously when the changes are replicated between the leaders, requiring specific strategies for resolution.

Understanding Write Conflicts
In a multi-leader setup, each leader processes write requests locally and then propagates the changes to other leaders. When two users modify the same record on different leaders at roughly the same time, both writes succeed locally. The conflict is only discovered later during the asynchronous replication process, by which point it's often too late to involve the user in the resolution.

In contrast, a single-leader system avoids this by having a single primary node manage all writes, either blocking or aborting subsequent writes to ensure sequential order and immediate consistency.

Conflict Resolution Strategies
Because conflicts are a natural outcome in multi-leader systems, mechanisms must be in place to ensure all replicas eventually converge to the same consistent state (convergent conflict resolution). Common strategies include:

Conflict Avoidance: The simplest approach is to design the application so that all writes for a specific record are routed to the same designated leader (e.g., a user's "home" data center).
Last Write Wins (LWW): Each write is tagged with a unique identifier, such as a timestamp (which requires synchronized clocks) or a UUID. The write with the highest ID is chosen as the winner, and others are discarded. This can lead to data loss.
Merging Values: For certain data types (like collaborative text editing or shopping carts), the system might merge the conflicting changes into a single value.
Application-Level Resolution: The database stores all conflicting versions, and custom application code (either on read or on write) handles the logic to resolve the conflict based on business rules, sometimes involving user input. For example, CouchDB uses this approach.
Conflict-Free Replicated Data Types (CRDTs): These are specialized data structures that are mathematically guaranteed to converge to the same state regardless of the order of concurrent updates, making conflict resolution automatic and safe.

Different SQL database implementations have varying levels of support for multi-leader replication and conflict handling. For example, MySQL Group Replication offers virtual synchronous replication with conflict handling, while in older MySQL multi-master setups, manual conflict avoidance or external tools were necessary. Modern SQL Server Peer-to-Peer replication can be configured for automatic LWW conflict resolution.