Обсуждение: 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

> 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
vacuumingare 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
somewhatlimited 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