Oracle and PostgreSQL, two popular database management systems, have their differences when it comes to exception handling. Oracle allows developers to perform various mental gymnastics within the exception handling section, utilizing statement-level rollbacks and commits. In contrast, PostgreSQL handles exceptions internally via savepoints.

This difference can lead to performance cliffs and potentially catastrophic subtransaction SLRU lock wait events, as seen in a famous GitLab incident. However, starting with PostgreSQL 17, developers can increase cache sizes related to SLRU to mitigate this risk.

To avoid using exceptions altogether, clever techniques like using the on conflict clause instead of pk violation exception section can be employed. This approach not only improves performance but also reduces dead tuples.

In addition, the PostgreSQL community has developed an open-source tool called ExtensionMigrationAssistant to detect cloud vendor-specific objects and functions in a migrated schema. This rapid response is a testament to Postgres's vibrant and innovative community.

Source: https://dev.to/mayur555b/oracle-to-postgres-happily-ever-after-edition-5766