DynamoDB vs Postgres - The Ultimate Comparison

Amazon DynamoDB and Postgres are two of the most common database services used by developers. However, selecting one out of the two might depend on various criteria, specifically since DynamoDB is NoSQL while Postgres is a relational database server that supports most SQL standards.

This article will go through the differences between the two and give tips on choosing one based on your requirements.

DynamoDB and Postgres: An Overview

DynamoDB

Amazon DynamoDB is a fully managed, serverless, NoSQL cloud database service offered by AWS. Since it supports key-value and document data models, developers can manage NoSQL data reliably and flexibly without a hassle. In addition, as DynamoDB is serverless, it adjusts for capacity by automatically scaling tables with zero administration and supports horizontal scaling to scale across multiple servers.

Unlike many NoSQL databases, it supports ACID (Atomicity, Consistency, Isolation, and Durability) transactions. Therefore, developers can use DynamoDB for applications that require complex business logic with mission-critical workloads.

PostgreSQL

PostgreSQL is an object-relational database system that is open-source. It supports SQL standards and concurrency, making it fully ACID-compliant by default. It also supports dynamic loading and catalog-driven operations, which enables users to customize data types and functions and write code from different programming languages apart from SQL and C without recompiling the database. However, unlike Amazon, Postgres does not offer a fully managed standard cloud service.

DynamoDB and Postgres: Comparison

As mentioned in the overview, the main difference between DynamoDB and Postgres is that DynamoDB is a NoSQL database service while Postgres is a relational database system.

This section will analyze the differences between the two databases, starting with their data models and ending with pricing details.

1. Data Model

DynamoDB

DynamoDB is a schemaless (NoSQL) database that supports key-value and document data models, with a primary key to uniquely identify each data item in every table. Other non-key attributes do not have any constraints, and therefore, DynamoDB can manage structured or semistructured data, including JSON documents. This flexibility allows each table row to have any number of columns at any time so that you can easily change the tables according to your business requirements without redefining the table schema.

PostgreSQL

Postgres has a relational model with a well-defined schema, where data is stored in tables, rows, and columns. All the relationships between tables, columns, indexes, and other database elements are also defined here. An important feature of Postgres is that it has extended the relational model with abstract data types and user-defined operators and procedures to simulate object-oriented data modeling constructs such as aggregation and generalization.

2. Latency and Performance

DynamoDB

Since DynamoDB can support tables of any virtual size with horizontal scaling and high I/O performance of the SSDs, DynamoDB can serve more than 10 trillion requests per day with 20 million requests per second at peaks (millisecond latency), over petabytes of storage. Moreover, DynamoDB utilizes a request router that offers O(1) time complexity (constant time), meaning that DynamoDB can query and fetch data consistently at a high rate to perform the same under low and heavy data sizes.

In addition, using the in-memory caching technique called DynamoDB Accelerator (DAX), the read performance of DynamoDB tables can be improved by up to 10 times, even at millions of requests per second (microsecond latency).

PostgreSQL

Since Postgres doesn't provide a database managing service, performance will depend on the disk subsystem and developers. Therefore, database administrators must carefully design the tables, queries, and indexes to achieve optimal performance.

However, Postgres provides some features that database administrators can utilize to improve performance. It includes:

• Partitioning and inheritance modules to help split large tables into smaller sections.
• Advanced indexing techniques such as GiST, SP-Gist, Covering indexes
• Just-in-time (JIT) compilation of expressions and many more to improve overall query performance.

On the other hand, if you use DigitalOcean to manage your database, you can use its metrics visualization functionality to monitor the performance and health of the database. Some metrics that can be monitored are the number of database connections, cache hit ratio, fetch, insert, update and delete throughput, rate of deadlock creation, and replication delay in bytes (if replication is enabled).

3. Availability and Durability

DynamoDB

DynamoDB automatically and synchronously replicates data across multiple physical nodes in a single region known as availability zones. This ensures that the durability and availability of data are maintained even in a major natural disaster or power outage, allowing DynamoDB to failover to another AZ if one AZ goes down.

Availability and durability are further improved as data are stored in partitions (storage for a table) backed by SSDs.

PostgreSQL

Configuring the database to ensure availability is the responsibility of the database administrator if a database management service is not used. In such instances, the primary database server must be configured to switch over to a second server to provide high availability and allow several servers to serve the same data to provide load balancing and avoid requests throttling. However, when configuring these manually, the administrator must carefully direct the read/write requests to avoid synchronization problems and stale, inconsistent data.

However, a database management service like DigitalOcean will make the administrator's life easier by providing some built-in functionalities ensuring availability. Some of the features are:

• Automated failover: Automatically detect and replace degraded nodes.
• Redundancy: Primary clusters have at least one standby node with replicated data to take over in case of a failure in the primary node, minimizing the risk of data loss.

4. Security

DynamoDB

Amazon DynamoDB takes multiple steps to provide secure database services to the most security-sensitive organizations. Some of the steps are:

• Encryption at rest: Reduce the operational burden and complexity of protecting sensitive data by encrypting all the data stored in DynamoDB using encryption keys stored in AWS Key Management Service (AWS KMS).
• Identity and access management: Manage the users accessing the DynamoDB account and implement security policies using AWS Identity and Access Management (IAM) service.
• Infrastructure security with VPC: For enhanced security, customers can use a Virtual Private Cloud (VPC) endpoint to host the DynamoDB instance to logically isolate it from other networks, including the public internet.

