9.4 Embedded Languages Versus PHP Functions

Many people are looking for the ultimate answer whether it is better to compute certain results on the application level or to implement additional functions in the database. The discussion is as old as the problem itself.

This section is not designed to present our ultimate view of the problem, but we will try to present an objective overview of the arguments presented by both sides.

Especially when huge amounts of data have to be processed, it might be useful to add functions to the database and not to write a PHP function, but respectively a PHP class. The reason for that is simple. Imagine a function used for computing the average of a list of columns. In the case of only a few records, you won't be able to see a significant difference whether things are done by the database or by the application, but if the amount of data grows, things will change because all the data must be sent to the PHP interpreter. Especially when performing aggregations, this will lead to a significant slowdown.

An additional argument for adding functions directly to the database is that it can be accessed by all programming languages. This is a very important point because features have to be implemented once but can be used by every programming language.

One reason for implementing functions on the application level is that this way it is possible to introduce database abstraction layers for making your application more independent from the database you are using. The question remains: Is there a useful reason to use a different database than PostgreSQL? In world situations the answer could be yes, but the answer to this question has to be found.

The idea of database abstraction layers is old and the concept is used by many protocols and by a lot of software. Just think of Perl's DBI module. The DBI is an abstract layer that was originally developed for Oracle databases, but meanwhile many drivers for almost all databases are available. Thanks to the abstraction layer, writing a new driver is an easy task. The same sort of attempts are made by many PHP developers. However, one problem remains: Not all databases have the same features and the same SQL syntax, so database abstraction layers can only be used to a certain extent and applications cannot be 100% compatible because ANSI SQL-92 compatibility has not been achieved.

If functions are implemented on the application level, they can easily be used by other parts of your application that are not directly interacting with PostgreSQL. This is a significant advantage especially if basic functions are affected.

As with every question related to the design of your software, you are the one who has to decide. In most cases the answer to the question will be based on your personal philosophy and on technical reasons, but it is still you who has to decide.