Is any key design/architectural changes should the app development team [...], should really aware about
Hi. One of the biggest pitfall of PostgreSQL, from the app-dev perspective,
is the fact any failed statement fails the whole transaction, with ROLLBACK as the only recourse.
So if you have any code that does try-something-and-if-it-fails-do-something-else,
which works in most RDBMS AFAIK, then that's NOT going to work with PostgreSQL.
I think there's an extension to add it (don't recall its name), but I'm always surprise it's not built-in,
even just as an opt-in choice. But maybe AWS Aurora is different in that regard? I'm talking OSS PostgreSQL.
OTOH, one the biggest benefits of PostgreSQL vs Oracle is transactional DDLs.
But for many/most, DDLs are mostly fixed, so doesn't matter as much as it does to us.
libpq is much better than OCI, although nowdays there's a better official C API on top of OCI.
And the protocol being open and OSS, unlike Oracle SQL*NET, there are alternate pure-JS,
pure-RUST, pure-GO, etc... implementations beside libpq to suit the client-side dev-stack better.
Of course, Oracle is batteries-included, while PostgreSQL relies on its vast extension ecosystem instead.
Except you're limited to the (small) subset that intersect the Cloud vendors managed PostgreSQL offer, if
you must also support those...
Another major difference is that the catalogs (dictionaries) in PostgreSQL are fully open (modulo pg_authid and a few others).
So there's no USER_, ALL_, DBA_ variants that hide what objects exist in the cluster, depending on privileges, like there is in Oracle.
Knowing an object exists doesn't mean you can access it, but that's a no-no for some security-wise.
If you care about LOBs, Oracle SecureFile are (way?) faster, last we tested a long time ago.
OTOH, PostgreSQL bytea is much larger and convenient that Oracle's RAW (but that's also very dated info).
These are the main ones that come to mind. I'm sure there are many others. FWIW. --DD