Database, the center point of each application, plays a crucial role as data is always needed to execute a project and must be stored in some fashion. As applications grow in complexity and scale, the process of storing and managing data becomes more difficult.
When it comes to storing and managing data, developers face the challenging decision of choosing between traditional SQL databases or modern NoSQL databases. This comparison—SQL vs NoSQL—highlights their unique strengths, weaknesses, and ideal use cases, making the decision even more daunting.
In this blog, we will dive into the art of SQL vs NoSQL, comparing their features, examining their suitability for different scenarios, and understanding the principles that help us choose between the two. For this exploration, we benchmark one popular SQL database, PostgreSQL, and a leading NoSQL database, DynamoDB.
What is Postgresql?
PostgreSQL is an open source rdbms(relational database management system) which evolved from the ingres project in the university of california. It follows the traditional ACID (Atomicity, Consistency, Isolation, Durability) properties, making it highly reliable and suitable for various applications.
What is DynamoDB?
DynamoDB is on the other hand one of fastest growing modern NoSQL databases which is fully managed by AWS(Amazon Web Services) and follows the single table design pattern. It is designed to handle high volumes of unstructured and schemaless data and traffic. It automatically scales up or down according to the workload.
Design Patterns
Postgresql
- ACID Properties: It follows the basic acid properties which a must have for sql databases. Atomicity ensures either a transaction will commit or not and there is no in between. Consistency ensures data should remain consistent no matter from where the query comes from. Isolation ensures only one person can write into the database in a single point of time and finally, durability ensures committed data will persist throughout the time.
- Schema Based Design: It uses a structured schema with different tables. Each table has a primary key(unique identifier) and is connected to another table through a foreign key. It has also join operations to combine two tables to fetch data as well as indexing to fetch data quickly.
Here is a sample table design in postgresql where CustomerID is defined as primary key in the Customers table and as far as the Orders table is concern OrderId is the primary key and CustomerID is the foreign key which is outlined in red as how the both table are being connected
3. Indexing: In SQL also in Postgresql uses different types of indexing to retrieve the data faster as the data grows and traditional sql databases tend to slow it down as the query uses sequential searches. So indexing comes as a savior as it uses B-tree to speed up the search.
4. Normalization: Organize data into related tables to reduce data redundancy. There are various types of normalization like 1NF, 2NF,3NF. This process involves large tables divided into smaller ones and multiple ones ensuring each table focuses on one topic. For example let us consider a table order table has the following items
| Order table | |||
| Order_id | Customer_id | Order_date | Product_id |
| 1 | 101 | 12-11-2003 | 1,2 |
| 2 | 102 | 12-11-2003 | 3,4 |
As we can see the product_id column holds multiple entries which violates the idea of normalization. As normalization states that each column should hold data atomic we need to break down the table into two and introduce order_items table and new table should look like as follows
| Order table | ||
| Order_id | Customer_id | Order_date |
| 1 | 101 | 12-11-2003 |
| 2 | 102 | 12-11-2003 |
|
Order items table |
|||
| Item_id | Order_id | Product_id | Quantity |
| 1 | 1 | 1 | 2 |
| 2 | 1 | 2 | 3 |
| 3 | 2 | 3 | 4 |
| 4 | 2 | 4 | 5 |
Here we saw the basic operation on normalization which breaks the initial two tables into two and this is called 1NF. However the tables can be further normalized and in order to retrieve data we might need join operation
5. Aggregation: SQL aggregation functions are used to perform calculations on multiple rows of a table’s column and return a single value. Common aggregate functions include SUM, which adds up all values in a column; AVG, which calculates the average; COUNT, which counts the number of rows; MAX, which finds the highest value; and MIN, which finds the lowest value. These functions are often used with the GROUP BY clause to group rows sharing a common attribute and apply the aggregate function to each group. For example, SELECT department, COUNT(*) FROM employees GROUP BY department counts employees in each department.
6. OLAP: OLAP (Online Analytical Processing) in SQL is a powerful data analysis approach used for complex querying and reporting. It enables users to perform multidimensional analysis of large datasets stored in data warehouses. OLAP operations include slicing, dicing, drilling down, and pivoting to view data from different perspectives. SQL-based OLAP leverages aggregate functions, groupings, and hierarchical data structures to provide insights into business metrics. For example, a retail company can analyze sales data by product, region, and time. OLAP enhances decision-making by allowing interactive analysis and quick retrieval of aggregated data, making it essential for business intelligence and strategic planning.
DynamoDB
- Single Table Design: Unlike the traditional SQL design DynamoDB follows single table design meaning it stores all types of data in a single table rather than multiple ones. This type of design introduces two new key words one is partition key and another one is sort key. In single table design we can declare each row as an item and if the items are being corresponding to one partition are called item collection. There some keyword which are being used in the single table design as follows
- Partition Key: In dynamodb partition key defines how data is being distributed among the partitions.It is also referred as hash key as data data in dynamodb is stored and retrieved as hash. Whenever a data is being inserted it generates a hash and based on the hash it stores the data into the corresponding partition and same goes for retrieving data from a particular partition.
- Sort Key: Sort key is determined as to how a data is being organized in a particular partition. There is always a possibility that items have the same value for the partition key but there must be a different value for the sort key.
- Primary Key: Primary key uniquely identifies a row. Sometimes the partition key is regarded as the primary key, however it can be the combination of both partition key and sort key.
2. Event Driven Architecture: dynamoDB streams is a feature of dynamoDB which captures any crud operations in a dynamoDB table and records them into streams. It allows any application to track any types of changes and triggering an event. Each event in the stream is called a stream record which contains information about the item affected and the type of modification. For e When a new order is placed in an order stable, a stream record is generated. A Lambda function can be triggered to process the order, update inventory, send notifications, or replicate the order to another data store.
3. Time to Leave(TTL): Time to leave is a feature of dynamoDB which is responsible for automatically deleting items from a table after a particular time. We don’t need to delete data manually or by query like in SQL. This reduces the storage cost by removing unnecessary data which is no longer needed after a particular amount of time which is about 48 hours by default.
4. Access Pattern: One of the most important yet most challenging tasks is to define the access pattern for your dynamoDB. The design solely depends on what kind of query can happen after the application is being live. The design of partition key and sort key depends on the access pattern. Depending on the query the database will first go to the desired partition where the desired data might reside. So we need to define the access pattern first before designing the database
5. Global and Local Secondary Indexes: It enables another layer of flexibility as far as querying is concerned. In LSI one can add another sort key along with the present sort key and partition key. But if one uses an LSI the partition size automatically becomes a 10gb of size. One can use at most 5 LSI for one table design and it comes free of cost meaning aws will not charge you any penny if one uses any kinds of LSI in a table.
On the other hand GSI allows you to add another partition key and sort key in a table. What it does is it copies all the data from the table and in that table we can apply the partition key and sort key. AWS allows you to used up to 20 GSI for a particular table but in that case dynamoDB will charge for each write and read operations.
6. Data Duplication: One of the main features of DynamoDB is the ability to duplicate data, which significantly enhances query performance and response times. However, data duplication increases storage costs, but AWS never charges for storage and only charges for write and read operations. However, while data duplication improves efficiency significantly, it also has drawbacks. Every change must be propagated across all duplicates, increasing the number of write operations that AWS charges for.
When to Choose PostgreSQL or DynamoDB?
Choose Postgresql
- Complex Queries and Joins are Necessary: Though join operations are sometimes costlier. PostgreSQL excels with complicated queries and joins, allowing you to quickly access and manage relevant data from numerous tables. Here is an example operations of join query
| SELECT customers.name, SUM(orders.amount) FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name; |
2. Extensive Analytical and Reporting Needs: If your application needs heavy analytical reports postgresql should be the go to databases as it can handle extensive queries and also reporting data
3. Data Integrity and Consistency : Foreign keys, unique constraints, and check constraints are among PostgreSQL’s several data integrity capabilities, helping in the maintenance of consistent and correct data.
|
CREATE TABLE products ( product_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price NUMERIC CHECK (price > 0), stock INT CHECK (stock >= 0) ); |
Using constraints to enforce data integrity.
Choose DynamoDB
- Scalability and Performance are Top Priorities: DynamoDB’s seamless scalability and flexibility to manage heavy traffic make it ideal for applications with varying or enormous workloads.
- Faster Response: If one’s access patterns are defined and you designed your database efficiently DynamoDB will always retrieve data faster than SQL regardless of the data volume it will always fetch data faster.
- Flexible Data Models and Schema-less Design: DynamoDB’s schema-less design enables flexible data modeling, accepting various data structures without specified schema.
- High Availability and Fault Tolerance: DynamoDB’s multi-region, multi-active architecture provides high availability and fault tolerance, ensuring that your application remains available even in the face of regional outages.
Scenario
Restaurant Management System
Consider a restaurant management system: customers go through the menus and and according to the menu they place orders and waiters serve the orders.In that case the following requirements are necessary
Requirements:
- Complex queries and joins for reporting.
- High data integrity and consistency.
- Scalability for handling variable traffic.
- Low-latency read and write operations.
Probable Access Pattern:
- Get all the menu
- Add an order
- Daily/monthly/yearly total order quantity/amount
Recommendation:
- Postgresql: It can be used for complex queries and joins operations for reporting purposes. Also any type of OLAP operations like how many orders for a particular time or over a period of time or how much sales generates over a period of time.
- DynamoDB: It can be used for faster read and write operations, for example retrieving all the menu items from the menu or adding an order. As these operations need faster response and also it can be used for variable traffic like when more users are entering the menu item or add in an order it should automatically scale up or down depending on the situations.
Conclusion
PostgreSQL and DynamoDB are both powerful database systems, with unique advantages based on your application’s requirements. PostgreSQL excels in handling complicated queries, transactions, and relational data models, making it ideal for traditional corporate applications and data warehousing. DynamoDB, on the other hand, provides seamless scalability, faster query response, low latency performance, and flexible data modeling, making it perfect for contemporary, high-traffic applications, IoT systems, and real-time analytics.
By evaluating your application’s specific needs around data structure, scalability, performance, and integration requirements with other AWS services, you can make an informed decision between PostgreSQL and DynamoDB. Both databases offer robust solutions to effectively manage and scale your data infrastructure, ensuring optimal performance and reliability for your application.
.png?width=170&height=122&name=glassdoor%20(1).png)
