Redis - Examples

 In an eCommerce site

• Preserve user cart information
• User browsing history
• User past purchases
• Automatically Expire sessions on timeout

Redis - Remote Dictionary Service

Redis - open-source key-value database server

Keep Experimenting!!!

NoSQL One Liners

During NoSQL Session, After the first half of sessions. Learners shared their comparison of RDBMS vs NoSQL

An RDBMS includes functions that maintain the security, accuracy, integrity and consistency of the data

RDBMS - consistent and availability

All tables are related to each other in RDBMS and  in NoSQL all tables are independent of each other

NoSQL - partition tolerance at the expense of availability

ACID (atomicity, consistency, isolation, durability) is a set of properties of RDBMS  for transaction 

RDBMS fixed schema and No SQL have dynamic schema (Schemaless)

In NoSQL, we can exclude the information which is not required 

When to Choose what?

• High reads consistent data - RDBMS
• High writes low reads - HBase, Cassandra
• Document-based storage (multiple key-value pairs, or key-array pairs, or even nested documents) - Mongodb, Couchdb
• Key-Value stores are similar to maps or dictionaries where data is addressed by a unique key - Redis

Keep Exploring!!!

NoSQL Summary - Options

 A bit of a relook on NoSQL for a class helped me consolidate my learning.

NoSql - Not only SQL. During Engineering when it comes to Database design it is all about

• Codd's Rule
• Normalization Techniques 

What I thought in 2005

• How we handle columns, data types, relationships everything is key. Handling Null, Default values, constraints, etc...

Systems data was structured in 2000

• 20 years back there was no social media, no WhatsApp. Most of the data is structured data, transactions, automating orders, etc..

What all performance improvements/challenges came as data volumes increased?

• Partitioning by products/duration
• Replication to manage read / writes
• Use of Snapshot isolation/options
• Denomalizing few tables
• Migrating to the latest version / Rewriting some of the slow-performing reports
• Pagination of reports instead of fetch all approach
• Archiving completed orders
• Vertical Scaling- Add more RAM, CPU

Since the social media age

• Now we have more unstructured, semi-structured data from mobile phones, social media, reviews, ratings, rankings, messages, images, and videos.

I still remember the 2010 period when Hadoop was much spoken about. Moving computation where data is available. I looked up my post in 2011 on MongoDB. 

The evolution of databases is from

• Stage 1 - Papers, Ledgers
• Stage 2 - Excel, Access
• Stage 3 - Databases
• Stage 4 - Hadoop for large-scale data
• Stage 5 - NoSQL
• Stage 6 - lakehouse = (Hadoop + RDBMS + NOSQL + AI for data extraction from unstructured sources)

Building a RDBMS perspective is Tables, Keys, Relationships

Ref - Link

Everything revolves around Reading Correct Data vs Dirty Data (Transactions in progress may or may not commit). 

Everything in DBMS is

• Create
• Read
• Update
• Delete

How does read/write balance, Essentially a record or row needs to be locked before update. This ensures we work in a consistent state.

CAP theorem is the Crux of Everything

Ref - Link

Now you need to choose DB based on preference (C - A - P)

Questions to ask to decide on the choice of Database?

• Is Query pattern aggregates or select for individual records?
• What is projected database growth?
• Is it structured / semi-structured data?
• What are my top 2 choices, can I do a quick prototype and performance test to validate 
• Schema design what practices are relevant to each database type? What maps closely to the current context?
• Is Consistency a key thing, What about Availability / Partition tolerance, Is this system queried across geography to have availability in different regions
• If it exists how different copies will sync up, Will there be a master-slave approach / Replication / Log copy?
• What is cost allocated considering volume, and high availability needs?

Different NoSQL Systems

• In one of the SaaS products we worked on, the Redis Key value pair was used for session management
• IoT platform for device management in one of my friends Team Cassandra was used to push device data / Generate reports
• One of the big retailers I was familiar used heavily columnar database Vertica to manage all their aggregate data for BI / ML work

We need to consider the use case, data volume, velocity, type of reporting, cost, growth, security everything to decide on choosing a database.

Ref - Link

I say table in RDBMS, Collection in MongoDB, What is the conceptual mapping?

