NoSQL design for Amazon Keyspaces

• In RDBMS, data can be queried flexibly, but queries are relatively expensive and don't scale well in high-traffic situations (see Data modeling in Amazon Keyspaces (for Apache Cassandra)).

• In a NoSQL database such as Amazon Keyspaces, data can be queried efficiently in a limited number of ways, outside of which queries can be expensive and slow.

• In RDBMS, you design for flexibility without worrying about implementation details or performance. Query optimization generally doesn't affect schema design, but normalization is important.

• In Amazon Keyspaces, you design your schema specifically to make the most common and important queries as fast and as inexpensive as possible. Your data structures are tailored to the specific requirements of your business use cases.

How NoSQL design is different

• By contrast, you shouldn't start designing your schema for Amazon Keyspaces until you know the questions it needs to answer. Understanding the business problems and the application use cases up front is essential.

• You should maintain as few tables as possible in an Amazon Keyspaces application. Having fewer tables keeps things more scalable, requires less permissions management, and reduces overhead for your Amazon Keyspaces application. It can also help keep backup costs lower overall.

• Data size: Knowing how much data will be stored and requested at one time helps to determine the most effective way to partition the data.

• Data shape: Instead of reshaping data when a query is processed (as an RDBMS system does), a NoSQL database organizes data so that its shape in the database corresponds with what will be queried. This is a key factor in increasing speed and scalability.

• Data velocity: Amazon Keyspaces scales by increasing the number of physical partitions that are available to process queries, and by efficiently distributing data across those partitions. Knowing in advance what the peak query loads will be might help determine how to partition data to best use I/O capacity.

• Keep related data together.   Research on routing-table optimization 20 years ago found that "locality of reference" was the single most important factor in speeding up response time: keeping related data together in one place. This is equally true in NoSQL systems today, where keeping related data in close proximity has a major impact on cost and performance. Instead of distributing related data items across multiple tables, you should keep related items in your NoSQL system as close together as possible.

  As a general rule, you should maintain as few tables as possible in an Amazon Keyspaces application.

  Exceptions are cases where high-volume time series data are involved, or datasets that have very different access patterns. A single table with inverted indexes can usually enable simple queries to create and retrieve the complex hierarchical data structures required by your application.

• Use sort order.   Related items can be grouped together and queried efficiently if their key design causes them to sort together. This is an important NoSQL design strategy.

• Distribute queries.   It is also important that a high volume of queries not be focused on one part of the database, where they can exceed I/O capacity. Instead, you should design data keys to distribute traffic evenly across partitions as much as possible, avoiding "hot spots."