Database selection

On Thu, 2023-09-21 at 01:17 +0530, veem v wrote:

I see multiple docs on the internet, stating the difference between Mysql and 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 application(It is a Hybrid type, mostly dealing with
batch processing but also supporting OLTP type UI screens etc. and ACID transactions preferred).

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

You are asking the wrong people. We here on the PostgreSQL mailing list will tell
you that you shouldn't touch MySQL except with a long stick, and for almost all
use cases PostgreSQL is preferable. The people on MySQL mailing lists or forums
might tell an entirely different story.

I would say something about license and free software, but if you plan to lock
yourself into the cage of a cloud hosted database, that probably doesn't matter much.

Yours,
Laurenz Albe

Thank you.

Yes feature wise postgres seems rich as I read multiple blogs. And right
now, I can't think of any other reason for opting mysql though as opposed
to postgres.

However, One of the things regarding the transaction management, which I
see in most postgres blogs is 'mvcc'(multiversion concurrency control)
being an issue at times in a heavy concurrent system, in postgres as its
maintaining exact copies of all the old versions of the rows(if someone
still reading those) and at times cleaning these(vacuuming) becomes a pain.

Does AWS aurora postgres depend on the same vacuuming technology for
maintaining the transactions?
Does Aurora Mysql opt for a similar strategy for transaction management? or
any different/better ones?

On Thu, 21 Sept 2023 at 02:09, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Sep 20, 2023, at 14:11, veem v <veema0000@gmail.com> wrote:

Does AWS aurora postgres depend on the same vacuuming technology for maintaining the transactions?

Yes. Aurora has replaced the PostgreSQL storage engine, but the MVCC part is largely the same. The issues with vacuuming are largely overstated, though, and alternative system for concurrency control have their own issues.

Does Aurora Mysql opt for a similar strategy for transaction management? or any different/better ones?

That's probably best addressed to a MySQL-oriented list, or directly to Amazon.

It should be noted that Aurora is a commercial product, and much of it is closed-source. User communities will have somewhat limited ability to offer advice about it.

On Thu, 2023-09-21 at 02:41 +0530, veem v wrote:

However, One of the things regarding the transaction management, which I see in
most postgres blogs is 'mvcc'(multiversion concurrency control) being an issue
at times in a heavy concurrent system, in postgres as its maintaining exact
copies of all the old versions of the rows(if someone still reading those) and
at times cleaning these(vacuuming) becomes a pain.

If your workload consists of updating rows over and over at high frequency, that
is particularly hard for PostgreSQL, and you might want to consider alternatives.
There are ways to mitigate the impact (HOT update), but that requires some design
and planning effort.

Does AWS aurora postgres depend on the same vacuuming technology for maintaining
the transactions?
Does Aurora Mysql opt for a similar strategy for transaction management? or any
different/better ones?

Nobody except Amazon can tell. It is closed source.

Yours,
Laurenz Albe