[Database] Simple SQL vs NoSQL

1. Schema

  • SQL stores data in tables.
  • NoSQL stores in different methods (key/value, document, columnar and graph)

2. Querying

  • SQL databases uses regular SQL statements.
  • NoSQL uses unQL (Unstructured Query Language)

3. Scalability

  • SQL databases are vertically scalable (beef the same server hardware).
  • NoSQL databases are horizontally scalable (add more servers)

4. Reliability

SQL databases are ACID friendly, NoSQL sacrifices ACID property for performance reasons.

4.1) What is ACID?

Set of properties of database transactions to guarantee validity even in the event of errors, power failures.etc.

Atomicity Consistency Isolation Durability

4.1.1) Atomicity

  • Transactions are often composed of multiple statements. 
  • Atomicity guarantees each transaction is treated a single 'unit', which either succeeds completely or fails completely. 
  • If any of the statements in a transaction fails to complete, the entire transaction fails and database is left unchanged. 
  • An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.
  • Eg. money transfer from account A to B. It consists of two operations: withdraw from A and deposit to B. These 2 operations are atomic and ensures that money is neither lost or created if either of those two operations fail.

4.1.2) Consistency

  • Ensures transaction can only bring the database from one valid state to another.
  • Any data written must be valid according to defined rule.

4.1.3) Isolation

  • Transactions are often executed concurrently. 
  • Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if transactions were executed sequentially. 
  • DBMS usually acquires locks on data which may result in a loss of concurrency or implements multiversion concurrency control. This requires adding logic for the application to function correctly.
  • Most DBMSs offer a number of transaction isolation levels, which control the degree of locking that occurs when selecting data. (Serializable, Repeatable reads, read committed, read uncommited)

4.1.4) Durability


  • Once a transaction has been committed, it will remain committed even in system failure.
  • This means that transactions are recorded in non-volatile memory (memory that can be restored after a power-failure - not temporary)
  • Eg. if flight booking reports that a seat is successfully booked, the seat remains booked even if the system crashes.
  • Many DBMSs implement durability by writing transactions into a transaction log that can be reprocessed to recreate the system state right before any later failure. A transaction is deemed committed only after it is entered in the log.

5) When to use SQL?

  • Data has table and defined structure.
  • ACID compilance
  • Not looking for insanely fast data retrieval (reading from dish vs memory)


6) When to use NoSQL?

  • Storing big amount of data that has no defined structure.
  • Using cloud storage and hosting (for scaling)
  • Quick software development and prototyping.



Comments

Post a Comment

Popular posts from this blog

[Redis] Redis Cluster vs Redis Sentinel

1) In-Depth Guides to Redis Cluster and Sentinel For more in-depth explanation for Redis Cluster or Redis Sentinel, please read my other posts: Sentinel:  https://notafraidofwong.blogspot.com/2019/05/redis-redis-sentinel.html Cluster:  https://notafraidofwong.blogspot.com/2019/05/redis-redis-cluster.html 2) Deciding between Redis Sentinel and Redis Cluster Similarities : Both solutions provide high availability for your system. 2.1) Redis Cluster 2.1.1) Pros Shards data across multiple nodes Has replication support Has built-in failover of master 2.1.2) Cons Not every library supports it May not be as robust (yet) as Standalone Redis or Sentinel Setup and maintenance is more complicated 2.2) Redis Sentinel 2.2.1) Pros Automatically selects new master in case of failure Easy to setup, (seems) easy to maintain 2.2.2) Cons No data sharding; master might be overutilized. Another distributed system to maintain. 3) So how do I decide? In
Read more

[Unit Testing] Test Doubles (Stubs, Mocks....etc)

Image
In unit testing, isolation is key. The goal of unit testing is to test individual components, and not an entire system . The class/object/function you are testing is System Under Test (SUT) , and the other components of the system are Collaborators or Dependencies. Test Double  is a generic term for any kind of 'pretend' object used in place of a real object for testing purposes.  There are several types of Testing Doubles. We will start with: Fake Stub Mock Command Queries Mocks vs Stubs And then: Dummy Spies Spies vs Mocks I) Basic Testing Doubles a) Fake Fake are objects that have working implementations, but not same as production one. Usually they take some shortcut and have simplified version of production code. Note that Fake is a generic term - that can point to anything; usually mock and stubs. An example of this is an in-memory implementation of a database. This fake implementation will not access the actual database, but will u
Read more

[Node.js] Pending HTTP requests lead to unresponsive nodeJS

This is a recap conclusion to the potential problems of a NodeJS program I worked on previously. The program simulates thousands of clients navigating a website by sending thousands of http requests to a given API . The idea is to load test a particular API to make sure they can handle high traffic. Because we are simulating clients, it's important to keep track of the state of the simulated client as well. The problem with this program is it is buggy and becomes unresponsive from time to time (the only solution is to restart it). The problem was fixed by re-writing the entire program in Java, but it was regretful that I never had enough time to find the exact reason the nodeJS version of it failed. The following are potential causes I suspect: 1. Exceptions The NodeJS program was not written very neatly and stacked callbacks on top of callbacks on top of callbacks. This made it very difficult to keep track of exceptions and unexpected http responses are prone to happen. So
Read more