PostgreSQL

Database administrators can ensure the security of a Postgres database via three main avenues:

1. Network-level security: Secure the connections to the database with Unix domain sockets, TCP/IP sockets, and firewalls deployed on the host machine.
2. Transport-level security: Secure the communications done with the database using SSL/TLS.
3. Database-level security: Secure the data stored with access control mechanisms such as roles and permissions, row-level security (RLS), and auditing.

If you were to use a database management cloud service such as DigitalOcean, it provides built-in security features to make the admin's life easier. For instance, in DigitalOcean, while data is encrypted in transit and at rest, the PostgreSQL database runs in the admin's private network. Therefore, only whitelisted requests through the public internet can reach the database.

5. Backups

DynamoDB

DynamoDB provides continuous backups with automated replication of data across multiple regions. The two main backup methods offered are:

1. Point-in-time recovery (PITR): Enabling PITR from the AWS Management Console or an API call will ensure your table data is continuously backed up, and you can restore the table to any point in time up to the second within the next 35 days. PITR helps to protect data from accidental write/delete operations.
2. On-demand backup and restore: Backing up data to meet corporate and governmental regulatory requirements can be easily done using this method, as it allows the creation of full backups of DynamoDB tables' data for data archiving. Data of any size, ranging from megabytes to terabytes, can be backed up without affecting the performance or availability of the application.

PostgreSQL

Database administrators can take several approaches for backing up PostgreSQL data. It includes techniques such as:

1. SQL dump
2. File system-level backup
3. Continuous archiving: It can be achieved by multiple techniques like logical replication, synchronous replication, point-in-time recovery, and hot standby

However, in a managed database hosted in a platform like DigitalOcean, backups are handled automatically, free of charge. Full backups are created once per day and retained for seven days without affecting performance or availability. They also maintain write-ahead-logs to allow users to restore to any point-in-time during the retention period.

6. Pricing

DynamoDB

DynamoDB charges for database operations such as reading, writing, and storing data, as well as optional features such as DAX if enabled. Based on the billing options for processing reads and writes, they have two modes:

1. On-demand capacity mode: DynamoDB charges for the data reads and writes the application performs on the tables without specifying the read and write throughput expectations. In this mode, DynamoDB will instantly accommodate any amount of workload as it increases or decreases. This pricing scheme is best suited if your application has very predictable traffic and you prefer to pay only for what you use.
2. Provisioned capacity mode: If you have predictable application traffic and have an idea about the read/write throughout, this pricing scheme would be the better fit, as you can specify the expected number of reads and writes per second the application requires. One drawback of this approach is that you are billed for the provisioned throughput, regardless of its usage.

However, they offer a free plan with 25 GB of storage, Write and Read Capacity Units (WCU and RCU) with 25 each, which is enough to handle up to 200 million monthly requests.

PostgreSQL

Even though PostgreSQL is an open-source database system, platforms that provide database management services are not free. This section utilizes the PostgreSQL engine hosted in DigitalOcean to analyze the pricing plan.

Pricing in DigitalOcean is based on the database cluster plan and the machine type.

The two database cluster plans are:

1. Single node clusters: Single node clusters are not highly available and can be used for preliminary development and testing. A single node cluster with 1 GB of RAM is offered at $15.00 per month.
2. High availability clusters: If you are looking for high availability, this is the plan you should go with, as it provides support for multiple backup nodes. Pricing begins at $30.00 per month for a 2 GB RAM/1 vCPU primary node with at least one matching standby node for automatic failover.

The price of the cluster includes inbound and outbound data transfer as well.

DigitalOcean deploys managed databases on virtual machines called Droplets. You can deploy your managed database in a Droplet with a shared CPU or dedicated CPU. Billing will be done differently, depending on the machine configuration. You can refer to their documentation for more details.

When to Use DynamoDB

DynamoDB is ideal if you require a cloud-based, serverless, NoSQL database service. Some of the common use cases of DynamoDB are:

1. High-performance software applications requiring high concurrency and handling millions of requests per second would overburden traditional relational databases.
2. Shopping carts and inventory tracking systems that need to handle a large number of queries per second.
3. Real-time applications that require minimal latency, such as gaming applications.

When to Use PostgreSQL

PostgreSQL is a good fit for your application if you are looking for an open-source SQL solution that's ACID compliant and don't mind the additional administrative tasks.

Here are some of the most common use cases:

1. As Postgres is ACID compliant, it is ideal for Online Transaction Processing (OLTP) workloads. It can also be integrated with mathematical software such as Matlab. Therefore, Postgres is a go-to database solution for financial applications.
2. Postgres is also used in GIS data applications as it supports a powerful GIS extension called "PostGIS", which provides functions to process geometric data in many formats.

Conclusion

This article presented a thorough comparison between DynamoDB and PostgreSQL. Even though PostgreSQL doesn't come with a cloud service by default, it has some robust features that compete with DynamoDB in some avenues. While choosing the exemplary service depends on the requirements of your application, this article will help you compare the main aspects that need to be considered in selecting one.

I hope that you have found this article helpful.

Thank you for reading.