SQL vs NoSQL Design Thinking 

• How do I design collection in document DB, nested 1 to 1, 1 to many relationships
• What information I store in key-value pair, What key value will be unique and will not result in duplicates
• What column family I will create, How many aggregate queries will look like

Schema / Relationships / Keys will vary based on the Database type.

Which Database for What Application Purpose?

• High reads consistent data - RDBMS
• High writes low reads - HBase, Cassandra
• Document-based storage (multiple key-value pairs, or key-array pairs, or even nested documents) - Mongodb, Couchdb
• Key-Value stores are similar to maps or dictionaries where data is addressed by a unique key - Redis

Above all cost also plays a key role. Knowing what to choose based on size, data growth, and access patterns is key to deciding the type of Database for implementation.

RDBMS, KeyValue, Columnar, Graph, Document Collection all these forms of databases will co-exist :)

Data Stack

Ref - Link

Modern Analytics Stack

Ref - Link

Source - Link

New Cardinality Notations and Styles for Modeling NoSQL Document-store Databases 

NoSQL data modeling

ERD Plus

Keep Learning!!!

CAP - #Consistency, #Availability, #PartitionTolerance

 #Consistency,

• When True, I remember exactly everything, I will recall the same details every time
• When False, I remember approximately before the latest info, I may have a poor memory

#Availability,

• When True, I will attend your call everything, You will get a reply (When you impress your partner)
• When False, I may not respond when I sleep (Post marriage no reply on every call)

#PartitionTolerance,

• When True, You have two networks/numbers to reach me, When one number is not available another number you can reach me, You have an alternate network
• When False, When one number is not available, Network issue, You cannot reach me

Keep Exploring!!!

Data Perspectives

Different perspectives to decide on choosing the right database?

• Strict data types - Schema on write
• Schemaless data - Schema on read
• Read-only immutable data
• Eventually consistent data
• Dirty read vs Committed data
• Multi-version concurrency control
• Replicate data based on logs
• Replay committed logs
• Data sharding
• High reads consistent data - RDBMS
• High writes low reads - HBase, Cassandra
• Document-based storage - Mongodb, Couchdb
• CAP, ACID Properties

Things I Wished More Developers Knew About Databases

Want to Debug Latency?

I wrote an initial draft on the things I wished more developers knew about DBs. It touches a variety of topics: write skews, external consistency, clock skews, database-generated IDs, nested transaction issues, caches & more. Is there anything you wished more devs knew about DBs?

— Jaana Dogan (@rakyll) April 13, 2020

Almost similar and deep-dive techniques from the tweet conversation

• Read heavy vs write heavy. Insert vs updates. Vaccuuming
• Replication or not, transaction logging, why indexes matter, performance tuning, i/o scheduler, unicode, gender isn't binary
• Locks, cache effects, isolation levels
• IO bound vs network bound especially in the situation of replication, scaling strayegy, concurrency vs distributed.
• Materialized views, and the dangers of invalidating them unexpectedly.
• Connection pool, scaling techniques to handle distributed application / system, improve performance, optimization of query etc.
• I'd be interested in how this applies to a distributed system. Concurrency (specifically MVCC), connections, DB threading, backpressure handling
• Disk storage implementation and optimization

Keep Thinking!!! 

NOSQL Internals and Design Practices

Objective – The objective of this paper is to analyze NoSQL internals from RDBMS developer perspective and provide design guidelines for NoSQL Applications
Analysis
RDBMS – RDBMS came into the picture to ensure the ACID properties are maintained and there is a single version of the truth. RDBMS plays a critical role in OLTP applications (Banking, Finance, and Payment) domains.
Database design– Database design is implemented to ensure it's normalized and avoid data redundancy. Primary Keys, Indexes are created to ensure query plans use the indexes to filter required rows and fetch required results within the shortest intervals.

Query Execution – Data is typically stored in a B-Tree format. The data is organized physically in the form of clustered indexes. This is the reason search based on the primary key is quick compared to any other non-indexed columns. Database Engine implements several other operations to optimize the execution plan by leveraging indexes, statistics, and partitioning, Non-clustered indexes. Depending on the query plan join operators, sort operators are applied to produce the execution plan. The execution plan is reused if it already exists in memory.

