The first things that come to my mind are :
- In mysql every table is an index , if you don't create a primary key
one, the system creates one for you ( but it is better to explicit the
primary key ) .
- there is no parallelism
- mysql can be very fast but usually is not used for very complex queries .
- backup/recovery is much simpler than Oracle, there is a dump or
tools like percona xtrbackup
- operation on data ( copying movying ) and so on can be much more
complex than with datapump.
Postgresql is much more complex than mysql .
it has quite everything through a lot of extensions you can find but
it is a huge product. In my opinion the core difference of postgres is
the way it manages undo data . It uses vacuum and when it changes a
row ( i am simplifying) it create a new copy and let the old one there
if needed ( if there are transaction open on it) .
This happens also for indexes so there are a lot of things to pay attention to.
Vacuum can be a very tedious things to fight against-
Datamoving can be more tricky with mysql or postgres than with oracle
and Datapump is much faster than mysqldump or pg_dump. Postgresql
works quite well but it is slow . It uses filesystem cache so for
making tuning, when you have problems , you have to pass through
operative system tuning . A bigger buffer cache can obtain a negative
impact because using sync operation on filesystem , you can have
doublebuffering between postgres cache and filesystem cache. This is
true for onpremis , for aurora or for alloydb you have to follow
their documentation .
Usually i have not got great performance problem with mysql , for the
most missing index or some memory areas to change but all was quite
easy. Postgres is another animal . I find it much more difficult to
manage even if postgres can do really everything .
Another thing of postgresql is that developers have to know it.
Usually , in non critical shop, developers don't need to know to much,
oracle, db2, sql resolve by themselves a lot of thing. Ok , they
costs, but they "do". Postresql is a different situation and
developers have to know how to use it .
On aurora or alloydb some issues are bypassed by the IO network, on
RDS it is not different than from on premis .
An example .If you have a big table, and you have an unused index on
column C and you update column C values, in db2, sql or oracle you
usually have no problem. In postgres this can be very problematic ( no
hot update ) .
Il giorno dom 14 gen 2024 alle ore 20:18 yudhi s
<learnerdatabase99@xxxxxxxxx> ha scritto:
--
Hello Listers,
We have mainly worked in on-premise database world and that too in "Oracle
database" and we are well versed with underlying Oracle database architecture
and its optimizers working and designed and application development on top of
this.
Now that organization is choosing AWS cloud as target state and thus the
databases we are exploring for some new development project are mainly AWS
aurora postgresql and mysql. So wanted to understand, if there is any key
design/architectural changes should the app development team or the database
design team, should really aware about, so as to take right decision on any
new development project or in case of moving an existing Oracle database
system to AWS postgresql/mysql databases?
Is there any list of differences(as compared to Oracle database) in key
concepts like for example basic design concepts, Normalization, Partitioning,
clustering, backup and recovery, Indexing strategy, isolation level,
performance which one should definitely be aware of?
I agree, It may not be the right forum for this one but I am trying to check
if anybody has gone through a similar path being worked on Oracle database
for majority of the career span.
Regards
Yudhi
