Database selection

Question

Hello all,

I see multiple docs on the internet, stating the difference between aurora Mysql and aurora postgres. But I want to understand the real views of the experienced folks here.

While we are on AWS and planning to opt for one of the relational databases out of mysql and postgres for our newly built application (It will be a Hybrid type, mostly dealing with batch processing but also supporting OLTP type UI screens etc. and ACID transactions preferred).

What all things/critical parameters should we keep in mind here for opting a database? or in other words how to evaluate the suitable database for our application here?

Answer

Hi there, these are key differences between MySQL & PostgreSQL.

| Category | MySQL | PostgreSQL |
| --- | --- | --- |
| Database technology | MySQL is a purely relational database management system | PostgreSQL is an object-relational database management system |
| Features | MySQL has limited support of database features like views, triggers, and procedures | PostgreSQL supports most advanced database features like materialized views, INSTEAD OF triggers, and stored procedures in multiple languages |
| Data Types | MySQL supports numeric, character, date and time, spatial, and JSON data types | PostgreSQL supports all MySQL data types along with geometric, enumerated, network address, arrays, ranges, XML, hstore, and composite |
| ACID Compliance | MySQL is ACID compliant only with InnoDB and NDB Cluster storage engines | PostgreSQL is always ACID compliant |
| Indexes | MySQL has B-tree and R-tree index support | PostgreSQL supports multiple index types like expression indexes, partial indexes, and hash indexes along with trees
| Performance | MySQL has improved performance for high-frequency read operations | PostgreSQL has improved performance for high-frequency write operations |

If you're a developer, you should know that MySQL doesn't yet support “LIMIT & IN/ALL/ANY/SOME subquery” & standard SQL clauses like "FULL OUTER JOINS, INTERSECT, and EXCEPT" as well as Partial Indexes, Bitmap Indexes, and Expression Indexes. 
However, PostgreSQL is the best choice for developers who have to write complex SQLs. It is a fully SQL-compliant database and supports all SQL standard features. Applications of pretty much any nature from any domain can use PostgreSQL as their database, makes it a popular choice for OLTP, OLAP, and DWH environments.

Since you said new DB should support OLTP as well as ACID transactions preferred, I reckon PostgreSQL is more suitable for you (, but if your team has never used either MySQL or PostgreSQL, usually MySQL is easier to get started with while PostgreSQL is more complex to get started with.

You can figure out more from resources below.
https://aws.amazon.com/compare/the-difference-between-mysql-vs-postgresql/

If you have any more questions, feel free to leave a comment below.
Thanks!

Category	MySQL	PostgreSQL
Database technology	MySQL is a purely relational database management system	PostgreSQL is an object-relational database management system
Features	MySQL has limited support of database features like views, triggers, and procedures	PostgreSQL supports most advanced database features like materialized views, INSTEAD OF triggers, and stored procedures in multiple languages
Data Types	MySQL supports numeric, character, date and time, spatial, and JSON data types	PostgreSQL supports all MySQL data types along with geometric, enumerated, network address, arrays, ranges, XML, hstore, and composite
ACID Compliance	MySQL is ACID compliant only with InnoDB and NDB Cluster storage engines	PostgreSQL is always ACID compliant
Indexes	MySQL has B-tree and R-tree index support	PostgreSQL supports multiple index types like expression indexes, partial indexes, and hash indexes along with trees
Performance	MySQL has improved performance for high-frequency read operations	PostgreSQL has improved performance for high-frequency write operations

Database selection

Relevant content