Can I get some help in understanding the real difference between YSQL vs YCQL? Based on the documentation I understand that the current implementation of underlying storage for YUGABYTE is DOCS DB and uses RAFT for replication.
Based on this can I assume that YSQL vs YCQL the only difference is that we have triggers, stored procs? and SQL features in YSQL and not in YCQL?
Great question. The plan is that over time YSQL will have most of the features in YCQL, but that is not the case today. This is because there is significant work left to be done in YSQL to achieve parity, some of which is already in progress.
YSQL features
YSQL re-uses the upper half of PostgreSQL with a horizontally scalable lower half called DocDB. Thus, YSQL would support all PostgreSQL features - including stored procedures, triggers, common table expressions, extensions and foreign data wrappers (last feature is not yet done).
YCQL features not in YSQL
Here is a list of YCQL features not in YSQL.
Cluster awareness: The client drivers are cluster aware, meaning the clients can discover all nodes of the cluster given just one contact point. These client drivers also get notified of node add/remove and therefore apps do not need a load balancer to use a distributed cluster. There is on-going work to incorporate this functionality into YSQL as a part of jdbc-yugabytedb project.
Topology awareness: The client drivers are also topology aware, meaning they are notified of the regions/zones in which the various nodes of the cluster are deployed. They can perform operations such as reading from nearest region/datacenter.
Automatic data expiry: YCQL supports automatic expiry of data using the TTL feature - you can set a retention policy for data at a table or row level and the older data is automatically purged from the DB.
Collection data types: YCQL supports collection data types such as sets, maps, lists. Note that both YCQL and YSQL support JSONB which can be used to model the above though.
Cassandra API compatible YCQL is Cassandra API compatible, and therefore supports the Cassandra ecosystem projects. Examples include Spark and Kafka connectors, JanusGraph and KairosDB support, etc. Note that while these ecosystem integrations can be built on top of YSQL, it does not exist today and is a matter of prioritization.
