Battle of the RDBMS Giants in AWS

MySQL vs PostgreSQL is one of the never-ending wars in the RDBMS world. This article will help you to choose the best fit for your use case.

Vithulan MV
Dec 25, 2020 · 6 min read
Source — silota

Background

Why Amazon Aurora?

CAP theorem

Optimizations

CREATE TABLE IF NOT EXISTS `table_1` (
`att1` VARCHAR(9) NOT NULL,
`att2` TINYINT(1) UNSIGNED NOT NULL,
`att3` VARCHAR(14) NOT NULL,
`att4` DATETIME NOT NULL,
`att5` BIGINT(10) UNSIGNED NOT NULL,
`att6` BIGINT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`att3`, `att1`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
CREATE TABLE IF NOT EXISTS `table_2` (
`att1` VARCHAR(9) NOT NULL,
`att2` TINYINT(1) UNSIGNED NOT NULL,
`att3` DOUBLE(13 , 3) NOT NULL,
`att4` DATETIME NOT NULL,
`att5` BIGINT(10) UNSIGNED NOT NULL,
`att6` CHAR(1) NOT NULL,
`att7` BIGINT(10) UNSIGNED NOT NULL,
`att8` BIGINT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`att1`,`att2`,`att4`, `att5`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8
SELECT
table_2_Outer.att6,
table_2_Outer.att1,
table_2_Outer.att2,
table_2_Outer.att3,
table_2_Outer.att4,
table_2_Outer.att5
FROM
att2 table_2_Outer force index (`PRIMARY`)
INNER JOIN
(
SELECT
Max(table_2_Inner.att3) max_att3,
table_2_Inner.att6,
table_2_Inner.att1
FROM
(
SELECT
e.att6,
e.att1,
e.att7
FROM
table_1 e force index (`PRIMARY`)
WHERE
e.att7 = ?
AND att6 IN
(
?
)
)
pz
INNER JOIN
table_2 table_2_Inner force index (`PRIMARY`)
ON pz.att6 = table_2_Inner.att6
AND pz.att1 = table_2_Inner.att1
AND table_2_Inner.att3 <= ?
GROUP BY
table_2_Inner.att6,
table_2_Inner.att1
)
c
ON c.att6 = table_2_Outer.att6
AND c.att1 = table_2_Outer.att1
AND c.MAX_EFF_DATE = table_2_Outer.att3

Aurora MySQL vs Aurora PostgreSQL

Fig.1 Cluster specs that were used in the experiment
Fig.2 db.r5.xlarge specs

Read Operation Comparison

Fig.3 Read operation results

Write & Index operation Comparison

Fig.4 First data source load results
Fig.5 Second data source load results

Verdict

Verdict

Acknowledgement

Sysco LABS Sri Lanka

Technical insights and practices from innovators in the…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store