0

Aurora Postgres 11.9

In SQL Server we strictly follow the good programming practice that "every single call land on DB from the application will be a stored procedure instead of simple queries". In Oracle, we haven't experienced the same thing may be due to select stored procedures required additional cursors, and so on.

Can any expert Postgres person advise me what practice should we follow in progress in this regard and what are pros and cons in this case of Postgres?

In addition in SQL Server we use "rowversion" for data sync with BI and other external modules, is there any built-in alternate available in Postgres or should we have to do it with manual triggers?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3706888
  • 159
  • 2
  • 11
  • 1
    That "good programming practice" is highly subjective in my opinion. I see nothing wrong with sending queries directly from the application. –  Jun 09 '21 at 12:15
  • Yes, there is nothing wrong at all with sending direct queries -- Agreed. But there are many benefits of using store procedures. including pre-compiled code, no dependency on app-dev or release to promote an updated version of code, etc. but there is a flip side of the coin as well like parameter sniffing, etc, that's why want an experts openion on this. Thanks – user3706888 Jun 09 '21 at 12:40
  • 1
    Note that usage of stored procedures and functions are very different between Postgres and SQL Server. For example, procedure in Postgres are not meant to return results, let alone multiple results (it can be done, but it is extremely cumbersome). Transaction handling is also quite different. So typically it's a recipe for disaster when you try to migrate code 1:1 from SQL Server to Postgres (or from SQL Server to Oracle for that matter) –  Jun 09 '21 at 12:42
  • 2
    See [Migrate your mind set too](https://blog.sql-workbench.eu/post/migrate-your-mindset/). – Belayer Jun 09 '21 at 19:27
  • 1
    Welcome. Not refactoring is probably the greatest error commuted when converting from one DB to another. And leads to maintenance nightmares down the line and usually abysmal performance. – Belayer Jun 10 '21 at 06:54

0 Answers0