This paper was very useful to understand OLTP Internals. Reposting notes from my blog post

• WAL – Changes are written in log and committed to disk when the checkpoint is reached
• Buffer Manager – cache for data fetched / recently used
• Two-Phase locking – Optimistic/pessimistic locking depending on isolation levels
• Concurrency control – Based on isolation levels

NoSQL Databases 

Similar to above OLTP aspects, There are few papers that describe designing NOSQL apps for Read heavy / Write Heavy Apps. This paper was very useful to understand NoSQL perspective of designing apps in columnar databases

For Heavy Writes

• Tall Skinny Tables
• Consolidate data into single columns

For Heavy Reads

• Fewer column families
• Use bloom filters

There are multiple NoSQL databases (Key-Value, Document-based, Columnar Databases, etc...). 

Happy Learning!!!

Difference between SQL and NOSQL Systems

Reposting from my two-year-old Quora answer

The Key differences between them lies in the understanding CAP theorem

• Consistency
• Availability
• Partition Tolerance

In layman terms. SQL systems ex-RDBMS will adhere ACID properties (Atomicity, Consistency, Isolation, Durability).

• The datatypes, schema are predefined, You cannot store non-matching datatypes
• To avoid dirty data, systems enforce isolation levels that govern only committed data is read (Consistency)
• Only latest records are available, records at that point in time are not available
• Banking Systems, ordering systems where data needs to consistent will be mostly SQL based systems where consistency is important

No-SQL systems (Not Only SQL)

• The schema is not tightly governed, its flexible you can store different datatypes in same columns
• These may be geographically distributed where data may be synced and eventually be consistent end of day not realtime
• They also support point in time data, data values at a point in time can also be looked up
• Where there is no requirement for consistency we can achieve other 2 Availability and partition tolerance
• Since some of the ACID properties are compromised you will have high availability of this systems

It is more to do with business need to decided SQL or SQL based storage.

Happy Learning!!!

Databases - IOT - CES

CES notes on IOT had a interesting tag line posted in MEMSQL blogpost

Tag line copied from the post

Also, vast landscape of DB products in multiple categories (RDBMS, NOSQL, In-memory, Hadoop, Stream processing) check-out 451 research paper. Depending on the application needs you can identify top products to evaluate / get started

Data Platforms Landscape 

Updated Data Platforms Landscape Map – October 2014 http://t.co/qhJawLQhNX Free download - now fully indexed pic.twitter.com/cH3QL5GFcm
— Matt Aslett (@maslett) November 18, 2014

NoSQL LinkedIn Skills Index – December 2014

NoSQL LinkedIn Skills Index – December 2014 http://t.co/bCvR4ipSGG
— Matt Aslett (@maslett) December 18, 2014

Current State

It is remarkable how many database vendors are struggling despite apparently pre-emptively designing their products to be perfect for IoT
— Matt Aslett (@maslett) December 11, 2014

Happy Learning!!!

NOSQL Basics

[You may also like - NOSQL - can it replace RDBMS Databases]

Deep Dive is very important to understand the basics/fundamentals of product design. I have explored a couple of NOSQL database products. Based on readings from blogs/papers. I have tried to document the underlying fundamentals behind NOSQL Databases

Tip #1 - NOSQL Stands for “Not Only SQL”
Tip #2 - ACID properites - What it is all about ?

From Earlier Post - ACID Properties short RECAP

• Atomicity - Transaction is one unit of work. All or None. Either all of its data modifications are performed or none of them are performed
• Consistency - Transaction must leave the database in a consistent state. Maintain data integrity. Governing Data Structures for Indexes/Storage must be in a correct state. If a Transaction violates a constraint it must be failed.
• Isolation - Keep Transaction Separate. Concurrency is governed by Isolation levels.
• Durability - In the case of System failures changes persist and can be recovered on abnormal termination

Tip #3 - What is CAP Theorem. In Every NOSQL White paper there is a reference to the CAP theorem.

CAP stands for consistency, availability and partition tolerance 

Short and easy summary of it I found from link

• Consistency - Consistent (Latest) Data Reflected querying any server in Distributed Environment
• Availability - Data Returned from Server irrespective it is latest / last updated data
• Partition Tolerance - System is available even if individual nodes are down

