Databases

Ahoy, mateys! My development journey of the first version of the geo-distributed messenger has ended. So in this last article of the series, I’d like to talk about multi-region database deployment options that were validated for the messenger. The high-level architecture of the geo-messenger is represented below: The microservice instances of the application layer are scattered across the globe in cloud regions of choice. The API layer, powered by Kong Gateway, lets the microservices communicate with each other via simple REST endpoints. The global load balancer intercepts user requests at the nearest PoP (point-of-presence) and forwards the requests to microservice instances that are geographically closest to the user. Once the microservice instance receives a request from the load balancer, it’s very likely that the service will need to read data from the database or write changes to it. And this step can become a painful bottleneck—an issue that you will need to solve if the data (database) is located far away from the microservice instance and the user. In this article, I’ll select a few multi-region database deployment options and demonstrate how to keep the read and write latency for database queries low regardless of the user’s location. So, if you’re still with me on this journey, then, as the pirates used to say, “Weigh anchor and hoist the mizzen!” which means, “Pull up the anchor and get this ship sailing!” Multi-Region Database Deployment Options There is no silver bullet for multi-region database deployments. It’s all about tradeoffs. Each option provides advantages, while others cost you something. YugabyteDB, my distributed SQL database of choice, supports four multi-region database deployment options that are used by geo-distributed applications: Single Stretched Cluster: The database cluster is “stretched” across multiple regions. The regions are usually located in relatively close proximity (e.g., US Midwest and East regions). Single Stretched Cluster With Read Replicas: As with the previous option, the cluster is deployed in one geographic location (e.g., North America) across cloud regions in relatively close proximity (e.g., US Midwest and East regions). But with this option, you can add read replica nodes to distant geographic locations (e.g., Europe and Asia) to improve performance for read workloads. Single Geo-Partitioned Cluster: The database cluster is spread across multiple distant geographic locations (e.g. North America, Europe, and Asia). Each geographic location has its own group of nodes deployed across one or more local regions in close proximity (e.g., US Midwest and East regions). Data is automatically pinned to a specific group of nodes based on the value of a geo-partitioning column. With this deployment option, you achieve low latency for both read and write workloads across the globe. Multiple Clusters With Async Replication: Multiple standalone clusters are deployed in various regions. The regions can be located in relatively close proximity (e.g., US Midwest and East regions) or in distant locations (e.g., US East and Asia South regions). The changes are replicated asynchronously between the clusters. You will achieve low latency for both read and write workloads across the globe, the same as with the previous option, but you will deal with multiple standalone clusters that exchange changes asynchronously. Alright, matey, let’s move forward and review the first three deployment options for the geo-messenger’s use case. I will skip the fourth one since it doesn’t fit the messenger’s architecture which requires a single database cluster. Single Stretched Cluster in the USA The first cluster spans three regions in the USA—US West, Central, and East. Application/microservice instances and the API servers (not shown in the picture) are running in those same locations plus in the Europe West and Asia East regions. Database Read/Write Latency for the US Traffic Suppose Ms. Blue is working from Iowa, US this week. She opens the geo-messenger to send a note to a corporate channel. Her traffic will be processed by the microservice instance deployed in the US Central region. Before Ms. Blue can send the message, the US Central microservice instance has to load the channel’s message history. Which database node will be serving that read request? In my case, the US Central region is configured as a preferred one for YugabyteDB, meaning a database node from that region will handle all the read requests from the application layer. It takes 10-15 ms to load the channel’s message history from that US Central database node to the application instance from the same region. Here is the output of my Spring Boot log with the last line showing the query execution time: Java Hibernate: select message0_.country_code as country_1_1_, message0_.id as id2_1_, message0_.attachment as attachme3_1_, message0_.channel_id as channel_4_1_, message0_.message as message5_1_, message0_.sender_country_code as sender_c6_1_, message0_.sender_id as sender_i7_1_, message0_.sent_at as sent_at8_1_ from message message0_ where message0_.channel_id=? order by message0_.id asc INFO 11744 --- [-nio-80-exec-10] i.StatisticalLoggingSessionEventListener : Session Metrics { 1337790 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 1413081 nanoseconds spent preparing 1 JDBC statements; 14788369 nanoseconds spent executing 1 JDBC statements; (14 ms!) Next, when Ms. Blue sends the message into the channel, the latency between the microservice and database will be around 90 ms. It takes more time than the previous operation because Hibernate generates multiple SQL queries for my JpaRepository.save(message) method call (which certainly can be optimized), and then YugabyteDB needs to store a copy of the message on all nodes running across the US West, Central, and East. Here is what the output and latency look like: Java Hibernate: select message0_.country_code as country_1_1_0_, message0_.id as id2_1_0_, message0_.attachment as attachme3_1_0_, message0_.channel_id as channel_4_1_0_, message0_.message as message5_1_0_, message0_.sender_country_code as sender_c6_1_0_, message0_.sender_id as sender_i7_1_0_, message0_.sent_at as sent_at8_1_0_ from message message0_ where message0_.country_code=? and message0_.id=? Hibernate: select nextval ('message_id_seq') Hibernate: insert into message (attachment, channel_id, message, sender_country_code, sender_id, sent_at, country_code, id) values (?, ?, ?, ?, ?, ?, ?, ?) 31908 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 461058 nanoseconds spent preparing 3 JDBC statements; 91272173 nanoseconds spent executing 3 JDBC statements; (90 ms) Database Read/Write Latency for the APAC Traffic Remember that with every multi-region database deployment there will be some tradeoffs. With the current database deployment, the read/write latency is low for the US-based traffic but high for the traffic originating from other, more distant locations. Let’s look at an example. Imagine Mr. Red, a colleague of Ms. Blue, receives a push notification about the latest message from Ms. Blue. Since Mr. Red is on a business trip in Taiwan, his traffic will be handled by the instance of the app deployed on that island. However, there is no database node deployed in or near Taiwan, so the microservice instance has to query the database nodes running in the US. This is why it takes 165 ms on average to load the entire channel’s history before Mr. Red sees the message of Ms. Blue: Java Hibernate: select message0_.country_code as country_1_1_, message0_.id as id2_1_, message0_.attachment as attachme3_1_, message0_.channel_id as channel_4_1_, message0_.message as message5_1_, message0_.sender_country_code as sender_c6_1_, message0_.sender_id as sender_i7_1_, message0_.sent_at as sent_at8_1_ from message message0_ where message0_.channel_id=? order by message0_.id asc [p-nio-80-exec-8] i.StatisticalLoggingSessionEventListener : Session Metrics { 153152267 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 153217915 nanoseconds spent preparing 1 JDBC statements; 165798894 nanoseconds spent executing 1 JDBC statements; (165 ms) When Mr. Red responds to Ms. Blue in the same channel, it takes about 450 ms for Hibernate to prepare, send, and store the message in the database. Well, thanks to the laws of physics, the packet(s) with the message has to travel through the Pacific Ocean from Taiwan, and then the message copy has to be stored across US West, Central, and East: Java Hibernate: select message0_.country_code as country_1_1_0_, message0_.id as id2_1_0_, message0_.attachment as attachme3_1_0_, message0_.channel_id as channel_4_1_0_, message0_.message as message5_1_0_, message0_.sender_country_code as sender_c6_1_0_, message0_.sender_id as sender_i7_1_0_, message0_.sent_at as sent_at8_1_0_ from message message0_ where message0_.country_code=? and message0_.id=? select nextval ('message_id_seq') insert into message (attachment, channel_id, message, sender_country_code, sender_id, sent_at, country_code, id) values (?, ?, ?, ?, ?, ?, ?, ?) i.StatisticalLoggingSessionEventListener : Session Metrics 23488 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 137247 nanoseconds spent preparing 3 JDBC statements; 454281135 nanoseconds spent executing 3 JDBC statements (450 ms); Now high latency for APAC-based traffic might not be a big deal for applications that don’t target users from the region, but that’s not so in my case. My geo-messenger has to function smoothly across the globe. Let’s fight this high latency, matey! We’ll start with the reads! Deploying Read Replicas in Distant Locations The most straightforward way to improve the latency for read workloads in YugabyteDB is by deploying read replica nodes in distant locations. This is a purely operational task that can be performed on a live cluster. So, I “attached” a replica node to my live US-based database cluster, and that replica was placed in the Asia East region nearby the microservice instance that serves requests for Mr.Red. I then requested the application instance from Taiwan to use that replica node for database queries. The latency time for the channel’s history preloading for Mr.Red dropped from 165 ms to 10-15 ms! This is as fast as for Ms. Blue, who is based in the USA. Java Hibernate: select message0_.country_code as country_1_1_, message0_.id as id2_1_, message0_.attachment as attachme3_1_, message0_.channel_id as channel_4_1_, message0_.message as message5_1_, message0_.sender_country_code as sender_c6_1_, message0_.sender_id as sender_i7_1_, message0_.sent_at as sent_at8_1_ from message message0_ where message0_.channel_id=? order by message0_.id asc i.StatisticalLoggingSessionEventListener : Session Metrics 1210615 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 1255989 nanoseconds spent preparing 1 JDBC statements; 12772870 nanoseconds spent executing 1 JDBC statements; (12 mseconds) As a result, with read replicas, my geo-messenger can serve read requests at low latency regardless of the user’s location! But it’s too soon for a celebration. The writes are still way too slow. Imagine that Mr.Red sends another message to the channel. The microservice instance from Taiwan will ask the replica node to execute the query. And the replica will forward almost all requests generated by Hibernate to the US-based nodes that store primary copies of records. Thus, the latency still can be as high as 640 ms for the APAC traffic: Java Hibernate: set transaction read write; Hibernate: select message0_.country_code as country_1_1_0_, message0_.id as id2_1_0_, message0_.attachment as attachme3_1_0_, message0_.channel_id as channel_4_1_0_, message0_.message as message5_1_0_, message0_.sender_country_code as sender_c6_1_0_, message0_.sender_id as sender_i7_1_0_, message0_.sent_at as sent_at8_1_0_ from message message0_ where message0_.country_code=? and message0_.id=? Hibernate: select nextval ('message_id_seq') Hibernate: insert into message (attachment, channel_id, message, sender_country_code, sender_id, sent_at, country_code, id) values (?, ?, ?, ?, ?, ?, ?, ?) i.StatisticalLoggingSessionEventListener : Session Metrics 23215 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 141888 nanoseconds spent preparing 4 JDBC statements; 640199316 nanoseconds spent executing 4 JDBC statements; (640 ms) At last, matey, let’s solve this issue once and for all! Switching to Global Geo-Partitioned Cluster A global geo-partitioned cluster can deliver fast reads and writes across distant locations but requires you to introduce a special geo-partitioning column to the database schema. Based on the value of the column, the database will automatically decide what database node in what geography a record belongs to. Database Schema Changes In a nutshell, my tables, such as the Message one, define the country_code column: SQL CREATE TABLE Message( id integer NOT NULL DEFAULT nextval('message_id_seq'), channel_id integer, sender_id integer NOT NULL, message text NOT NULL, attachment boolean NOT NULL DEFAULT FALSE, sent_at TIMESTAMP NOT NULL DEFAULT NOW(), country_code varchar(3) NOT NULL ) PARTITION BY LIST (country_code); Depending on the value of that column, a record can be placed in one of the following database partitions: SQL CREATE TABLE Message_USA PARTITION OF Message (id, channel_id, sender_id, message, sent_at, country_code, sender_country_code, PRIMARY KEY(id, country_code)) FOR VALUES IN ('USA') TABLESPACE us_central1_ts; CREATE TABLE Message_EU PARTITION OF Message (id, channel_id, sender_id, message, sent_at, country_code, sender_country_code, PRIMARY KEY(id, country_code)) FOR VALUES IN ('DEU') TABLESPACE europe_west3_ts; CREATE TABLE Message_APAC PARTITION OF Message (id, channel_id, sender_id, message, sent_at, country_code, sender_country_code, PRIMARY KEY(id, country_code)) FOR VALUES IN ('TWN') TABLESPACE asia_east1_ts; Each partition is mapped to one of the tablespaces: SQL CREATE TABLESPACE us_central1_ts WITH ( replica_placement='{"num_replicas": 1, "placement_blocks": [{"cloud":"gcp","region":"us-central1","zone":"us-central1-b","min_num_replicas":1}]}' ); CREATE TABLESPACE europe_west3_ts WITH ( replica_placement='{"num_replicas": 1, "placement_blocks": [{"cloud":"gcp","region":"europe-west3","zone":"europe-west3-b","min_num_replicas":1}]}' ); CREATE TABLESPACE asia_east1_ts WITH ( replica_placement='{"num_replicas": 1, "placement_blocks": [{"cloud":"gcp","region":"asia-east1","zone":"asia-east1-b","min_num_replicas":1}]}' ); And each tablespace belongs to a group of database nodes from a particular geography. For instance, all the records with the country code of Taiwan (country_code=TWN) will be stored on cluster nodes from the Asia East cloud region because those nodes hold the partition and tablespace for the APAC data. Check out the following article if you want to learn the details of geo-partitioning. Low Read/Write Latency Across Continents So, I deployed a three-node geo-partitioned cluster across the US Central, Europe West, and Asia East regions. Now, let's ensure that the read latency for Mr. Red's requests remains intact. As with the read replica deployment, it still takes 5-15 ms to load the channel's message history (for the channels and messages belonging to the APAC region): Java Hibernate: select message0_.country_code as country_1_1_, message0_.id as id2_1_, message0_.attachment as attachme3_1_, message0_.channel_id as channel_4_1_, message0_.message as message5_1_, message0_.sender_country_code as sender_c6_1_, message0_.sender_id as sender_i7_1_, message0_.sent_at as sent_at8_1_ from message message0_ where message0_.channel_id=? and message0_.country_code=? order by message0_.id asc i.StatisticalLoggingSessionEventListener : Session Metrics 1516450 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 1640860 nanoseconds spent preparing 1 JDBC statements; 7495719 nanoseconds spent executing 1 JDBC statements; (7 ms) And…drumroll please….when Mr. Red posts a message into an APAC channel, the writes latency has dropped from 400-650 ms to 6 ms on average! Java Hibernate: select message0_.country_code as country_1_1_0_, message0_.id as id2_1_0_, message0_.attachment as attachme3_1_0_, message0_.channel_id as channel_4_1_0_, message0_.message as message5_1_0_, message0_.sender_country_code as sender_c6_1_0_, message0_.sender_id as sender_i7_1_0_, message0_.sent_at as sent_at8_1_0_ from message message0_ where message0_.country_code=? and message0_.id=? Hibernate: select nextval ('message_id_seq') Hibernate: insert into message (attachment, channel_id, message, sender_country_code, sender_id, sent_at, country_code, id) values (?, ?, ?, ?, ?, ?, ?, ?) 1123280 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 123249 nanoseconds spent preparing 3 JDBC statements; 6597471 nanoseconds spent executing 3 JDBC statements; (6 ms) Mission accomplished, matey! Now my geo-messenger’s database can serve reads and writes with low latency across countries and continents. I just need to tell my messenger where to deploy microservice instances and database nodes. The Case for Cross-Continent Queries Now for a quick comment on why I skipped the database deployment option with multiple standalone YugabyteDB clusters. It was important to me to have a single database for the messenger so that: Users could join discussion channels belonging to any geography. Microservice instances from any location could access data in any location. For instance, if Mr. Red joins a discussion channel belonging to the US nodes (country_code=’USA') and sends a message there, the microservice instance from Taiwan will send the request to the Taiwan-based database node and that node will forward the request to the US-based counterpart. The latency for this operation is comprised of three SQL requests and will be around 165 ms: Java Hibernate: select message0_.country_code as country_1_1_0_, message0_.id as id2_1_0_, message0_.attachment as attachme3_1_0_, message0_.channel_id as channel_4_1_0_, message0_.message as message5_1_0_, message0_.sender_country_code as sender_c6_1_0_, message0_.sender_id as sender_i7_1_0_, message0_.sent_at as sent_at8_1_0_ from message message0_ where message0_.country_code=? and message0_.id=? Hibernate: select nextval ('message_id_seq') Hibernate: insert into message (attachment, channel_id, message, sender_country_code, sender_id, sent_at, country_code, id) values (?, ?, ?, ?, ?, ?, ?, ?) i.StatisticalLoggingSessionEventListener : Session Metrics 1310550 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 159080 nanoseconds spent preparing 3 JDBC statements; 164660288 nanoseconds spent executing 3 JDBC statements; (164 ms) 165 ms is undoubtedly higher than 6 ms (the latency when Mr. Red posts a message into a local APAC-based channel), but what’s important here is the ability to make cross-continent requests via a single database connection when necessary. Plus, as the execution plan shows, there is a lot of room for optimization at the Hibernate level. Presently, Hibernate translates my JpaRepository.save(message) call into 3 JDBC statements. This is what can be optimized further to bring down the latency for the cross-continent requests from 165 ms to a much lower value. Wrapping Up Alright, matey! As you see, distributed databases can function seamlessly across geographies. You need to pick a deployment option that works best for your application. In my case, the geo-partitioned YugabyteDB cluster fits the most for the messenger's requirements. Well, this article ends the series about my geo-messenger's development journey. The application source code on GitHub is available so that you can explore the logic and run the app in your environment. Next, I'll take a little break and then start preparing for my upcoming SpringOne session that will feature the geo-messenger's version running on Spring Cloud components. So, if it's relevant to you, follow me to get notified about future articles related to Spring Cloud and geo-distributed apps.

You have launched your application with an intuitive, user-friendly UI. But if your application is experiencing a load problem, it will frustrate your end customers while using it. Chances are the issue is not within the application but with the database. According to a survey, 38% of database professionals reported database downtime as the significant issue that kept them up at night. The downtime could be due to any number of issues, including improper database configuration, poor load handling, database queries being timed out, and so on. In this article, we will discuss whether you need to scale your database and how to tackle database scalability issues. Let’s begin with the most obvious question — why database scaling? Why Scale Your Database? Your application's database should be able to expand or contract its compute resources to match the dynamic needs of the application. For example, your database should be able to scale up to handle a sudden surge in traffic. Also, when not in condition, your database should be able to contract to save resources. One of the best ways to ensure good database scalability is to choose the proper database according to your requirements. With physical servers, database expansion and contraction can be a headache. Cloud database solutions can do the trick. Database scalability is a resource-intensive and challenging task. Hence, before starting the project, you need to ensure that your product needs a scalable database. First, determine whether your product will experience a surge in traffic in the foreseeable future. If not, you can still do it with your old database. If your business is a startup, there is no point in investing resources in acquiring a scalable database. You can do so once your application hits critical mass and expect a good surge in traffic. Here are a few scenarios in which database scalability is necessary: Your application is outdated, and you want to migrate to a cloud system Your application experiences high loads Your application needs to comply with the regulations You want a balanced workload that can serve users all over the world Database Scaling Solutions 1. Cache Database Queries One of the most straightforward ways to improve your database’s load-handling capability is to cache database queries. Usually, an application has only a handful of queries that make up the bulk of requests made. You can cache such queries so that, in the future, these requests are read from the cache. This eliminates the need to fetch data from the database every time such common requests are made. The user is served with the required data quickly. This way, caching helps in improving the performance of your database. Amazon ElastiCache is a caching service that helps you in caching your database. With Amazon ElastiCache, you can scale with in-memory caching. Amazon ElastiCache supports real-time use cases. The following are the ideal use cases for Amazon ElastiCache: Gaming leaderboards Analytics Streaming 2. Database Indexes Scaling a database doesn’t always mean adding more databases to the existing setup. Sometimes, by optimizing the current database, you can potentially scale to some extent. This is where database indexing comes into play. Using the database indexing technique, you are structuring the data to improve the speed of data retrieval from the database table. 3. Data Replication Data replication is a strategy used to make identical copies of the database to create additional machines. A replication strategy is beneficial in tackling peak loads. As the data is replicated, queries can be spread across multiple databases, and this, in turn, will reduce the load on a single database. Moreover, in the unlikely event that a storage device fails, the replicated data will come in handy, and the system will still be fully functional. 4. Sharding One of the major bottlenecks in scaling your database is the improperly designed database. To ensure that you do not face this bottleneck, it is essential to start by choosing the correct database for the correct business application. For example: A bank should choose relational DBMS to ensure ACID (atomicity, consistency, isolation, durability) for its structured data An online multiplayer game can rely on key-value no SQL database No matter which database you select, ensure that it has a sharding feature in it. Sharding means splitting a single large chunk of the database into smaller chunks of data, known as shards, that can be stored across multiple databases. There are two types of sharding: Horizontal sharding Vertical sharding Horizontal sharding proves effective when your database queries return a subset of rows of data. These rows are often grouped together. For instance, the queries in which the data is filtered are based on short date ranges. Vertical sharding proves effective when your database queries return a subset of columns of data. For instance, if some database queries request only names while others want only cities, then vertical sharding proves effective in such cases. Shards have two major advantages: The system's overall storage capacity is directly proportional to the number of database shards. If one shard is offline, you can still rely on the shard pool to retrieve and store your data. When a shard goes offline, only a portion of the overall dataset is unavailable at the moment. Therefore, it won’t majorly impact the operation of the system. Conclusion The database is a crucial element of any application. If you want to scale your application, you can’t do it without scaling the database. Fortunately, due to technological advancements in recent years, we have all the tools we need to make the scaling process seamless and effortless. One can leverage cloud service providers like Azure, AWS, or Google Cloud to scale their application. However, one needs to consider certain factors before jumping into scalability. I hope this article has served you well in understanding the fundamental problems associated with scaling and how to tackle them. In case I have missed some crucial points, do let me know in the comments below.

Motivation In my previous article, I demonstrated how JWT tokens can replace passwords for a safer and more secure cloud-native future. Check out my previous articles covering SSO for DB Console using Google OAuth, Microsoft Identity Platform, and Okta. High-Level Steps Provision a CockroachDB cluster Configure Okta Write code Verify Step-By-Step Instructions Provision a CockroachDB Cluster SSO for SQL can be set up for CockroachDB self-hosted and our hosted offerings (learn how to set up a dedicated cluster). I'm using a Docker environment with the latest 22.2 beta image where this capability is available. Configure Okta I am using an Okta developer account. Assuming the steps from my previous article are finished, you may go ahead and follow this tutorial to get JWT AuthN working with your Python application. The application we're going to use as an example is our Python Psycopg3 tutorial. We are going to demonstrate how we can request an id_token for the initial authentication and in the case of a long-running Python application, demonstrate how we can request a refresh_token to swap id_token with a new one. The first order of business is adding the ability to refresh tokens in our Okta console. At this point, we're going to pull down the application code and attempt to run it. Write Code We need to set up a few environment variables to keep sensitive information out of the code base. export OKTAURL=https://dev-number.okta.com/oauth2/v1/token export CLIENT_ID=<okta-client-id> export CLIENT_SECRET=<okta-client-secret> export OKTAUSERNAME=<Okta user> export OKTAPASSWORD=<Okta password> Refer to the previous article for where to find the properties. The very first change I want to tackle is changing the application's dependence on $DATABASE_URL variable. I'm using the psycopg3 practice of setting up a connection. For example: with psycopg.connect("host=lb dbname=defaultdb user=roach password={} port=26257 sslmode=verify-full sslrootcert=/certs/ca.crt options=--crdb:jwt_auth_enabled=true".format(id_token), application_name="$ using_jwt_token_psycopg3", row_factory=namedtuple_row) as conn I am adding the options parameter, options=--crdb:jwt_auth_enabled=true and also passing the JWT token as an argument, i.e. password={} and .format(id_token). The goal here is to codify the following curl command: curl --request POST \ --url $OKTAURL \ --header 'accept: application/json' \ -u "$CLIENT_ID:$CLIENT_SECRET" \ --header 'content-type: application/x-www-form-urlencoded' \ --data "grant_type=password&username=$OKTAUSERNAME&password=$OKTAPASSWORD&scope=openid" I am using the requests module to implement this logic. Since we're going to also refresh our tokens, we have to add a slight change to the command, i.e. offline_access as per the Otka documentation. --data "grant_type=password&username=$OKTAUSERNAME&password=$OKTAPASSWORD&scope=openid offline_access If you compare my code with the original version, you can see I have a call for a token: json_response = get_id_token(url, data, headers, client_id, client_secret) id_token = json_response["id_token"] refresh_token = json_response["refresh_token"] The result of the function is sent back as a JSON object. I extract the id_token and refresh_token for the consecutive call. def get_id_token(url, data, headers, client_id, client_secret): r = requests.post(url, data, headers=headers, auth=(client_id, client_secret)) return json.loads(r.text) If you have a short-lived application, you can stop here and perhaps discard the refresh token but in case your application runs as a service, the next call will include the refresh_token as part of the payload to generate a new id_token. We are trying to codify the following curl example: curl --request POST \ --url $OKTAURL \ --header 'accept: application/json' \ -u "$CLIENT_ID:$CLIENT_SECRET" \ --header 'content-type: application/x-www-form-urlencoded' \ --data "grant_type=refresh_token&scope=openid offline_access&refresh_token=<refresh-token-goes-here>" I concatenate the refresh token in the data field. data = "grant_type=refresh_token&scope=openid offline_access&refresh_token=" + refresh_token json_refresh_response = get_id_token(url, data, headers, client_id, client_secret) new_id_token = json_refresh_response["id_token"] I am going to stop here, but in your long-running application, you will continue this loop by passing each consecutive refresh token to a new request and renewing your tokens. Verify If we were to execute the application as is, the output will be: Initiate authentication with a new id_token: Balances at Thu Oct 27 19:59:38 2022: account id: 12df8892-1800-4f91-afde-2f0742e9757e balance: $250 account id: a6f5618e-5301-466c-ba9b-1bb67cedb39c balance: $1000 Balances at Thu Oct 27 19:59:38 2022: account id: 12df8892-1800-4f91-afde-2f0742e9757e balance: $350 account id: a6f5618e-5301-466c-ba9b-1bb67cedb39c balance: $900 The following output is generated by the refreshed id_token: Initiate authentication with a refreshed id_token: Balances at Thu Oct 27 19:59:38 2022: account id: 11f31444-5271-4ad2-9dcd-3d1245d4faf5 balance: $1000 account id: 3c52d759-8790-45fc-9a3a-f31027b64398 balance: $250 Balances at Thu Oct 27 19:59:38 2022: account id: 11f31444-5271-4ad2-9dcd-3d1245d4faf5 balance: $900 account id: 3c52d759-8790-45fc-9a3a-f31027b64398 balance: $350 Finally, I am passing a fake token to demonstrate that the cluster will reject it. Initiate authentication with a bogus id_token: CRITICAL:root:database connection failed CRITICAL:root:connection failed: ERROR: JWT authentication: invalid token We can also run this workflow manually by accessing Cockroach without a password: cockroach sql --url "postgresql://roach@lb:26257/defaultdb?sslmode=verify-full&sslrootcert=%2Fcerts%2Fca.crt&options=--crdb:jwt_auth_enabled=true" If you enter an invalid token: Connecting to server "lb:26257" as user "roach". Enter password: ERROR: JWT authentication: invalid token SQLSTATE: 28000 Failed running "sql" Hopefully, you were able to follow along. If you have any questions, feel free to leave a comment below.

Read/write splitting is a technique to route reads and writes to multiple database servers, allowing you to perform query-based load balancing. Implementing this at the application level is hard because it couples code or configuration parameters to the underlying database topology. For example, you might have to define different connection pools for each server in the database cluster. MariaDB MaxScale is an advanced database proxy that can be used as a read/write splitter that routes SELECT statements to replica nodes and INSERT/UPDATE/DELETE statements to primary nodes. This happens automatically without having to change your application code or even configuration—with MaxScale, the database looks like a single-node database to your application. In this hands-on tutorial, you’ll learn how to configure MariaDB database replication with one primary and two replica nodes, as well as how to set up MaxScale to hide the complexity of the underlying topology. The best part: you’ll learn all this without leaving your web browser! The Play With Docker Website Play With Docker (PWD) is a website that allows you to create virtual machines with Docker preinstalled and interact with them directly in your browser. Log in and start a new session. The Play With Docker website You will use a total of 5 nodes: node1: Primary server node2: Replica server A node3: Replica server B node4: MaxScale database proxy node5: Test machine (equivalent to a web server, for example) Note: Even though databases on Docker containers are a good fit for the most simple scenarios and for development environments, it might not be the best option for production environments. MariaDB Corporation does not currently offer support for Docker deployments in production environments. For production environments, it is recommended to use MariaDB Enterprise (on the cloud or on-premise) or MariaDB SkySQL (currently available on AWS and GCP). Running the Primary Server Add a new instance using the corresponding button: A Docker-ready instance On node1, run a MariaDB primary server as follows: docker run --name mariadb-primary \ -d \ --net=host \ -e MARIADB_ROOT_PASSWORD=password \ -e MARIADB_DATABASE=demo \ -e MARIADB_USER=user \ -e MARIADB_PASSWORD=password \ -e MARIADB_REPLICATION_MODE=master \ -e MARIADB_REPLICATION_USER=replication_user \ -e MARIADB_REPLICATION_PASSWORD=password \ bitnami/mariadb:latest This configures a container running MariaDB Community Server with a database user for replication (replication_user). Replicas will use this user to connect to the primary. Running the Replica Servers Create two new instances (node2 and node3) and run the following command on both of them: docker run --name mariadb-replica \ -d \ --net=host \ -e MARIADB_MASTER_ROOT_PASSWORD=password \ -e MARIADB_REPLICATION_MODE=slave \ -e MARIADB_REPLICATION_USER=replication_user \ -e MARIADB_REPLICATION_PASSWORD=password \ -e MARIADB_MASTER_HOST=<PRIMARY_IP_ADDRESS> \ bitnami/mariadb:latest Replace <PRIMARY_IP_ADDRESS> with the IP address of node1. You can find the IP address in the instances list. Now you have a cluster formed by one primary node and two replicas. All the writes you perform on the primary node (node1) are automatically replicated to all replica nodes (node1 and node2). Running MaxScale MaxScale is a database proxy that understands SQL. This allows it to route write operations to the master node and read operations to the replicas in a load-balanced fashion. Your application can connect to MaxScale using a single endpoint as if it was a one-node database. Create a new instance (node4) and run MaxScale as follows: docker run --name maxscale \ -d \ --publish 4000:4000 \ mariadb/maxscale:latest You can configure MaxScale through config files, but in this tutorial, we’ll use the command line to make sure you understand each step. In less ephemeral environments you should use config files, especially in orchestrated deployments such as Docker Swarm and Kubernetes. Launch a new shell in node4: docker exec -it maxscale bash You need to create server objects in MaxScale. These are the MariaDB databases to which MaxScale routes reads and writes. Replace <NODE_1_IP_ADDRESS>, <NODE_2_IP_ADDRESS>, and <NODE_3_IP_ADDRESS> with the IP addresses of the corresponding nodes (node1, node2, and node3) and execute the following: maxctrl create server node1 <NODE_1_IP_ADDRESS> maxctrl create server node2 <NODE_2_IP_ADDRESS> maxctrl create server node3 <NODE_3_IP_ADDRESS> Next, you need to create a MaxScale monitor to check the state of the cluster. Run the following command: maxctrl create monitor mdb_monitor mariadbmon \ --monitor-user root --monitor-password 'password' \ --servers node1 node2 node3 Note: Don’t use the root user in production environments! It’s okay in this ephemeral lab environment, but in other cases create a new database user for MaxScale and give it the appropriate grants. Now that MaxScale is monitoring the servers and making this information available to other modules, you can create a MaxScale service. In this case, the service uses a MaxScale router to make reads and writes go to the correct type of server in the cluster (primary or replica). Run the following to create a new service: maxctrl create service query_router_service readwritesplit \ user=root \ password=password \ --servers node1 node2 node3 Finally, you need to create a MaxScale listener. This kind of object defines a port that MaxScale uses to receive requests. You have to associate the listener with the router. Run the following to create a new listener: maxctrl create listener \ query_router_service query_router_listener 4000 \ --protocol=MariaDBClient Notice how the listener is configured to use port 4000. This is the same port you published when you run the Docker container. Check that the servers are up and running: maxctrl list servers You should see something like the following: A cluster of 3 MariaDB nodes configured in MaxScale for read/write splitting Testing the Setup To test the cluster, create a new instance (node5) and start an Ubuntu container: docker run --name ubuntu -itd ubuntu This container is equivalent to, for example, a machine that hosts a web application that connects to the database. Run a new Bash session in the machine: docker exec -it ubuntu bash Update the package catalog: apt update Install the MariaDB SQL client so you can run SQL code: apt install mariadb-client -y Connect to the database, or more precisely, to the MaxScale database proxy: mariadb -h 192.168.0.15 --port 4000 -u user -p As you can see, it’s as if MaxScale was a single database. Create the following table: MariaDB SQL CREATE TABLE demo.message(content TEXT); We want to insert rows that contain the unique server ID of the MariaDB instance that actually performs the insert operation. Here’s how: MariaDB SQL INSERT INTO demo.message VALUES \ (CONCAT("Write from server ", @@server_id)), \ (CONCAT("Write from server ", @@server_id)), \ (CONCAT("Write from server ", @@server_id)); Now let’s see which MariaDB server performed the write and read operations: MariaDB SQL SELECT *, CONCAT("Read from server ", @@server_id) FROM demo.message; Run the previous query several times. You should get a result like this: Testing read/write splitting In my cluster, all the writes were performed by server ID 367 which is the primary node. Reads were executed by server IDs 908 and 308 which are the replica nodes. You can confirm the ID values by running the following on the primary and replica nodes: docker exec -it mariadb-primary mariadb -u root -p \ --execute="SELECT @@server_id" docker exec -it mariadb-replica mariadb -u root -p \ --execute="SELECT @@server_id" What’s Next? We focused on basic read/write splitting in this tutorial, but MaxScale can do much more than this. For example, enforce security to your backend database topology, perform automated failover, perform connection-based load balancing, import and export data from and into Kafka, and even convert NoSQL/MongoDB API commands to SQL. MaxScale also includes a REST API and web-based GUI for operations. Check the documentation to learn more about MaxScale.

What does it take to build an efficient and sound data model for Apache Cassandra and DataStax Astra DB? Where would one start? Are there any data modeling rules to follow? Can it be done consistently time and time again? The answers to these and many other questions can be found in the Cassandra data modeling methodology. In this post, we present a high-level overview of the data modeling methodology for Cassandra and Astra DB, and share over half a dozen complete data modeling examples from various real-life domains. We apply the methodology to create Cassandra and Astra DB data models for IoT, messaging data, digital library, investment portfolio, time series, shopping cart, and order management. We even provide our datasets and queries for you to try. As a side note, if you are new to Cassandra or if the terms single-row partitions and multi-row partitions sound unfamiliar, we recommend taking a closer look at Cassandra Fundamentals before deep diving into data modeling. Data Modeling and the Methodology Data modeling is a process that involves many activities: Collecting and analyzing data requirements Understanding domain entities and relationships Identifying data access patterns Organizing and structuring data in a particular way Designing and specifying a database schema Optimizing schema and data indexing techniques Data modeling can have a profound effect on data quality and data access. For data quality, think about data completeness, consistency, and accuracy. With respect to data access, think about queryability, efficiency, and scalability. An efficient and sound data model is crucial for both data and applications. Our methodology defines how the data modeling process can be carried out in a well-organized and repeatable fashion. In particular, the Cassandra data modeling methodology is based on four objectives, four models, and two transitions; along with specific modeling, visualization, mapping, and optimization techniques and methods. Figure 1: Cassandra data modeling methodology. Four Objectives The Cassandra data modeling process, when discussed at a high level, can be distilled into these four key objectives: Understand the data: Whether starting from scratch or dealing with an existing dataset, do you understand data that needs to be managed? Things like entities, relationships, and key constraints come to mind. Identify data access patterns: Do you have a good idea of what a data-driven application should be able to do? Think of tasks (or microservices) and their required data access patterns, execution sequences and workflows, and how data retrieved in one task is used by the next one. Apply the query-first approach: Do you know how to design Cassandra tables to support specific queries? It is called a query-first or query-driven approach because designing table schemas depends on both data and queries. Optimize and implement: How do you verify that both database tables and application queries are efficient and scalable? For example, large partitions and queries that access many partitions may require additional optimizations. Four Models The four models directly correspond to the four objectives and are meant to make the process more concrete, manageable, repeatable, documentable, collaborative, and shareable. They are: Conceptual data model: A technology-independent, high-level view of data. Its purpose is to understand the data in a particular domain. While there are a number of conceptual data modeling techniques, we use the Entity-Relationship Model and Entity-Relationship Diagrams in Chen’s Notation to document entity types, relationship types, attribute types, and cardinality and key constraints. Application workflow model: A technology-independent, high-level view of a data-driven application, consisting of application tasks, execution dependencies, and access patterns. Its purpose is to identify data access patterns and how they may be executed in sequences. These include queries, inserts, updates, and deletes required by different data-driven tasks. We use simple graph-like diagrams to represent application workflows. Logical data model: A Cassandra-specific data model featuring tables, materialized views, secondary indexes, user-defined types, and other database schema constructs. It is derived from a conceptual data model by organizing data into Cassandra-specific data structures based on data access patterns identified by an application workflow. This is where the query-first approach is applied. Logical data models can be conveniently captured and visualized using Chebotko Diagrams that can feature tables, materialized views, indexes, and so forth. Physical data model: A Cassandra-specific data model that is directly derived from a logical data model by analyzing and optimizing for performance. Physical data models can be conveniently captured and visualized using Chebotko Diagrams and implemented in Cassandra using CQL. Two Transitions To complete the picture, the methodology must define the transitions between the models: Mapping a conceptual data model and an application workflow model to a logical data model Optimizing a logical data model to produce a physical data model In many aspects, the transitions are the most interesting and profound components of the methodology. To carry out the first transition, the methodology defines mapping rules and mapping patterns. For the second transition, some common optimization techniques include splitting and merging partitions, data indexing, data aggregation, and concurrent data access optimizations. You can find more information about the Cassandra data modeling methodology in the original paper, conference presentation, or DataStax Academy video course DS220. Data Modeling in Action One of the best ways to become skilled in data modeling is to explore concrete examples. We maintain this growing collection of data modeling examples from various domains to help you get started with Cassandra and Astra DB data modeling. Each example applies the Cassandra data modeling methodology to produce and visualize four important artifacts: conceptual data model, application workflow model, logical data model, and physical data model. Moreover, each example has a hands-on portion with practice questions and solutions. The hands-on scenarios make it straightforward to implement a data model in Cassandra, express data access patterns as CQL queries and run the queries against our sample datasets. Figure 2: Example hands-on scenario with schema, data, and queries. Go ahead and explore these data models, and execute real queries against them in your browser: Sensor data model: Modeling sensor networks, sensors, and temperature measurements. The resulting database schema has four tables supporting four data access patterns. Messaging data model: Modeling users, email folders, emails, and email attachments. The resulting database schema has five tables supporting four data access patterns. Digital library data model: Modeling performers, albums, album tracks, and users. The resulting database schema has eight tables supporting nine data access patterns. Investment portfolio data model: Modeling users, investment accounts, trades, and trading instruments. The resulting database schema has six tables supporting seven data access patterns. Time series data model: Modeling IoT data sources, groups of related sources, metrics, data points, and time series with higher or lower resolution. The resulting database schema has seven tables supporting seven data access patterns. Shopping cart data model: Modeling users, items, and shopping carts. The resulting database schema has three tables and one materialized view supporting seven data access patterns, including updates that use batches and lightweight transactions. Order management data model: Modeling users, payment methods, addresses, items, shopping carts, orders, delivery options, and order statuses. The resulting database schema has four tables supporting five data access patterns, including multi-step updates that use lightweight transactions. Data Modeling and Astra DB Astra DB is a cloud database service built on Apache Cassandra. It is a serverless and multi-region service that works in AWS, Azure and GCP. If you haven’t already, you should take advantage of Astra DB’s free tier to create your own fully managed Cassandra database in the cloud. Astra DB databases are Cassandra databases. The same data modeling methodology applies and the above example data models can be instantiated in Astra DB. However, there are a couple of minor differences that you may want to be aware of: Astra DB does not support materialized views. Materialized views are experimental in Cassandra and the use of regular tables is usually recommended instead. Astra DB does not support user-defined functions. Strictly speaking, user-defined functions are not data modeling constructs. They usually can be readily replaced with computation outside of a database. Astra DB supports Storage-Attached Indexing or SAI. Storage-attached indexes in Astra DB are secondary indexes with better performance, space efficiency, and more capabilities than regular secondary indexes or experimental SASI in Cassandra. With that said, it is important to understand that SAI and other secondary indexes still have the same use cases and limitations, and should be used with caution. The Astra DB and Cassandra differences with respect to materialized views, user-defined functions, and secondary indexes should not have any profound effect on data modeling. Data Modeling and K8ssandra K8ssandra is a cloud-native distribution of Cassandra that runs on Kubernetes. Besides Cassandra, the distribution also includes several integrated components that enable richer data APIs, and provide better automation for observability, metrics monitoring, backup and restore, and data anti-entropy services. K8ssandra is open-source, free to use, and data modeling in K8ssandra is identical to data modeling in Cassandra. Data modeling and Stargate Stargate is an open-source data gateway deployed between applications and a database. It supports different API options for an application to interact with Cassandra, Astra DB, and K8ssandra. Stargate’s API extensions include CQL, REST, GraphQL, and Document APIs. The use of CQL, REST, and GraphQL APIs has no effect on data modeling: the same data modeling methodology applies. The use of Document API has a significant impact on data modeling. With Document API, the focus shifts from organizing data as rows, columns, and partitions to structuring data as JSON documents. Stargate then uses the predefined mapping to shred JSON documents and store them as rows in Cassandra tables. The topic of data modeling for document databases is beyond the scope of this article. Conclusion Data modeling in Cassandra and Astra DB is a very important topic and we just scratched the surface in this post. We presented a high-level overview of the Cassandra data modeling methodology and urged you to sharpen your skills by exploring the data modeling examples. We also established that data modeling in Cassandra, Astra DB, and K8ssandra are practically identical; with Astra DB having a significant advantage of being serverless and fully managed. Finally, we briefly discussed how Stargate APIs — namely CQL, REST, GraphQL, and Document APIs — can affect data modeling.

The use of graph databases has grown massively in recent years, and they are becoming promising solutions for organizations in any industry. Their increased flexibility makes it easier to leverage relationships and connections in a way that traditional relational databases can't do. But how do you know when to use a graph database? In this article, we explore what to consider if you’re thinking of using a graph database and show how the best approach may be to not use one at all. What Is a Graph Database? A graph database is a type of database that uses graph theory as the foundation for its data model. Graph databases consider connectedness as a first-class citizen, making them better suited to represent connected data than more old-school relational databases. There are two types of graph technologies: RDF/Semantic Web and property graphs. While Semantic Web standards have existed for much longer, property graphs have become the dominant technology, with Cypher the most adopted graph query language. In essence, the data model in a property graph database consists of the following attributes: Nodes, edges, properties, and labels: Nodes can have properties. Nodes can have tags with one or more labels. Edges are directed and connect nodes to create structure in the graph. Edges can have properties. Graph databases tend to be used for applications that consume interconnected data. A few common use cases include: Recommendation engines Semantic search Anti-money laundering Fraud detection 360 customer views Beyond these applications, graph databases have become promising solutions for organizations that not only have to manage large volumes of data but need to generate business-critical insights. Graph databases promise to be the easiest way to gain such insights by making it easy to understand the relationships and context in our data. Challenges With Graph Databases But despite these lofty promises, graph databases are not conquering the world. According to DB-Engines, as of October 2022, they comprised just 1.8% of the database space, a far cry from fulfilling those promises. There are several reasons that explain the difficulties in the adoption of graph databases. They can be summarized as follows: Modeling Highly Interconnected Data Due to a graph database’s high level of expressivity and the associated complexity, modeling data in a graph is not easy. It is akin to modeling knowledge, also known as knowledge engineering—an advanced skill that requires highly specialized engineers. This makes it difficult for graphs to be adopted by the general developer audience, setting a high barrier to entry. Maintaining Consistency of Data Another big issue with graph databases is the lack of an enforced schema. Graph databases mostly delegate schema verification to the application layer, whether implicitly or explicitly. This makes it difficult to build applications with complex data where data consistency in the database is crucial. The lack of an explicitly enforced schema in graph databases can become a major hurdle for widespread adoption. Querying for Data Interrogating the graph also comes with its own difficulties. Given that an (implicit) data model governs the paths that can be expressed, you have to somehow design your queries in a way that matches that implicit data model. What makes this particularly challenging is that you may not have modeled your data in the most optimal way. Moreover, most graph databases lack important modeling constructs such as nested or n-ary relations, which leads to inconsistencies in modeling decisions. Sometimes you may have defined a relationship as a node, other times as an edge. Your queries may then not always be touching the right data. A Strongly-Typed Database Overcoming these kinds of challenges is crucial to help fulfill the promises of graphs. That’s why we've developed a new type of database: the strongly-typed database, TypeDB. We built TypeDB (available open-source) to abstract away the low-level implementations of graph databases by offering a higher-level type system that makes it much easier for developers to work with complex data. TypeDB’s type system is based on the following core concepts. Entity-Relationship Model TypeDB makes it possible to model data using the well-known Entity-relationship model. Unlike in a graph database, this means we can map any ER diagram directly to how we implement it in TypeQL (TypeDB’s query language), avoiding the need to go through a normalization process. This means that the way we think of a model conceptually as humans is also how we implement it in code in TypeDB. TypeDB’s model is composed of entity types, relation types, and attribute types, with the introduction of role types. The example below shows how a basic model in TypeQL is written: define person sub entity, owns name, plays employment:employee; company sub entity, owns name, plays employment:employer; employment sub relation, relates employee, relates employer; name sub attribute, value string; In this nomenclature, squares denote entities; diamonds denote relations; ovals denote attributes. This figure outlines a model with two entities — a person and a company — and both entities own a name attribute. The person plays the role of the employee in the employment relation, while the company plays the role of employer. Type Hierarchies TypeDB offers the ability to model type hierarchies out of the box, which is a feature graph databases don’t natively support. Following the principles of an object-oriented type system, TypeDB ensures that all types inherit the behaviors and properties of their super-types. This makes complex data structures reusable and data interpretation richer through polymorphism. In the example below, a three-level entity person hierarchy is modeled. All of its subtypes will inherit the attributes' first-name and last-name without having to re-declare these one by one: define person sub entity, owns first-name, owns last-name; student sub person; undergrad sub student; postgrad sub student; teacher sub person; supervisor sub teacher; professor sub teacher; This type hierarchy describes an entity of type person that is sub-typed by the student and teacher types. There are two types of students, undergrads, and postgrads, and there are two types of teachers, supervisors, and professors. Nested Relations Relations are concepts that describe the association between two or more things. Sometimes, those things are relations themselves, which means modeling a relation that directly refers to another relation—nested relations. Graph databases don’t allow modeling nested relations, which would require having a binary edge point to a binary edge. The only possible way to achieve this would be through reification, transforming an edge into a node so that another edge can point to it. TypeDB, however, does allow nested relations, making it possible to model data in its most natural form. In the example below, the relation of type marriage is assigned to the variable $mar, which is then used to connect it to a city through the relation located: match $alice isa person, has name "Alice"; $bob isa person, has name "Bob"; $mar ($alice, $bob) isa marriage; $city isa city; ($mar, $city) isa location; In this figure, the person “Alice” plays the role of wife, and the person “Bob” plays the role of husband in a marriage relation. Marriage is a nested relation, as it also plays the role of located in a location relation, where the city “London” plays the role of locating in that same relation. N-ary Relations In the real world, relations aren’t just binary connections between two things. That’s why it’s often necessary to capture three or more things related to each other at once. Representing them as separate binary relationships would lead to a loss of information, which is what happens in graph databases. TypeDB, on the other hand, can naturally represent an arbitrary number of things as one relation, for example, without needing to reify the model. In this example, the n-ary relation cast connects three different entities: a person entity, a character entity, and a movie entity: match $person isa person, has name "Leonardo"; $character isa character, has name "Jack"; $movie isa movie, has name $movie-name; (actor: $person, character: $character, movie: $movie) isa cast; get $movie-name; This is an example of an n-ary relation, specifically a ternary relation, of type cast. This relation relates to three entities: the movie “Titanic” plays the role of movie, the person “Leonardo” plays the role of actor, and the character “Jack” plays the role of character in this cast relation. Safety Unlike graph databases, TypeDB provides a way to describe the logical structures of your data, allowing TypeDB to validate that your code inserts and queries data correctly. Query validation goes beyond static type-checking and includes logical validation of meaningless queries. With strict type-checking errors, you have a dataset that you can trust. In the example insert query below, the relation between Charlie and DataCo would be rejected, as a person cannot marry a company (assuming the schema follows the real world). insert $charlie isa person, has name "Charlie"; $dataCo isa company, has name "DataCo"; (husband: $charlie, wife: $dataCo) isa marriage; # invalid relation commit>> ERROR: invalid data detected during type validation Inference Finally, TypeDB offers a built-in inference engine, which enables TypeDB to derive new insights, and provides full explainability of those insights. Property graphs, on the other hand, don’t offer native inference capabilities. Inferences in TypeDB are based on rules that are defined in your schema. During query runtime, if a certain logical form in your dataset is satisfied (as defined in a rule), the system will derive new conclusions. Like functions in programming, rules can chain onto one another, creating abstractions of behavior at the data level. With the rule below, TypeDB would be able to infer that a city is located in a continent, even though no explicit relationship exists between the two. define rule transitive-location: when { (located: $city, locating: $country) isa location; (located: $country, locating: $continent) isa location; } then { (located: $city, locating: $continent) isa location; }; Using automated reasoning, TypeDB can infer a relationship (dotted line) between the borough “Camden” and the country “UK,” even though they’re not directly connected. Conclusion When do we use a graph database, then? Graphs were meant for applications that rely on complex and interconnected data. However, as we’ve seen, their lack of widespread adoption highlights the key failures and challenges of existing graph database solutions. To overcome those challenges and fulfill the original promise of graph databases, we built TypeDB. You can find TypeDB on Github here.

In this article, we will continue to explain how different components in Milvus interact with each other to complete real-time data queries. Some useful resources before getting started are listed below. We recommend reading them first to better understand the topic in this post. Deep dive into the Milvus architecture Milvus data model The role and function of each Milvus component Data processing in Milvus Data insertion and data persistence in Milvus Load Data to Query Node Before a query is executed, the data has to be loaded to the query nodes first. There are two types of data that are loaded to the query node: streaming data from the log broker, and historical data from object storage (also called persistent storage below). A flowchart of loading data to query node The data coord is in charge of handling streaming data that are continuously inserted into Milvus. When a Milvus user calls collection.load()to load a collection, the query coord will inquire the data coord to learn which segments have persisted in storage and their corresponding checkpoints. A checkpoint is a mark to signify that persisted segments before the checkpoints are consumed while those after the checkpoint are not. Then, the query coord outputs an allocation strategy based on the information from the data coord: either by segment or by channel. The segment allocator is responsible for allocating segments in persistent storage (batch data) to different query nodes. For instance, in the image above, the segment allocator allocates segments 1 and 3 (S1, S3) to query node 1, and segments 2 and 4 (S2, S4) to query node 2. The channel allocator assigns different query nodes to watch multiple data manipulation channels (DMChannels) in the log broker. For instance, in the image above, the channel allocator assigns query node 1 to watch channel 1 (Ch1), and query node 2 to watch channel 2 (Ch2). With the allocation strategy, each query node loads segment data and watches the channels accordingly. In query node 1 in the image, historical data (batch data), are loaded via the allocated S1 and S3 from persistent storage. Meanwhile, query node 1 loads incremental data (streaming data) by subscribing to channel 1 in the log broker. Data Management in Query Node A query node needs to manage both historical and incremental data. Historical data are stored in sealed segments while incremental data are stored in growing segments. Historical Data Management There are mainly two considerations for historical data management: load balance and query node failover. Load balance. For instance, as shown in the illustration, query node 4 has been allocated more sealed segments than the rest of the query nodes. Very likely, this will make query node 4 the bottleneck that slows down the whole query process. To solve this issue, the system needs to allocate several segments in query node 4 to other query nodes. This is called load balance. Incremental Data Management The query node watches DMChannels to receive incremental data. Flowgraph is introduced in this process. It first filters all the data insertion messages. This is to ensure that only data in a specified partition is loaded. Each collection in Milvus has a corresponding channel, which is shared by all partitions in that collection. Therefore, a flowgraph is needed for filtering inserted data if a Milvus user only needs to load data in a certain partition. Otherwise, data in all partitions in the collection will be loaded to the query node. After being filtered, the incremental data are inserted into growing segments and further passed on to server time nodes. During data insertion, each insertion message is assigned a timestamp. The server time node provides an updated tsafe value every time it receives a timetick from the insert node. tsafe means safety time, and all data inserted before this point of time can be queried. Take an example, if tsafe = 9, inserted data with timestamps smaller than 9 can all be queried. Real-Time Query in Milvus Real-time querying in Milvus is enabled by query messages. Query messages are inserted into the log broker by proxy. Then query nodes obtain query messages by watching the query channel in the log broker. Query Message A query message includes the following crucial information about a query: msgID: Message ID, the ID of the query message assigned by the system. collectionID: The ID of the collection to query (if specified by the user). execPlan: The execution plan is mainly used for attribute filtering in a query. service_ts: Service timestamp will be updated together with tsafe mentioned above. The service timestamp signifies at which point the service is in. All data inserted before service_ts are available for query. travel_ts: Travel timestamp specifies a range of time in the past. And the query will be conducted on data existing in the time period specified by travel_ts. guarantee_ts: Guarantee timestamp specifies a period of time after which the query needs to be conducted. The query will only be conducted when service_ts > guarantee_ts. Real-Time Query When a query message is received, Milvus first judges if the current service time, service_ts, is larger than the guarantee timestamp, guarantee_ts, in the query message. If yes, the query will be executed. The query will be conducted in parallel on both historical data and incremental data. Since there can be an overlap of data between streaming data and batch data, an action called "local reduce" is needed to filter out the redundant query results. However, if the current service time is smaller than the guarantee timestamp in a newly inserted query message, the query message will become an unsolved message and wait to be processed till the service time becomes bigger than the guarantee timestamp. Query results are ultimately pushed to the result channel. The proxy obtains the query results from that channel. Likewise, the proxy will conduct a "global reduce" as well because it receives results from multiple query nodes and query results might be repetitive. To ensure that the proxy has received all query results before returning them to the SDK, a result message will also keep a record of information including searched sealed segments, searched DMChannels, and global sealed segments (all segments on all query nodes). The system can conclude that the proxy has received all query results only if both of the following conditions are met: The union of all searched sealed segments recorded in all result messages is larger than global sealed segments, All DMChannels in the collection are queried. Ultimately, the proxy returns the final results after “global reduce” to the Milvus SDK.

This is an article from DZone's 2022 Database Systems Trend Report.For more: Read the Report Data plays a vital role in conceptualizing the preliminary design for an architecture. You may want to decide the requirements for security, performance, and infrastructure to handle workload, scalability, and agility in design. In this case, you need to understand data models and how to handle architectural decisions, including data privacy and security, compliance requirements, data size to handle, and user handling requirements. Figure 1: Data architecture as the foundational pillar for enterprise architecture This is the reason that data-driven architecture is the driving factor for an enterprise design development. The modern enterprise architectures that are referred to in this article include microservices, cloud-native applications, event-driven solutions, and data-intensive solutions. The article intends to share modern enterprise data architecture perspectives, including solution approaches and architectural models to develop new-age solutions catering to velocity, veracity, volume, and variety of data handling services. Polyglot Persistence and Database as a Service A recent data architecture trend based on various case studies recommends the move toward polyglot persistence, which is a group of multiple types of data storage technologies for integrated architecture. Integrated architecture is needed to provide high performance for any type of data processing across different services. Typically used in cloud adoption, this kind of implementation is supported by Database as a Service (DBaaS). DBaaS is implemented for the following benefits: Cloud-based database management system (e.g., Amazon Aurora, Azure Cosmos DB, Google Spanner) High scalability Rapid provisioning Enhanced security in cloud architecture Suitable for large enterprise design Shared infrastructure Availability of monitoring and performance tuning tools This type of polyglot-persistence-based microservices architecture helps to develop resilient, robust, and high-performing architecture to support variant types of data services for different microservices (see Figure 2). Figure 2: Example architecture using polyglot persistence In the example above, each microservice handles data at different capacities and performance requirements. Hence, based on these data access requirements, the choice of database can be picked to cater to performance, scalability, and the optimal data model to be stored in the system. This leverages microservices to make cost-effective and agile architectures in a polyglot persistence model. Data Modeling in Modern Data Architecture In traditional architecture development, data modeling is the simple task of deriving data elements from requirements, depicting the relation between the entities through entity relationship (ER) diagrams, and defining the parameters (data types, constraints, validations) around the data elements. This means that data modeling is done as a single-step activity in a traditional architecture by defining the data definition language (DDL) scripts from requirements. Figure 3: Modern enterprise data modeling stages In modern enterprise data architecture, this is split into a multi-stage activity as conceptual, logical, and physical data modeling, as illustrated below in Figure 3. In a data-driven architecture where data intensity is high, data modeling is a fundamental and crucial step (and, of course, time-consuming). In such an architecture development, data modeling is innovatively divided into three different types: Conceptual data model (CDM) – Derived from business requirements to define "what" is handled in the data flow. Usually, CDM is defined by business stakeholders (e.g., consultants, business owners, application analysts) and data architects. Logical data model (LDM) – Derived from the CDM to drill down the logical relation between the entities and detail the data types of the entities. Deals with "how" data is handled in the data flow and is defined by business consultants and data architects/engineers. Physical data model (PDM) – Defines the actual blueprint based on th LDM, which gets translated to data scripts for execution in the live environment. This is the crucial stage where the performance of data structure, the transaction handling mechanism, and the tuning and optimization of the data model are being carried out and typically handled by database administrators or data engineers. Data Intelligence Data intelligence and data analytics are modern techniques used in modern enterprise data architectures for NoSQL databases to handle big data as well as data-intensive application architectures. It involves one or more of the popular technology solutions, like cloud platforms for agility and scalability of solutions, AI/ML for advanced algorithms to build intelligence in data processing, and big data platforms to handle the storage and analysis of the data. Data intelligence handles data for visualization and analytics by using predictive intelligence to focus the data so that it's visualized for the future (forecasting). How a stock for an enterprise has trended so far is an example of data intelligence, and data analytics is using history to predict how it will change in the next year. Both use AI/ML and deep learning techniques, and both read data from various sources, including data feeds, images, video streaming, and audio extracts to interpret and prepare the results of data processing. The use of data intelligence helps to visually interpret data to different stakeholders including business and technical personas. When you develop a data intelligence solution, you need to have a self-management facility in the database system so that the database is self-sufficient and able to automatically handle crisis situations. This is known as an autonomous database and is part of the future of new-age data persistence systems. Autonomous Database A database acts as the brain for an IT application because it serves as the central store for data being transacted and referenced in the application. Database administrators (DBAs) handle database tuning, security activities, backup, DR activities, server/platform updates, health checks, and all other management and monitoring activities of databases. When you use a cloud platform for application and database development, the aforementioned activities are critical for better security, performance, and cost efficiency. The important aspect here is to operationalize these by reducing effort and making them more proactive in nature. Oracle has coined this an "autonomous database," and it automates many of the DBA's activities in managing the database platform and reducing human interventions. Data Mesh Traditionally, data used to be monolithic in nature by having all domains in a single data store, and effective data portioning and data solutioning would be done through data warehouse and data lake solutions. Data lakes used to be more efficient in data management and modern data analytics, which cater to agile data architectures, but the way data is accessed is missing a federated or autonomous approach in a data lake. Figure 4: Data mesh architecture As shown in Figure 4, unified data solutions are addressed by modern enterprise data architectures with a data mesh, which is a microservices pattern of a data store. A data mesh replicates a service mesh in terms of features. Where a service mesh creates a proxy to interface between services, a data mesh creates a proxy for data abstraction and interfacing for consuming applications like data analytics, dashboards, and data querying applications. Data mesh architectures help to develop multi-dimensional data solutions to handle an operational data plane and an analytical data plane together in a unified architecture without the need for developing two distinct data solutions. Heterogeneous Data Management Using Lakehouse Architecture For data analytics and intelligent data management, we prefer to use a data lake solution or a data warehouse solution, but these solutions both have their own way of organizing and managing data. A data warehouse handles relational data (raw data feed) and processed data (after data ingestion), which is organized in a schema structure before it gets stored in a data storage service (data enrichment). Therefore, data analytics work on cleansed data. A data warehouse is a costly form of storage, but it's faster at query processing since it handles schema-based structured data and is suitable for data intelligence, batch processing, and data visualization in real time. A data lakehouse architecture is a hybrid approach that handles heterogeneous data management using the following: Data lake Data warehouse Purpose-built store for intermediate data handling Data governance mechanism for better data handling policies Data integrity services A data lakehouse architecture overcomes the shortcomings of both data lake and data warehouse solutions and, hence, is increasingly popular in modern enterprise data solutions like lead generation and market analysis using data feeds from various sources. A lakehouse architecture can handle inside-out data movement, from data stored in a data lake to a set of extracted data to a purpose-built store for analytics or querying activities. Outside-in data movement, from data warehouse to a data lake, can help run analytics on complete a dataset. With a lakehouse architecture, we can also handle data for both massively parallel processing (MPP), as in data warehouse applications, high-velocity data querying, and data lake applications. Conclusion Modern enterprise data practices elevate the approach toward building a resilient, agile, and scalable architecture. These approaches will address performance efficiency, operational excellence, high availability, and security/compliance requirements for building integrated data solutions by adopting technology and frameworks such as a polyglot persistence, modern data modeling stages, data lakes, and data meshes. This is an article from DZone's 2022 Database Systems Trend Report.For more: Read the Report

There are multiple ways to fetch the data stored in the database management system. We have classified the architecture of DBMS based on their structure. In this article, I will discuss their structure, advantages, features, etc.; multiple architectures are used for various purposes. Overview of Architecture in DBMS Understanding 2-tier and 3-tier architecture is quite an important topic, not just for academics or for seeking a good job but also for general awareness related to technology. We will see this difference with the help of real-life examples. 2-Tier Architecture in DBMS 2-tier simple means two layers; here, tier means simply layer. There are 2 layers one is the client layer, The one where the data is stored, i.e., the database server. And another is the Client layer which is basically a machine, That runs the query on the database server and gets the desired data. The client machine has an interface that shows the desired data to the user. The data from the database server can be fetched using an API in the client machine. Also, API stands for an Application programming interface. The purpose of API is to get the data from the database server on the interval of pre-defined time and utilize it for various purposes. For example, there are various applications that show the live location of the train, or while using cab services like Ola and Uber, they use google maps API, for which they definitely pay some amount to google. They track the delivery guy or the cab using that API and show it to you. Here you can also observe that the time interval between fetching data using API is very less as we want to get the exact location of our cab. Still, while locating a train, we do not need this high precision so that we can reduce the frequency of data fetching using API. An API first makes a connection and then writes a query on the interface; a simple query means, I want to fetch a particular data, then the query will go to the database server, and this server will process that query, which means we wrote an application program, an application program means I want to fetch a particular data, and that program can be in any language like simple example if I am writing in Java, then that application program will come to the database server, there it will first get converted to the low-level language because the application program can be in any other language, in high language, we will convert it here, means the server will convert it, process it, and whatever data is demanded in that query, that data Will be given back to the client. This is how the 2-tier architecture actually works. 2-tier is also called client-server architecture. We have seen the use of the 2-tier architecture in our daily life, like when we book a ticket on Railways, not through the web and other applications. But if I go to the station and fill out the form and reserve the ticket, then the people who are sitting on that side of the window, having a client machine, in that client machine, they will fill the information. They will call the details of that train from the database server about how many seats are available; according to that, whatever the process is, they will process it and make a ticket. The advantage of this architecture is that it is very simple as there are only 2 layers; maintenance is very easy because there are limited and authorized bank employees, and there is a limited database. 3-Tier Architecture in DBMS But today, every bank and other organization wants to grow and have more users, manage them with fewer resources, and provide the service 24 X 7. They allow the user to perform all the queries using the web or mobile application. It is quite complex for the database server to process the queries of all the users from high-level language to low-level language and further executing all the queries after authorizing them with this security is another main issue as the user has direct access to the database. To solve all such problems, we use Tier- 3 architecture. In Tier- 3 architecture, another layer is introduced between the client and server. 3 Tier Architecture means 3 layers arrived. First is the application layer, which we call directly is client layer; the second is the business layer, and the third is the database or data layer. First of all, the client layer is the same, which means here, all are my users who are normal users like we are also normal users; all of us, by opening the railway's website or the application, our machine, is called a client machine. Now there is an interface running that helps us to make connectivity with the database; An interface is an application that can be in Java, Python, PHP, etc. To support that language or application, there is the business layer, which means that the query from the client machine is processed at the business layer. This reduces the load on the data server. The request made by us, i.e., the client machine, first goes to the application layer. That application layer verifies it and processes it from the high-level language to the low-level language and then passes that simplified query to the database server. Then the server just gives back the data to the application layer, and it returns the data to the client machine after converting it into the high-level language from the low-level language. The application layer also avoids the direct interaction of the client machine with the database server, which increases the security of the database server. After introducing the third layer, i.e., the application layer, the architecture in DBMS has become a little bit complex but efficient and safe. It is a bit harder for us to do the maintenance of the tier 3 architecture. Still, when we are providing service to this user base, we expect to have good resources for maintenance since it is a bit more costly than tier 2 architecture, but still, it is worth the resources.

The O’Reilly book, Cassandra: The Definitive Guide, features a quote from Ray Kurzweil, the noted inventor and futurist: “An invention has to make sense in the world in which it is finished, not the world in which it is started.” This quote has a prophetic ring to it, especially considering my co-author Eben Hewitt included it in the 2010 first edition of this book we wrote, back when Apache Cassandra, the open-source, distributed, and highly scalable NoSQL database, was just on its 0.7 release. In those days, other NoSQL databases were appearing on the scene as part of platforms on a worldwide scale from vendors like Amazon, YouTube, and Facebook. With many competing database projects and a slowly emerging response from relational database vendors, the future of this emerging landscape wasn’t yet clear, and Hewitt qualified his assessment with this summary: “In a world now working at web scale and looking to the future, Apache Cassandra might be one part of the answer.” (emphasis added) While many of those databases from the NoSQL revolution and the NewSQL counter-revolution have now faded into history, Cassandra has stood the test of time, maturing into a rock-solid database that arguably still scales with performance and reliability better than any other. Twelve-plus years after its invention, Cassandra is now used by approximately 90 per cent of the Fortune 100, and its appeal is broadening quickly, driven by a rush to harness today’s “data deluge” with apps that are globally distributed and always-on. Add to this recent advances in the Cassandra ecosystem such as Stargate, K8ssandra, and cloud services like Astra DB, and the cost and complexity barriers to using Cassandra are fading into the past. So while it’s fair to say that while Cassandra might have been ahead of its time in 2007, it’s primed and ready for the data demands of the 2020s and beyond. Cassandra Grows up Fast Cassandra made a lot of sense to its inventors at Facebook when they developed it in 2007 to store and access reams of data for Messenger, which was growing insanely fast. From the start, Cassandra scaled quickly and accessed huge amounts of data within strict SLAs—in a way that relational databases and SQL, which had long been the standard ways to access and manipulate data, couldn’t. As it became clear that this technology was suitable for other use cases, Facebook handed Cassandra to the Apache Software Foundation, where it became an open-source project (it was voted into a top-level project in 2010). The reliability and fail-over capabilities offered by Cassandra quickly won over some rising web stars, who loved its scalability and reliability. Netflix launched its streaming service in 2007, using an Oracle database in a single data centre. As the company’s streaming service users, the devices they binge-watched with, and data expanded rapidly, the limitations on scalability and the potential for failures became a serious threat to Netflix’s success. At the time, Netflix’s then-cloud architect Adrian Cockroft said he viewed the single data centre that housed Netflix’s backend as a single point of failure. Cassandra, with its distributed architecture, was a natural choice, and by 2013, most of Netflix’s data was housed there, and Netflix still uses Cassandra today. Cassandra survived its adolescent years by retaining its position as the database that scales more reliably than anything else, with a continual pursuit of operational simplicity at scale. It demonstrated its value even further by integrating with a broader data infrastructure stack of open source components, including the analytics engine Apache Spark, stream-processing platform Apache Kafka, and others. The Cassandra Constellation Cassandra hit a major milestone with the release of 4.0. The members of the Cassandra community pledged to do something that’s unusual for a dot-zero release: make 4.0 so stable that major users would run it in production from the get-go. But the real headline is the overall growth of the Cassandra ecosystem, measured by changes both within the project and related projects, and improvements in how Cassandra plays within your infrastructure. A host of complementary open-source technologies have sprung up around Cassandra to make it easier for developers to build apps with it. Stargate, for example, is an open-source data gateway that provides a pluggable API layer that greatly simplifies developer interaction with any Cassandra database. REST, GraphQL, Document, and gRPC APIs make it easy to just start coding with Cassandra without having to learn the complexities of CQL and Cassandra data modelling. K8ssandra is another open-source project that demonstrates this approachability, making it possible to deploy Cassandra on any Kubernetes engine, from the public cloud providers to VMWare and OpenStack. K8ssandra extends the Kubernetes promise of application portability to the data tier, providing yet another weapon against vendor-lock. What if Data Wasn’t a Problem? There’s a question that Hewitt poses in Cassandra: The Definitive Guide: “What kind of things would I do with data if it wasn’t a problem?” Netflix asked this question—and ran with the answer—almost a decade ago. The $25-billion company is a paragon of the kind of success that can be built with the right tools and the right strategy at the right time. But today, for a broad spectrum of companies that want to achieve business success, data also can’t be a “problem.” Think of the modern applications and workloads that should never go down, like online banking services, or those that operate at a huge, distributed scale, such as airline booking systems or popular retail apps. Cassandra’s seamless and consistent ability to scale to hundreds of terabytes, along with its exceptional performance under heavy loads, has made it a key part of the data infrastructures of companies that operate these kinds of applications. Across industries, companies have staked their business on the reliability and scalability of Cassandra. Best Buy, the world’s largest multichannel consumer electronics retailer, refers to Cassandra as “flawless” in how it handles massive spikes in holiday purchasing traffic. Bloomberg News has relied on Cassandra since 2016 because it’s easy to use, easy to scale, and always available; the financial news service serves 20 billion requests per day on nearly a petabyte of data (that’s the rough equivalent of over 4,000 digital pictures a day—for every day of an average person’s life). But Cassandra isn’t just for big, established sector leaders like Best Buy or Bloomberg. Ankeri, an Icelandic startup that operates a platform to help cargo shipping operators manage real-time vessel data, chose Cassandra—delivered through DataStax’s Astra DB—in part because of its ability to scale as the company gathers an increasing amount of data from a growing number of ships. It wanted a data platform that wouldn’t make data a problem, and wouldn’t get in the way of its success. Making Cassandra Simpler and More Cost-effective A handful of organizations have built services around Cassandra, in an effort to make it more accessible, and to solve some of the inherent challenges that come with operating a robust database. One particularly hard nut to crack when it comes to managing databases has been provisioning. With cloud computing services (think AWS Lambda), scaling, capacity planning, and cost management are all automated, resulting in software that’s easy to maintain, and cost-effective—” serverless,” in other words. But because modern databases store data by partitioning it across nodes of a database cluster, they’ve proved challenging to make serverless. Doing so requires rebalancing data across nodes when more are added, in order to balance storage and computing capabilities. Because of this, enterprises have been required to guess what their peak usage will be—and pay for that level, even if they aren’t using that capacity. That’s why it was a big deal when DataStax announced earlier this year that it's Astra DB cloud database built on Cassandra is available as a serverless, pay-as-you-go service. According to recent research by analyst firm GigaOm, the serverless Astra DB can deliver significant cost savings. And developers will only pay for what they use, no matter how many database clusters they create and deploy. Carl Olofson, research vice president at IDC, noted: “A core benefit of the cloud is dynamic scalability, but this has been more difficult to achieve for storage than with compute. By decoupling compute from storage, DataStax’s Astra DB service lets users take advantage of the innate elasticity of the cloud for data, with a cloud-agnostic database.” A Database for Today While Cassandra is more than a decade young, it is a database for today. If the argument of 2010 was “Cassandra may be the future,” and in 2017 “Cassandra is mature,” the 2021 version is “Cassandra is an essential part of any modern data platform.” The developments in Cassandra and its surrounding ecosystem point to a coming wave of new developers and enterprises worldwide for whom Cassandra is not just a sensible choice, but an obvious one.