Re: Moving to Postgresql database

On Mon, Jan 15, 2024 at 5:17 AM veem v <veema0000(at)gmail(dot)com> wrote:

> 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