As per CAP Theorem only two parameters can be targeted for complete support. To Summarize it

• As per CAP Theorem, RDBMS targets Consistency & Partition Tolerance
• NoSQL targets Availability and Partition Tolerance

Tip #4 - What is MVCC? While working on NoSQL DB, I noticed MVCC for versioning/managing locks.  

  

MVCC refers to Multiversion concurrency control. MVCC Managing providing latest committed updates for read transactions by versioning. Here with versions present Reads will not block writes. MSSQL 2005 onwards we have Snapshot isolation feature. This is also based on the versioning concept. Reposting my notes on how snapshot isolation is achieved in MSSQL

READ COMMITTED SNAPSHOT using Row Versioning in Microsoft SQL Server 2005 onwards (Applicable for 2008, 2012..) 

a. How it works - A new data snapshot is taken and remains consistent for each statement until the statement finishes execution.   uses a version store and reads the data from the version store.

b. How it solves the concurrency issues  

• SELECT statements do not lock data during a read operation  (readers do not block writers, and vice versa).  

c. Performance Advantages 

• SELECT statements can access the last committed value of the row, while other transactions are updating the row without getting blocked
• Reduces disk contention on the data files. Reducing locking resources, readers do not block writers. No more deadlocks involving readers and writers.  

d. Resource usage and overhead

•  Row versioning increases resource usage during data modification as row versions are maintained in tempdb. tempdb growth, contention. Additional memory usage.

Tip #5 - Below are common list of features implemented by NoSQL Databases and advantages of it 

• NO Schema Reqd - Data Types need not be defined
• CouchDB also uses MVCC for managing versions of data (Good Read Link )
• Auto Sharding - Spread data across servers to scale out
• Support for Replication

Still I have a long way to go to understand NOSQL, I am planning to explore NOSQL Database Architecture in detail in coming posts.

More Reads - NOSQL Patterns

Consistency Models
MongoDB 4.2.6
YugaByte DB 1.3.1
Aerospike 3.99.0.3

Happy Learning!!!!

NOSQL Updates & Database Products Update - II

[Previous Post in Series - NOSQL Updates & Database Products Update]
[You may also like - NOSQL - can it replace RDBMS Databases?]

Amazon has launched its own NOSQL database - Dynamo DB . NOSQL space is growing big with multiple players launching their own NOSQL databases.

Features

• Index management (Predictable performance Even if Data Size grows, Need to explore more to understand how this is taken care)
• Flexible Schema (Feature common in all NOSQL databases)
• Scalability (Scale as per your need), High Availability by having data replicated across multiple zones & Performance
• More Detailed Notes - Get Started with Dynamo DB
• Amazon CTO Dynamo DB introduction blogpost

More Reads - Cassandra and Dynamo Comparision

Another very good read - Top BI Trends for 2012 and a Look Back at 2011

Focus for this year 2012 BI market is

• Mobile BI
• Cloud BI
• NOSQL BI Strategy
• NOSQL Adoption (ACID compliant, cloud based DBs - NuoDB)

More Reads

• Facebook Time line Feature Design - Denomarlize Data, Build Data Marts (BI)
• The State of NoSQL in 2012
• Cassandra NYC 2011: Matt Dennis - Data Modeling Workshop
• 7 Databases in 7 Weeks – 8 Week Review and Experience

Happy Reading!!!

NOSQL Updates & Database Products Update

I follow brentonzar blog for SQL updates. Latest SQL 2012 features please check post

Consolidating list of other interesting news on databases

• Oracle is going to provide connectors for NOSQL Databases (Link)
• Oracle develops its own version of NOSQL Database (Link)
• SQL Server 2012 is going to provide connectors for NOSQL and Hadoop Databases (Link)
• Column based storage concept seem to be in roadmap for both SQL Server and Oracle, SQL Server provides column storage option for tables (link).
• Oracle also mentioned data compression by storing data as columns than as rows (Link)

Lots of Intersting news and Updates supporting and consolidating data from NOSQL Databases

More Reads
They’re Here! SQL Server 2012 Early Adoption Cook Book Videos!

Happy Reading!!