Database

Overview Both are concurrency-control strategies for preventing lost updates when multiple transactions touch the same data, but they differ in when they deal with conflict. Optimistic locking assumes collisions are rare and only performs a version check at commit time, while pessimistic locking assumes collisions are likely and takes an exclusive lock before any read or write proceeds. Comparison Diagram Optimistic Pessimistic Record (v1) no lock taken Txn A Txn B v1 → v2 committed v1 ≠ v2 conflict, retry conflict caught at commit time Record blocked Txn A holds lock Txn B waiting commits & releases lock acquires lock then runs conflict prevented up front Comparison Table Aspect Optimistic Locking Pessimistic Locking Access phase No lock taken; any transaction can read or begin writing the row immediately Lock acquired (e.g. SELECT FOR UPDATE) before the transaction reads or writes the row Concurrent access Other transactions freely read and write the same row in parallel Other transactions attempting the same row must wait for the lock holder to finish Conflict detection timing Deferred until commit, via a version number, timestamp, or hash comparison Not needed as a separate step; the lock physically prevents overlapping access On conflict Commit is rejected; the transaction is rolled back and typically retried No conflict occurs; the waiting transaction simply blocks until the lock is released Implementation mechanism Application-level version column checked in the UPDATE’s WHERE clause Database-level row or table locks managed by the lock manager Throughput under low contention High; no blocking overhead when collisions are rare Lower; locking overhead is paid even when no real conflict would occur Behavior under high contention Retry storms and wasted work as many transactions repeatedly fail and re-run Orderly queueing keeps correctness but serializes work and limits parallelism Deadlock risk None, since no locks are ever held Possible when transactions acquire multiple locks in inconsistent order Key Differences Optimistic locking detects conflicts at commit time; pessimistic locking prevents them via upfront locking. Optimistic locking never blocks other transactions, it only forces a retry on collision. Pessimistic locking holds an exclusive lock for the transaction’s full duration, serializing access to the row. Optimistic locking carries no deadlock risk because it never holds locks. Pessimistic locking trades raw throughput for predictability when contention is heavy. When to Use Each Optimistic Locking ...

Overview Both are techniques for scaling a database beyond a single node, but they solve different problems: replication copies the entire dataset onto multiple nodes to boost availability and read capacity, while sharding splits the dataset into disjoint partitions across nodes to boost storage and write capacity. Large-scale systems typically use both together — sharding for horizontal scale, replication within each shard for durability. Comparison Diagram Replication Sharding Primary A B C D Replica A A B C D Replica B A B C D full dataset, copied to every node Router key lookup Shard 1 keys A-M Shard 2 keys N-Z dataset split into disjoint subsets Comparison Table Aspect Replication Sharding Primary goal Increase availability and read capacity Increase storage and write capacity Data distribution Full dataset copied to every node Dataset split into disjoint partitions across nodes Write path Writes go to primary, then propagate to replicas Writes routed to the single shard owning the key Read path Any replica (or primary) can serve any read Read must be routed to the shard holding the key Node failure impact Data survives since other copies exist That shard’s data becomes unavailable unless also replicated Consistency concern Replication lag between primary and replicas Cross-shard transactions and joins are hard to coordinate Scaling ceiling Bounded by primary’s write throughput Bounded by cross-shard coordination and key hotspots Operational overhead Failover and leader election Shard key design, rebalancing, and resharding Key Differences Replication duplicates the same data everywhere; sharding partitions it so each node holds only a slice Replication scales reads and durability; sharding scales writes and total storage Sharding introduces a routing layer that must know which shard owns a given key Losing a replica is harmless, but losing an unreplicated shard causes real data loss Production systems commonly combine both: shard for scale, replicate each shard for resilience When to Use Each Replication ...

Overview SQL and NoSQL databases differ in how they structure, store, and query data: SQL enforces a fixed schema of related tables joined by keys, while NoSQL favors a flexible schema optimized for scale and varied data shapes. The choice affects everything from how you model relationships to how the system behaves under heavy write load or schema change. Comparison Diagram SQLNoSQLUsersidname1AliceOrdersiduser_iditem91Bookforeign key join{"id": 1,"name": "Alice","orders": [{ "item": "Book" },{ "item": "Pen" }]}embedded, self-contained document Comparison Table Aspect SQL NoSQL Data model Rows in normalized tables with fixed columns Documents, key-value pairs, wide columns, or graphs with flexible fields Schema definition Defined upfront; changes require migrations (ALTER TABLE) Schema-on-read; fields can vary per record without migration Relationships Modeled explicitly via foreign keys and JOINs Modeled by embedding related data or denormalizing across documents Query language Standardized SQL across most vendors Vendor-specific APIs or query languages (e.g. MongoDB query, CQL) Transactions & consistency ACID guarantees across multi-row/multi-table operations Often eventual consistency; ACID typically limited to single-document scope Scaling approach Primarily vertical scaling; sharding is possible but complex Built for horizontal scaling via native partitioning/sharding Best-fit workload Structured data with complex, ad-hoc relational queries High-volume, high-velocity data with evolving or hierarchical structure Key Differences SQL requires a fixed schema agreed on before writing data; NoSQL allows each record to carry its own shape Relational databases resolve relationships through JOINs, while NoSQL typically resolves them through embedding SQL guarantees ACID transactions across tables; most NoSQL systems trade that for eventual consistency SQL systems scale primarily by scaling up hardware; NoSQL systems are designed to scale out across nodes Query language is a standardized across SQL vendors, whereas NoSQL query APIs are largely proprietary When to Use Each SQL ...

Overview A managed database hands the operating system, patching, backups, and failover to a cloud provider, while a self-hosted database keeps the entire stack under your team’s direct control. The choice trades operational convenience against flexibility, cost structure, and how much low-level tuning you’re allowed to do. Comparison Diagram Managed DatabaseSelf-Hosted DatabaseApplication / QueriesProvider ManagesDB EngineOperating SystemHardware / StorageLess control, less toilYou Manage EverythingApplication / QueriesDB EngineOperating SystemHardware / StorageMore control, more toil Comparison Table Aspect Managed Database Self-Hosted Database Provisioning & setup Spin up via console or API in minutes; provider installs and configures the engine Manually install and configure the OS, storage, and database software yourself Configuration & tuning access Limited to exposed parameters and flags; some engine internals and OS access are locked Full root or admin access to every config file, kernel setting, and storage layout Scaling Resize compute or add read replicas with a click or API call; provider automates the process Provision new hardware and reconfigure sharding or replication topology yourself Backups & recovery Automated snapshots and point-in-time restore built into the service You script, schedule, and test your own backup and restore procedures Patching & upgrades Provider applies OS and engine security patches on a maintenance schedule You plan, test, and execute every patch and major version upgrade High availability & failover Multi-AZ replication and automatic failover configured with a toggle You design, build, and test the replication and failover setup yourself Monitoring & support Built-in dashboards and alerts, plus vendor support tickets for engine-level issues You assemble your own monitoring stack; support is internal or community-based Cost model Higher per-hour price that bundles operational labor into the bill Lower raw infrastructure cost but a hidden cost in engineering time Key Differences Managed services abstract patching and OS maintenance behind a provider SLA. Self-hosted setups grant full root access to tune kernel, storage, and engine internals. Failover and multi-AZ replication are automated in managed offerings but hand-built elsewhere. Cost shifts from engineering hours to a recurring subscription fee with managed databases. Self-hosting permits any custom extension or fork that managed platforms often restrict. When to Use Each Managed Database ...