Postgresql – Proper use of database functions for business logic

best practicesfunctionspostgresqlstored-procedures

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry.

These functions are called by an application in java that has almost no business logic in the code.

This database structure is used for different clients and some of these functions have small adaptations per customer with comment in the function body.

  1. This approach where most of the logic is in the database is correct?

  2. If you update a function in a database and we must pass it to each other how we can easily distinguish which function has changes To don’t delete or replace a change in client database by error?

Best Answer

Two queries - two replies:

a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative different environments. Some projects doesn't require really effective data processing and more valuable is simply monolithic deployment. There are a some arguments for placing business logic to database (only processes that are not INTERACTIVE!). You have to divide application to interactive (presentation) layer and non interactive layer (what is usually a best way, what you can do):

  • natural decomposition of your application between data processing and data presentation layers
  • very well accessible API in heterogeneous environment - logic in database is accessible from shell scripts, all languages, ...
  • very efficient data processing due closeness data and processing unit (in PostgreSQL it is same process). It removes a network latency, conversions between protocols, layers, drivers - PL/pgSQL runs in same process as PostgreSQL core database engine and it use same data types.
  • you can create a API based on stored procedures, so any changes on database layer are transparent for you and for your application.

Generally I can say, a power of stored procedures is more significant in heterogenous environment and less in monolititic single application environment.

b) PostgreSQL has a CREATE OR REPLACE FUNCTION statement and data visibility based on snapshots - so update in production usually is not problem. You can update database dictionary (with functions) under transaction.

Related Question