Introduction
We will go over Aurora MySQL vs Aurora Postgres.
Did you know AWS Aurora is generally x5 faster than the equivalent MySQL and Postgres databases?
I will break this review in the following sections:
- The main differences between the two backends
- How those compare over time in terms of popularity
- Which one is suitable for your use case
I have personally used both backends in some of my projects and have quite a bit of experience on knowing their differences.
I will go over each point on their main differences to help you decide which is good for you.
Cost – Aurora MySQL vs Postgres
Cost, cost and cost. This is the most important question that I get asked when I try to compare the two. After we have deployed both instances in many different scenarios I can safely say for the most part they both cost approximately the same thing.
However there’s one case where Aurora MySQL is cheaper than Aurora Postgres and this is for very small instances. Let me be more specific, if you require a database that’s going to be smaller than 2GB in size then only Aurora MySQL offers that option at the time of this writing. If you try to go with Aurora Postgres then you need to go to the second tier. The instance size only supported in Aurora MySQL is db.t3.small.
Having said that in all other cases and in intense load they both score equally in cost.
Winner:Â Aurora MySQL
Features –Â Aurora MySQL vs Postgres
In terms of features it really depends on what you need. If you are looking for all the bells and whistles in a database then your answer is going to be hands down Postgres. Postgres has much more features as a database in general than MySQL. Thus when it carries over to the Aurora domain it wins the fight and it’s a better option.
If you are coming however from a previously used MySQL database and you know what you will be needing then you can safely go with Aurora MySQL as well. Most features that Postgres has are very particular in most cases and you need to dig deep to find them or have very special cases.
Winner: Aurora Postgres
Security – Aurora MySQL vs Postgres
Since both databases are hosted in the AWS infrastructure and they are maintained by the AWS team I would say security is a non-issue here however there is a catch.
If we look at historical records we will see the traditional MySQL has a lot more vulnerabilities than Postgres.
To add on this Postgres supports more authentication methods, roles and policies which you can set at the data level. Since we are looking security as a whole here and those issues may not be as important since you are in the AWS ecosystem and roles already exist there they are also not as significant.
However coding vulnerabilities in the database backend itself may spill over to the AWS ecosystem as it’s using that as the base code when deploying your instances. Considering the better track record for Postgres I believe they come on top here.
Winner: Aurora Postgres
Reliability – Aurora MySQL vs Postgres
In terms of reliability if we were outside the AWS world there could be a lot of things we would say. MySQL is very well known for it’s seamless clustering and scalability however this is not significant inside AWS as it abstracts all of this for us and we need to base our judgement on that alone.
I believe in terms of reliability and in my experience using both instances of the databases with millions of requests I would say this is a tie here. AWS has done a phenomenal job scaling both and making them highly reliable.
If you throw over this RDS failovers and redundancy then you have a solid database system unparalleled from a traditional database instance.
Winner: Neither
Compatibility – Aurora MySQL vs Postgres
In terms of compatibility the backends are mostly up to date with the existing versions of the databases.
However if you are planning to use Aurora Serverless then the compatibility is lagging behind the actual databases.
At the current time of this writing Aurora Serverless is only compatible with versions of MySQL of 5.6 and Postgres with versions of 10.7+.
This means if you fall outside that range of support Aurora Serverless will not be an option for you. Since everyone likes the idea of automatic scaling and taking away the work from maintaining RDS instances or fail overs across regions this can end up being a real problem for your use case.
Speed –Â Aurora MySQL vs Postgres
One thing that always comes up as a circulating question is which database is faster Aurora MySQL or Postgres. To put things simply I don’t think anyone in particular has an edge here however there’s a few things that you need to take into account when you are making a decision for the speed of the Aurora backend you will be using.
Focus more on your usage and here I want to cover two specific aspects which I think each of those database backends excels at.
Indexing Aurora MySQL vs Postgres
The edge here is certainly for postgres, it has faster indexing creation and usage and it will surpass MySQL backends in most cases.
Complex Queries Aurora MySQL vs Postgres
Again Postgres is better at performing complex queries in all the benchmarks shown more specifically it outperforms in the following scenarios tested:
- JOIN operations in massive tables
- Sub select queries
- Conditional based queries on big data sets such as setting where clauses
Stored Procedures Aurora MySQL vs Postgres
This one goes to MySQL because Postgres does not support stored procedures. The good thing about stored procedures and functions is that they can be stored in memory and get optimized by the backend itself. Also it offers an easy re-useable code which could ensure optimized code always runs properly and fast. MySQL is the winner on this one.
Read Only Queries Aurora MySQL vs Postgres
This is another reason where MySQL excels at. When you are performing generally heavy read only operations (not index related) MySQL almost always outperforms postgres in our testing. This starts to change as indexing gets introduced which then that changes in favor of Postgres.
Overall when it comes to speed I think both backends are great for Aurora but you should use what is suits your needs. I tried to break it down into details above to give you a better idea and understanding and making a selection.
Popularity – Aurora MySQL vs Postgres
In terms of popularity since I have no idea I tapped into Google trends for some help and insights. In general all my experience has been with bigger blue chip companies where we have done a lot of deployments and generally speaking it’s always a split between the two.
Clearly my limited exposure to implementations of Aurora and the backends it supports is not the most objective judge so let’s take a look at what the data is showing us.
Based on the graph there’s a few observations on how the popularity went for both backends for Aurora:
- Early in time where the Postgres backend wasn’t available the MySQL backend had a lot more traction in searches, Postgres was close to zero because it simply wasn’t there.
- Mid time the MySQL backend maintained it’s lead in terms of popularity and was clearly the most popular choice.
- Lately they are almost tied as you can see with MySQL still maintaining a slight difference over PostgreSQL so I’d say Postgres aurora is gaining a lot of traction and aurora mysql is losing some.
One thing to note is that the search could have been varied to Aurora PostgreSQL and Aurora Postgres which may be limiting the results but more over when I aggregated the two the change is not that much of a big deal and the above observations are still valid.
Which Is Better Aurora MySQL or Aurora Postgres
This is a very difficult and situation based question. Since I have offered the differences above hopefully this should be a good guide for you to make an educated decision on which one you consider better.
What I’m going to do here is provide you with some questions you can ask yourself in order to help you make a decision and hopefully this will lead to something good for you.
- Do you need the extra features of Aurora Postgres?
- No – then both would work
- Yes – Go with Aurora Postgres
- Do you have a really small instance below 2GB?
- No – Both would work
- Yes – Go with MySQL
- Do you use standard SQL and don’t have any special use cases?
- Both would work for you
- Are you migrating from a MySQL database?
- Go with Aurora MySQL
- Are you migrating from a Postgres database?
- Go with Aurora Postgres
- Are you using any Postgres toolsets?
- Go with Aurora Postgres
- Are you using any MySQL toolsets?
- Go with Aurora MySQL
- Are you planning to use Aurora Serverless?
- Check the compatibility of your code with the database backends
Conclusion
We were able to successfully go over the differences between the two database backends for Aurora. I hope you enjoyed Aurora MySQL vs Aurora Postgres and I answered any questions you may have had to get started on your AWS Aurora deployment.
If you have any questions, comments please post them below or send me a note on my twitter. I check periodically and try to answer them in the priority they come in. Also if you have any corrections please do let me know and I’ll update the article with new updates or mistakes I did.
Which Aurora database backend do you use?
I personally use both and based on the use-case I have I select accordingly. Since a lot of projects I’m migrating from are already in Postgres I have had a little more experience with that Aurora database backend.
If you would like to visit the official AWS Aurora documentation you can find a reference here.
If you would like to find more AWS database related articles below: